2

Before this post, I've got already posted, but i should delete that, because there was some misspelled column names, so that was my fault.

What i want to do is, to grant all privileges to a user to a database. These users will be our partners. My system designs require that, I need 1 table in every partners database, what I will use, and I want to prevent them from doing any operations, but SELECT on that table. (Please skip the WITH GRANT OPTION thing).

The name of the database and name of the user is the same, csp_ytic.

What i did:

CREATE USER 'csp_ytic'@'localhost' IDENTIFIED BY 'somepass'; 

FLUSH PRIVILEGES;

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE ON `csp_ytic`.* TO 'csp_ytic'@'localhost' WITH GRANT OPTION;

So with this, I added the user with all the privileges.

After this I revoke the privileges in my table.

REVOKE ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON `csp_ytic`.`tag_scanned` FROM 'csp_ytic'@'localhost';

I thought it's enough.

But when I run my script with this user, I can SELECT, INSERT, UPDATE, and DELETE.

What am I doing wrong?

Burpy Burp
  • 459
  • 3
  • 12
vaso123
  • 12,347
  • 4
  • 34
  • 64
  • Have you tried including `SELECT` in your `GRANT` and `REVOKE` statements? – paul Oct 03 '14 at 15:13
  • No, because i want to them to enable the SELECT on this table. This is the only table, what i want to prevent them to ALTER, UPDATE, DELETE, INSERT, etc... – vaso123 Oct 03 '14 at 15:18
  • You will need to set table-level privileges, not assign database-level privileges (`csp_ytic`.*) to get what you need. – wchiquito Oct 03 '14 at 20:26
  • Ok, but I want to give them a phpMyAdmin to maintain their database. So, is it means, if partners need a new table, I need to give them rights for every single tables everytime? This is what i do not want :( Maybe I need to redesign my structure of the project. – vaso123 Oct 06 '14 at 11:39
  • 1
    Possible duplicate of [MySQL grant all privileges to database except one table](http://stackoverflow.com/questions/6288554/mysql-grant-all-privileges-to-database-except-one-table) – Aman Aggarwal Jun 10 '16 at 04:49

0 Answers0