sp-security.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 523 行 · 第 1/2 页

drop database if exists mysqltest_1;
create database mysqltest_1;
create procedure mysqltest_1.p1()
begin
select 1 from dual;
end//
grant usage on *.* to mysqltest_1@localhost;
call mysqltest_1.p1();
ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1'
call mysqltest_1.p1();
ERROR 42000: execute command denied to user 'mysqltest_1'@'localhost' for routine 'mysqltest_1.p1'
drop procedure mysqltest_1.p1;
drop database mysqltest_1;
revoke usage on *.* from mysqltest_1@localhost;
drop user mysqltest_1@localhost;
drop function if exists bug12812|
create function bug12812() returns char(2)
begin
return 'ok';
end;
create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
SELECT test.bug12812()|
ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812'
CREATE VIEW v1 AS SELECT test.bug12812()|
ERROR 42000: execute command denied to user 'user_bug12812'@'localhost' for routine 'test.bug12812'
DROP USER user_bug12812@localhost|
drop function bug12812|
create database db_bug14834;
create user user1_bug14834@localhost identified by '';
grant all on `db\_bug14834`.* to user1_bug14834@localhost;
create user user2_bug14834@localhost identified by '';
grant all on `db\_bug14834`.* to user2_bug14834@localhost;
create user user3_bug14834@localhost identified by '';
grant all on `db__ug14834`.* to user3_bug14834@localhost;
create procedure p_bug14834() select user(), current_user();
call p_bug14834();
user()	current_user()
user1_bug14834@localhost	user1_bug14834@localhost
call p_bug14834();
user()	current_user()
user2_bug14834@localhost	user1_bug14834@localhost
call p_bug14834();
user()	current_user()
user3_bug14834@localhost	user1_bug14834@localhost
drop user user1_bug14834@localhost;
drop user user2_bug14834@localhost;
drop user user3_bug14834@localhost;
drop database db_bug14834;
create database db_bug14533;
use db_bug14533;
create table t1 (id int);
create user user_bug14533@localhost identified by '';
create procedure bug14533_1()
sql security definer
desc db_bug14533.t1;
create procedure bug14533_2()
sql security definer
select * from db_bug14533.t1;
grant execute on procedure db_bug14533.bug14533_1 to user_bug14533@localhost;
grant execute on procedure db_bug14533.bug14533_2 to user_bug14533@localhost;
call db_bug14533.bug14533_1();
Field	Type	Null	Key	Default	Extra
id	int(11)	YES		NULL	
call db_bug14533.bug14533_2();
id
desc db_bug14533.t1;
ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1'
select * from db_bug14533.t1;
ERROR 42000: SELECT command denied to user 'user_bug14533'@'localhost' for table 't1'
drop user user_bug14533@localhost;
drop database db_bug14533;
CREATE DATABASE db_bug7787;
use db_bug7787;
CREATE PROCEDURE p1()
SHOW INNODB STATUS;
Warnings:
Warning	1287	'SHOW INNODB STATUS' is deprecated; use 'SHOW ENGINE INNODB STATUS' instead
GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost;
DROP DATABASE db_bug7787;
drop user user_bug7787@localhost;
use test;

---> connection: root
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
CREATE USER mysqltest_1@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
CREATE USER mysqltest_2@localhost;
GRANT SUPER ON *.* TO mysqltest_2@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;

---> connection: mysqltest_2_con
use mysqltest;
CREATE PROCEDURE wl2897_p1() SELECT 1;
CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;

---> connection: mysqltest_1_con
use mysqltest;
CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;
ERROR 42000: Access denied; you need the SUPER privilege for this operation

---> connection: mysqltest_2_con
use mysqltest;
CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;
Warnings:
Note	1449	There is no 'a @ b @ c'@'localhost' registered
CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;
Warnings:
Note	1449	There is no 'a @ b @ c'@'localhost' registered

---> connection: con1root
use mysqltest;
SHOW CREATE PROCEDURE wl2897_p1;
Procedure	sql_mode	Create Procedure
wl2897_p1		CREATE DEFINER=`mysqltest_2`@`localhost` PROCEDURE `wl2897_p1`()
SELECT 1
SHOW CREATE PROCEDURE wl2897_p3;
Procedure	sql_mode	Create Procedure
wl2897_p3		CREATE DEFINER=`a @ b @ c`@`localhost` PROCEDURE `wl2897_p3`()
SELECT 3
SHOW CREATE FUNCTION wl2897_f1;
Function	sql_mode	Create Function
wl2897_f1		CREATE DEFINER=`mysqltest_2`@`localhost` FUNCTION `wl2897_f1`() RETURNS int(11)
RETURN 1
SHOW CREATE FUNCTION wl2897_f3;
Function	sql_mode	Create Function
wl2897_f3		CREATE DEFINER=`a @ b @ c`@`localhost` FUNCTION `wl2897_f3`() RETURNS int(11)
RETURN 3
DROP USER mysqltest_1@localhost;
DROP USER mysqltest_2@localhost;
DROP DATABASE mysqltest;

