Mysql – How to change the thesql user that has all privileges on a database to only have select privileges on one specific table

databaseMySQLpermissions

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?

Best Answer

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.

Related Topic