MDEV-22312: Bad error message for SET DEFAULT ROLE when user account is not granted the role

- `SET DEFAULT ROLE xxx [FOR yyy]` should say:
  "User yyy has not been granted a role xxx" if:
    - The current user (not the user `yyy` in the FOR clause) can see the
    role xxx. It can see the role if:
      * role exists in `mysql.roles_mappings` (traverse the graph),
      * If the current user has read access on `mysql.user` table - in
    that case, it can see all roles, granted or not.
    - Otherwise it should be "Invalid role specification".

In other words, it should not be possible to use `SET DEFAULT ROLE` to discover whether a specific role exist or not.

4 files changed, 197 insertions, 3 deletions

diff --git a/mysql-test/suite/roles/set_default_role_for.result b/mysql-test/suite/roles/set_default_role_for.result
index 7289319a428..fcea28e882a 100644
--- a/mysql-test/suite/roles/set_default_role_for.result
+++ b/mysql-test/suite/roles/set_default_role_for.result
@@ -14,7 +14,7 @@ set default role role_a for user_a@localhost;
 set default role invalid_role for user_a@localhost;
 ERROR OP000: Invalid role specification `invalid_role`.
 set default role role_b for user_a@localhost;
-ERROR OP000: Invalid role specification `role_b`.
+ERROR OP000: User `user_a@localhost` has not been granted role `role_b`
 set default role role_b for user_b@localhost;
 show grants;
 Grants for user_a@localhost
@@ -36,7 +36,7 @@ user	host	default_role
 user_a	localhost	role_a
 user_b	localhost	role_b
 set default role role_b for current_user;
-ERROR OP000: Invalid role specification `role_b`.
+ERROR OP000: User `user_a@localhost` has not been granted role `role_b`
 show grants;
 Grants for user_b@localhost
 GRANT role_b TO 'user_b'@'localhost'
diff --git a/mysql-test/suite/roles/set_default_role_invalid.result b/mysql-test/suite/roles/set_default_role_invalid.result
index 3916bacfd4c..b6036e8de99 100644
--- a/mysql-test/suite/roles/set_default_role_invalid.result
+++ b/mysql-test/suite/roles/set_default_role_invalid.result
@@ -38,3 +38,90 @@ ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'us
 drop role test_role;
 drop role not_granted_role;
 drop user test_user@localhost;
+#
+# MDEV-22312: Bad error message for SET DEFAULT ROLE when user account
+#             is not granted the role
+#
+CREATE USER a;
+CREATE USER b;
+CREATE ROLE r1;
+CREATE ROLE r2;
+SET DEFAULT ROLE r1 FOR a;
+ERROR OP000: User `a@%` has not been granted role `r1`
+GRANT r1 TO b;
+GRANT r2 TO b;
+SET DEFAULT ROLE r1 FOR b;
+# Change user b
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+r1
+SET ROLE r2;
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+r2
+SET DEFAULT ROLE r1 FOR a;
+ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
+SET DEFAULT ROLE r2;
+# Change user root (session 1: select_priv to b)
+GRANT SELECT ON mysql.* TO b;
+# Change user b (session 1: select_priv)
+SHOW GRANTS FOR b;
+Grants for b@%
+GRANT r1 TO 'b'@'%'
+GRANT r2 TO 'b'@'%'
+GRANT USAGE ON *.* TO 'b'@'%'
+GRANT SELECT ON `mysql`.* TO 'b'@'%'
+SET DEFAULT ROLE r1 FOR a;
+ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+r2
+SET DEFAULT ROLE NONE;
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+r2
+SET DEFAULT ROLE current_role FOR current_user;
+SET DEFAULT ROLE invalid_role;
+ERROR OP000: Invalid role specification `invalid_role`.
+SET DEFAULT ROLE invalid_role FOR a;
+ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
+SET DEFAULT ROLE none FOR a;
+ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
+# Change user root (session 2: adding update_priv to user b)
+GRANT UPDATE ON mysql.* TO b;
+# Change user b
+SHOW GRANTS FOR b;
+Grants for b@%
+GRANT r1 TO 'b'@'%'
+GRANT r2 TO 'b'@'%'
+GRANT USAGE ON *.* TO 'b'@'%'
+GRANT SELECT, UPDATE ON `mysql`.* TO 'b'@'%'
+SET DEFAULT ROLE r1 FOR a;
+ERROR OP000: User `a@%` has not been granted role `r1`
+SET DEFAULT ROLE invalid_role;
+ERROR OP000: Invalid role specification `invalid_role`.
+SET DEFAULT ROLE invalid_role FOR a;
+ERROR OP000: Invalid role specification `invalid_role`.
+SET DEFAULT ROLE none FOR a;
+# Change user root (session 3: Grant role to user a)
+GRANT r1 TO a;
+SET DEFAULT ROLE r1 FOR a;
+# Change user a (verify session 3)
+SELECT CURRENT_ROLE;
+CURRENT_ROLE
+r1
+SET DEFAULT ROLE None;
+# Change user b (session 3: role granted to user a)
+SET DEFAULT ROLE r1 FOR a;
+SET DEFAULT ROLE r2 FOR a;
+ERROR OP000: User `a@%` has not been granted role `r2`
+SET DEFAULT ROLE invalid_role;
+ERROR OP000: Invalid role specification `invalid_role`.
+SET DEFAULT ROLE invalid_role FOR a;
+ERROR OP000: Invalid role specification `invalid_role`.
+SELECT user, host, default_role FROM mysql.user where user='a' or user='b';
+user	host	default_role
+a	%	r1
+b	%	r2
+DROP ROLE r1, r2;
+DROP USER a, b;
diff --git a/mysql-test/suite/roles/set_default_role_invalid.test b/mysql-test/suite/roles/set_default_role_invalid.test
index 8e72e316d4b..02fca1107e2 100644
--- a/mysql-test/suite/roles/set_default_role_invalid.test
+++ b/mysql-test/suite/roles/set_default_role_invalid.test
@@ -60,3 +60,110 @@ change_user 'root';
 drop role test_role;
 drop role not_granted_role;
 drop user test_user@localhost;
