Debian Codegraph @

mysql-test/suite/roles/prepare_stmt_with_role.test

raw
1 --source include/not_embedded.inc
2
3
4 --echo #
5 --echo # Test user to check if we can grant the created role to it.
6 --echo #
7 create user test_user;
8 --echo #
9 --echo # First create the role.
10 --echo #
11 SET @createRole = 'CREATE ROLE developers';
12 PREPARE stmtCreateRole FROM @createRole;
13 EXECUTE stmtCreateRole;
14 --echo #
15 --echo # Test to see if the role is created.
16 --echo #
17 SELECT user, host,is_role FROM mysql.user
18 WHERE user = 'developers';
19 SHOW GRANTS;
20
21 --echo # Test reexecution.
22 --error ER_CANNOT_USER
23 EXECUTE stmtCreateRole;
24
25 --echo #
26 --echo # Now grant the role to the test user.
27 --echo #
28 SET @grantRole = 'GRANT developers to test_user';
29 PREPARE stmtGrantRole FROM @grantRole;
30 EXECUTE stmtGrantRole;
31 --echo # Test reexecution.
32 EXECUTE stmtGrantRole;
33
34 --echo #
35 --echo # We should see 2 entries in the roles_mapping table.
36 --echo #
37 --sorted_result
38 SELECT * FROM mysql.roles_mapping;
39 SHOW GRANTS FOR test_user;
40
41 --echo #
42 --echo # Test revoking a role.
43 --echo #
44 SET @revokeRole = 'REVOKE developers FROM test_user';
45 PREPARE stmtRevokeRole FROM @revokeRole;
46 EXECUTE stmtRevokeRole;
47 --error ER_CANNOT_REVOKE_ROLE
48 EXECUTE stmtRevokeRole;
49 SHOW GRANTS FOR test_user;
50
51 EXECUTE stmtGrantRole;
52 SHOW GRANTS FOR test_user;
53 EXECUTE stmtRevokeRole;
54 SHOW GRANTS FOR test_user;
55
56 --echo #
57 --echo # Now drop the role.
58 --echo #
59 SET @dropRole = 'DROP ROLE developers';
60 PREPARE stmtDropRole FROM @dropRole;
61 EXECUTE stmtDropRole;
62
63 --echo #
64 --echo # Check both user and roles_mapping table for traces of our role.
65 --echo #
66 SELECT user, host,is_role FROM mysql.user
67 WHERE user = 'developers';
68 SELECT * FROM mysql.roles_mapping;
69 SHOW GRANTS;
70 SHOW GRANTS FOR test_user;
71
72 --echo #
73 --echo # Test reexecution.
74 --echo #
75 EXECUTE stmtCreateRole;
76 SELECT user, host,is_role FROM mysql.user
77 WHERE user = 'developers';
78 SELECT * FROM mysql.roles_mapping;
79
80 SHOW GRANTS;
81 SHOW GRANTS FOR test_user;
82 EXECUTE stmtDropRole;
83
84 --echo # Cleanup.
85 DROP USER test_user;