view_grant.test

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

--disable_warnings
create database mysqltest;
--enable_warnings

create table mysqltest.t1 (a int, b int);

grant select on mysqltest.t1 to mysqltest_1@localhost;
grant create view,select on test.* to mysqltest_1@localhost;

connection user1;

create view v1 as select * from mysqltest.t1;

connection root;
# check view definer information
show create view v1;
revoke select on mysqltest.t1 from mysqltest_1@localhost;
-- error ER_VIEW_INVALID
select * from v1;
grant select on mysqltest.t1 to mysqltest_1@localhost;
select * from v1;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop view v1;
drop database mysqltest;

#
# rights on execution of view underlying functiond (BUG#9505)
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
create table t1 (a int);
insert into t1 values (1);
create table t2 (s1 int);
--disable_warnings
drop function if exists f2;
--enable_warnings
delimiter //;
create function f2 () returns int begin declare v int; select s1 from t2
into v; return v; end//
delimiter ;//
create algorithm=TEMPTABLE view v1 as select f2() from t1;
create algorithm=MERGE view v2 as select f2() from t1;
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
create SQL SECURITY INVOKER view v5 as select * from v4;
grant select on v1 to mysqltest_1@localhost;
grant select on v2 to mysqltest_1@localhost;
grant select on v3 to mysqltest_1@localhost;
grant select on v4 to mysqltest_1@localhost;
grant select on v5 to mysqltest_1@localhost;

connection user1;
use mysqltest;
select * from v1;
select * from v2;
-- error ER_VIEW_INVALID
select * from v3;
-- error ER_VIEW_INVALID
select * from v4;
-- error ER_VIEW_INVALID
select * from v5;
use test;

connection root;
drop view v1, v2, v3, v4, v5;
drop function f2;
drop table t1, t2;
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# revertion of previous test, definer of view lost his/her rights to execute
# function
#

connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
create table t1 (a int);
insert into t1 values (1);
create table t2 (s1 int);
--disable_warnings
drop function if exists f2;
--enable_warnings
delimiter //;
create function f2 () returns int begin declare v int; select s1 from t2
into v; return v; end//
delimiter ;//
grant select on t1 to mysqltest_1@localhost;
grant execute on function f2 to mysqltest_1@localhost;
grant create view on mysqltest.* to mysqltest_1@localhost;

connection user1;
use mysqltest;
create algorithm=TEMPTABLE view v1 as select f2() from t1;
create algorithm=MERGE view v2 as select f2() from t1;
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select f2() from t1;
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select f2() from t1;
use test;

connection root;
create view v5 as select * from v1;
revoke execute on function f2 from mysqltest_1@localhost;
-- error ER_VIEW_INVALID
select * from v1;
-- error ER_VIEW_INVALID
select * from v2;
select * from v3;
select * from v4;
-- error ER_VIEW_INVALID
select * from v5;

drop view v1, v2, v3, v4, v5;
drop function f2;
drop table t1, t2;
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# definer/invoker rights for columns
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
create table t1 (a int);
create table v1 (a int);
insert into t1 values (1);
grant select on t1 to mysqltest_1@localhost;
grant select on v1 to mysqltest_1@localhost;
grant create view on mysqltest.* to mysqltest_1@localhost;
drop table v1;

connection user1;
use mysqltest;
create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
create algorithm=MERGE view v2 as select *, a as b from t1;
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
create view v5 as select * from v1;
use test;

connection root;
revoke select on t1 from mysqltest_1@localhost;
-- error ER_VIEW_INVALID
select * from v1;
-- error ER_VIEW_INVALID
select * from v2;
select * from v3;
select * from v4;
-- error ER_VIEW_INVALID
select * from v5;

#drop view v1, v2, v3, v4, v5;
drop table t1;
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;


connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
create table t1 (a int);
insert into t1 values (1);
create algorithm=TEMPTABLE view v1 as select *, a as b from t1;
create algorithm=MERGE view v2 as select *, a as b from t1;
create algorithm=TEMPTABLE SQL SECURITY INVOKER view v3 as select *, a as b from t1;
create algorithm=MERGE SQL SECURITY INVOKER view v4 as select *, a as b from t1;
create SQL SECURITY INVOKER view v5 as select * from v4;
grant select on v1 to mysqltest_1@localhost;
grant select on v2 to mysqltest_1@localhost;
grant select on v3 to mysqltest_1@localhost;
grant select on v4 to mysqltest_1@localhost;
grant select on v5 to mysqltest_1@localhost;

connection user1;
use mysqltest;
select * from v1;
select * from v2;
-- error ER_VIEW_INVALID
select * from v3;
-- error ER_VIEW_INVALID
select * from v4;
-- error ER_VIEW_INVALID
select * from v5;
use test;

connection root;
drop view v1, v2, v3, v4, v5;
drop table t1;
use test;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_1@localhost;
drop database mysqltest;

#
# BUG#14256: definer in view definition is not fully qualified
#
--disable_warnings
drop view if exists v1;
--enable_warnings

# Backup anonymous users and remove them. (They get in the way of
# the one we test with here otherwise.)
create table t1 as select * from mysql.user where user='';
delete from mysql.user where user='';
flush privileges;

# Create the test user
grant all on test.* to 'test14256'@'%';

connect (test14256,localhost,test14256,,test);
connection test14256;
use test;

create view v1 as select 42;
show create view v1;

select definer into @v1def1 from information_schema.views
  where table_schema = 'test' and table_name='v1';
