Debian Codegraph @

mysql-test/suite/roles/prepare_stmt_with_role.result

raw
1 #
2 # Test user to check if we can grant the created role to it.
3 #
4 create user test_user;
5 #
6 # First create the role.
7 #
8 SET @createRole = 'CREATE ROLE developers';
9 PREPARE stmtCreateRole FROM @createRole;
10 EXECUTE stmtCreateRole;
11 #
12 # Test to see if the role is created.
13 #
14 SELECT user, host,is_role FROM mysql.user
15 WHERE user = 'developers';
16 User Host is_role
17 developers Y
18 SHOW GRANTS;
19 Grants for root@localhost
20 GRANT `developers` TO `root`@`localhost` WITH ADMIN OPTION
21 GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
22 GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
23 # Test reexecution.
24 EXECUTE stmtCreateRole;
25 ERROR HY000: Operation CREATE ROLE failed for 'developers'
26 #
27 # Now grant the role to the test user.
28 #
29 SET @grantRole = 'GRANT developers to test_user';
30 PREPARE stmtGrantRole FROM @grantRole;
31 EXECUTE stmtGrantRole;
32 # Test reexecution.
33 EXECUTE stmtGrantRole;
34 #
35 # We should see 2 entries in the roles_mapping table.
36 #
37 SELECT * FROM mysql.roles_mapping;
38 Host User Role Admin_option
39 % test_user developers N
40 localhost root developers Y
41 SHOW GRANTS FOR test_user;
42 Grants for test_user@%
43 GRANT `developers` TO `test_user`@`%`
44 GRANT USAGE ON *.* TO `test_user`@`%`
45 #
46 # Test revoking a role.
47 #
48 SET @revokeRole = 'REVOKE developers FROM test_user';
49 PREPARE stmtRevokeRole FROM @revokeRole;
50 EXECUTE stmtRevokeRole;
51 EXECUTE stmtRevokeRole;
52 ERROR HY000: Cannot revoke role 'developers' from: 'test_user'@'%'
53 SHOW GRANTS FOR test_user;
54 Grants for test_user@%
55 GRANT USAGE ON *.* TO `test_user`@`%`
56 EXECUTE stmtGrantRole;
57 SHOW GRANTS FOR test_user;
58 Grants for test_user@%
59 GRANT `developers` TO `test_user`@`%`
60 GRANT USAGE ON *.* TO `test_user`@`%`
61 EXECUTE stmtRevokeRole;
62 SHOW GRANTS FOR test_user;
63 Grants for test_user@%
64 GRANT USAGE ON *.* TO `test_user`@`%`
65 #
66 # Now drop the role.
67 #
68 SET @dropRole = 'DROP ROLE developers';
69 PREPARE stmtDropRole FROM @dropRole;
70 EXECUTE stmtDropRole;
71 #
72 # Check both user and roles_mapping table for traces of our role.
73 #
74 SELECT user, host,is_role FROM mysql.user
75 WHERE user = 'developers';
76 User Host is_role
77 SELECT * FROM mysql.roles_mapping;
78 Host User Role Admin_option
79 SHOW GRANTS;
80 Grants for root@localhost
81 GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
82 GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
83 SHOW GRANTS FOR test_user;
84 Grants for test_user@%
85 GRANT USAGE ON *.* TO `test_user`@`%`
86 #
87 # Test reexecution.
88 #
89 EXECUTE stmtCreateRole;
90 SELECT user, host,is_role FROM mysql.user
91 WHERE user = 'developers';
92 User Host is_role
93 developers Y
94 SELECT * FROM mysql.roles_mapping;
95 Host User Role Admin_option
96 localhost root developers Y
97 SHOW GRANTS;
98 Grants for root@localhost
99 GRANT `developers` TO `root`@`localhost` WITH ADMIN OPTION
100 GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
101 GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION
102 SHOW GRANTS FOR test_user;
103 Grants for test_user@%
104 GRANT USAGE ON *.* TO `test_user`@`%`
105 EXECUTE stmtDropRole;
106 # Cleanup.
107 DROP USER test_user;