view_grant.test

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

# Bug#20363: Create view on just created view is now denied
#
eval CREATE USER mysqltest_db1@localhost identified by 'PWD';
eval GRANT ALL ON mysqltest_db1.* TO mysqltest_db1@localhost WITH GRANT OPTION;

# The session with the non root user is needed.
--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (session1,localhost,mysqltest_db1,PWD,test);

CREATE SCHEMA mysqltest_db1 ;
USE mysqltest_db1 ;

CREATE TABLE t1 (f1 INTEGER);

CREATE VIEW view1 AS
SELECT * FROM t1;
SHOW CREATE VIEW view1;

CREATE VIEW view2 AS
SELECT * FROM view1;
--echo # Here comes a suspicious warning
SHOW CREATE VIEW view2;
--echo # But the view view2 is usable
SELECT * FROM view2;

CREATE VIEW view3 AS
SELECT * FROM view2;

SELECT * from view3;

connection default;
DROP VIEW mysqltest_db1.view3;
DROP VIEW mysqltest_db1.view2;
DROP VIEW mysqltest_db1.view1;
DROP TABLE mysqltest_db1.t1;
DROP SCHEMA mysqltest_db1;
DROP USER mysqltest_db1@localhost;
#
# BUG#20482: failure on Create join view with sources views/tables 
#             in different schemas
#
--disable_warnings
CREATE DATABASE test1;
CREATE DATABASE test2;
--enable_warnings

CREATE TABLE test1.t0 (a VARCHAR(20));
CREATE TABLE test2.t1 (a VARCHAR(20));
CREATE VIEW  test2.t3 AS SELECT * FROM test1.t0;
CREATE OR REPLACE VIEW test.v1 AS 
  SELECT ta.a AS col1, tb.a AS col2 FROM test2.t3 ta, test2.t1 tb;

DROP VIEW test.v1;
DROP VIEW test2.t3;
DROP TABLE test2.t1, test1.t0;
DROP DATABASE test2;
DROP DATABASE test1;


#
# BUG#20570: CURRENT_USER() in a VIEW with SQL SECURITY DEFINER
# returns invoker name
#
--disable_warnings
DROP VIEW IF EXISTS v1;
DROP VIEW IF EXISTS v2;
DROP VIEW IF EXISTS v3;
DROP FUNCTION IF EXISTS f1;
DROP FUNCTION IF EXISTS f2;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

CREATE SQL SECURITY DEFINER VIEW v1 AS SELECT CURRENT_USER() AS cu;

CREATE FUNCTION f1() RETURNS VARCHAR(77) SQL SECURITY INVOKER
  RETURN CURRENT_USER();
CREATE SQL SECURITY DEFINER VIEW v2 AS SELECT f1() AS cu;

CREATE PROCEDURE p1(OUT cu VARCHAR(77)) SQL SECURITY INVOKER
  SET cu= CURRENT_USER();
delimiter |;
CREATE FUNCTION f2() RETURNS VARCHAR(77) SQL SECURITY INVOKER
BEGIN
  DECLARE cu VARCHAR(77);
  CALL p1(cu);
  RETURN cu;
END|
delimiter ;|
CREATE SQL SECURITY DEFINER VIEW v3 AS SELECT f2() AS cu;

CREATE USER mysqltest_u1@localhost;
GRANT ALL ON test.* TO mysqltest_u1@localhost;

connect (conn1, localhost, mysqltest_u1,,);

--echo
--echo The following tests should all return 1.
--echo
SELECT CURRENT_USER() = 'mysqltest_u1@localhost';
SELECT f1() = 'mysqltest_u1@localhost';
CALL p1(@cu);
SELECT @cu = 'mysqltest_u1@localhost';
SELECT f2() = 'mysqltest_u1@localhost';
SELECT cu = 'root@localhost' FROM v1;
SELECT cu = 'root@localhost' FROM v2;
SELECT cu = 'root@localhost' FROM v3;

disconnect conn1;
connection default;

DROP VIEW v3;
DROP FUNCTION f2;
DROP PROCEDURE p1;
DROP FUNCTION f1;
DROP VIEW v2;
DROP VIEW v1;
DROP USER mysqltest_u1@localhost;


#
# Bug#17254: Error for DEFINER security on VIEW provides too much info
#
connect (root,localhost,root,,);
connection root;
CREATE DATABASE db17254;
USE db17254;
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (10),(20);
CREATE USER def_17254@localhost;
GRANT SELECT ON db17254.* TO def_17254@localhost;
CREATE USER inv_17254@localhost;
GRANT SELECT ON db17254.t1 TO inv_17254@localhost;
GRANT CREATE VIEW ON db17254.* TO def_17254@localhost;

connect (def,localhost,def_17254,,db17254);
connection def;
CREATE VIEW v1 AS SELECT * FROM t1;

connection root;
DROP USER def_17254@localhost;

connect (inv,localhost,inv_17254,,db17254);
connection inv;
--echo for a user
--error 1142
SELECT * FROM v1;

connection root;
--echo for a superuser
--error 1449
SELECT * FROM v1;
DROP USER inv_17254@localhost;
DROP DATABASE db17254;
disconnect def;
disconnect inv;


#
# BUG#24404: strange bug with view+permission+prepared statement
#
--disable_warnings
DROP DATABASE IF EXISTS mysqltest_db1;
DROP DATABASE IF EXISTS mysqltest_db2;
--enable_warnings
--error 0,ER_CANNOT_USER
DROP USER mysqltest_u1;
--error 0,ER_CANNOT_USER
DROP USER mysqltest_u2;

CREATE USER mysqltest_u1@localhost;
CREATE USER mysqltest_u2@localhost;

CREATE DATABASE mysqltest_db1;
CREATE DATABASE mysqltest_db2;

GRANT ALL ON mysqltest_db1.* TO mysqltest_u1@localhost WITH GRANT OPTION;
GRANT ALL ON mysqltest_db2.* TO mysqltest_u2@localhost;

connect (conn1, localhost, mysqltest_u1, , mysqltest_db1);

CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);

# Use view with subquery for better coverage.
CREATE VIEW v1 AS SELECT i FROM t1 WHERE 1 IN (SELECT * FROM t1);

CREATE TABLE t2 (s CHAR(7));
INSERT INTO t2 VALUES ('public');

GRANT SELECT ON v1 TO mysqltest_u2@localhost;
GRANT SELECT ON t2 TO mysqltest_u2@localhost;

connect (conn2, localhost, mysqltest_u2, , mysqltest_db2);

SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2;
PREPARE stmt1 FROM "SELECT * FROM mysqltest_db1.t2";
EXECUTE stmt1;
PREPARE stmt2 FROM "SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2";
EXECUTE stmt2;

connection conn1;
# Make table 't2' private.
REVOKE SELECT ON t2 FROM mysqltest_u2@localhost;
UPDATE t2 SET s = 'private' WHERE s = 'public';

connection conn2;
--error ER_TABLEACCESS_DENIED_ERROR
SELECT * FROM mysqltest_db1.v1, mysqltest_db1.t2;
--error ER_TABLEACCESS_DENIED_ERROR
EXECUTE stmt1;
# Original bug was here: the statement didn't fail.
--error ER_TABLEACCESS_DENIED_ERROR
EXECUTE stmt2;

# Cleanup.
disconnect conn2;
disconnect conn1;
connection default;
REVOKE ALL ON mysqltest_db1.* FROM mysqltest_u1@localhost;
REVOKE ALL ON mysqltest_db2.* FROM mysqltest_u2@localhost;
DROP DATABASE mysqltest_db1;
DROP DATABASE mysqltest_db2;
DROP USER mysqltest_u1@localhost;
DROP USER mysqltest_u2@localhost;

#
# Bug#26813: The SUPER privilege is wrongly required to alter a view created
#            by another user.
#
connection root;
CREATE DATABASE db26813;
USE db26813;
CREATE TABLE t1(f1 INT, f2 INT);
CREATE VIEW v1 AS SELECT f1 FROM t1;
CREATE VIEW v2 AS SELECT f1 FROM t1;
CREATE VIEW v3 AS SELECT f1 FROM t1;
CREATE USER u26813@localhost;
GRANT DROP ON db26813.v1 TO u26813@localhost;
GRANT CREATE VIEW ON db26813.v2 TO u26813@localhost;
GRANT DROP, CREATE VIEW ON db26813.v3 TO u26813@localhost;
GRANT SELECT ON db26813.t1 TO u26813@localhost;

connect (u1,localhost,u26813,,db26813);
connection u1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v1 AS SELECT f2 FROM t1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v2 AS SELECT f2 FROM t1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v3 AS SELECT f2 FROM t1;

connection root;
SHOW CREATE VIEW v3;

DROP USER u26813@localhost;
DROP DATABASE db26813;
disconnect u1;

--echo #
--echo # Bug#29908: A user can gain additional access through the ALTER VIEW.
--echo #
connection root;
CREATE DATABASE mysqltest_29908;
USE mysqltest_29908;
CREATE TABLE t1(f1 INT, f2 INT);
CREATE USER u29908_1@localhost;
CREATE DEFINER = u29908_1@localhost VIEW v1 AS SELECT f1 FROM t1;
CREATE DEFINER = u29908_1@localhost SQL SECURITY INVOKER VIEW v2 AS
  SELECT f1 FROM t1;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v1 TO u29908_1@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_1@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_1@localhost;
CREATE USER u29908_2@localhost;
GRANT DROP, CREATE VIEW ON mysqltest_29908.v1 TO u29908_2@localhost;
GRANT DROP, CREATE VIEW, SHOW VIEW ON mysqltest_29908.v2 TO u29908_2@localhost;
GRANT SELECT ON mysqltest_29908.t1 TO u29908_2@localhost;

connect (u2,localhost,u29908_2,,mysqltest_29908);
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v1 AS SELECT f2 FROM t1;
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;

connect (u1,localhost,u29908_1,,mysqltest_29908);
ALTER VIEW v1 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v1;
ALTER VIEW v2 AS SELECT f2 FROM t1;
SHOW CREATE VIEW v2;

connection root;
ALTER VIEW v1 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v1;
ALTER VIEW v2 AS SELECT f1 FROM t1;
SHOW CREATE VIEW v2;

DROP USER u29908_1@localhost;
DROP USER u29908_2@localhost;
DROP DATABASE mysqltest_29908;
disconnect u1;
disconnect u2;
--echo #######################################################################

#
# BUG#24040: Create View don't succed with "all privileges" on a database.
#

# Prepare.

--disable_warnings
DROP DATABASE IF EXISTS mysqltest1;
DROP DATABASE IF EXISTS mysqltest2;
--enable_warnings

CREATE DATABASE mysqltest1;
CREATE DATABASE mysqltest2;

# Test.

CREATE TABLE mysqltest1.t1(c1 INT);
CREATE TABLE mysqltest1.t2(c2 INT);
CREATE TABLE mysqltest1.t3(c3 INT);
CREATE TABLE mysqltest1.t4(c4 INT);

INSERT INTO mysqltest1.t1 VALUES (11), (12), (13), (14);
INSERT INTO mysqltest1.t2 VALUES (21), (22), (23), (24);
INSERT INTO mysqltest1.t3 VALUES (31), (32), (33), (34);
INSERT INTO mysqltest1.t4 VALUES (41), (42), (43), (44);

GRANT SELECT ON mysqltest1.t1 TO mysqltest_u1@localhost;
GRANT INSERT ON mysqltest1.t2 TO mysqltest_u1@localhost;
GRANT SELECT, UPDATE ON mysqltest1.t3 TO mysqltest_u1@localhost;
GRANT SELECT, DELETE ON mysqltest1.t4 TO mysqltest_u1@localhost;

GRANT ALL PRIVILEGES ON mysqltest2.* TO mysqltest_u1@localhost;

--connect (bug24040_con,localhost,mysqltest_u1,,mysqltest2)
--echo
--echo ---> connection: bug24040_con

SELECT * FROM mysqltest1.t1;
INSERT INTO mysqltest1.t2 VALUES(25);
UPDATE mysqltest1.t3 SET c3 = 331 WHERE c3 = 31;
DELETE FROM mysqltest1.t4 WHERE c4 = 44;

CREATE VIEW v1 AS SELECT * FROM mysqltest1.t1;
CREATE VIEW v2 AS SELECT * FROM mysqltest1.t2;
CREATE VIEW v3 AS SELECT * FROM mysqltest1.t3;
CREATE VIEW v4 AS SELECT * FROM mysqltest1.t4;

SELECT * FROM v1;
INSERT INTO v2 VALUES(26);
UPDATE v3 SET c3 = 332 WHERE c3 = 32;
DELETE FROM v4 WHERE c4 = 43;

--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v12 AS SELECT c1, c2 FROM mysqltest1.t1, mysqltest1.t2;
CREATE VIEW v13 AS SELECT c1, c3 FROM mysqltest1.t1, mysqltest1.t3;
CREATE VIEW v14 AS SELECT c1, c4 FROM mysqltest1.t1, mysqltest1.t4;

--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v21 AS SELECT c2, c1 FROM mysqltest1.t2, mysqltest1.t1;
--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v23 AS SELECT c2, c3 FROM mysqltest1.t2, mysqltest1.t3;
--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v24 AS SELECT c2, c4 FROM mysqltest1.t2, mysqltest1.t4;

CREATE VIEW v31 AS SELECT c3, c1 FROM mysqltest1.t3, mysqltest1.t1;
--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v32 AS SELECT c3, c2 FROM mysqltest1.t3, mysqltest1.t2;
CREATE VIEW v34 AS SELECT c3, c4 FROM mysqltest1.t3, mysqltest1.t4;

CREATE VIEW v41 AS SELECT c4, c1 FROM mysqltest1.t4, mysqltest1.t1;
--error ER_COLUMNACCESS_DENIED_ERROR
CREATE VIEW v42 AS SELECT c4, c2 FROM mysqltest1.t4, mysqltest1.t2;
CREATE VIEW v43 AS SELECT c4, c3 FROM mysqltest1.t4, mysqltest1.t3;

--connection default
--echo
--echo ---> connection: default

SELECT * FROM mysqltest1.t1;
SELECT * FROM mysqltest1.t2;
SELECT * FROM mysqltest1.t3;
SELECT * FROM mysqltest1.t4;

# Cleanup.

-- disconnect bug24040_con

DROP DATABASE mysqltest1;
DROP DATABASE mysqltest2;
DROP USER mysqltest_u1@localhost;

--echo End of 5.0 tests.