Debian Codegraph @

mysql-test/suite/roles/acl_statistics.test

raw
1 # Test case for validating acl statistics for the feedback plugin.
2 --source include/not_embedded.inc
3 --source include/hostname_is_not_localhost.inc
4
5 # First get a baseline of the initial statistics.
6 SHOW STATUS LIKE 'Acl%';
7 SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
8 SELECT count(*) DATABASE_GRANTS from mysql.db;
9 SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
10 SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
11 SELECT count(*) PROXY_USERS from mysql.proxies_priv;
12 SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
13 SELECT count(*) ROLES from mysql.user where is_role='Y';
14 SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
15 SELECT count(*) USERS from mysql.user where is_role='N';
16
17 # Next add some users, roles and privileges to them.
18 CREATE USER u1;
19 CREATE ROLE r1;
20 CREATE ROLE r2;
21 GRANT PROXY ON root TO u1;
22 GRANT SELECT ON *.* to u1;
23 GRANT SELECT ON *.* to r1;
24 GRANT DELETE ON mysql.* to u1;
25 GRANT DELETE ON mysql.* to r1;
26 GRANT INSERT ON mysql.user to u1;
27 GRANT INSERT ON mysql.user to r1;
28 GRANT UPDATE (host) ON mysql.user to u1;
29 GRANT UPDATE (host) ON mysql.user to r1;
30
31 GRANT r1 to u1;
32 GRANT r2 to r1;
33
34 delimiter |;
35 create procedure mysql.test_proc (OUT param1 INT)
36 begin
37 select COUNT(*) into param1 from mysql.roles_mapping;
38 end|
39 delimiter ;|
40 GRANT EXECUTE ON PROCEDURE mysql.test_proc TO r1;
41 GRANT EXECUTE ON PROCEDURE mysql.test_proc TO u1;
42
43 CREATE FUNCTION mysql.test_func (param INT) RETURNS INT
44 RETURN (SELECT COUNT(*) FROM mysql.user);
45 GRANT EXECUTE ON FUNCTION mysql.test_func TO r1;
46 GRANT EXECUTE ON FUNCTION mysql.test_func TO u1;
47 # Extra grant to differentiate procedure from function grants.
48 GRANT EXECUTE ON FUNCTION mysql.test_func TO r2;
49
50 # Recheck how statistics are updated. Make sure that both the information
51 # schema and the actualy physical rows are the same.
52 SHOW STATUS LIKE 'Acl%';
53 SELECT count(*) COLUMN_GRANTS from mysql.columns_priv;
54 SELECT count(*) DATABASE_GRANTS from mysql.db;
55 SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION';
56 SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE';
57 SELECT count(*) PROXY_USERS from mysql.proxies_priv;
58 SELECT count(*) ROLE_GRANTS from mysql.roles_mapping;
59 SELECT count(*) ROLES from mysql.user where is_role='Y';
60 SELECT count(*) TABLE_GRANTS from mysql.tables_priv;
61 SELECT count(*) USERS from mysql.user where is_role='N';
62
63 DROP PROCEDURE mysql.test_proc;
64 DROP FUNCTION mysql.test_func;
65 DROP ROLE r2;
66 DROP ROLE r1;
67 DROP USER u1;