MySQL grant privileges access denial

Question

I have an Yii2 project, which operates some set of databases. It has one basic database connection where I keep users authentification data, and the second connection points to database automatically created for each user.

I have to create databases programmatically; so, I have a user project that has a global grant privilege and a wildcard privileges on project\_%.*. That's what I do:

    $queries = [
        "CREATE DATABASE ".$dbname,
        "FLUSH PRIVILEGES",
        "CREATE USER '{$dbuser}'@'localhost' IDENTIFIED BY '{$dbpass}'",
        "GRANT ALL PRIVILEGES ON {$dbname}.* TO '{$dbuser}'@'localhost'"
    ];

    foreach($queries as $q) $application->db->createCommand($q)->execute();

$dbname is 'project_'.randomString(8).

I double checked all the project privileges, checked mysql.user and information_scheme too, I have grantable privileges and grant option, but still get an access error:

SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'project'@'localhost' to database 'project_sck6jdyb' The SQL being executed was: GRANT ALL PRIVILEGES ON project_sck6jdyb.* TO 'pu_sck6jDyB'@'localhost'.

MySQL version is 5.6.21, running under XAMPP, Windows. Is that a bug, or am I doing something wrong?

Update: SHOW GRANTS for project@localhost;

GRANT INSERT, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE USER ON *.* TO 'project'@'localhost' IDENTIFIED BY PASSWORD '*[secret]' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON project.* TO 'project'@'localhost'

GRANT ALL PRIVILEGES ON project\_%.* TO 'project'@'localhost' WITH GRANT OPTION

Can you please [add](http://stackoverflow.com/posts/34202475/edit) the result of [`SHOW GRANTS for project`](http://dev.mysql.com/doc/refman/5.7/en/show-grants.html) to your question? — VolkerK, Dec 10 '15 at 13:11

score 0 · Answer 1 · answered Dec 10 '15 at 16:43

0

To grant privileges, you must have the GRANT_OPTION privilege, and you must also have the privilege that you want to grant. From your post, you have the following privileges: INSERT, CREATE, DROP, RELOAD, INDEX, ALTER, SUPER, CREATE USER.

Try granting only the privileges that you have. I expect that this would work.

answered Dec 10 '15 at 16:43

Richard St-Cyr

970
1
8
14

well, as you can see, `project` has all privileges on a wildcard name with grant option. I'm trying to grant these privileges to another user on the database which matches my wildcard mask. I could try it with all global privileges with grant option, but I don't think this is safe and neat. – Nastya Kizza Dec 10 '15 at 17:00
I see the same problem as you. I've been able to get around it with `GRANT ALL PRIVILEGES ON project_sck6jdyb.* TO 'project'@'localhost'`. I've also been able to make it work with the following: `GRANT ALL PRIVILEGES ON project%`.* TO 'project'@'localhost'` (removed the _ in the DB name). Unless you can remove the "_", that doesn't really help you, but it leads me to believe that the problem is with the use of the wildcard in the DB name. I don't have time to investigate more right now. – Richard St-Cyr Dec 10 '15 at 19:01
thank you for your time, every bit of extra research helps alot. I'll play around wildcards and update this page with the results. – Nastya Kizza Dec 10 '15 at 20:41
While you're at it you should get rid of the `IDENTIFIED BY PASSWORD '*[secret]'` of the first GRANT. That should be part of the `CREATE USER` statement. Having it in the GRANT creates a warning with MySQL 5.7. I don't think that's the root of your immediate problem though. – VolkerK Dec 11 '15 at 01:00

MySQL grant privileges access denial

1 Answers1