---> connection: root
DROP DATABASE IF EXISTS mysqltest;
CREATE DATABASE mysqltest;
CREATE USER mysqltest_1@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_1@localhost;
CREATE USER mysqltest_2@localhost;
GRANT ALL PRIVILEGES ON mysqltest.* TO mysqltest_2@localhost;

---> connection: mysqltest_1_con
use mysqltest;
CREATE PROCEDURE bug13198_p1()
SELECT 1;
CREATE FUNCTION bug13198_f1() RETURNS INT
RETURN 1;
CALL bug13198_p1();
1
1
SELECT bug13198_f1();
bug13198_f1()
1

---> connection: mysqltest_2_con
use mysqltest;
CALL bug13198_p1();
1
1
SELECT bug13198_f1();
bug13198_f1()
1

---> connection: root
DROP USER mysqltest_1@localhost;

---> connection: mysqltest_2_con
use mysqltest;
CALL bug13198_p1();
ERROR HY000: There is no 'mysqltest_1'@'localhost' registered
SELECT bug13198_f1();
ERROR HY000: There is no 'mysqltest_1'@'localhost' registered

---> connection: root
DROP USER mysqltest_2@localhost;
DROP DATABASE mysqltest;
GRANT USAGE ON *.* TO user19857@localhost IDENTIFIED BY 'meow';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ROUTINE, ALTER ROUTINE ON test.* TO
user19857@localhost;
SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
Host	User	Password
localhost	user19857	*82DC221D557298F6CE9961037DB1C90604792F5C

---> connection: mysqltest_2_con
use test;
CREATE PROCEDURE sp19857() DETERMINISTIC
BEGIN
DECLARE a INT;
SET a=1;
SELECT a;
END //
SHOW CREATE PROCEDURE test.sp19857;
Procedure	sql_mode	Create Procedure
sp19857		CREATE DEFINER=`user19857`@`localhost` PROCEDURE `sp19857`()
    DETERMINISTIC
BEGIN
DECLARE a INT;
SET a=1;
SELECT a;
END
DROP PROCEDURE IF EXISTS test.sp19857;

---> connection: root
SELECT Host,User,Password FROM mysql.user WHERE User='user19857';
Host	User	Password
localhost	user19857	*82DC221D557298F6CE9961037DB1C90604792F5C
DROP USER user19857@localhost;
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP FUNCTION IF EXISTS f_suid;
DROP PROCEDURE IF EXISTS p_suid;
DROP FUNCTION IF EXISTS f_evil;
DELETE FROM mysql.user WHERE user LIKE 'mysqltest\_%';
DELETE FROM mysql.db WHERE user LIKE 'mysqltest\_%';
DELETE FROM mysql.tables_priv WHERE user LIKE 'mysqltest\_%';
DELETE FROM mysql.columns_priv WHERE user LIKE 'mysqltest\_%';
FLUSH PRIVILEGES;
CREATE TABLE t1 (i INT);
CREATE FUNCTION f_suid(i INT) RETURNS INT SQL SECURITY DEFINER RETURN 0;
CREATE PROCEDURE p_suid(IN i INT) SQL SECURITY DEFINER SET @c:= 0;
CREATE USER mysqltest_u1@localhost;
GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;
CREATE DEFINER=mysqltest_u1@localhost FUNCTION f_evil () RETURNS INT
SQL SECURITY INVOKER
BEGIN
SET @a:= CURRENT_USER();
SET @b:= (SELECT COUNT(*) FROM t1);
RETURN @b;
END|
CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();
SELECT COUNT(*) FROM t1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
SELECT f_evil();
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
SELECT f_suid(f_evil());
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
CALL p_suid(f_evil());
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 't1'
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
SELECT * FROM v1;
ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for table 'v1'
SELECT @a, @b;
@a	@b
mysqltest_u1@localhost	NULL
DROP VIEW v1;
DROP FUNCTION f_evil;
DROP USER mysqltest_u1@localhost;
DROP PROCEDURE p_suid;
DROP FUNCTION f_suid;
DROP TABLE t1;
End of 5.0 tests.