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 the result of SHOW GRANTS for project to your question? — VolkerK

Richard St-Cyr Richard St-Cyr · Accepted Answer · 2015-12-10T16:43:22

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.

MySQL grant privileges access denial

1 Answers