I have user "U" who I want to give him full access to all the existing databases except "db1". I want him to only have select privilege for that db. What I did is I gave "U" all permissions for each db one by one and select permission on "db1". What I want to do is to give "U", the create database privilege and full access to any created database by him. Is there anyway that I could do it? I don't want to add privileges every time someone creates a db.
It is impossible to do this using permissions only .
The only way is to create a stored procedure as described here
If you want to avoid stored procedures, a workaround is:
GRANT ALL PRIVILEGES ONtestuser_%
. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.
For example if user
aaa creates database
bbb_xyz, it can then be accessed exclusively by user
bbb but not by user