Bug #104423 SHOW CREATE DATABASE fails with a role that has global level privilege

Description:
SHOW CREATE DATABASE fails with a user whose role has the global level privilege.

The implementation as follows only checks database-level privileges and seems not to take global level privileges into account when it checks if the role has privileges to access the database which means that it has at least one or more of SELECT, INSERT, and UPDATE.  

https://github.com/mysql/mysql-server/blob/mysql-8.0.26/sql/sql_show.cc#L1266

How to repeat:
1. Create a user whose default role has a global level privilege. (The base user account doesn't have any privileges to the certain database)
2. Run SHOW CREATE DATABASE to a database.

test SQL

```
CREATE ROLE test_role;
GRANT SELECT ON *.* TO test_role;
CREATE USER test_user DEFAULT ROLE test_role;
-- Login as test_user
SHOW CREATE DATABASE mysql;
```

```
mysql> select current_role();
+-----------------+
| current_role()  |
+-----------------+
| `test_role`@`%` |
+-----------------+
1 row in set (0.00 sec)

mysql> show grants for test_role;
+----------------------------------------+
| Grants for test_role@%                 |
+----------------------------------------+
| GRANT SELECT ON *.* TO `test_role`@`%` |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE mysql;
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'mysql'
```

mtr script

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug104423.test (application/octet-stream, text), 867 bytes.

I think adding 3rd argument "true" into sctx->check_access.

https://github.com/mysql/mysql-server/blob/mysql-8.0.26/sql/sql_show.cc#L1262

Because of 3rd argument of Security_context::check_access is "match_any", SHOW CREATE DATABASE needs any kind of database privilege.

https://github.com/mysql/mysql-server/blob/mysql-8.0.26/sql/auth/sql_security_ctx.cc#L317-...

But now, sctx->check_access(DB_OP_ACLS, orig_dbname) requires *ALL OF* DB_OP_ACLS instead of *AT LEAST ONE OF* DB_OP_ACLS.

patch for MySQL 8.0.26

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug104423.patch (application/octet-stream, text), 436 bytes.

Test result.

Before

$ ./mtr --nocheck-testcases bug104423.test
Logging: ./mtr  --nocheck-testcases bug104423.test
MySQL Version 8.0.26
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/yoku0825/mysql-8.0.26/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
DROP USER IF EXISTS user_native_grant;
DROP USER IF EXISTS user_role_grant;
DROP ROLE IF EXISTS test_role;
DROP DATABASE IF EXISTS d1;
CREATE DATABASE d1;
CREATE ROLE test_role;
GRANT SELECT ON *.* TO test_role;
CREATE USER user_role_grant DEFAULT ROLE test_role;
CREATE USER user_native_grant;
GRANT SELECT ON *.* TO user_native_grant;
user_native_grant
SHOW GRANTS;
Grants for user_native_grant@%
GRANT SELECT ON *.* TO `user_native_grant`@`%`
SHOW CREATE DATABASE d1;
Database        Create Database
d1      CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
user_role_grant
SHOW GRANTS;
Grants for user_role_grant@%
GRANT SELECT ON *.* TO `user_role_grant`@`%`
GRANT `test_role`@`%` TO `user_role_grant`@`%`
SHOW CREATE DATABASE d1;
[ 50%] main.bug104423                             [ fail ]
        Test ended at 2021-07-23 14:28:18

CURRENT_TEST: main.bug104423
mysqltest: At line 26: Query 'SHOW CREATE DATABASE d1' failed.
ERROR 1044 (42000): Access denied for user 'user_role_grant'@'localhost' to database 'd1'
safe_process[31188]: Child process: 31194, exit: 1

 - the logfile can be found in '/home/yoku0825/mysql-8.0.26/mysql-test/var/log/main.bug104423/bug104423.log'

[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.000 of 9 seconds executing testcases

Completed: Failed 1/2 tests, 50.00% were successful.

Failing test(s): main.bug104423

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases

After

$ ./mtr --nocheck-testcases bug104423.test
Logging: ./mtr  --nocheck-testcases bug104423.test
MySQL Version 8.0.26
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/yoku0825/mysql-8.0.26/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
DROP USER IF EXISTS user_native_grant;
DROP USER IF EXISTS user_role_grant;
DROP ROLE IF EXISTS test_role;
DROP DATABASE IF EXISTS d1;
CREATE DATABASE d1;
CREATE ROLE test_role;
GRANT SELECT ON *.* TO test_role;
CREATE USER user_role_grant DEFAULT ROLE test_role;
CREATE USER user_native_grant;
GRANT SELECT ON *.* TO user_native_grant;
user_native_grant
SHOW GRANTS;
Grants for user_native_grant@%
GRANT SELECT ON *.* TO `user_native_grant`@`%`
SHOW CREATE DATABASE d1;
Database        Create Database
d1      CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
user_role_grant
SHOW GRANTS;
Grants for user_role_grant@%
GRANT SELECT ON *.* TO `user_role_grant`@`%`
GRANT `test_role`@`%` TO `user_role_grant`@`%`
SHOW CREATE DATABASE d1;
Database        Create Database
d1      CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
DROP USER user_native_grant;
DROP USER user_role_grant;
DROP ROLE test_role;
DROP DATABASE d1;
[ 50%] main.bug104423                             [ pass ]     49
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.049 of 10 seconds executing testcases

Completed: All 2 tests were successful.

Hello Yusuke Suzuki,

Thank you for the report and Tanaka-San for the mtr test case.

regards,
Umesh

- 8.0.26

 ./mtr bug104423
Logging: ./mtr  bug104423
MySQL Version 8.0.26
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.26/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
[ 50%] main.bug104423                            [ fail ]
        Test ended at 2021-07-27 12:13:05

CURRENT_TEST: main.bug104423
mysqltest: At line 24: Query 'SHOW CREATE DATABASE d1' failed.
ERROR 1044 (42000): Access denied for user 'user_role_grant'@'localhost' to database 'd1'
safe_process[1200]: Child process: 1201, exit: 1

Mysqltest client output from logfile
----------- MYSQLTEST OUTPUT START -----------

DROP USER IF EXISTS user_native_grant;
DROP USER IF EXISTS user_role_grant;
DROP ROLE IF EXISTS test_role;
DROP DATABASE IF EXISTS d1;
CREATE DATABASE d1;
CREATE ROLE test_role;
GRANT SELECT ON *.* TO test_role;
CREATE USER user_role_grant DEFAULT ROLE test_role;
CREATE USER user_native_grant;
GRANT SELECT ON *.* TO user_native_grant;
user_native_grant
SHOW CREATE DATABASE d1;
Database        Create Database
d1      CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
user_role_grant
SHOW CREATE DATABASE d1;

------------ MYSQLTEST OUTPUT END -----------

 - the logfile can be found in '/export/home/tmp/ushastry/mysql-8.0.26/mysql-test/var/log/main.bug104423/bug104423.log'

[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.000 of 8 seconds executing testcases

Completed: Failed 1/2 tests, 50.00% were successful.

Failing test(s): main.bug104423

The log files in var/log may give you some hint of what went wrong.

If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html

mysql-test-run: *** ERROR: there were failing test cases

Posted by developer:
 
Fixed as of the upcoming 8.0.28 release:

A user account that was granted a MySQL role with a global SELECT
privilege was denied access to the mysql database. The user account's
global privileges were not checked when the role was granted.