Debian Codegraph @

mysql-test/suite/roles/set_default_role_invalid.result

raw
1 create user test_user@localhost;
2 create role test_role;
3 create role not_granted_role;
4 grant select on *.* to test_role;
5 grant test_role to test_user@localhost;
6 show grants;
7 Grants for test_user@localhost
8 GRANT `test_role` TO `test_user`@`localhost`
9 GRANT USAGE ON *.* TO `test_user`@`localhost`
10 select user, host, default_role from mysql.user;
11 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`user`
12 set default role invalid_role;
13 ERROR OP000: Invalid role specification `invalid_role`
14 set default role not_granted_role;
15 ERROR OP000: Invalid role specification `not_granted_role`
16 set default role test_role;
17 select user, host, default_role from mysql.user;
18 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`user`
19 select user, host, default_role from mysql.user where user='test_user';
20 User Host default_role
21 test_user localhost test_role
22 show grants;
23 Grants for test_user@localhost
24 GRANT `test_role` TO `test_user`@`localhost`
25 GRANT USAGE ON *.* TO `test_user`@`localhost`
26 GRANT SELECT ON *.* TO `test_role`
27 SET DEFAULT ROLE `test_role` FOR `test_user`@`localhost`
28 select user, host, default_role from mysql.user where user='test_user';
29 User Host default_role
30 test_user localhost test_role
31 set default role invalid_role;
32 ERROR OP000: Invalid role specification `invalid_role`
33 select user, host, default_role from mysql.user where user='test_user';
34 User Host default_role
35 test_user localhost test_role
36 revoke test_role from test_user@localhost;
37 select user, host, default_role from mysql.user where user='test_user';
38 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `mysql`.`user`
39 drop role test_role;
40 drop role not_granted_role;
41 drop user test_user@localhost;
42 #
43 # MDEV-22312: Bad error message for SET DEFAULT ROLE when user account
44 # is not granted the role
45 #
46 CREATE USER a;
47 CREATE USER b;
48 CREATE ROLE r1;
49 CREATE ROLE r2;
50 SET DEFAULT ROLE r1 FOR a;
51 ERROR OP000: User `a`@`%` has not been granted role `r1`
52 GRANT r1 TO b;
53 GRANT r2 TO b;
54 SET DEFAULT ROLE r1 FOR b;
55 # Change user b
56 SELECT CURRENT_ROLE;
57 CURRENT_ROLE
58 r1
59 SET ROLE r2;
60 SELECT CURRENT_ROLE;
61 CURRENT_ROLE
62 r2
63 SET DEFAULT ROLE r1 FOR a;
64 ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
65 SET DEFAULT ROLE r2;
66 # Change user root (session 1: select_priv to b)
67 GRANT SELECT ON mysql.* TO b;
68 # Change user b (session 1: select_priv)
69 SHOW GRANTS FOR b;
70 Grants for b@%
71 GRANT `r1` TO `b`@`%`
72 GRANT `r2` TO `b`@`%`
73 GRANT USAGE ON *.* TO `b`@`%`
74 GRANT SELECT ON `mysql`.* TO `b`@`%`
75 SET DEFAULT ROLE `r2` FOR `b`@`%`
76 SET DEFAULT ROLE r1 FOR a;
77 ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
78 SELECT CURRENT_ROLE;
79 CURRENT_ROLE
80 r2
81 SET DEFAULT ROLE NONE;
82 SELECT CURRENT_ROLE;
83 CURRENT_ROLE
84 r2
85 SET DEFAULT ROLE current_role FOR current_user;
86 SET DEFAULT ROLE invalid_role;
87 ERROR OP000: Invalid role specification `invalid_role`
88 SET DEFAULT ROLE invalid_role FOR a;
89 ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
90 SET DEFAULT ROLE none FOR a;
91 ERROR 42000: Access denied for user 'b'@'%' to database 'mysql'
92 # Change user root (session 2: adding update_priv to user b)
93 GRANT UPDATE ON mysql.* TO b;
94 # Change user b
95 SHOW GRANTS FOR b;
96 Grants for b@%
97 GRANT `r1` TO `b`@`%`
98 GRANT `r2` TO `b`@`%`
99 GRANT USAGE ON *.* TO `b`@`%`
100 GRANT SELECT, UPDATE ON `mysql`.* TO `b`@`%`
101 SET DEFAULT ROLE `r2` FOR `b`@`%`
102 SET DEFAULT ROLE r1 FOR a;
103 ERROR OP000: User `a`@`%` has not been granted role `r1`
104 SET DEFAULT ROLE invalid_role;
105 ERROR OP000: Invalid role specification `invalid_role`
106 SET DEFAULT ROLE invalid_role FOR a;
107 ERROR OP000: Invalid role specification `invalid_role`
108 SET DEFAULT ROLE none FOR a;
109 # Change user root (session 3: Grant role to user a)
110 GRANT r1 TO a;
111 SET DEFAULT ROLE r1 FOR a;
112 # Change user a (verify session 3)
113 SELECT CURRENT_ROLE;
114 CURRENT_ROLE
115 r1
116 SET DEFAULT ROLE None;
117 # Change user b (session 3: role granted to user a)
118 SET DEFAULT ROLE r1 FOR a;
119 SET DEFAULT ROLE r2 FOR a;
120 ERROR OP000: User `a`@`%` has not been granted role `r2`
121 SET DEFAULT ROLE invalid_role;
122 ERROR OP000: Invalid role specification `invalid_role`
123 SET DEFAULT ROLE invalid_role FOR a;
124 ERROR OP000: Invalid role specification `invalid_role`
125 SELECT user, host, default_role FROM mysql.user where user='a' or user='b';
126 User Host default_role
127 a % r1
128 b % r2
129 DROP ROLE r1, r2;
130 DROP USER a, b;