mariadb, PHP7 -- cannot grant all DB privs

Question

Within my PHP code, I've tried various iterations of this directive:

        $dbc->exec("CREATE DATABASE $dbName;
                    CREATE USER '$user'@'%' IDENTIFIED BY '$pass';
                    GRANT ALL ON $dbName.* to '$user'@'%' IDENTIFIED BY '$pass';
                    FLUSH PRIVILEGES;")

And, by iterations, I mean tweaking the db privilege values:

`$dbName`.*
$dbName.*
$dbName
'$dbName'.*
and so on...

For all attempts, I end-up with a user created with the GRANT USAGE statement and non-bound (associated) with the targeted database ($dbName).

The one and only time I've gotten this to work successfully is by changing the privilege levels to *.* which grants everything for all databases for this user which I am trying to avoid; I'd like to limit the grant to the named database only. Instead, when I exec a GRANT ALL, or GRANT ALL PRIVILEGES, of ending up with this result every time:

MariaDB [(none)]> show grants for someAdmin@localhost;
+-------------------------------------------------------------------------------+
| Grants for someAdmin@localhost       
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'someAdmin'@'localhost' IDENTIFIED BY PASSWORD '[snip]' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)

I've also tried restarting mariadb after issuing the commands just to see if the restart would force the correct privs but nope.

What's the correct SQL to grant all privileges on the database (with grant option) to the new user s.t. the user is actually granted all privileges on the named db?

(PHP 7.2, MariaDB 10.1, Ubuntu 18.04)

From the MariaDB Doc:

Global privileges are granted using *.* for priv_level. Global privileges include privileges to administer the database and manage user accounts, as well as privileges for all tables, functions, and procedures. Global privileges are stored in the mysql.user table.

Database privileges are granted using db_name.* for priv_level, or using just * to use default database. Database privileges include privileges to create tables and functions, as well as privileges for all tables, functions, and procedures in the database. Database privileges are stored in the mysql.db table.

I'd suggest separating out each line into their own query, instead of passing it all as one. It may not be running all lines. — aynber
Executing the queries individually produces the same results. — Micheal Shallop
Mind the SQL injections? Also consider not making a MySQL user from application code as it is not safe.. — Raymond Nijland
SQL injections comment was pretty relevant as your code looks to be prone to that.. Which isn't a Opinion.. — Raymond Nijland

Rick James Rick James · Accepted Answer · 2019-08-22T22:50:27

'$user'@'%'
'someAdmin'@'localhost'

'%' does not include 'localhost', but rather everything else.

The normal pattern is

CREATE USER            'user'@'...' IDENTIFIED BY '...';
GRANT ALL ON 'db'.* TO 'user'@'...';
                       ^^^^^^^^^^^^
                       These must match exactly.

(In the old days, GRANT would CREATE the user; in the future (MySQL 8.0) it won't. MariaDB will probably go that direction eventually.)

As for the ON 'db'.*, the dbname is optionally quoted. (If not quoted, then beware of certain punctuation marks.) The * is a wild card meaning "all of that db".

I think this context allows double quotes, single quotes, or backtics for quoting.

mariadb, PHP7 -- cannot grant all DB privs

3 Answers