11
11
create procedure stamp(i int)
12
12
insert into db1_secret.t1 values (user(), i);
13
13
show procedure status like 'stamp';
14
Db Name Type Definer Modified Created Security_type Comment
15
db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
14
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
15
db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
16
16
create function db() returns varchar(64) return database();
17
17
show function status like 'db';
18
Db Name Type Definer Modified Created Security_type Comment
19
db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER
18
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
19
db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 DEFINER latin1 latin1_swedish_ci latin1_swedish_ci
45
45
select * from db1_secret.t1;
46
46
ERROR 42000: SELECT command denied to user ''@'localhost' for table 't1'
47
47
create procedure db1_secret.dummy() begin end;
48
ERROR 42000: Access denied for user ''@'localhost' to database 'db1_secret'
48
ERROR 42000: Access denied for user ''@'%' to database 'db1_secret'
49
49
drop procedure db1_secret.dummy;
50
50
ERROR 42000: PROCEDURE db1_secret.dummy does not exist
56
56
alter procedure stamp sql security invoker;
57
57
show procedure status like 'stamp';
58
Db Name Type Definer Modified Created Security_type Comment
59
db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER
58
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
59
db1_secret stamp PROCEDURE root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci
60
60
alter function db sql security invoker;
61
61
show function status like 'db';
62
Db Name Type Definer Modified Created Security_type Comment
63
db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER
62
Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation
63
db1_secret db FUNCTION root@localhost 0000-00-00 00:00:00 0000-00-00 00:00:00 INVOKER latin1 latin1_swedish_ci latin1_swedish_ci
74
74
call db1_secret.stamp(5);
75
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret'
75
ERROR 42000: INSERT command denied to user 'user1'@'localhost' for table 't1'
76
76
select db1_secret.db();
77
ERROR 42000: Access denied for user 'user1'@'localhost' to database 'db1_secret'
78
79
call db1_secret.stamp(6);
79
ERROR 42000: Access denied for user ''@'localhost' to database 'db1_secret'
80
ERROR 42000: INSERT command denied to user ''@'localhost' for table 't1'
80
81
select db1_secret.db();
81
ERROR 42000: Access denied for user ''@'localhost' to database 'db1_secret'
82
84
drop database if exists db2;
83
85
create database db2;
348
350
---> connection: con1root
350
352
SHOW CREATE PROCEDURE wl2897_p1;
351
Procedure sql_mode Create Procedure
353
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
352
354
wl2897_p1 CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
355
SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci
354
356
SHOW CREATE PROCEDURE wl2897_p3;
355
Procedure sql_mode Create Procedure
357
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
356
358
wl2897_p3 CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
359
SELECT 3 latin1 latin1_swedish_ci latin1_swedish_ci
358
360
SHOW CREATE FUNCTION wl2897_f1;
359
Function sql_mode Create Function
361
Function sql_mode Create Function character_set_client collation_connection Database Collation
360
362
wl2897_f1 CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
363
RETURN 1 latin1 latin1_swedish_ci latin1_swedish_ci
362
364
SHOW CREATE FUNCTION wl2897_f3;
363
Function sql_mode Create Function
365
Function sql_mode Create Function character_set_client collation_connection Database Collation
364
366
wl2897_f3 CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
367
RETURN 3 latin1 latin1_swedish_ci latin1_swedish_ci
366
368
DROP USER mysqltest_1@localhost;
367
369
DROP USER mysqltest_2@localhost;
368
370
DROP DATABASE mysqltest;
428
430
SHOW CREATE PROCEDURE test.sp19857;
429
Procedure sql_mode Create Procedure
431
Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation
430
432
sp19857 CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`()
438
END latin1 latin1_swedish_ci latin1_swedish_ci
437
439
DROP PROCEDURE IF EXISTS test.sp19857;
439
441
---> connection: root