Geek Answers Handbook

Is there a way to effectively use GRANT in TRUNCATE or DROP TABLE in MySQL?

I recently tried this in MySQL 5.5.x:

GRANT
    SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON crawler.*
    TO 'my_user'@'localhost' WITH GRANT OPTION;

This will result in an error:

ERROR 1064 (42000): You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use next to the TRUNCATE ON crawler. * TO 'my_user' @ 'localhost' WITH GRANT OPTION 'on line 2

This worked before I added TRUNCATE, so after a little research, I found that this is not supported in MySQL.

The reason for this is that it is TRUNCATEclassified as a DDL operation, so it does not use DELETEinternally, it uses DROP. Ok, so I would like to limit this user to dropping tables (in case of a security violation, at least a malicious user will have to determine the names of the tables and drop them separately).

However, it turns out that I need to give this user a privilege DROPthat allows the user to delete all databases as well. Given that there is no grant for individual tables , is there any other way to do this? I suppose I could pass this on to another process by another user, but for such a small problem this is a bit cumbersome.

DELETE, ! ( ~ 55 , 1,6 , - ). , , .

+4
1

DROP MySQL, GRANT . (, fi fee, , 'fo'@'%' TRUNCATE):

  GRANT DROP ON TABLE fee.fi TO 'fo'@'%'

, :

  SHOW GRANTS FOR 'fo'@'%' ;

'fo'@'%' :

  TRUNCATE TABLE fee.fi ;

(, DROP , , MySQL).

, TRUNCATE DROP ...

, TRUNCATE fee.fi; (, , , DDL.) DEFINER , .

:

  GRANT EXECUTE ON fee.truncate_table_fee_fi TO 'fo'@'%';

'fo'@'%'

  CALL fee.truncate_table_fee_fi
+4
source

More articles:


All Articles