Prevent MySQL user from creating/deleting databases

user_1369587 0
Viewed 80

We are working on a UI panel that will manage the creation/altering/deletion of MySQL users and databases. To prevent out-of-sync situations, we would like to disable the creation/deletion of MySQL databases for MySQL users.

Our 'global' users - that can access all databases that starts with its username - have the capability to create and drop databases currently. This is how the privileges for the global user looks like (this is not an admin/root user):

GRANT USAGE ON *.* TO `testtt`@`%`
GRANT ALL PRIVILEGES ON `testtt\_%`.* TO `testtt`@`%`

I know the creation of databases can be disabled within PHPMyAdmin, but I feel more confident to configure this in the PRIVILEGES of a specific user. I'm having a hard time finding out which PRIVILEGE we need to revoke or alter in order to prevent users from having the ability to create/delete databases.

Hopefully you can help us out!

mysql
1 Answers

You can make use of Privilege Restriction Using Partial Revokes.

The partial_revokes system variable controls whether privilege restrictions can be placed on accounts. To permit the REVOKE operation, enable partial_revokes:

SET PERSIST partial_revokes = ON;

mysql> REVOKE UPDATE ON mysql.* FROM u1;
mysql> REVOKE DELETE ON world.* FROM u1;

As of MySQL 8.0.16, that is possible if the partial_revokes system variable is enabled. Specifically, for users who have privileges at the global level.

For a better understanding of it, refer to the documentation:- partial-revokes in MYSQL DB

user_121506930
Related