You can grant specific privileges to specific SQL user here is the list of possible permissions that can be granted.

  • ALL PRIVILEGES– as we saw previously, this would allow a MySQL user full access to a designated database (or if no database is selected, global access across the system)
  • CREATE– allows them to create new tables or databases
  • DROP– allows them to them to delete tables or databases
  • DELETE– allows them to delete rows from tables
  • INSERT– allows them to insert rows into tables
  • SELECT– allows them to use the SELECT command to read through databases
  • UPDATE– allow them to update table rows
  • GRANT OPTION– allows them to grant or remove other users’ privileges

Lets proceed with granting specific permission to a user first you will have to login to MySQL

mysql -u root -p

For granting permission.

GRANT specify_permission_here ON your_database_name.table_name TO ‘your_database_username’@'localhost’;

If you want access to specific table or DB you have to use * sign in the place of DB or table
After that please make sure you run this command.

Flush Privileges;

If you want to revoke permissions again you have to follow same structure but will need to replace GRANT with REVOKE like this for example.

REVOKE specify_permission_here ON your_database_name.table_name FROM ‘your_database_username’@‘localhost’;

If you want to check what permissions specific user has you can check by using this command.

SHOW GRANTS your_database_username;

Furthermore in case you want to delete a user you can that by using this command.

DROP USER ‘your_database_username’@‘localhost’; 

In order to exit MySQL you have to do this.


Good Luck!

(Visited 31 times, 1 visits today)