drop view v1;

create definer=`test14256`@`%` view v1 as select 42;
show create view v1;

select definer into @v1def2 from information_schema.views
  where table_schema = 'test' and table_name='v1';
drop view v1;

select @v1def1, @v1def2, @v1def1=@v1def2;

connection root;
drop user test14256;

# Restore the anonymous users.
insert into mysql.user select * from t1;
flush privileges;

drop table t1;

#
# BUG#14726: freeing stack variable in case of an error of opening
# a view when we have locked tables with LOCK TABLES statement.
#
connection root;
--disable_warnings
create database mysqltest;
--enable_warnings

use mysqltest;
CREATE TABLE t1 (i INT);
CREATE VIEW  v1 AS SELECT * FROM t1;
SHOW CREATE VIEW v1;
GRANT SELECT, LOCK TABLES ON mysqltest.* TO mysqltest_1@localhost;

connection user1;

use mysqltest;
LOCK TABLES v1 READ;
-- error ER_TABLEACCESS_DENIED_ERROR
SHOW CREATE TABLE v1;
UNLOCK TABLES;
use test;

connection root;
use test;
drop user mysqltest_1@localhost;
drop database mysqltest;

#
# switch to default connaction
#
disconnect user1;
disconnect root;
connection default;

#
# DEFINER information check
#
create definer=some_user@`` sql security invoker view v1 as select 1;
create definer=some_user@localhost sql security invoker view v2 as select 1;
show create view v1;
show create view v2;
drop view v1;
drop view v2;

#
# Bug#18681: View privileges are broken
#
CREATE DATABASE mysqltest1;
CREATE USER readonly@localhost;
CREATE TABLE mysqltest1.t1 (x INT);
INSERT INTO mysqltest1.t1 VALUES (1), (2);
CREATE SQL SECURITY INVOKER VIEW mysqltest1.v_t1 AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ts AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_ti AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tu AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tus AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_td AS SELECT * FROM mysqltest1.t1;
CREATE SQL SECURITY DEFINER VIEW mysqltest1.v_tds AS SELECT * FROM mysqltest1.t1;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysqltest1.v_t1 TO readonly@localhost;
GRANT SELECT ON mysqltest1.v_ts TO readonly@localhost;
GRANT INSERT ON mysqltest1.v_ti TO readonly@localhost;
GRANT UPDATE ON mysqltest1.v_tu TO readonly@localhost;
GRANT UPDATE,SELECT ON mysqltest1.v_tus TO readonly@localhost;
GRANT DELETE ON mysqltest1.v_td TO readonly@localhost;
GRANT DELETE,SELECT ON mysqltest1.v_tds TO readonly@localhost;

CONNECT (n1,localhost,readonly,,);
CONNECTION n1;

--error 1356
SELECT * FROM mysqltest1.v_t1;
--error 1356
INSERT INTO mysqltest1.v_t1 VALUES(4);
--error 1356
DELETE FROM mysqltest1.v_t1 WHERE x = 1;
--error 1356
UPDATE mysqltest1.v_t1 SET x = 3 WHERE x = 2;
--error 1356
UPDATE mysqltest1.v_t1 SET x = 3;
--error 1356
DELETE FROM mysqltest1.v_t1;
--error 1356
SELECT 1 FROM mysqltest1.v_t1;
--error 1142
SELECT * FROM mysqltest1.t1;

SELECT * FROM mysqltest1.v_ts;
--error 1142
SELECT * FROM mysqltest1.v_ts, mysqltest1.t1 WHERE mysqltest1.t1.x = mysqltest1.v_ts.x;
--error 1142
SELECT * FROM mysqltest1.v_ti;

--error 1142
INSERT INTO mysqltest1.v_ts VALUES (100);
INSERT INTO mysqltest1.v_ti VALUES (100);

--error 1142
UPDATE mysqltest1.v_ts SET x= 200 WHERE x = 100;
--error 1142
UPDATE mysqltest1.v_ts SET x= 200;
UPDATE mysqltest1.v_tu SET x= 200 WHERE x = 100;
UPDATE mysqltest1.v_tus SET x= 200 WHERE x = 100;
UPDATE mysqltest1.v_tu SET x= 200;

--error 1142
DELETE FROM mysqltest1.v_ts WHERE x= 200;
--error 1142
DELETE FROM mysqltest1.v_ts;
--error 1143
DELETE FROM mysqltest1.v_td WHERE x= 200;
DELETE FROM mysqltest1.v_tds WHERE x= 200;
DELETE FROM mysqltest1.v_td;

CONNECTION default;
DROP VIEW mysqltest1.v_tds;
DROP VIEW mysqltest1.v_td;
DROP VIEW mysqltest1.v_tus;
DROP VIEW mysqltest1.v_tu;
DROP VIEW mysqltest1.v_ti;
DROP VIEW mysqltest1.v_ts;
DROP VIEW mysqltest1.v_t1;
DROP TABLE mysqltest1.t1;
DROP USER readonly@localhost;
DROP DATABASE mysqltest1;

#
# BUG#14875: Bad view DEFINER makes SHOW CREATE VIEW fail
#
CREATE TABLE t1 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1), (2), (3);
CREATE DEFINER = 'no-such-user'@localhost VIEW v AS SELECT a from t1;
#--warning 1448
SHOW CREATE VIEW v;
--error 1449
SELECT * FROM v;
DROP VIEW v;
DROP TABLE t1;
USE test;

#