set a lower privilege permission on a specific database when a GRANT ALL exists on a pattern matching this database on MySQL

Question

I have a user which has access (GRANT ALL) to a set of databases matching a wildcard pattern, now I would like to remove one specific database matching this wildcard from it's access.

mysql> select * from mysql.db where host='server' AND user='user' and Db LIKE 'db_%'\G
*************************** 1. row ***************************
                 Host: server
                   Db: db_%
                 User: db
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)

I've tried to revoke access on this specific database but that does not work because that does not match a existing rule I assume

mysql> REVOKE ALL on db_6.* FROM 'user'@'server';                               
ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'server'

eventually I granted the lowest permission (USAGE) for this specific database.

mysql> show grants for 'user'@'server';
+--------------------------------------------------------+
| Grants for user@server                                 |
+--------------------------------------------------------+
| GRANT ALL PRIVILEGES ON `db_%`.* TO 'user'@'server'    |
+--------------------------------------------------------+
mysql> GRANT USAGE on db_6.* to 'user'@'server';
mysql> show grants for 'user'@'server';
+--------------------------------------------------------+
| Grants for user@server                                 |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'server'                  |
| GRANT ALL PRIVILEGES ON `db_%`.* TO 'user'@'server'    |
+--------------------------------------------------------+

is there a way to achieve that ?

Thanks

Grants system in MySQL uses the rules literally (i.e. you may revoke only those privilege which was granted previously literally). And none permission set on some level can be revoked/blocked on lower level. If you grant ALL to db_% and then want to restrict privileges to definite database which matches this pattern then you must revoke ALL to this pattern, grant ALL to each separate database (and/or use less wide pattern groups which does not match the database to be restricted) and USAGE to the database in interest. — Akina, Sep 15 '21 at 13:35

score 1 · Answer 1 · answered Sep 15 '21 at 19:49

Here is the basic problem

There is no such thing as GRANT USAGE at the database level.

Logically, GRANT USAGE indicates the ability to login (authenticate) and nothing more. This is implemented in mysql.user holding login information and all global privileges set to 'N'. Please see my old post from April 12, 2012 Cannot GRANT privileges as root for how mysql.user appears in all versions of MySQL past and present. The same would apply to MariaDB.

PROPOSED SOLUTION

You would have to delete the row from mysql.db table.

DELETE FROM mysql.db WHERE host='server' AND user='db' AND db='db_%';

Then issue a separate grant for all databases that have db_ but exclude db_6

The following bash script will do that for you

MYSQL_USER=root
MYSQL_PASS=whateverpassword
MYSQL_AUTH="-hlocalhost -u${MYSQL_USER} -p${MYSQL_PASS}"
GRANTS_SQL=reshape_grants.sql
echo "DELETE FROM mysql.db WHERE host='server' AND user='db' AND db='db_%';" > ${GRANTS_SQL}
echo "FLUSH PRIVILEGES;" >> ${GRANTS_SQL}
SQL="SELECT schema_name FROM INFORMATION_SCHEMA.SCHEMATA"
SQL="${SQL} WHERE schema_name LIKE 'db_%' AND schema_name <> 'db_6'"
DBLIST=mysql ${MYSQL_AUTH} -ANe&quot;${SQL}&quot;
for DB in ${DBLIST}
do
    echo "GRANT ALL ON ${DB}.* TO 'user'@'server';" >> ${GRANT_SQL}
done
echo "FLUSH PRIVILEGES;" >> ${GRANTS_SQL}

This will create the grants file to do the following

Remove all database-level grants for databases starting with db_
Grant 'user'@'server' full access to all db_ databases other than db_6

Look at the contents to make sure

cat ${GRANTS_SQL}

This is something I thought about, but the number of databases evolves and the application does not manage, as the time of writing, the part to set PERMISSION on the applicative user when a new database is created. Perhaps this is the only real and good solution. — Baptiste Mille-Mathias, Sep 16 '21 at 08:59

set a lower privilege permission on a specific database when a GRANT ALL exists on a pattern matching this database on MySQL

1 Answers1

PROPOSED SOLUTION