Debian Codegraph @

mysql-test/suite/sysschema/t/v_privileges_by_table_by_level.test

raw
1 -- source include/not_embedded.inc
2 -- source include/hostname_is_not_localhost.inc
3 # Tests for sys schema
4 # Verify the sys.privileges_by_table_by_level view
5
6 # Ensure structure changes don't slip in
7 DESC sys.privileges_by_table_by_level;
8
9 CREATE DATABASE test1;
10 USE test1;
11 CREATE TABLE t1 (a int);
12 CREATE USER test1_user;
13
14 GRANT ALL PRIVILEGES ON test1.* TO test1_user;
15
16 --echo # Grant all table privileges
17 GRANT ALL ON t1 to test1_user;
18 SELECT table_schema, table_name,
19 CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END,
20 privilege, level
21 FROM sys.privileges_by_table_by_level WHERE table_name='t1';
22
23 --echo # Revoke some table privileges
24 REVOKE REFERENCES, DELETE, ALTER, DROP ON t1 FROM test1_user;
25 SELECT table_schema, table_name,
26 CASE WHEN grantee LIKE '\'root%' THEN '<some-root-user>' ELSE GRANTEE END,
27 privilege, level
28 FROM sys.privileges_by_table_by_level WHERE table_name='t1';
29
30 CREATE ROLE test1_role;
31 GRANT SELECT, UPDATE, DELETE, DROP, INDEX ON t1 to test1_role;
32
33 --echo # Must show both the user and the role
34 SELECT * FROM sys.privileges_by_table_by_level
35 WHERE table_schema='test1' AND table_name='t1' AND grantee LIKE '%test1%';
36
37 CREATE VIEW v1 AS SELECT * FROM t1;
38 SELECT * FROM sys.privileges_by_table_by_level
39 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
40
41 GRANT SELECT ON v1 TO test1_role;
42 SELECT * FROM sys.privileges_by_table_by_level
43 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
44
45 GRANT ALL ON v1 TO test1_user;
46 SELECT * FROM sys.privileges_by_table_by_level
47 WHERE table_schema='test1' AND table_name='v1' AND grantee LIKE '%test1%';
48
49 DROP TABLE t1;
50 DROP VIEW v1;
51 DROP USER test1_user;
52 DROP ROLE test1_role;
53 DROP DATABASE test1;
54
55