Description:
he user is granted the privileges to access the database and the database name includes the special character `_`. Per https://dev.mysql.com/doc/refman/8.0/en/grant.html,
The _ and % wildcards are permitted when specifying database names in GRANT statements that grant privileges at the database level (GRANT ... ON db_name.*). This means, for example, that to use a _ character as part of a database name, specify it using the \ escape character as \_ in the GRANT statement, to prevent the user from being able to access additional databases matching the wildcard pattern (for example, GRANT ... ON `foo\_bar`.* TO ...).
The issue is that a user cannot access the database with wildcard name after executing 'set role' statement.
How to repeat:
Log on as the admin user:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> create role test_role;
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on test.* to test_role;
Query OK, 0 rows affected (0.01 sec)
mysql> create database my_test;
Query OK, 1 row affected (0.00 sec)
mysql> create user user1 identified by 'password';
Query OK, 0 rows affected (0.01 sec)
mysql> grant test_role to user1;
Query OK, 0 rows affected (0.00 sec)
mysql> grant SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `my\_test`.* to user1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql.db where User='user1'\G
*************************** 1. row ***************************
Host: %
Db: my\_test
User: user1
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: Y
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
1 row in set (0.00 sec)
Log on as 'user1':
mysql> use my_test;
Database changed
mysql> set role test_role;
Query OK, 0 rows affected (0.00 sec)
mysql> use my_test;
ERROR 1044 (42000): Access denied for user 'user1'@'%' to database 'my_test'
mysql> set role none;
Query OK, 0 rows affected (0.00 sec)
mysql> use my_test;
Database changed
Suggested fix:
I was able to fix the bug by changing the default value for "use_pattern_scan" to "false"
https://code.amazon.com/packages/OscarMysql80/blobs/0d43a0b52f36ff6b15cc22e96279b801948832...
The fix is verified by updating the existing mysql-test/t/roles_bugs_wildcard.test
--source include/not_partial_revokes.inc
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
--echo #
--echo # Bug#31013538 ALL PRIVILEGES ON A DATABASE FOR
--echo # ROLE DOESN'T ALLOW USER TO CREATE
--echo #
CREATE USER u1;
CREATE DATABASE admin_db_name;
CREATE TABLE admin_db_name.test(a int);
INSERT INTO admin_db_name.test VALUES (1), (2), (3), (4), (5);
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP ON `admin\_db\_name`.* to u1;
CREATE ROLE r1;
GRANT CREATE, DROP ON db_name.* TO r1;
GRANT r1 TO u1;
--connect(conn_u1, localhost, u1,,,,)
--echo # Grants without role activation
SHOW GRANTS;
USE admin_db_name;
SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM admin_db_name.test;
# Activate r1
SET ROLE r1;
--echo # Grants after role activation: Must show privileges from r1
SHOW GRANTS;
INSERT INTO test VALUES (11), (12);
SELECT COUNT(*) FROM test;
SELECT COUNT(*) FROM admin_db_name.test;
DROP TABLE test;
DROP DATABASE admin_db_name;
CREATE DATABASE db_name;
USE db_name;
CREATE TABLE test(a int);
DROP TABLE test;
DROP DATABASE db_name;
CREATE DATABASE db1name;
DROP DATABASE db1name;
--connection default
--disconnect conn_u1
DROP ROLE r1;
DROP USER u1;
--echo
--echo # End of 8.0 tests
--echo
# Wait till we reached the initial number of concurrent sessions
--source include/wait_until_count_sessions.inc
