how do I grant a new user the privilege to create a new database in MySQL

Question

how do I grant a new user the privilege to create a new database in MySQL

Specifically:

1. the database does not exist yet
2. I have successfuly created a new DB user account (that is not admin)
3. I want that non-admin user to create a new database
4. I do NOT want the 'admin' user to create the database and then grant privs to the database to the new user
5. as 'admin', I want to grant the new user the privilege to create a new database
6. I do not want to grant the new user any additional privileges on existing databases

This is not covered anywhere in the documentation that I can find.

---

Monday 2022-04-04 Update:

I created user 'scott' and then logged in as MySQL user 'admin' When I run this command

Note: The 'test' database does not yet exist

mysql>GRANT CREATE ON test.* to 'scott'@'localhost';

I get an error ==> ERROR 1410 (42000): You are not allowed to create a user with GRANT

Why do I get this error? I am not attempting to create a user, but rather grant a user access to a non-existent database (as is the approach with MySQL to grant a user privileges to create a database).

---

If up update the SQL statement to: mysql>GRANT CREATE ON test.* to scott;

It runs OK Query OK, 0 rows affected (0.07 sec)

---

And so now I login as user 'scott and run this statement:

mysql>create database rum;

==> ERROR 1049 (42000): Unknown database 'test'

Why do I get this error?

At this point, I am still not able to create a database as a non-admin user.

Answer 1

Example: grant "scott" the privilege to create the test3 database, which does not exist yet:

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

mysql> grant create on test3.* to 'scott'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Now try as scott to create the database:

mysql> select user();
+-----------------+
| user()          |
+-----------------+
| scott@localhost |
+-----------------+

mysql> show grants;
+---------------------------------------------------------+
| Grants for scott@localhost                              |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `scott`@`localhost`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `scott`@`localhost` |
| GRANT CREATE ON `test3`.* TO `scott`@`localhost`        |
+---------------------------------------------------------+

mysql> create database test3;
Query OK, 1 row affected (0.00 sec)

mysql> use test3;
Database changed

MySQL has one privilege called CREATE which is for creating both databases and tables. See https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_create

You can either grant the user privilege to create a database of a specific name, or else grant them the privilege to create a database of any name, but that means they can also create other tables, either in the new database or in other existing databases. Sorry, there may not be a solution for you to allow them to create any new database without specifying the name when you grant the privilege, but then only have privilege in that database.

---

You are not allowed to create a user with GRANT

You did not create the user scott. Older versions of MySQL allows GRANT to implicitly create a user if one does not exist, but that has been disabled on more recent versions because folks realized it is a security weakness.

To be clear, the user "scott" is just an example I used. Don't literally use the name "scott" if that's not the user to whom you want to grant privileges.

The other errors you got seem to be that you granted the user privileges on a database named test.* but then you tried to create a database with a different name. The example I showed only grants the privilege to create the specific named database, not a database named rum or any other database.

I understand you want to grant privilege to create a database of any name. The syntax for that would be GRANT CREATE ON *.* TO... but that would grant the user privileges on all the other existing databases too.

There is no combination of syntax to grant privileges on any database name wildcard that means any database, provided that it is not yet created.