Database user with global privileges does not have access to newly created database

Question

I have a database user create_db_user to which I have granted all privileges on databases with a certain prefix:

GRANT ALL PRIVILEGES ON 'myprefix\_%'.* TO 'create_db_user'@'localhost' WITH GRANT OPTION;

I have another database user account standard_user to which I grant SELECT, INSERT, UPDATE, DELETE privileges.

The global user executes a SQL statement that creates a new database: myprefix_new_db. It appears to do so without any problems (at least no exceptions are thrown at that point). When, however, it then tries to grant SIUD privileges to the standard account for that newly created database, the following exception is thrown:

SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'create_db_user'@'localhost' to database 'myprefix_new_db' @ #0

So, on the surface at least, it appears that my global user account has enough privileges to create a database, but not enough to interact with it.

The exact code flow is as follows:

$create_db_user->query(" SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO' ");
$create_db_user->execute();

$stmt = ' CREATE DATABASE IF NOT EXISTS ' . $myprefix_new_db . ' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ';
$stmt.= ' USE ' . $myprefix_new_db . ';';
$stmt.= $sql_stmt;

$create_db_user->query($stmt);
$create_db_user->execute();

// with or without grant option
$create_db_user->query(" GRANT ALL PRIVILEGES ON  {$myprefix_new_db}.* TO 'create_db_user'@'localhost' WITH GRANT OPTION; ");
$create_db_user->execute();

$sql = " GRANT SELECT, INSERT, UPDATE, DELETE ON `{$myprefix_new_db}`.* TO 'standard_user'@'localhost' ";
$create_db_user->query($sql);
$create_db_user->execute();

My question is: what must I do to ensure that my create_db_user account has sufficient privileges to do whatever it wants to dynamically created databases? Or, what must I do to ensure that once a database is created, that it is included under the scope of the global user?

EDIT: I have admin-level cPanel and WHM access.

Answer 1

Every database has different and separate permission rules. When a new database created only some accounts are add by default. Each time a new database created old permissions should be extracted,filtered, (possible edited) and copied into new database.

If new and old database has different in these information_schema tables;

You can copy these and paste them into the new database via a account with schema privileges.(however it may require copying/filtering/editing)

• SCHEMA_PRIVILEGES
• USER_PRIVILEGES

Access permissions will be different. Can you verify these tables have required rows in them ?

If massive amount of databases created dynamically, in order to avoid copying/filtering/editing. Create a base default database and use import/export scripts to clone it with your variables.

Just to highlight: Granting permission on data and granting permission on users requires different level of access which u are aware of.

An other caution please: Try to create a new connection and run GRANT on that connection which is connected to the new DB. If you are using some preset platform creating a DB and GRANTING from outside (even for a global user) might be blocked and these statements can get ignored silently.

Answer 2

From what I understood, you are trying to get create_db_user to grant himself all privileges along with the with grant option on a database. I am not sure it should be allowed.

Could you grant the privileges to create_db_user from another user who has the proper WITH GRANT privileges, through a trigger for instance ?

Good luck :-)