MariaDB: granting access to a DB before creating the DB

mariadbpermissions

I've just discovered that I can create a user and grant right to it on a given database even if that DB doesn't exist yet (it will be created in a second step).

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.08 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBNotCreatedYet.* TO 'foo2'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Do you think this procedure can cause troubles and it'd be better to create the DB and THEN granting rights to a user on it? I'd prefer not to put a constraint about what operation to do first but I don't want to run into troubles.

Best Answer

I think I have the answer: it works.

I leave it here just in case someone else run into the same doubt.

Here is an example:

D:\xampp\mysql\bin>mysql.exe -uroot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE USER foo2@localhost IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SELECT, UPDATE, DELETE ON  DBtest.* TO foo2@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select host, user, password from mysql.user;
+-----------+------+-------------------------------------------+
| host      | user | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root |                                           |
| 127.0.0.1 | root |                                           |
| ::1       | root |                                           |
| localhost |      |                                           |
| localhost | pma  |                                           |
| localhost | foo2 | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+------+-------------------------------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> CREATE DATABASE DBtest CHARACTER SET utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]>  CREATE TABLE t1 (a int, b int);
Query OK, 0 rows affected (0.23 sec)

MariaDB [DBtest]> quit;
Bye

D:\xampp\mysql\bin>mysql.exe -ufoo2 -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 7
Server version: 10.1.13-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use DBtest;
Database changed
MariaDB [DBtest]> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| t1               |
+------------------+
1 row in set (0.00 sec)

MariaDB [DBtest]> select * from t1;
Empty set (0.00 sec)

MariaDB [DBtest]> INSERT INTO t1 (a, b) VALUES (23, 23);
ERROR 1142 (42000): INSERT command denied to user 'foo2'@'localhost' for table '
t1'
MariaDB [DBtest]>

The user can work in the specified DB, with the granted rights, even if it was created (and granted its rights) before the DB was created.

Related Question