To Nha Notes | Sept. 21, 2023, 10:48 a.m.
You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, INDEX, CREATE, ALTER, DROP, GRANT OPTION or ALL.
Let's look at some examples of how to grant privileges on tables in MySQL.
For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called contacts to a user name smithj, you would run the following GRANT statement:
GRANT SELECT, INSERT, UPDATE, DELETE ON contacts TO 'smithj'@'localhost';
You can also use the ALL keyword to indicate that you wish to grant all permissions except GRANT OPTION to a user named smithj. For example:
GRANT ALL ON contacts TO 'smithj'@'localhost';
If you wanted to grant only SELECT access on the contacts table to all users, you could grant the privileges to *. For example:
GRANT SELECT ON contacts TO '*'@'localhost';
Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL.
Let's look at some examples of how to revoke privileges on tables in MySQL.
For example, if you wanted to revoke DELETE and UPDATE privileges on a table called contacts from a user named smithj, you would run the following REVOKE statement:
REVOKE DELETE, UPDATE ON contacts FROM 'smithj'@'localhost';
If you wanted to revoke all permissions (except GRANT OPTION) on a table for a user named smithj, you could use the ALL keyword as follows:
REVOKE ALL ON contacts FROM 'smithj'@'localhost';
If you had granted SELECT privileges to * (ie: all users) on the contacts table and you wanted to revoke these privileges, you could run the following REVOKE statement:
REVOKE SELECT ON contacts FROM '*'@'localhost';