| Bug #104732 | In the case of using the role, there is a problem with the acl check of the view | ||
|---|---|---|---|
| Submitted: | 26 Aug 2021 7:20 | Modified: | 26 Aug 2021 10:43 |
| Reporter: | deng yl (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S3 (Non-critical) |
| Version: | 8.0.25, 8.0.26, 8.0.28 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[26 Aug 2021 7:20]
deng yl
[26 Aug 2021 10:43]
MySQL Verification Team
Hello deng yl, Thank you for the report and test case. regards, Umesh
[26 Aug 2021 10:44]
MySQL Verification Team
-- bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.26 MySQL Community Server - GPL Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET global activate_all_roles_on_login=ON; Query OK, 0 rows affected (0.00 sec) mysql> create user 'user1'@'localhost' IDENTIFIED with mysql_native_password; Query OK, 0 rows affected (0.01 sec) mysql> create role user; Query OK, 0 rows affected (0.00 sec) mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE, ROLE_ADMIN ON *.* TO user; Query OK, 0 rows affected (0.00 sec) mysql> grant user to 'user1'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'user1'@'localhost'; +-------------------------------------------+ | Grants for user1@localhost | +-------------------------------------------+ | GRANT USAGE ON *.* TO `user1`@`localhost` | | GRANT `user`@`%` TO `user1`@`localhost` | +-------------------------------------------+ 2 rows in set (0.00 sec) mysql> show grants for 'user'@'localhost'; ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'localhost' mysql> show grants for 'user'@'%'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user`@`%` | | GRANT ROLE_ADMIN ON *.* TO `user`@`%` | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) -- bin/mysql -uuser1 -S /tmp/mysql_ushastry.sock --local-infile Welcome to the MySQL monitor. Commands end with ; or \g. mysql> create database db; Query OK, 1 row affected (0.01 sec) mysql> use db; Database changed mysql> create table tt(id int unsigned auto_increment primary key, aa varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql> create view t11 as select tt.id as id, tt.aa as aa from tt; Query OK, 0 rows affected (0.01 sec) mysql> select * from t11; ERROR 1356 (HY000): View 'db.t11' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> mysql> show warnings; +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | Error | 1356 | View 'db.t11' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show errors; +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ | Error | 1356 | View 'db.t11' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them | +-------+------+--------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show grants; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for user1@localhost | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `user1`@`localhost` | | GRANT ROLE_ADMIN ON *.* TO `user1`@`localhost` | | GRANT `user`@`%` TO `user1`@`localhost` | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
[8 Oct 2021 6:28]
Bharathy Satish
Posted by developer: Problem: Privileges associated with a role activated via activate_all_roles_on_login is not checked when a view is accessed. Analysis: A role can be activated via global variable activate_all_roles_on_login or mandatory_roles. During authentication, all active roles are set in thd's security context. When accessing a view, server checks for the required privileges and it only looks for roles assigned with DEFAULT ROLE via CREATE USER. Thus causing an access denied error. This is however an expected behaviour. Only default roles privileges are checked when accessing stored programs or views. However this behaviour contradicts with the documentation. In this page: https://dev.mysql.com/doc/refman/8.0/en/roles.html#roles-activating Stored program and view objects that execute in definer context execute with the default roles of the user named in their DEFINER attribute. If activate_all_roles_on_login is enabled, such objects execute with all roles granted to the DEFINER user, including mandatory roles. The above paragraph the second sentence should be replaced with: Roles activated via activate_all_roles_on_login or including mandatory roles privileges should not be considered for stored program and view objects. Will be closing this as a documentation bug.
[25 Feb 2022 9:53]
MySQL Verification Team
Bug #106564 marked as duplicate of this one
