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

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}

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