How can I change my mysql user that has all privileges on a database to only have select privileges on one specific table?

Question

I gave my mysql user the "GRANT ALL PRIVILEGES ON database_name.* to my_user@localhost" treatment. Now I would like to be more granular, starting with lowering privileges on a specific table.

I am hoping mysql has or can be set to follow a "least amount of privileges" policy, so I can keep the current setup and lower it for the one table. But I have not seen anything like this in the docs or online.

Other than removing the DB level grant and re-granting on a table level, is there a way to get the same result by adding another rule?

Answer 1

All privileges in MySQL are additive; there is no concept of a subtractive privilege.

You must remove the GRANT ALL ON db.* ... and assign the granular table level permissions you desire.

Keep in mind that you have a very wide set of privileges available to you in MySQL. Perhaps you could start with a database level GRANT SELECT which would provide read-only access to your user. Then you can selectively add the destructive privileges on a per-table basis, as dictated by your needs.

Answer 2

Unfortunately, if a user already has database-level privileges, running revoke all on db.table from user@host; does not create all the existing table-level privileges (excepting the one you are revoking) for you.

The best way I can think of doing this is to add all the table-level privileges that you require to the user and then revoke the database-level privilege.

MySQL keeps its privileges in different tables in the mysql database depending on what context the privilege is for.

• If you grant a privilege on *.* it goes in the mysql.User table.
• If you grant a privilege on db.* it goes in the mysql.db table.
• If you grant a privilege on db.table it goes in the mysql.tables_priv table.
• If you grant column privileges on db.table it goes in the mysql.columns_priv table.

So running the final revoke below only removes the entries from the db table and leaves everything that was already in the tables_priv table.

Example: User already has this:

mysql> grant all on db.* to user@host; 

You run this:

mysql> grant all on db.table1 to user@host; mysql> grant all on db.table2 to user@host; mysql> grant all on db.table3 to user@host; mysql> grant all on db.table4 to user@host; mysql> revoke all on db.* from user@host; 

The user will be able to access db.table5 right up until you run that revoke command. The user will have access to the other four tables both before and after the revoke.

The exception to this (there's always an exception) is with column privileges. If you grant column privileges to a user that already has table-level privileges on the same table, they will exist in the columns_priv table but will not show up in a show grants command and will disappear if you run a revoke ... on db.table from user@host; command.

You should be able to script the adding of all the table-level privileges rather than creating them all manually.