Creating a user with privileges using wildcard in database name doesn't work

November 5, 2019 138 views
DigitalOcean Managed MySQL Database

I’ve tried a support ticket on this, but it just went back and forth with no resolution.

My issue here is that the doadmin user does not have the ability to grant permissions to a user to multiple databases using a wildcard in the db name:

mysql> GRANT ALL PRIVILEGES ON `freehand\_%`.* to 'freehand'@'%';
ERROR 1044 (42000): Access denied for user 'doadmin'@'%' to database 'freehand\_%'

To prove this SHOULD work, I created a local database to demonstrate. This is an ephemeral environment that was created strictly for testing this.

First, I created a test user - this would be the equivalent of the doadmin user:

mysql> create user 'test'@'%' identified by 'password';
Query OK, 0 rows affected (0.01 sec)

I then granted all privileges to the test user, and flushed privileges:

mysql> grant all on *.* to 'test'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

I then logged in to mysql as the test user, and created a freehand user:

mysql> create user 'freehand'@'%' identified by 'freehand';
Query OK, 0 rows affected (0.00 sec)

I then went to grant all permissions on any database starting with ‘freehand_’ - this is what I’m attempting to do as the doadmin user and getting an error - however, as you can see, this works in my test environment:


mysql> grant all on `freehand\_%`.* to 'freehand'@'%';
Query OK, 0 rows affected (0.00 sec)

For comparison purposes - here’s what show grants looks like for my test user:

mysql> show grants for 'test';
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Grants for test@% |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `test`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `test`@`%` WITH GRANT OPTION |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)

And here’s what it looks like for the doadmin user in my managed DB cluster:

mysql> SHOW GRANTS FOR doadmin;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Grants for doadmin@% |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| GRANT CREATE, DROP, PROCESS, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT, CREATE USER, CREATE ROLE, DROP ROLE ON *.* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ROLE_ADMIN ON *.* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "defaultdb".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "mysql".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "sys".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON "freehand_test".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "metrics_user_telegraf".* TO "doadmin"@"%" WITH GRANT OPTION |
| GRANT SELECT ON "performance_schema".* TO "doadmin"@"%" WITH GRANT OPTION |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------```

Does anyone have any other ideas on how to accomplish this that works with the managed DB product?
1 Answer

This has been resolved by DO support, they had to push an update to my cluster.

Have another answer? Share your knowledge.