MySQL grant syntax & dynamic database using wildcards

MySQL grant syntax & dynamic database using wildcards

I was recently looking at a MySQL grant script to create default system users after server setup.
One of the syntax failed with error and that drew my attention to the details.

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''dbname%'.* to 'dbuser'@'%'' at line 1

The issue above was fixed by correcting the syntax. You shall read below to know more.

A sample mysql grant syntax could be:

GRANT ...PRIVILEGES ON DB.TABLE TO 'USER'@'HOST' IDENTIFIED BY 'PASSWORD';

# There are varients and ways to create user but let’s consider this for simplicity.

You may refer to the documentation for list of MySQL user privileges & the grant syntax itself.

We’re looking into dynamically specifying MySQL user privileges.

GRANT …PRIVILEGES ON DB.TABLE TO ‘USER’@’HOST’ IDENTIFIED BY ‘PASSWORD’;

A user in MySQL is combination of USERNAME and HOSTNAME.

• Username@SPECIFIC-HOST
  ‘mysql-user’@’localhost’: User named ‘mysql-user’ can connect from localhost
  ‘mysql-user’@’192.168.1.1’: User named ‘mysql-user’ can connect from 192.168.1.1

Example:

GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

This will grant someuser to connect from somehost and the privileges of SELECT & INSERT on mydb.mytbl.

• Username@DYNAMIC-HOST
  ‘mysql-user’@’192.168.%’: User named ‘mysql-user’ can connect from all IPs from the any IP of network “192.168”.
  ‘mysql-user’@’%’: User named ‘mysql-user’ can connect from any IP.

Example:

GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'%';

This will grant someuser to connect from any machine and the privileges of SELECT & INSERT on mydb.mytbl.

Let’s see what options we have while specifying MySQL Grants on database objects.

GRANT …PRIVILEGES ON DB.TABLE TO ‘USER’@’HOST’ IDENTIFIED BY ‘PASSWORD’;

• ALL DATABASES, ALL TABLES (GRANT … ON *.*): The user can connect to server and have specified privileges on all databases, tables. Normally Global privileges are given using this.
• ONE DATABASE, ALL TABLES (GRANT … ON DB.*): The user can enjoy the given privileges on only specified database “DB”
• ONE DATABASE, ONE TABLE (GRANT … ON DB.TABLE): The user can only work with one table “TABLE” of database “DB”
• DYNAMIC DATABASE, ALL TABLES: The user has the privileges assigned on all the databases that matches the pattern.

The case I met was the GRANT where the SQL was written to provide privileges on a dynamic database name and had incorrect syntax.

So yes it is fairly possible to specify a regular expression for a database name while granting privileges to a MySQL user.

So we can grant SELECT permissions on all databases starting with WP to a user as follows:

mysql> GRANT SELECT ON `WP%`.* to 'mysql-user'@'%';
Query OK, 0 rows affected (0.00 sec)

Above syntax grants SELECT command on all databases starting with “WP” (matching the regexp). Note the backtick (`) around “%”. The escape character (\) is required for the literal usage of underscore (_) or percentage (%) in the syntax. I corrected those two to resolve said error earlier.

MySQL documentation reads:

The "_" and "%" wildcards are permitted when specifying database names in GRANT statements that grant privileges at the global or database levels.

In above line, it is clear about “usage of wildcard specifying database names at database level” but couldn’t quite understand what MySQL documentation meant about the usage of wildcards at global levels!!!

mysql> grant replication slave on `%`.* to 'test'@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES