sp-security.test

视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.

--enable_warnings
create function bug12812() returns char(2)
begin
  return 'ok';
end;
create user user_bug12812@localhost IDENTIFIED BY 'ABC'|
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (test_user_12812,localhost,user_bug12812,ABC,test)|
--error ER_PROCACCESS_DENIED_ERROR
SELECT test.bug12812()|
--error ER_PROCACCESS_DENIED_ERROR
CREATE VIEW v1 AS SELECT test.bug12812()|
# Cleanup
connection default|
disconnect test_user_12812|
DROP USER user_bug12812@localhost|
drop function bug12812|
delimiter ;|


#
# BUG#14834: Server denies to execute Stored Procedure
#
# The problem here was with '_' in the database name.
#
create database db_bug14834;

create user user1_bug14834@localhost identified by '';
# The exact name of the database (no wildcard)
grant all on `db\_bug14834`.* to user1_bug14834@localhost;

create user user2_bug14834@localhost identified by '';
# The exact name of the database (no wildcard)
grant all on `db\_bug14834`.* to user2_bug14834@localhost;

create user user3_bug14834@localhost identified by '';
# Wildcards in the database name
grant all on `db__ug14834`.* to user3_bug14834@localhost;

connect (user1_bug14834,localhost,user1_bug14834,,db_bug14834);
# Create the procedure and check that we can call it
create procedure p_bug14834() select user(), current_user();
call p_bug14834();

connect (user2_bug14834,localhost,user2_bug14834,,db_bug14834);
# This didn't work before
call p_bug14834();

connect (user3_bug14834,localhost,user3_bug14834,,db_bug14834);
# Should also work
call p_bug14834();

# Cleanup
connection default;
disconnect user1_bug14834;
disconnect user2_bug14834;
disconnect user3_bug14834;
drop user user1_bug14834@localhost;
drop user user2_bug14834@localhost;
drop user user3_bug14834@localhost;
drop database db_bug14834;


#
# BUG#14533: 'desc tbl' in stored procedure causes error
# ER_TABLEACCESS_DENIED_ERROR
#
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;

connect (user_bug14533,localhost,user_bug14533,,test);

# These should work
call db_bug14533.bug14533_1();
call db_bug14533.bug14533_2();

# For reference, these should not work
--error ER_TABLEACCESS_DENIED_ERROR
desc db_bug14533.t1;
--error ER_TABLEACCESS_DENIED_ERROR
select * from db_bug14533.t1;

# Cleanup
connection default;
disconnect user_bug14533;
drop user user_bug14533@localhost;
drop database db_bug14533;


#
# BUG#7787: Stored procedures: improper warning for "grant execute" statement
#

# Prepare.

CREATE DATABASE db_bug7787;
use db_bug7787;

# Test.

CREATE PROCEDURE p1()
  SHOW INNODB STATUS; 

GRANT EXECUTE ON PROCEDURE p1 TO user_bug7787@localhost; 

# Cleanup.

DROP DATABASE db_bug7787;
drop user user_bug7787@localhost;
use test;


#
# WL#2897: Complete definer support in the stored routines.
#
# The following cases are tested:
#   1. check that if DEFINER-clause is not explicitly specified, stored routines
#     are created with CURRENT_USER privileges;
#   2. check that if DEFINER-clause specifies non-current user, SUPER privilege
#     is required to create a stored routine;
#   3. check that if DEFINER-clause specifies non-existent user, a warning is
#     emitted.
#   4. check that SHOW CREATE PROCEDURE | FUNCTION works correctly;
#
# The following cases are tested in other test suites:
#   - check that mysqldump dumps new attribute correctly;
#   - check that slave replicates CREATE-statements with explicitly specified
#     DEFINER correctly.
#

# Setup the environment.

--echo
--echo ---> connection: root
--connection con1root

--disable_warnings
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings

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;

--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)
--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)

# test case (1).

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use mysqltest;

CREATE PROCEDURE wl2897_p1() SELECT 1;

CREATE FUNCTION wl2897_f1() RETURNS INT RETURN 1;

# test case (2).

--echo
--echo ---> connection: mysqltest_1_con
--connection mysqltest_1_con

use mysqltest;

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE DEFINER=root@localhost PROCEDURE wl2897_p2() SELECT 2;

--error ER_SPECIFIC_ACCESS_DENIED_ERROR
CREATE DEFINER=root@localhost FUNCTION wl2897_f2() RETURNS INT RETURN 2;

# test case (3).

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use mysqltest;

CREATE DEFINER='a @ b @ c'@localhost PROCEDURE wl2897_p3() SELECT 3;

CREATE DEFINER='a @ b @ c'@localhost FUNCTION wl2897_f3() RETURNS INT RETURN 3;

# test case (4).

--echo
--echo ---> connection: con1root
--connection con1root

use mysqltest;

SHOW CREATE PROCEDURE wl2897_p1;
SHOW CREATE PROCEDURE wl2897_p3;

SHOW CREATE FUNCTION wl2897_f1;
SHOW CREATE FUNCTION wl2897_f3;

# Cleanup.

DROP USER mysqltest_1@localhost;
DROP USER mysqltest_2@localhost;

DROP DATABASE mysqltest;

--disconnect mysqltest_1_con
--disconnect mysqltest_2_con


#
# BUG#13198: SP executes if definer does not exist
#

# Prepare environment.

--echo
--echo ---> connection: root
--connection con1root

--disable_warnings
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings

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;

--connect (mysqltest_1_con,localhost,mysqltest_1,,mysqltest)
--connect (mysqltest_2_con,localhost,mysqltest_2,,mysqltest)

# Create a procedure/function under u1.

--echo
--echo ---> connection: mysqltest_1_con
--connection mysqltest_1_con

use mysqltest;

CREATE PROCEDURE bug13198_p1()
  SELECT 1;

CREATE FUNCTION bug13198_f1() RETURNS INT
  RETURN 1;

CALL bug13198_p1();

SELECT bug13198_f1();

# Check that u2 can call the procedure/function.

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use mysqltest;

CALL bug13198_p1();

SELECT bug13198_f1();

# Drop user u1 (definer of the object);

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_1_con

DROP USER mysqltest_1@localhost;

# Check that u2 can not call the procedure/function.

--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use mysqltest;

--error ER_NO_SUCH_USER
CALL bug13198_p1();

--error ER_NO_SUCH_USER
SELECT bug13198_f1();

# Cleanup.

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_2_con

DROP USER mysqltest_2@localhost;

DROP DATABASE mysqltest;


#
# Bug#19857 - When a user with CREATE ROUTINE priv creates a routine,
#             it results in NULL p/w
#

# Can't test with embedded server that doesn't support grants

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';

--connect (mysqltest_2_con,localhost,user19857,meow,test)
--echo
--echo ---> connection: mysqltest_2_con
--connection mysqltest_2_con

use test;

DELIMITER //;
  CREATE PROCEDURE sp19857() DETERMINISTIC
  BEGIN
    DECLARE a INT;
    SET a=1;
    SELECT a;
  END //
DELIMITER ;//

SHOW CREATE PROCEDURE test.sp19857;

--disconnect mysqltest_2_con
--connect (mysqltest_2_con,localhost,user19857,meow,test)
--connection mysqltest_2_con

DROP PROCEDURE IF EXISTS test.sp19857;

--echo
--echo ---> connection: root
--connection con1root

--disconnect mysqltest_2_con

SELECT Host,User,Password FROM mysql.user WHERE User='user19857';

DROP USER user19857@localhost;

--disconnect con1root
--connection default


#
# BUG#18630: Arguments of suid routine calculated in wrong security
# context
#
# Arguments of suid routines were calculated in definer's security
# context instead of caller's context thus creating security hole.
#
--disable_warnings
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;
--enable_warnings
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;
# Thanks to this grant statement privileges of anonymous users on
# 'test' database are not applicable for mysqltest_u1@localhost.
GRANT EXECUTE ON test.* TO mysqltest_u1@localhost;

delimiter |;
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|
delimiter ;|

CREATE SQL SECURITY INVOKER VIEW v1 AS SELECT f_evil();

connect (conn1, localhost, mysqltest_u1,,);

--error ER_TABLEACCESS_DENIED_ERROR
SELECT COUNT(*) FROM t1;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT f_evil();
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT f_suid(f_evil());
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
CALL p_suid(f_evil());
SELECT @a, @b;

--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM v1;
SELECT @a, @b;

disconnect conn1;
connection default;

DROP VIEW v1;
DROP FUNCTION f_evil;
DROP USER mysqltest_u1@localhost;
DROP PROCEDURE p_suid;
DROP FUNCTION f_suid;
DROP TABLE t1;

--echo End of 5.0 tests.