0

I've a Mysql DB with more than 250 tables, i need to block delete permissions for a single user on a single one of them.

we've already tried with grant but there's permission for delete for this DB, so the grant query for the single table doesn't take effect, i still can delete rows from this table. I did:

GRANT SELECT, INSERT, UPDATE ON db.table TO 'user'@'host';

the global permission: 

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON `db`.* TO 'user'@'host'

Is there a way to override permissions for a single table or we've to delete the global permissions and set permissions for the user in each table? Maybe some way to set global permission for all (*) except one so the second grant will take effect?

Also tried @kiks73 post:

REVOKE DELETE ON db.table FROM 'user'@'host';

Getting:

ERROR 1147 (42000): There is no such grant defined for user 'user' on host 'host' on table 'table'

But there's the global permission set for this user, including delete permissions for this table.

thanks

Joel Bonet Rodríguez
  • 123
  • 1
  • 14
  • Unfortunately, MySQL cannot deny access, it only knows (cumulative) grants. See e.g. [MySQL grant all privileges to database except one table](https://stackoverflow.com/q/6288554) (which is still valid after 8 years), there you can also find scripts that would allow you to generate the grants automatically. – Solarflare Apr 03 '19 at 11:49

1 Answers1

3

I think that you need to use the REVOKE statement, because you have to remove a previously granted permission:

REVOKE DELETE ON db.table FROM 'user'@'host';

UPDATE

Referring to this Super User Q&A, if there is no specific grant to revoke on that table for that user, you should try to remove the GRANT ALL ON db.* and assign a grant to all to every single user on every single table, except the specific user on the specific table, that will be granted only for insert and update.

If you have 250 tables, you could create a script combining this SQL to get the list of user created tables: 

SELECT * from information_schema.tables 
WHERE table_schema not in ('information_schema', 'mysql', 'performance_schema')

and users:

SELECT User FROM mysql.user;

to create a SQL string with the grant you need.

kiks73
  • 3,718
  • 3
  • 25
  • 52
  • Getting: ERROR 1147 (42000): There is no such grant defined for user 'user' on host 'host' on table 'table' – Joel Bonet Rodríguez Apr 03 '19 at 10:44
  • 1
    Unfortunately, MySQL does not work that way (you can only revoke given grants, there is no deny-feature in mysql that would prohibit access to something that there is a grant for). – Solarflare Apr 03 '19 at 11:46
  • 1
    just did the script 2 hours ago for that purpose, thanks for the help, keep this answer as approved for helping others in future =D – Joel Bonet Rodríguez Apr 03 '19 at 14:45