195 lines
8.3 KiB
Plaintext
195 lines
8.3 KiB
Plaintext
#
|
|
# WL#11864 Implement I_S.VIEW_TABLE_USAGE and I_S.VIEW_ROUTINE_USAGE
|
|
#
|
|
# Basic schema definition used by test cases.
|
|
CREATE DATABASE db1;
|
|
USE db1;
|
|
CREATE TABLE t1 (c1 INT);
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 1;
|
|
CREATE FUNCTION f2() RETURNS INT RETURN 2;
|
|
CREATE VIEW v1 AS SELECT f1() AS f1;
|
|
CREATE VIEW v2 AS SELECT f1, f2() AS f2 FROM v1;
|
|
CREATE VIEW v3 AS SELECT c1, f2 FROM t1, v2;
|
|
# Case 1: Show table and views used by view v2 and v3.
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v2 def db1 v1
|
|
def db1 v3 def db1 t1
|
|
def db1 v3 def db1 v2
|
|
# Case 2: Show routines used by views.
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
def db1 v1 def db1 f1
|
|
def db1 v2 def db1 f2
|
|
# Case 3: Rename the column of t1 and list view dependency.
|
|
# The query should throw warning on view v3.
|
|
ALTER TABLE t1 RENAME COLUMN c1 TO c2;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' AND VIEW_NAME='v3';
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v3 def db1 t1
|
|
def db1 v3 def db1 v2
|
|
Warnings:
|
|
Warning 1356 View 'db1.v3' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
ALTER TABLE t1 RENAME COLUMN c2 TO c1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' AND VIEW_NAME='v3';
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v3 def db1 t1
|
|
def db1 v3 def db1 v2
|
|
# Case 4: Drop function f1 and list dependency.
|
|
# The query should throw warning on view v1.
|
|
DROP FUNCTION f1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='v1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
Warnings:
|
|
Warning 1356 View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
CREATE FUNCTION f1() RETURNS INT return 1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1' AND TABLE_NAME='v1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
def db1 v1 def db1 f1
|
|
# Case 5: Access checks.
|
|
CREATE USER 'testuser'@'localhost';
|
|
# 5.1 Grant permission on v2, but not v1. VIEW_TABLE_USAGE will not
|
|
# list any entry because user has no permission on v1.
|
|
GRANT ALL ON db1.v2 TO 'testuser'@'localhost';
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
# 5.2 Give permission on v1 and that VIEW_TABLE_USAGE shows v2 entry.
|
|
GRANT ALL ON db1.v1 TO 'testuser'@'localhost';
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v2 def db1 v1
|
|
# 5.3 VIEW_ROUTINE_USAGE does not list v1, as it have no grant on f1.
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
# 5.4 Grant access to f1 and check that VIEW_ROUTINE_USAGE shows it.
|
|
GRANT EXECUTE ON FUNCTION db1.f1 TO 'testuser'@'localhost';
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
def db1 v1 def db1 f1
|
|
# 5.5 Grant access on v3, see that dependency 't1' is not listed.
|
|
GRANT ALL ON db1.v3 TO 'testuser'@'localhost';
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v2 def db1 v1
|
|
def db1 v3 def db1 v2
|
|
# 5.6 Grant access on t1, see that dependency 't1' is listed.
|
|
GRANT SELECT on db1.t1 TO 'testuser'@'localhost';
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v2 def db1 v1
|
|
def db1 v3 def db1 t1
|
|
def db1 v3 def db1 v2
|
|
DROP USER 'testuser'@'localhost';
|
|
# Cleanup
|
|
DROP FUNCTION f1;
|
|
DROP FUNCTION f2;
|
|
DROP VIEW v1, v2, v3;
|
|
DROP TABLE t1;
|
|
# Case 6:
|
|
# 6.1 Rename table t1 to t2 and list view dependency.
|
|
# The query should throw warning on view v1.
|
|
CREATE TABLE t1 (c1 INT);
|
|
CREATE VIEW v1 AS SELECT c1 FROM t1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
RENAME TABLE t1 to t2;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
Warnings:
|
|
Warning 1356 View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
RENAME TABLE t2 to t1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
# 6.2 Drop table t1 and list view dependency.
|
|
DROP TABLE t1;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
Warnings:
|
|
Warning 1356 View 'db1.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
DROP VIEW v1;
|
|
# Case 7: List dependent table and function names
|
|
# that are permitted to the current user.
|
|
CREATE USER testuser1@localhost;
|
|
GRANT CREATE ON db1.* TO testuser1@localhost;
|
|
GRANT SELECT ON db1.* TO testuser1@localhost;
|
|
GRANT CREATE VIEW ON db1.* TO testuser1@localhost;
|
|
GRANT CREATE ROUTINE ON db1.* TO testuser1@localhost;
|
|
GRANT DROP ON db1.* TO testuser1@localhost;
|
|
CREATE USER testuser2@localhost;
|
|
GRANT CREATE ON db1.* TO testuser2@localhost;
|
|
GRANT SELECT ON db1.* TO testuser2@localhost;
|
|
GRANT CREATE VIEW ON db1.* TO testuser2@localhost;
|
|
GRANT CREATE ROUTINE ON db1.* TO testuser2@localhost;
|
|
GRANT DROP ON db1.* TO testuser2@localhost;
|
|
CREATE TABLE t1(c1 INT);
|
|
CREATE VIEW v1 AS SELECT c1 FROM t1;
|
|
CREATE FUNCTION f1() RETURNS INT RETURN 1;
|
|
CREATE VIEW v2 AS SELECT f1();
|
|
CREATE TABLE t2(c1 INT);
|
|
CREATE VIEW v3 AS SELECT c1 FROM t2;
|
|
CREATE FUNCTION f2() RETURNS INT RETURN 1;
|
|
CREATE VIEW v4 AS SELECT f2();
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
def db1 v2 def db1 f1
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v3 def db1 t2
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_ROUTINE_USAGE
|
|
WHERE TABLE_SCHEMA='db1';
|
|
TABLE_CATALOG TABLE_SCHEMA TABLE_NAME SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME
|
|
def db1 v4 def db1 f2
|
|
DROP VIEW v1,v2;
|
|
DROP FUNCTION f1;
|
|
DROP TABLE t1;
|
|
DROP VIEW v3,v4;
|
|
DROP FUNCTION f2;
|
|
DROP TABLE t2;
|
|
DROP USER testuser1@localhost;
|
|
DROP USER testuser2@localhost;
|
|
# Case 8: Revoke view privilege
|
|
CREATE USER testuser1@localhost;
|
|
CREATE TABLE t1(c1 INT);
|
|
CREATE VIEW v1 AS SELECT c1 FROM t1;
|
|
GRANT ALL ON db1.v1 TO testuser1@localhost;
|
|
GRANT ALL ON db1.t1 TO testuser1@localhost;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
def db1 v1 def db1 t1
|
|
REVOKE ALL ON db1.v1 FROM testuser1@localhost;
|
|
SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
|
|
WHERE VIEW_SCHEMA='db1' ORDER BY VIEW_NAME, TABLE_NAME;
|
|
VIEW_CATALOG VIEW_SCHEMA VIEW_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
|
|
REVOKE ALL ON db1.t1 FROM testuser1@localhost;
|
|
DROP USER testuser1@localhost;
|
|
DROP VIEW v1;
|
|
DROP TABLE t1;
|
|
DROP DATABASE db1;
|