Debian Codegraph @

mysql-test/suite/rpl/t/rpl_sp_privileges.test

raw
1 ###############################################################################
2 # Bug#20049894: AUTOMATIC_SP_PRIVILEGES ARE NOT CORRECTLY REPLICATED WHEN
3 # SETTING DEFINER
4 #
5 # Problem:
6 # ========
7 # Privileges created by automatic_sp_privileges are not correctly replicated
8 # when the procedure is created by a super user using DEFINER.
9 #
10 # Test:
11 # =====
12 # Have a privileged user who already has EXECUTE and ALTER ROUTINE privileges
13 # and execute CREATE PROCEDURE command with and without DEFINER class.
14 # mysq.procs_priv table should not be updated. Take a non privileged user and
15 # execute CREATE PROCEDURE command with and without DEFINER class.
16 # mysql.procs_priv table should be updated.
17 ###############################################################################
18 --source include/set_privilege_checks_user_as_system_user.inc
19 --let $rpl_privilege_checks_user_grant_option = 1
20 --source include/master-slave.inc
21 --source include/rpl_connection_master.inc
22
23 CREATE TABLE t1 (f INT NOT NULL PRIMARY KEY);
24 INSERT INTO t1 VALUES (10);
25 INSERT INTO t1 VALUES (20);
26 --source include/sync_slave_sql_with_master.inc
27
28 # Case 1: Privileged user root creating procedure with
29 # Definer. Procs_priv table should be empty
30 --source include/rpl_connection_master.inc
31 CREATE USER 'testuser'@'localhost';
32 GRANT CREATE ROUTINE ON test.* TO 'testuser'@'localhost';
33 GRANT REPLICATION CLIENT ON *.* TO 'testuser'@'localhost';
34 FLUSH PRIVILEGES;
35
36 DELIMITER |;
37 CREATE DEFINER='testuser'@'localhost' PROCEDURE row_cnt()
38 BEGIN
39 SELECT COUNT(*) as total_rows FROM test.t1;
40 END|
41 DELIMITER ;|
42 --source include/sync_slave_sql_with_master.inc
43
44 --source include/rpl_connection_master.inc
45 --let $assert_text= Assert that mysql.procs_priv table is empty
46 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv] = 0
47 --source include/assert.inc
48
49 --source include/rpl_connection_slave.inc
50 --let $assert_text= Assert that mysql.procs_priv table is empty
51 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv] = 0
52 --source include/assert.inc
53
54 --source include/rpl_connection_master.inc
55 DROP PROCEDURE row_cnt;
56 --source include/sync_slave_sql_with_master.inc
57
58 # Case 2: Privileged user root creating procedure without
59 # Definer. Procs_priv table should be empty
60 --source include/rpl_connection_master.inc
61 DELIMITER |;
62 CREATE PROCEDURE row_cnt()
63 BEGIN
64 SELECT COUNT(*) as total_rows FROM test.t1;
65 END|
66 DELIMITER ;|
67 --source include/sync_slave_sql_with_master.inc
68
69 --source include/rpl_connection_master.inc
70 --let $assert_text= Assert that mysql.procs_priv table is empty
71 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv] = 0
72 --source include/assert.inc
73
74 --source include/rpl_connection_slave.inc
75 --let $assert_text= Assert that mysql.procs_priv table is empty
76 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv] = 0
77 --source include/assert.inc
78
79 --source include/rpl_connection_master.inc
80 DROP PROCEDURE row_cnt;
81 --source include/sync_slave_sql_with_master.inc
82
83 # Case 3: Non Privileged user testuser creating procedure with
84 # Definer. Procs_priv table should be updated
85 --connect(con1,localhost,testuser,,)
86 --connection con1
87 DELIMITER |;
88 CREATE DEFINER='testuser'@'localhost' PROCEDURE row_cnt()
89 BEGIN
90 SELECT COUNT(*) as total_rows FROM test.t1;
91 END|
92 DELIMITER ;|
93 --source include/sync_slave_sql_with_master.inc
94
95 --source include/rpl_connection_master.inc
96 --let $assert_text= Assert that mysql.procs_priv table has one row for testuser
97 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv WHERE User="testuser"] = 1
98 --source include/assert.inc
99
100 --source include/rpl_connection_slave.inc
101 --let $assert_text= Assert that mysql.procs_priv table has one row for testuser
102 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv WHERE User="testuser"] = 1
103 --source include/assert.inc
104
105 --connection con1
106 DROP PROCEDURE row_cnt;
107 --source include/sync_slave_sql_with_master.inc
108
109 # Case 4: Non Privileged user testuser creating procedure with
110 # Definer. Procs_priv table should be updated
111 --connection con1
112 DELIMITER |;
113 CREATE PROCEDURE row_cnt()
114 BEGIN
115 SELECT COUNT(*) as total_rows FROM test.t1;
116 END|
117 DELIMITER ;|
118 --source include/sync_slave_sql_with_master.inc
119
120 --source include/rpl_connection_master.inc
121 --let $assert_text= Assert that mysql.procs_priv table has one row for testuser
122 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv WHERE User="testuser"] = 1
123 --source include/assert.inc
124
125 --source include/rpl_connection_slave.inc
126 --let $assert_text= Assert that mysql.procs_priv table has one row for testuser
127 --let $assert_cond= [SELECT COUNT(*) FROM mysql.procs_priv WHERE User="testuser"] = 1
128 --source include/assert.inc
129
130 # Cleanup
131 --source include/rpl_connection_master.inc
132 DROP TABLE t1;
133 DROP PROCEDURE row_cnt;
134 DROP USER 'testuser'@'localhost';
135
136 --source include/rpl_end.inc