Granting and revoking access to users

You can restrict the user to access specific databases or tables and also only specific operations, such as SELECT, INSERT, and UPDATE. For granting privileges to other users, you should have the GRANT privilege.

Granting privileges

Grant the READ ONLY(SELECT) privileges to the company_read_only user:

GRANT SELECT ON company.* TO 'company_read_only'@'localhost';

The asterisk (*) represents all tables inside the database.

Grant the INSERT privilege to the new company_insert_only user:

GRANT INSERT ON company.* TO 'company_insert_only'@'localhost' IDENTIFIED BY 'xxxx';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxx'' at line 1

SHOW WARNINGS\G

*************************** 1. row ***************************
  Level: Error
   Code: 1064
Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'xxxx'' at line 1
1 row in set (0.00 sec)

Grant the WRITE privileges to the new company_write user:

GRANT INSERT, DELETE, UPDATE ON company.* TO 'company_write'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8' at line 1

Restrict to a specific table. Restrict the employees_read_only user to SELECT only from the employees table:

GRANT SELECT ON employees.employees TO 'employees_read_only'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8' at line 1

Checking grants

You can check all the user's grants. Check grants for the employee_col_ro user: