0

Currently I have a master user in RDS like this

'master'@'%'

and I've create a user like this

'new_user'@'%'

As of now I can grant that user with these privileges

 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'new_user'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION

Is it possible to grant these permission but with the limit of

DELETE PRIVILEGE? on a specific table like this tblCart

Like, I can delete on all but except for table tblCart deleting is not allowed in this table?

Pablo
  • 1,357
  • 1
  • 11
  • 40
  • Does this answer your question? [MySQL grant all privileges to database except one table](https://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table) – Simon S. Jul 03 '20 at 07:05
  • can I add it on my current privileges? Seems like this is the default privileges recommended by aws.. – Pablo Jul 03 '20 at 07:08
  • Yes, you should be able to grant permission like you did and then revoke a specific privilege on your table. – Simon S. Jul 03 '20 at 07:10

1 Answers1

2

The old answer is wrong. Since you didn't define DELETE ON *.tblCart this wont work. You will have to revoke DELETE on all and add them manually.

REVOKE DELETE ON *.* FROM `new_user`@`%`;
GRANT DELETE ON <db>.<tbl1> TO `new_user`@`%`;
...
GRANT DELETE ON <db>.<tblN> TO `new_user`@`%`;

See this post for how to generate the statement for all tables.

Old answer

After you applied your grants the way you did bove you can revoke permissions from tblCart like so:

REVOKE DELETE ON *.`tblCart` FROM `new_user`@`%`;

This will keep all permissions on all tables but remove permission do delete rows from tblCart.

Simon S.
  • 931
  • 1
  • 7
  • 21
  • 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 '`tblCart` FROM `new_user`@`%`' at line 1 just copied your cmd – Pablo Jul 03 '20 at 07:32
  • `revoke delete on .tblCart from 'new_user'@'%';` tried this cmd but gave me an error like this `ERROR 1147 (42000): There is no such grant defined ` – Pablo Jul 03 '20 at 07:39
  • Hmmm, This is awful. I have multiple tables. Something like I will revoke all the delete privilege on my current database and after that grant manually the tables with delete privileges just to apply delete privilege on tblCart – Pablo Jul 03 '20 at 07:56
  • Yeah it's not nice but it's a one time operation (hopefully). I guess you saw that the answer I linked to has a query to generate the SQL for **all** tables so you can just cop/paste it once? At least that will make it a bit more convenient. Otherwise I think creating a stored procedure or an external script is your best option. – Simon S. Jul 03 '20 at 08:47
  • Also just skip adding the `DELETE` privilege in you `GRANT` query and you won't have to `REVOKE` anything, then just run the linked query and copy the result to grant delete on all but selected tables. – Simon S. Jul 03 '20 at 08:47
  • As of now, tried this solution and it works. But if someone has the answer for revoking specific table without manually granting delete privileges per tables will appreciate too. Thanks for this man – Pablo Jul 03 '20 at 10:44
  • @RaeIan you can't grant all and then revoke one or more. That is a fundamental departure from how the privilege grant system is designed to work. – Michael - sqlbot Jul 03 '20 at 13:16