Debian Codegraph @

mysql-test/suite/sql_sequence/grant.result

raw
1 SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_AUTO_CREATE_USER', '');
2 create database mysqltest_1;
3 use mysqltest_1;
4 grant all on mysqltest_1.* to 'normal'@'%';
5 grant select on mysqltest_1.* to 'read_only'@'%';
6 grant select,insert on mysqltest_1.* to 'read_write'@'%';
7 grant select,insert,alter on mysqltest_1.* to 'alter'@'%';
8 grant alter on mysqltest_1.* to only_alter@'%';
9 connect normal,localhost,normal,,mysqltest_1;
10 connect read_only,localhost,read_only,,mysqltest_1;
11 connect read_write,localhost,read_write,,mysqltest_1;
12 connect alter,localhost,alter,,mysqltest_1;
13 connect only_alter, localhost, only_alter,,mysqltest_1;
14 connection normal;
15 create sequence s1;
16 select next value for s1;
17 next value for s1
18 1
19 alter sequence s1 restart= 11;
20 select * from s1;
21 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
22 11 1 9223372036854775806 1 1 1000 0 0
23 connection read_only;
24 select next value for s1;
25 ERROR 42000: INSERT command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
26 alter sequence s1 restart= 11;
27 ERROR 42000: ALTER command denied to user 'read_only'@'localhost' for table `mysqltest_1`.`s1`
28 select * from s1;
29 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
30 11 1 9223372036854775806 1 1 1000 0 0
31 connection read_write;
32 select next value for s1;
33 next value for s1
34 11
35 alter sequence s1 restart= 11;
36 ERROR 42000: ALTER command denied to user 'read_write'@'localhost' for table `mysqltest_1`.`s1`
37 select * from s1;
38 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
39 1011 1 9223372036854775806 1 1 1000 0 0
40 connection alter;
41 select next value for s1;
42 next value for s1
43 12
44 alter sequence s1 restart= 11;
45 select * from s1;
46 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
47 11 1 9223372036854775806 1 1 1000 0 0
48 connection only_alter;
49 select next value for s1;
50 ERROR 42000: SELECT, INSERT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
51 alter sequence s1 restart= 11;
52 select * from s1;
53 ERROR 42000: SELECT command denied to user 'only_alter'@'localhost' for table `mysqltest_1`.`s1`
54 connection default;
55 drop user 'normal'@'%';
56 drop user 'read_only'@'%';
57 drop user 'read_write'@'%';
58 drop user 'alter'@'%';
59 drop user 'only_alter'@'%';
60 drop sequence s1;
61 #
62 # MDEV-36413 User without any privileges to a sequence can read from
63 # it and modify it via column default
64 #
65 create sequence s1;
66 create sequence s2;
67 select * from s2;
68 next_not_cached_value minimum_value maximum_value start_value increment cache_size cycle_option cycle_count
69 1 1 9223372036854775806 1 1 1000 0 0
70 create table t2 (a int not null default(nextval(s1)));
71 insert into t2 values();
72 create user u;
73 grant create, insert, select, drop on mysqltest_1.t1 to u;
74 grant insert, select on mysqltest_1.s1 to u;
75 grant select on mysqltest_1.t2 to u;
76 connect con1,localhost,u,,mysqltest_1;
77 select nextval(s2);
78 ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
79 show create sequence s2;
80 ERROR 42000: SHOW command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
81 create table t1 (a int not null default(nextval(s1)));
82 drop table t1;
83 create table t1 (a int not null default(nextval(s1))) select a from t2;
84 insert into t1 values();
85 select * from t1;
86 a
87 1
88 2
89 drop table t1;
90 create table t1 (a int not null default(nextval(s1))) select a from (select t2.a from t2,t2 as t3 where t2.a=t3.a) as t4;
91 drop table t1;
92 create table t1 (a int not null default(nextval(s2)));
93 ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
94 create table t1 (a int not null default(nextval(s1)),
95 b int not null default(nextval(s2)));
96 ERROR 42000: SELECT, INSERT command denied to user 'u'@'localhost' for table `mysqltest_1`.`s2`
97 disconnect con1;
98 connection default;
99 drop user u;
100 create user u_alter;
101 create table t1 (id int);
102 grant alter on t1 to u_alter;
103 connect con_alter,localhost,u_alter,,mysqltest_1;
104 alter table t1 modify id int default nextval(s1);
105 ERROR 42000: SELECT, INSERT command denied to user 'u_alter'@'localhost' for table `mysqltest_1`.`s1`
106 connection default;
107 grant insert, select on s1 to u_alter;
108 connection con_alter;
109 alter table t1 modify id int default nextval(s1);
110 disconnect con_alter;
111 connection default;
112 drop user u_alter;
113 drop database mysqltest_1;
114 #
115 # MDEV-36870 Spurious unrelated permission error when selecting from table with default that uses nextval(sequence)
116 #
117 create database db1;
118 use db1;
119 create sequence s1 cache 0;
120 create table t1 (id int unsigned default (10+nextval(s1)));
121 insert t1 values ();
122 create table t2 (id int unsigned default nextval(s1), b int default(default(id)));
123 insert t2 values ();
124 create function f1(x int) returns int sql security invoker
125 begin
126 select id+x into x from t1;
127 return x;
128 insert t1 values ();
129 end|
130 create user u1@localhost;
131 grant select on db1.* to u1@localhost;
132 grant execute on db1.* to u1@localhost;
133 grant all privileges on test.* to u1@localhost;
134 use test;
135 create table t3 (id int unsigned default (20+nextval(db1.s1)), b int);
136 insert t3 values ();
137 create sequence s2 cache 0;
138 create table t4 (id int unsigned default (10+nextval(s2)), b int);
139 insert t4 values ();
140 connect u1,localhost,u1,,db1;
141 select * from t1;
142 id
143 11
144 connection default;
145 flush tables;
146 connection u1;
147 select * from t1;
148 id
149 11
150 select default(id) from t1;
151 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
152 select * from t2;
153 id b
154 2 3
155 select f1(100);
156 f1(100)
157 111
158 select column_name, data_type, column_default from information_schema.columns where table_schema='db1' and table_name='t1';
159 column_name data_type column_default
160 id int (10 + nextval(`db1`.`s1`))
161 use test;
162 insert t3 values ();
163 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
164 insert t4 values ();
165 insert t3 (b) select 5;
166 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
167 insert t4 (b) select 5;
168 update t3 set id=default;
169 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
170 update t4 set id=default;
171 prepare stmt from "update t3 set id=?";
172 execute stmt using default;
173 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
174 prepare stmt from "update t4 set id=?";
175 execute stmt using default;
176 deallocate prepare stmt;
177 insert t4 (b) values ((select * from db1.t1));
178 insert t4 (b) values ((select default(id) from db1.t1));
179 ERROR 42000: INSERT command denied to user 'u1'@'localhost' for table `db1`.`s1`
180 connection default;
181 disconnect u1;
182 select nextval(db1.s1) as 'must be 5';
183 must be 5
184 5
185 drop user u1@localhost;
186 drop database db1;
187 drop table t3, t4, s2;
188 # End of 10.6 tests