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
SELECTcommand 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.
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.