| Bug #85561 | Users can be assigned non-existing roles as default | ||
|---|---|---|---|
| Submitted: | 21 Mar 2017 9:18 | Modified: | 4 Jan 2018 18:48 |
| Reporter: | Giuseppe Maxia (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Security: Roles | Severity: | S2 (Serious) |
| Version: | 8.0.1 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Mar 2017 9:18]
Giuseppe Maxia
[21 Mar 2017 9:28]
MySQL Verification Team
Hello Giuseppe, Thank you for the report and feedback! Verified as described. Thanks, Umesh
[21 Mar 2017 9:29]
MySQL Verification Team
-- 8.0.2 [umshastr@hod03]/export/umesh/server/binaries/Trunk/mysql-commercial-8.0.2: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 8.0.2-dmr-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 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. root@localhost [(none)]> DROP ROLE IF EXISTS r_test_rw; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [(none)]> DROP ROLE IF EXISTS r_test_ro; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [(none)]> CREATE ROLE r_test_ro; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> CREATE ROLE r_test_rw; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> GRANT SELECT on test.* TO r_test_ro; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> GRANT INSERT, UPDATE, DELETE on test.* TO r_test_rw; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]> DROP USER IF EXISTS u_test_ro; Query OK, 0 rows affected, 1 warning (0.00 sec) root@localhost [(none)]> DROP USER IF EXISTS u_test_rw; Query OK, 0 rows affected, 1 warning (0.01 sec) root@localhost [(none)]> CREATE USER u_test_ro IDENTIFIED BY 'msandbox'; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> CREATE USER u_test_rw IDENTIFIED BY 'msandbox'; Query OK, 0 rows affected (0.01 sec) root@localhost [(none)]> GRANT r_test_ro TO u_test_ro; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> ALTER USER u_test_ro DEFAULT ROLE r_test_ro; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> GRANT r_test_ro, r_test_rw TO u_test_rw; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> ALTER USER u_test_rw DEFAULT ROLE r_test_ro, r_test_rw; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> DROP ROLE r_test_rw; Query OK, 0 rows affected (0.00 sec) root@localhost [(none)]> use mysql Database changed root@localhost [mysql]> select * from role_edges; +-----------+-----------+---------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+---------+-----------+-------------------+ | % | r_test_ro | % | u_test_ro | N | | % | r_test_ro | % | u_test_rw | N | +-----------+-----------+---------+-----------+-------------------+ 2 rows in set (0.00 sec) root@localhost [mysql]> select * from default_roles; +------+-----------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-----------+-------------------+-------------------+ | % | u_test_ro | % | r_test_ro | | % | u_test_rw | % | r_test_ro | | % | u_test_rw | % | r_test_rw | +------+-----------+-------------------+-------------------+ 3 rows in set (0.00 sec) root@localhost [mysql]> set default role donald_duck to u_test_ro; Query OK, 0 rows affected (0.00 sec) root@localhost [mysql]> select * from mysql.default_roles; +------+-----------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-----------+-------------------+-------------------+ | % | u_test_ro | % | donald_duck | | % | u_test_rw | % | r_test_ro | | % | u_test_rw | % | r_test_rw | +------+-----------+-------------------+-------------------+ 3 rows in set (0.00 sec) root@localhost [mysql]> select count(*) from mysql.user where user = 'donald_duck'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
[2 Jun 2017 6:11]
Giuseppe Maxia
Changed category to "Roles"
[29 Aug 2017 9:10]
Kristofer Pettersson
Posted by developer: The default role is an attribute (policy) which hints of a preferred behavior when the corresponding user logins in. If the implicit relation between this attribute and the authid it represents is broken this isn't a necessarily bug but can be seen as a rather a missing feature (or it might be a feature that you can relax the order of operations). Specifically a default role isn't an object in itself.
[26 Sep 2017 7:48]
Giuseppe Maxia
With the current code, when I assign a default role that does not exist, I don't get any error or warning. If my purpose was to enable a user to start working with a given set of privileges, I will consider the lack of errors as correct assignment. The problem will be felt only when the user starts operations, and by then we will have errors that need to be addressed and operations that can't continue. We may think of "users" as humans who will realize that they lack the proper privileges and take action to activate the proper role. But more often in modern operations a "user" is just an account for an automated process in an application server or in a nighttime cron job. Here, the lack of error during the assignment will result in lost minutes or hours during operations. When I try to assign a non-existing role to a user, i get a rightful error. I would like to have the same loud error when I try to assign a non-existing default role to an user.
[4 Jan 2018 18:48]
Paul DuBois
Posted by developer: Fixed in 8.0.5, 9.0.0. It was possible to assign nonexistent roles to an account as its default roles.