+
+--echo #
+--echo # MDEV-22312: Bad error message for SET DEFAULT ROLE when user account
+--echo #             is not granted the role
+--echo #
+
+CREATE USER a;
+CREATE USER b;
+CREATE ROLE r1;
+CREATE ROLE r2;
+# Role has not been granted to user a, but the role is visible to current_user
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE r1 FOR a;
+# Granting roles to user b
+GRANT r1 TO b;
+GRANT r2 TO b;
+# After granting the role, role can be set as default
+SET DEFAULT ROLE r1 FOR b;
+
+--echo # Change user b
+change_user b;
+SELECT CURRENT_ROLE;
+SET ROLE r2;
+SELECT CURRENT_ROLE;
+# User b has no UPDATE_PRIV for mysql.user
+--error ER_DBACCESS_DENIED_ERROR
+SET DEFAULT ROLE r1 FOR a;
+SET DEFAULT ROLE r2;
+
+--echo # Change user root (session 1: select_priv to b)
+change_user root;
+# Let's grant select_priv to user b
+GRANT SELECT ON mysql.* TO b;
+
+--echo # Change user b (session 1: select_priv)
+change_user b;
+SHOW GRANTS FOR b;
+# User must have update_priv before setting the role
+--error ER_DBACCESS_DENIED_ERROR
+SET DEFAULT ROLE r1 FOR a;
+# Testing the `CURRENT_ROLE` as a special case
+SELECT CURRENT_ROLE;
+SET DEFAULT ROLE NONE;
+SELECT CURRENT_ROLE;
+SET DEFAULT ROLE current_role FOR current_user;
+# Testing of non-existing role
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE invalid_role;
+# Testing of non-existing role for different user
+--error ER_DBACCESS_DENIED_ERROR
+SET DEFAULT ROLE invalid_role FOR a;
+# Testing the `None` role for different user
+-- error ER_DBACCESS_DENIED_ERROR
+SET DEFAULT ROLE none FOR a;
+
+--echo # Change user root (session 2: adding update_priv to user b)
+change_user root;
+# update_priv are enough
+GRANT UPDATE ON mysql.* TO b;
+
+--echo # Change user b
+change_user b;
+SHOW GRANTS FOR b;
+# In all tests in session user a has not been granted the role
+# Testing setting role for different user, should fail with new error
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE r1 FOR a;
+# Testing of non-existing role
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE invalid_role;
+# Testing of non-existing role for different user with update_priv
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE invalid_role FOR a;
+# Testing the `None` role for different user with update_priv
+SET DEFAULT ROLE none FOR a;
+
+--echo # Change user root (session 3: Grant role to user a)
+change_user root;
+# After granting the privilege for a, user b can set default role
+GRANT r1 TO a;
+SET DEFAULT ROLE r1 FOR a;
+
+--echo # Change user a (verify session 3)
+change_user a;
+SELECT CURRENT_ROLE;
+SET DEFAULT ROLE None;
+
+--echo # Change user b (session 3: role granted to user a)
+change_user b;
+# This should set role because b has update_priv
+SET DEFAULT ROLE r1 FOR a;
+# Testing non-granted role r2 still should fail
+-- error ER_INVALID_ROLE
+SET DEFAULT ROLE r2 FOR a;
+# Testing of non-existing role
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE invalid_role;
+# Testing of non-existing role for different user
+--error ER_INVALID_ROLE
+SET DEFAULT ROLE invalid_role FOR a;
+
+# Clear the workspace
+change_user root;
+--sorted_result
+SELECT user, host, default_role FROM mysql.user where user='a' or user='b';
+DROP ROLE r1, r2;
+DROP USER a, b;
diff --git a/mysql-test/suite/roles/set_role-recursive.result b/mysql-test/suite/roles/set_role-recursive.result
index 9e62558fc14..b0d79377183 100644
--- a/mysql-test/suite/roles/set_role-recursive.result
+++ b/mysql-test/suite/roles/set_role-recursive.result
@@ -66,7 +66,7 @@ Grants for test_user@localhost
 GRANT USAGE ON *.* TO 'test_user'@'localhost'
 GRANT test_role1 TO 'test_user'@'localhost'
 set role test_role2;
-ERROR OP000: Invalid role specification `test_role2`.
+ERROR OP000: User `test_user@localhost` has not been granted role `test_role2`
 select current_user(), current_role();
 current_user()	current_role()
 test_user@localhost	NULL