grant.result 47.4 KB
Newer Older
1
drop table if exists t1;
2
drop database if exists mysqltest;
3
SET NAMES binary;
4 5 6 7 8 9 10 11 12
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
flush privileges;
grant select on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant delete on mysqltest.* to mysqltest_1@localhost;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
13
select * from mysql.user where user="mysqltest_1";
14 15
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	SPECIFIED	EDH-RSA-DES-CBC3-SHA			0	0	0	0
16 17 18 19 20 21 22 23 24 25 26 27
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT, DELETE ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke delete on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant select on mysqltest.* to mysqltest_1@localhost require NONE;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
28
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
29 30 31 32 33 34 35 36 37 38 39 40
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant USAGE on mysqltest.* to mysqltest_1@localhost require cipher "EDH-RSA-DES-CBC3-SHA" AND SUBJECT "testsubject" ISSUER "MySQL AB";
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' REQUIRE ISSUER 'MySQL AB' SUBJECT 'testsubject' CIPHER 'EDH-RSA-DES-CBC3-SHA'
delete from mysql.user where user='mysqltest_1';
flush privileges;
41 42 43 44
delete from mysql.user where user='mysqltest_1';
flush privileges;
grant usage on *.* to mysqltest_1@localhost with max_queries_per_hour 10;
select * from mysql.user where user="mysqltest_1";
45 46
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					10	0	0	0
47 48 49 50 51
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10
grant usage on *.* to mysqltest_1@localhost with max_updates_per_hour 20 max_connections_per_hour 30;
select * from mysql.user where user="mysqltest_1";
52 53
Host	User	Password	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Reload_priv	Shutdown_priv	Process_priv	File_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Show_db_priv	Super_priv	Create_tmp_table_priv	Lock_tables_priv	Execute_priv	Repl_slave_priv	Repl_client_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Create_user_priv	Event_priv	Trigger_priv	ssl_type	ssl_cipher	x509_issuer	x509_subject	max_questions	max_updates	max_connections	max_user_connections
localhost	mysqltest_1		N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N					10	20	30	0
54 55 56 57 58 59 60 61 62
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' WITH MAX_QUERIES_PER_HOUR 10 MAX_UPDATES_PER_HOUR 20 MAX_CONNECTIONS_PER_HOUR 30
delete from mysql.user where user='mysqltest_1';
flush privileges;
63 64 65
grant CREATE TEMPORARY TABLES, LOCK TABLES on mysqltest.* to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
66
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
67 68 69 70
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
71
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
72 73 74 75
GRANT CREATE TEMPORARY TABLES, LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
revoke CREATE TEMPORARY TABLES on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
76
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
77 78 79 80 81
GRANT LOCK TABLES ON `mysqltest`.* TO 'mysqltest_1'@'localhost'
grant ALL PRIVILEGES on mysqltest.* to mysqltest_1@localhost with GRANT OPTION;
flush privileges;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
82
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
83 84 85 86
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
revoke LOCK TABLES, ALTER on mysqltest.* from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
87
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
88
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
89 90 91
revoke all privileges on mysqltest.* from mysqltest_1@localhost;
delete from mysql.user where user='mysqltest_1';
flush privileges;
92 93 94 95 96 97 98 99 100 101 102
grant usage on test.* to mysqltest_1@localhost with grant option;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT USAGE ON `mysqltest`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
GRANT USAGE ON `test`.* TO 'mysqltest_1'@'localhost' WITH GRANT OPTION
delete from mysql.user where user='mysqltest_1';
delete from mysql.db where user='mysqltest_1';
delete from mysql.tables_priv where user='mysqltest_1';
delete from mysql.columns_priv where user='mysqltest_1';
flush privileges;
103 104
show grants for mysqltest_1@localhost;
ERROR 42000: There is no such grant defined for user 'mysqltest_1' on host 'localhost'
105 106 107 108 109 110 111 112 113 114 115 116 117 118 119
create table t1 (a int);
GRANT select,update,insert on t1 to mysqltest_1@localhost;
GRANT select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, SELECT (a), INSERT, INSERT (a), UPDATE, UPDATE (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv	column_priv
Select,Insert,Update	Select,Insert,Update,References
REVOKE select (a), update on t1 from mysqltest_1@localhost;
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
GRANT SELECT, INSERT, INSERT (a), REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
120
REVOKE select,update,insert,insert (a) on t1 from mysqltest_1@localhost;
121 122 123
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost'
124 125
GRANT REFERENCES (a) ON `test`.`t1` TO 'mysqltest_1'@'localhost'
GRANT select,references on t1 to mysqltest_1@localhost;
126 127 128
select table_priv,column_priv from mysql.tables_priv where user="mysqltest_1";
table_priv	column_priv
Select,References	References
129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152
grant all on test.* to mysqltest_3@localhost with grant option;
revoke all on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
GRANT USAGE ON `test`.* TO 'mysqltest_3'@'localhost' WITH GRANT OPTION
revoke grant option on test.* from mysqltest_3@localhost;
show grants for mysqltest_3@localhost;
Grants for mysqltest_3@localhost
GRANT USAGE ON *.* TO 'mysqltest_3'@'localhost'
grant all on test.t1 to mysqltest_2@localhost with grant option;
revoke all on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
GRANT USAGE ON `test`.`t1` TO 'mysqltest_2'@'localhost' WITH GRANT OPTION
revoke grant option on test.t1 from mysqltest_2@localhost;
show grants for mysqltest_2@localhost;
Grants for mysqltest_2@localhost
GRANT USAGE ON *.* TO 'mysqltest_2'@'localhost'
delete from mysql.user where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.db where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.tables_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
delete from mysql.columns_priv where user='mysqltest_1' or user="mysqltest_2" or user="mysqltest_3";
153
flush privileges;
154
drop table t1;
155
GRANT FILE on mysqltest.*  to mysqltest_1@localhost;
156
ERROR HY000: Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
157 158 159
select 1;
1
1
monty@mysql.com's avatar
monty@mysql.com committed
160
insert into mysql.user (host, user) values ('localhost', 'test11');
161 162 163 164
Warnings:
Warning	1364	Field 'ssl_cipher' doesn't have a default value
Warning	1364	Field 'x509_issuer' doesn't have a default value
Warning	1364	Field 'x509_subject' doesn't have a default value
monty@mysql.com's avatar
monty@mysql.com committed
165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188
insert into mysql.db (host, db, user, select_priv) values 
('localhost', 'a%', 'test11', 'Y'), ('localhost', 'ab%', 'test11', 'Y');
alter table mysql.db order by db asc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
alter table mysql.db order by db desc;
flush privileges;
show grants for test11@localhost;
Grants for test11@localhost
GRANT USAGE ON *.* TO 'test11'@'localhost'
GRANT SELECT ON `ab%`.* TO 'test11'@'localhost'
GRANT SELECT ON `a%`.* TO 'test11'@'localhost'
delete from mysql.user where user='test11';
delete from mysql.db where user='test11';
create database mysqltest1;
grant usage on mysqltest1.* to test6123 identified by 'magic123';
select host,db,user,select_priv,insert_priv from mysql.db where db="mysqltest1";
host	db	user	select_priv	insert_priv
delete from mysql.user where user='test6123';
drop database mysqltest1;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
189 190 191 192 193
create table t1 (a int);
grant ALL PRIVILEGES on *.* to drop_user2@localhost with GRANT OPTION;
show grants for drop_user2@localhost;
Grants for drop_user2@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user2'@'localhost' WITH GRANT OPTION
194
revoke all privileges, grant option from drop_user2@localhost;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
195 196 197 198 199 200 201 202
drop user drop_user2@localhost;
grant ALL PRIVILEGES on *.* to drop_user@localhost with GRANT OPTION;
grant ALL PRIVILEGES on test.* to drop_user@localhost with GRANT OPTION;
grant select(a) on test.t1 to drop_user@localhost;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
203
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234
set sql_mode=ansi_quotes;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
set sql_mode=default;
set sql_quote_show_create=0;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
set sql_mode="ansi_quotes";
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON test.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON test.t1 TO 'drop_user'@'localhost'
set sql_quote_show_create=1;
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON "test".* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON "test"."t1" TO 'drop_user'@'localhost'
set sql_mode="";
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT ALL PRIVILEGES ON *.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON `test`.* TO 'drop_user'@'localhost' WITH GRANT OPTION
GRANT SELECT (a) ON `test`.`t1` TO 'drop_user'@'localhost'
235
revoke all privileges, grant option from drop_user@localhost;
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
236 237 238 239
show grants for drop_user@localhost;
Grants for drop_user@localhost
GRANT USAGE ON *.* TO 'drop_user'@'localhost'
drop user drop_user@localhost;
240
revoke all privileges, grant option from drop_user@localhost;
241
ERROR HY000: Can't revoke all privileges for one or more of the requested users
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
242 243 244 245 246 247
grant select(a) on test.t1 to drop_user1@localhost;
grant select on test.t1 to drop_user2@localhost;
grant select on test.* to drop_user3@localhost;
grant select on *.* to drop_user4@localhost;
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
drop_user4@localhost;
248
revoke all privileges, grant option from drop_user1@localhost, drop_user2@localhost,
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
249
drop_user3@localhost, drop_user4@localhost;
250
ERROR HY000: Can't revoke all privileges for one or more of the requested users
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
251 252
drop user drop_user1@localhost, drop_user2@localhost, drop_user3@localhost,
drop_user4@localhost;
253
ERROR HY000: Operation DROP USER failed for 'drop_user1'@'localhost','drop_user2'@'localhost','drop_user3'@'localhost','drop_user4'@'localhost'
monty@mashka.mysql.fi's avatar
monty@mashka.mysql.fi committed
254
drop table t1;
255
grant usage on *.* to mysqltest_1@localhost identified by "password";
256
grant select, update, insert on test.* to mysqltest_1@localhost;
257 258 259
show grants for mysqltest_1@localhost;
Grants for mysqltest_1@localhost
GRANT USAGE ON *.* TO 'mysqltest_1'@'localhost' IDENTIFIED BY PASSWORD '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
260
GRANT SELECT, INSERT, UPDATE ON `test`.* TO 'mysqltest_1'@'localhost'
261
drop user mysqltest_1@localhost;
262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283
SET NAMES koi8r;
CREATE DATABASE ;
USE ;
CREATE TABLE  ( int);
GRANT SELECT ON .* TO @localhost;
SHOW GRANTS FOR @localhost;
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
GRANT SELECT ON ``.* TO ''@'localhost'
REVOKE SELECT ON .* FROM @localhost;
GRANT SELECT ON . TO @localhost;
SHOW GRANTS FOR @localhost;
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
GRANT SELECT ON ``.`` TO ''@'localhost'
REVOKE SELECT ON . FROM @localhost;
GRANT SELECT () ON . TO @localhost;
SHOW GRANTS FOR @localhost;
Grants for @localhost
GRANT USAGE ON *.* TO ''@'localhost'
GRANT SELECT () ON ``.`` TO ''@'localhost'
REVOKE SELECT () ON . FROM @localhost;
284
DROP USER @localhost;
285 286
DROP DATABASE ;
SET NAMES latin1;
dellis@goetia.(none)'s avatar
dellis@goetia.(none) committed
287 288 289 290 291 292 293 294 295 296 297
USE test;
CREATE TABLE t1 (a int );
CREATE TABLE t2 LIKE t1;
CREATE TABLE t3 LIKE t1;
CREATE TABLE t4 LIKE t1;
CREATE TABLE t5 LIKE t1;
CREATE TABLE t6 LIKE t1;
CREATE TABLE t7 LIKE t1;
CREATE TABLE t8 LIKE t1;
CREATE TABLE t9 LIKE t1;
CREATE TABLE t10 LIKE t1;
298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317
CREATE DATABASE testdb1;
CREATE DATABASE testdb2;
CREATE DATABASE testdb3;
CREATE DATABASE testdb4;
CREATE DATABASE testdb5;
CREATE DATABASE testdb6;
CREATE DATABASE testdb7;
CREATE DATABASE testdb8;
CREATE DATABASE testdb9;
CREATE DATABASE testdb10;
GRANT ALL ON testdb1.* TO testuser@localhost;
GRANT ALL ON testdb2.* TO testuser@localhost;
GRANT ALL ON testdb3.* TO testuser@localhost;
GRANT ALL ON testdb4.* TO testuser@localhost;
GRANT ALL ON testdb5.* TO testuser@localhost;
GRANT ALL ON testdb6.* TO testuser@localhost;
GRANT ALL ON testdb7.* TO testuser@localhost;
GRANT ALL ON testdb8.* TO testuser@localhost;
GRANT ALL ON testdb9.* TO testuser@localhost;
GRANT ALL ON testdb10.* TO testuser@localhost;
dellis@goetia.(none)'s avatar
dellis@goetia.(none) committed
318 319 320 321 322 323 324 325 326 327
GRANT SELECT ON test.t1 TO testuser@localhost;
GRANT SELECT ON test.t2 TO testuser@localhost;
GRANT SELECT ON test.t3 TO testuser@localhost;
GRANT SELECT ON test.t4 TO testuser@localhost;
GRANT SELECT ON test.t5 TO testuser@localhost;
GRANT SELECT ON test.t6 TO testuser@localhost;
GRANT SELECT ON test.t7 TO testuser@localhost;
GRANT SELECT ON test.t8 TO testuser@localhost;
GRANT SELECT ON test.t9 TO testuser@localhost;
GRANT SELECT ON test.t10 TO testuser@localhost;
328 329 330 331 332 333 334 335 336 337
GRANT SELECT (a) ON test.t1 TO testuser@localhost;
GRANT SELECT (a) ON test.t2 TO testuser@localhost;
GRANT SELECT (a) ON test.t3 TO testuser@localhost;
GRANT SELECT (a) ON test.t4 TO testuser@localhost;
GRANT SELECT (a) ON test.t5 TO testuser@localhost;
GRANT SELECT (a) ON test.t6 TO testuser@localhost;
GRANT SELECT (a) ON test.t7 TO testuser@localhost;
GRANT SELECT (a) ON test.t8 TO testuser@localhost;
GRANT SELECT (a) ON test.t9 TO testuser@localhost;
GRANT SELECT (a) ON test.t10 TO testuser@localhost;
dellis@goetia.(none)'s avatar
dellis@goetia.(none) committed
338 339 340 341 342 343
REVOKE ALL PRIVILEGES, GRANT OPTION FROM testuser@localhost;
SHOW GRANTS FOR testuser@localhost;
Grants for testuser@localhost
GRANT USAGE ON *.* TO 'testuser'@'localhost'
DROP USER testuser@localhost;
DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10;
344 345 346 347 348 349 350 351 352 353
DROP DATABASE testdb1;
DROP DATABASE testdb2;
DROP DATABASE testdb3;
DROP DATABASE testdb4;
DROP DATABASE testdb5;
DROP DATABASE testdb6;
DROP DATABASE testdb7;
DROP DATABASE testdb8;
DROP DATABASE testdb9;
DROP DATABASE testdb10;
354 355 356 357 358 359
create table t1(a int, b int, c int, d int);
grant insert(b), insert(c), insert(d), insert(a) on t1 to grant_user@localhost;
show grants for grant_user@localhost;
Grants for grant_user@localhost
GRANT USAGE ON *.* TO 'grant_user'@'localhost'
GRANT INSERT (a, d, c, b) ON `test`.`t1` TO 'grant_user'@'localhost'
360
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv order by Column_name;
361
Host	Db	User	Table_name	Column_name	Column_priv
monty@mysql.com's avatar
monty@mysql.com committed
362
localhost	test	grant_user	t1	a	Insert
363
localhost	test	grant_user	t1	b	Insert
monty@mysql.com's avatar
monty@mysql.com committed
364
localhost	test	grant_user	t1	c	Insert
365
localhost	test	grant_user	t1	d	Insert
366 367 368 369 370 371 372 373
revoke ALL PRIVILEGES on t1 from grant_user@localhost;
show grants for grant_user@localhost;
Grants for grant_user@localhost
GRANT USAGE ON *.* TO 'grant_user'@'localhost'
select Host,Db,User,Table_name,Column_name,Column_priv from mysql.columns_priv;
Host	Db	User	Table_name	Column_name	Column_priv
drop user grant_user@localhost;
drop table t1;
374 375 376 377 378 379 380 381 382 383
create database mysqltest_1;
create database mysqltest_2;
create table mysqltest_1.t1 select 1 a, 2 q;
create table mysqltest_1.t2 select 1 b, 2 r;
create table mysqltest_2.t1 select 1 c, 2 s;
create table mysqltest_2.t2 select 1 d, 2 t;
grant update (a) on mysqltest_1.t1 to mysqltest_3@localhost;
grant select (b) on mysqltest_1.t2 to mysqltest_3@localhost;
grant select (c) on mysqltest_2.t1 to mysqltest_3@localhost;
grant update (d) on mysqltest_2.t2 to mysqltest_3@localhost;
384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404
SELECT * FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES 
WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
ORDER BY TABLE_NAME,COLUMN_NAME,PRIVILEGE_TYPE;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_3'@'localhost'	NULL	mysqltest_1	t1	a	UPDATE	NO
'mysqltest_3'@'localhost'	NULL	mysqltest_2	t1	c	SELECT	NO
'mysqltest_3'@'localhost'	NULL	mysqltest_1	t2	b	SELECT	NO
'mysqltest_3'@'localhost'	NULL	mysqltest_2	t2	d	UPDATE	NO
SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
ORDER BY TABLE_NAME,PRIVILEGE_TYPE;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT * from INFORMATION_SCHEMA.SCHEMA_PRIVILEGES
WHERE GRANTEE = '''mysqltest_3''@''localhost''' 
ORDER BY TABLE_SCHEMA,PRIVILEGE_TYPE;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
SELECT * from INFORMATION_SCHEMA.USER_PRIVILEGES
WHERE GRANTEE = '''mysqltest_3''@''localhost'''
 ORDER BY TABLE_CATALOG,PRIVILEGE_TYPE;
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_3'@'localhost'	NULL	USAGE	NO
405
update mysqltest_1.t1, mysqltest_1.t2 set q=10 where b=1;
406
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for column 'q' in table 't1'
monty@mysql.com's avatar
monty@mysql.com committed
407
update mysqltest_1.t2, mysqltest_2.t2 set d=20 where d=1;
408
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 'd' in table 't2'
monty@mysql.com's avatar
monty@mysql.com committed
409
update mysqltest_1.t1, mysqltest_2.t2 set d=20 where d=1;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
410
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
411
update mysqltest_2.t1, mysqltest_1.t2 set c=20 where b=1;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
412
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
413
update mysqltest_2.t1, mysqltest_2.t2 set d=10 where s=2;
414
ERROR 42000: SELECT command denied to user 'mysqltest_3'@'localhost' for column 's' in table 't1'
415 416 417 418 419 420 421 422 423 424 425 426 427 428
update mysqltest_1.t1, mysqltest_2.t2 set a=10,d=10;
update mysqltest_1.t1, mysqltest_2.t1 set a=20 where c=20;
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
a	q	b	r
10	2	1	2
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
c	s	d	t
1	2	10	2
revoke all on mysqltest_1.t1 from mysqltest_3@localhost;
revoke all on mysqltest_1.t2 from mysqltest_3@localhost;
revoke all on mysqltest_2.t1 from mysqltest_3@localhost;
revoke all on mysqltest_2.t2 from mysqltest_3@localhost;
grant all on mysqltest_2.* to mysqltest_3@localhost;
grant select on *.* to mysqltest_3@localhost;
429
grant select on mysqltest_2.t1 to mysqltest_3@localhost;
430 431 432 433
flush privileges;
use mysqltest_1;
update mysqltest_2.t1, mysqltest_2.t2 set c=500,d=600;
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
434
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
435 436
use mysqltest_2;
update mysqltest_1.t1, mysqltest_1.t2 set a=100,b=200;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
437
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
438
update mysqltest_2.t1, mysqltest_1.t2 set c=100,b=200;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
439
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't2'
440
update mysqltest_1.t1, mysqltest_2.t2 set a=100,d=200;
serg@sergbook.mysql.com's avatar
serg@sergbook.mysql.com committed
441
ERROR 42000: UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
442 443 444 445 446 447 448 449 450 451 452 453 454
select t1.*,t2.* from mysqltest_1.t1,mysqltest_1.t2;
a	q	b	r
10	2	1	2
select t1.*,t2.* from mysqltest_2.t1,mysqltest_2.t2;
c	s	d	t
500	2	600	2
delete from mysql.user where user='mysqltest_3';
delete from mysql.db where user="mysqltest_3";
delete from mysql.tables_priv where user="mysqltest_3";
delete from mysql.columns_priv where user="mysqltest_3";
flush privileges;
drop database mysqltest_1;
drop database mysqltest_2;
455 456 457
SHOW PRIVILEGES;
Privilege	Context	Comment
Alter	Tables	To alter the table
458
Alter routine	Functions,Procedures	To alter or drop stored functions/procedures
459
Create	Databases,Tables,Indexes	To create new databases and tables
460
Create routine	Functions,Procedures	To use CREATE FUNCTION/PROCEDURE
461 462
Create temporary tables	Databases	To use CREATE TEMPORARY TABLE
Create view	Tables	To create new views
463
Create user	Server Admin	To create new users
464 465
Delete	Tables	To delete existing rows
Drop	Databases,Tables	To drop databases, tables, and views
466
Event	Server Admin	To create, alter, drop and execute events
467
Execute	Functions,Procedures	To execute stored routines
468
File	File access on server	To read and write files on the server
469
Grant option	Databases,Tables,Functions,Procedures	To give to other users those privileges you possess
470 471 472 473 474 475 476 477 478 479 480 481 482
Index	Tables	To create or drop indexes
Insert	Tables	To insert data into tables
Lock tables	Databases	To use LOCK TABLES (together with SELECT privilege)
Process	Server Admin	To view the plain text of currently executing queries
References	Databases,Tables	To have references on tables
Reload	Server Admin	To reload or refresh tables, logs and privileges
Replication client	Server Admin	To ask where the slave or master servers are
Replication slave	Server Admin	To read binary log events from the master
Select	Tables	To retrieve rows from table
Show databases	Server Admin	To see all databases with SHOW DATABASES
Show view	Tables	To see views with SHOW CREATE VIEW
Shutdown	Server Admin	To shut down the server
Super	Server Admin	To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.
483
Trigger	Tables	To use triggers
484 485
Update	Tables	To update existing rows
Usage	Server Admin	No privileges - allow connect only
486 487 488 489 490 491 492 493
create database mysqltest;
create table mysqltest.t1 (a int,b int,c int);
grant all on mysqltest.t1 to mysqltest_1@localhost;
alter table t1 rename t2;
ERROR 42000: INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'
revoke all privileges on mysqltest.t1 from mysqltest_1@localhost;
delete from mysql.user where user=_binary'mysqltest_1';
drop database mysqltest;
494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509
CREATE USER dummy@localhost;
CREATE DATABASE mysqltest;
CREATE TABLE mysqltest.dummytable (dummyfield INT);
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
GRANT ALL PRIVILEGES ON mysqltest.dummytable TO dummy@localhost;
GRANT ALL PRIVILEGES ON mysqltest.dummyview TO dummy@localhost;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
use INFORMATION_SCHEMA;
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
510 511
mysqltest	dummytable	ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
mysqltest	dummyview	ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
512 513 514 515 516 517 518 519 520 521
FLUSH PRIVILEGES;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT ALL PRIVILEGES ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
522 523
mysqltest	dummytable	ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
mysqltest	dummyview	ALTER, CREATE, CREATE VIEW, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, SHOW VIEW, TRIGGER, UPDATE
524 525 526 527 528 529 530
SHOW FIELDS FROM mysql.tables_priv;
Field	Type	Null	Key	Default	Extra
Host	char(60)	NO	PRI		
Db	char(64)	NO	PRI		
User	char(16)	NO	PRI		
Table_name	char(64)	NO	PRI		
Grantor	char(77)	NO	MUL		
531
Timestamp	timestamp	NO		CURRENT_TIMESTAMP	
532
Table_priv	set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger')	NO			
533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605
Column_priv	set('Select','Insert','Update','References')	NO			
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
DROP USER dummy@localhost;
DROP DATABASE mysqltest;
CREATE USER dummy@localhost;
CREATE DATABASE mysqltest;
CREATE TABLE mysqltest.dummytable (dummyfield INT);
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
GRANT CREATE VIEW ON mysqltest.dummytable TO dummy@localhost;
GRANT CREATE VIEW ON mysqltest.dummyview TO dummy@localhost;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
use INFORMATION_SCHEMA;
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
mysqltest	dummytable	CREATE VIEW
mysqltest	dummyview	CREATE VIEW
FLUSH PRIVILEGES;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT CREATE VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT CREATE VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
mysqltest	dummytable	CREATE VIEW
mysqltest	dummyview	CREATE VIEW
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
DROP USER dummy@localhost;
DROP DATABASE mysqltest;
CREATE USER dummy@localhost;
CREATE DATABASE mysqltest;
CREATE TABLE mysqltest.dummytable (dummyfield INT);
CREATE VIEW mysqltest.dummyview AS SELECT dummyfield FROM mysqltest.dummytable;
GRANT SHOW VIEW ON mysqltest.dummytable TO dummy@localhost;
GRANT SHOW VIEW ON mysqltest.dummyview TO dummy@localhost;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
use INFORMATION_SCHEMA;
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
mysqltest	dummytable	SHOW VIEW
mysqltest	dummyview	SHOW VIEW
FLUSH PRIVILEGES;
SHOW GRANTS FOR dummy@localhost;
Grants for dummy@localhost
GRANT USAGE ON *.* TO 'dummy'@'localhost'
GRANT SHOW VIEW ON `mysqltest`.`dummyview` TO 'dummy'@'localhost'
GRANT SHOW VIEW ON `mysqltest`.`dummytable` TO 'dummy'@'localhost'
SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY
PRIVILEGE_TYPE SEPARATOR ', ') AS PRIVILEGES FROM TABLE_PRIVILEGES WHERE GRANTEE
= '\'dummy\'@\'localhost\'' GROUP BY TABLE_SCHEMA, TABLE_NAME;
TABLE_SCHEMA	TABLE_NAME	PRIVILEGES
mysqltest	dummytable	SHOW VIEW
mysqltest	dummyview	SHOW VIEW
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM dummy@localhost;
DROP USER dummy@localhost;
DROP DATABASE mysqltest;
606
use mysql;
jimw@mysql.com's avatar
jimw@mysql.com committed
607
insert into tables_priv values ('','test_db','mysqltest_1','test_table','test_grantor',CURRENT_TIMESTAMP,'Select','Select');
608 609
flush privileges;
delete from tables_priv where host = '' and user = 'mysqltest_1';
jimw@mysql.com's avatar
jimw@mysql.com committed
610
flush privileges;
611
use test;
bar@mysql.com's avatar
bar@mysql.com committed
612 613
set @user123="non-existent";
select * from mysql.db where user=@user123;
614
Host	Db	User	Select_priv	Insert_priv	Update_priv	Delete_priv	Create_priv	Drop_priv	Grant_priv	References_priv	Index_priv	Alter_priv	Create_tmp_table_priv	Lock_tables_priv	Create_view_priv	Show_view_priv	Create_routine_priv	Alter_routine_priv	Execute_priv	Event_priv	Trigger_priv
bar@mysql.com's avatar
bar@mysql.com committed
615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635
set names koi8r;
create database ;
grant select on .* to root@localhost;
select hex(Db) from mysql.db where Db='';
hex(Db)
D0B1D0B4
show grants for root@localhost;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT SELECT ON ``.* TO 'root'@'localhost'
flush privileges;
show grants for root@localhost;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT SELECT ON ``.* TO 'root'@'localhost'
drop database ;
revoke all privileges on .* from root@localhost;
show grants for root@localhost;
Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
set names latin1;
636 637 638 639 640 641 642 643
create user mysqltest_7@;
set password for mysqltest_7@ = password('systpass');
show grants for mysqltest_7@;
Grants for mysqltest_7@
GRANT USAGE ON *.* TO 'mysqltest_7'@'' IDENTIFIED BY PASSWORD '*2FB071A056F9BB745219D9C876814231DAF46517'
drop user mysqltest_7@;
show grants for mysqltest_7@;
ERROR 42000: There is no such grant defined for user 'mysqltest_7' on host ''
644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661
create database mysqltest;
use mysqltest;
create table t1(f1 int);
GRANT DELETE ON mysqltest.t1 TO mysqltest1@'%';
GRANT SELECT ON mysqltest.t1 TO mysqltest1@'192.%';
show grants for mysqltest1@'192.%';
Grants for mysqltest1@192.%
GRANT USAGE ON *.* TO 'mysqltest1'@'192.%'
GRANT SELECT ON `mysqltest`.`t1` TO 'mysqltest1'@'192.%'
show grants for mysqltest1@'%';
Grants for mysqltest1@%
GRANT USAGE ON *.* TO 'mysqltest1'@'%'
GRANT DELETE ON `mysqltest`.`t1` TO 'mysqltest1'@'%'
delete from mysql.user where user='mysqltest1';
delete from mysql.db where user='mysqltest1';
delete from mysql.tables_priv where user='mysqltest1';
flush privileges;
drop database mysqltest;
662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872
use test;
create table t1 (a int);
create table t2 as select * from mysql.user where user='';
delete from mysql.user where user='';
flush privileges;
create user mysqltest_8@'';
create user mysqltest_8;
create user mysqltest_8@host8;
create user mysqltest_8@'';
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@''
create user mysqltest_8;
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'%'
create user mysqltest_8@host8;
ERROR HY000: Operation CREATE USER failed for 'mysqltest_8'@'host8'
select user, QUOTE(host) from mysql.user where user="mysqltest_8";
user	QUOTE(host)
mysqltest_8	''
mysqltest_8	'%'
mysqltest_8	'host8'
Schema privileges
grant select on mysqltest.* to mysqltest_8@'';
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
grant select on mysqltest.* to mysqltest_8@;
show grants for mysqltest_8@;
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
grant select on mysqltest.* to mysqltest_8;
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@'%'
select * from  information_schema.schema_privileges
where grantee like "'mysqltest_8'%";
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_8'@'%'	NULL	mysqltest	SELECT	NO
'mysqltest_8'@''	NULL	mysqltest	SELECT	NO
select * from t1;
a
revoke select on mysqltest.* from mysqltest_8@'';
revoke select on mysqltest.* from mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
select * from  information_schema.schema_privileges
where grantee like "'mysqltest_8'%";
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	PRIVILEGE_TYPE	IS_GRANTABLE
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8@;
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
grant select on mysqltest.* to mysqltest_8@'';
flush privileges;
show grants for mysqltest_8@;
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT SELECT ON `mysqltest`.* TO 'mysqltest_8'@''
revoke select on mysqltest.* from mysqltest_8@'';
flush privileges;
Column privileges
grant update (a) on t1 to mysqltest_8@'';
grant update (a) on t1 to mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@'%'
select * from  information_schema.column_privileges;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_8'@'%'	NULL	test	t1	a	UPDATE	NO
'mysqltest_8'@''	NULL	test	t1	a	UPDATE	NO
select * from t1;
a
revoke update (a) on t1 from mysqltest_8@'';
revoke update (a) on t1 from mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
select * from  information_schema.column_privileges;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
Table privileges
grant update on t1 to mysqltest_8@'';
grant update on t1 to mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT UPDATE ON `test`.`t1` TO 'mysqltest_8'@'%'
select * from  information_schema.table_privileges;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_8'@'%'	NULL	test	t1	UPDATE	NO
'mysqltest_8'@''	NULL	test	t1	UPDATE	NO
select * from t1;
a
revoke update on t1 from mysqltest_8@'';
revoke update on t1 from mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
select * from  information_schema.table_privileges;
GRANTEE	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
"DROP USER" should clear privileges
grant all privileges on mysqltest.* to mysqltest_8@'';
grant select on mysqltest.* to mysqltest_8@'';
grant update on t1 to mysqltest_8@'';
grant update (a) on t1 to mysqltest_8@'';
grant all privileges on mysqltest.* to mysqltest_8;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''
GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
select * from  information_schema.user_privileges
where grantee like "'mysqltest_8'%";
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_8'@'host8'	NULL	USAGE	NO
'mysqltest_8'@'%'	NULL	USAGE	NO
'mysqltest_8'@''	NULL	USAGE	NO
select * from t1;
a
flush privileges;
show grants for mysqltest_8@'';
Grants for mysqltest_8@
GRANT USAGE ON *.* TO 'mysqltest_8'@''
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@''
GRANT UPDATE, UPDATE (a) ON `test`.`t1` TO 'mysqltest_8'@''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
drop user mysqltest_8@'';
show grants for mysqltest_8@'';
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host ''
show grants for mysqltest_8;
Grants for mysqltest_8@%
GRANT USAGE ON *.* TO 'mysqltest_8'@'%'
GRANT ALL PRIVILEGES ON `mysqltest`.* TO 'mysqltest_8'@'%'
select * from  information_schema.user_privileges
where grantee like "'mysqltest_8'%";
GRANTEE	TABLE_CATALOG	PRIVILEGE_TYPE	IS_GRANTABLE
'mysqltest_8'@'host8'	NULL	USAGE	NO
'mysqltest_8'@'%'	NULL	USAGE	NO
drop user mysqltest_8;
connect(localhost,mysqltest_8,,test,MASTER_PORT,MASTER_SOCKET);
ERROR 28000: Access denied for user 'mysqltest_8'@'localhost' (using password: NO)
show grants for mysqltest_8;
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host '%'
drop user mysqltest_8@host8;
show grants for mysqltest_8@host8;
ERROR 42000: There is no such grant defined for user 'mysqltest_8' on host 'host8'
insert into mysql.user select * from t2;
flush privileges;
drop table t2;
drop table t1;
873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904
CREATE DATABASE mysqltest3;
use mysqltest3;
CREATE TABLE t_nn (c1 INT);
CREATE VIEW  v_nn AS SELECT * FROM t_nn;
CREATE DATABASE mysqltest2;
use mysqltest2;
CREATE TABLE t_nn (c1 INT);
CREATE VIEW  v_nn AS SELECT * FROM t_nn;
CREATE VIEW  v_yn AS SELECT * FROM t_nn;
CREATE VIEW  v_gy AS SELECT * FROM t_nn;
CREATE VIEW  v_ny AS SELECT * FROM t_nn;
CREATE VIEW  v_yy AS SELECT * FROM t_nn WHERE c1=55;
GRANT SHOW VIEW        ON mysqltest2.v_ny TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
GRANT SELECT           ON mysqltest2.v_yn TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
GRANT SELECT           ON mysqltest2.*    TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
GRANT SHOW VIEW,SELECT ON mysqltest2.v_yy TO 'mysqltest_1'@'localhost' IDENTIFIED BY 'mysqltest_1';
SHOW CREATE VIEW  mysqltest2.v_nn;
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
SHOW CREATE TABLE mysqltest2.v_nn;
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
SHOW CREATE VIEW  mysqltest2.v_yn;
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
SHOW CREATE TABLE mysqltest2.v_yn;
ERROR 42000: SHOW VIEW command denied to user 'mysqltest_1'@'localhost' for table 'v_yn'
SHOW CREATE TABLE mysqltest2.v_ny;
View	Create View
v_ny	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
SHOW CREATE VIEW  mysqltest2.v_ny;
View	Create View
v_ny	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_ny` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn`
SHOW CREATE TABLE mysqltest3.t_nn;
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
905 906
SHOW CREATE VIEW  mysqltest3.t_nn;
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 't_nn'
907 908 909 910 911 912 913
SHOW CREATE VIEW  mysqltest3.v_nn;
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
SHOW CREATE TABLE mysqltest3.v_nn;
ERROR 42000: SELECT command denied to user 'mysqltest_1'@'localhost' for table 'v_nn'
SHOW CREATE TABLE mysqltest2.t_nn;
Table	Create Table
t_nn	CREATE TABLE `t_nn` (
914
  `c1` int(11) DEFAULT NULL
915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE VIEW  mysqltest2.t_nn;
ERROR HY000: 'mysqltest2.t_nn' is not VIEW
SHOW CREATE VIEW mysqltest2.v_yy;
View	Create View
v_yy	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
SHOW CREATE TABLE mysqltest2.v_yy;
View	Create View
v_yy	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `mysqltest2`.`v_yy` AS select `mysqltest2`.`t_nn`.`c1` AS `c1` from `mysqltest2`.`t_nn` where (`mysqltest2`.`t_nn`.`c1` = 55)
SHOW CREATE TABLE mysqltest2.v_nn;
View	Create View
v_nn	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
SHOW CREATE VIEW  mysqltest2.v_nn;
View	Create View
v_nn	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_nn` AS select `t_nn`.`c1` AS `c1` from `t_nn`
SHOW CREATE TABLE mysqltest2.t_nn;
Table	Create Table
t_nn	CREATE TABLE `t_nn` (
933
  `c1` int(11) DEFAULT NULL
934 935 936 937 938 939 940 941 942 943 944 945 946 947
) ENGINE=MyISAM DEFAULT CHARSET=latin1
SHOW CREATE VIEW mysqltest2.t_nn;
ERROR HY000: 'mysqltest2.t_nn' is not VIEW
DROP VIEW  mysqltest2.v_nn;
DROP VIEW  mysqltest2.v_yn;
DROP VIEW  mysqltest2.v_ny;
DROP VIEW  mysqltest2.v_yy;
DROP TABLE mysqltest2.t_nn;
DROP DATABASE mysqltest2;
DROP VIEW  mysqltest3.v_nn;
DROP TABLE mysqltest3.t_nn;
DROP DATABASE mysqltest3;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'mysqltest_1'@'localhost';
DROP USER 'mysqltest_1'@'localhost';
kostja@bodhi.local's avatar
kostja@bodhi.local committed
948
use test;
949
create user mysqltest1_thisisreallytoolong;
kostja@bodhi.local's avatar
kostja@bodhi.local committed
950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974
ERROR HY000: String 'mysqltest1_thisisreallytoolong' is too long for user name (should be no longer than 16)
GRANT CREATE ON mysqltest.* TO 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
GRANT CREATE ON mysqltest.* TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
REVOKE CREATE ON mysqltest.* FROM 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
REVOKE CREATE ON mysqltest.* FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
GRANT CREATE ON t1 TO 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
GRANT CREATE ON t1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
REVOKE CREATE ON t1 FROM 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
REVOKE CREATE ON t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
GRANT EXECUTE ON PROCEDURE p1 TO 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
GRANT EXECUTE ON PROCEDURE p1 TO some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
REVOKE EXECUTE ON PROCEDURE p1 FROM 1234567890abcdefGHIKL@localhost;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
REVOKE EXECUTE ON PROCEDURE t1 FROM some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994
CREATE USER bug23556@localhost;
CREATE DATABASE bug23556;
GRANT SELECT ON bug23556.* TO bug23556@localhost;
USE bug23556;
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
GRANT DELETE ON t1 TO bug23556@localhost;
USE bug23556;
TRUNCATE t1;
ERROR 42000: DROP command denied to user 'bug23556'@'localhost' for table 't1'
USE bug23556;
REVOKE DELETE ON t1 FROM bug23556@localhost;
GRANT DROP ON t1 TO bug23556@localhost;
USE bug23556;
TRUNCATE t1;
USE bug23556;
DROP TABLE t1;
USE test;
DROP DATABASE bug23556;
DROP USER bug23556@localhost;
995
End of 5.0 tests