diff options
Diffstat (limited to 'mysql-test/suite/roles')
| -rw-r--r-- | mysql-test/suite/roles/acl_statistics.result | 106 | ||||
| -rw-r--r-- | mysql-test/suite/roles/acl_statistics.test | 66 | ||||
| -rw-r--r-- | mysql-test/suite/roles/create_and_drop_role.result | 18 | ||||
| -rw-r--r-- | mysql-test/suite/roles/create_and_drop_role.test | 19 | ||||
| -rw-r--r-- | mysql-test/suite/roles/definer.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/roles/definer.test | 1 |
6 files changed, 211 insertions, 0 deletions
diff --git a/mysql-test/suite/roles/acl_statistics.result b/mysql-test/suite/roles/acl_statistics.result new file mode 100644 index 00000000000..bf74cbf2e85 --- /dev/null +++ b/mysql-test/suite/roles/acl_statistics.result @@ -0,0 +1,106 @@ +SHOW STATUS LIKE 'Acl%'; +Variable_name Value +Acl_column_grants 0 +Acl_database_grants 2 +Acl_function_grants 0 +Acl_procedure_grants 0 +Acl_proxy_users 2 +Acl_role_grants 0 +Acl_roles 0 +Acl_table_grants 0 +Acl_users 4 +SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; +COLUMN_GRANTS +0 +SELECT count(*) DATABASE_GRANTS from mysql.db; +DATABASE_GRANTS +2 +SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION'; +FUNCTION_GRANTS +0 +SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE'; +PROCEDURE_GRANTS +0 +SELECT count(*) PROXY_USERS from mysql.proxies_priv; +PROXY_USERS +2 +SELECT count(*) ROLE_GRANTS from mysql.roles_mapping; +ROLE_GRANTS +0 +SELECT count(*) ROLES from mysql.user where is_role='Y'; +ROLES +0 +SELECT count(*) TABLE_GRANTS from mysql.tables_priv; +TABLE_GRANTS +0 +SELECT count(*) USERS from mysql.user where is_role='N'; +USERS +4 +CREATE USER u1; +CREATE ROLE r1; +CREATE ROLE r2; +GRANT PROXY ON root TO u1; +GRANT SELECT ON *.* to u1; +GRANT SELECT ON *.* to r1; +GRANT DELETE ON mysql.* to u1; +GRANT DELETE ON mysql.* to r1; +GRANT INSERT ON mysql.user to u1; +GRANT INSERT ON mysql.user to r1; +GRANT UPDATE (host) ON mysql.user to u1; +GRANT UPDATE (host) ON mysql.user to r1; +GRANT r1 to u1; +GRANT r2 to r1; +create procedure mysql.test_proc (OUT param1 INT) +begin +select COUNT(*) into param1 from mysql.roles_mapping; +end| +GRANT EXECUTE ON PROCEDURE mysql.test_proc TO r1; +GRANT EXECUTE ON PROCEDURE mysql.test_proc TO u1; +CREATE FUNCTION mysql.test_func (param INT) RETURNS INT +RETURN (SELECT COUNT(*) FROM mysql.user); +GRANT EXECUTE ON FUNCTION mysql.test_func TO r1; +GRANT EXECUTE ON FUNCTION mysql.test_func TO u1; +GRANT EXECUTE ON FUNCTION mysql.test_func TO r2; +SHOW STATUS LIKE 'Acl%'; +Variable_name Value +Acl_column_grants 2 +Acl_database_grants 4 +Acl_function_grants 3 +Acl_procedure_grants 2 +Acl_proxy_users 3 +Acl_role_grants 4 +Acl_roles 2 +Acl_table_grants 2 +Acl_users 5 +SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; +COLUMN_GRANTS +2 +SELECT count(*) DATABASE_GRANTS from mysql.db; +DATABASE_GRANTS +4 +SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION'; +FUNCTION_GRANTS +3 +SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE'; +PROCEDURE_GRANTS +2 +SELECT count(*) PROXY_USERS from mysql.proxies_priv; +PROXY_USERS +3 +SELECT count(*) ROLE_GRANTS from mysql.roles_mapping; +ROLE_GRANTS +4 +SELECT count(*) ROLES from mysql.user where is_role='Y'; +ROLES +2 +SELECT count(*) TABLE_GRANTS from mysql.tables_priv; +TABLE_GRANTS +2 +SELECT count(*) USERS from mysql.user where is_role='N'; +USERS +5 +DROP PROCEDURE mysql.test_proc; +DROP FUNCTION mysql.test_func; +DROP ROLE r2; +DROP ROLE r1; +DROP USER u1; diff --git a/mysql-test/suite/roles/acl_statistics.test b/mysql-test/suite/roles/acl_statistics.test new file mode 100644 index 00000000000..c76d3760959 --- /dev/null +++ b/mysql-test/suite/roles/acl_statistics.test @@ -0,0 +1,66 @@ +# Test case for validating acl statistics for the feedback plugin. +--source include/not_embedded.inc + +# First get a baseline of the initial statistics. +SHOW STATUS LIKE 'Acl%'; +SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; +SELECT count(*) DATABASE_GRANTS from mysql.db; +SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION'; +SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE'; +SELECT count(*) PROXY_USERS from mysql.proxies_priv; +SELECT count(*) ROLE_GRANTS from mysql.roles_mapping; +SELECT count(*) ROLES from mysql.user where is_role='Y'; +SELECT count(*) TABLE_GRANTS from mysql.tables_priv; +SELECT count(*) USERS from mysql.user where is_role='N'; + +# Next add some users, roles and privileges to them. +CREATE USER u1; +CREATE ROLE r1; +CREATE ROLE r2; +GRANT PROXY ON root TO u1; +GRANT SELECT ON *.* to u1; +GRANT SELECT ON *.* to r1; +GRANT DELETE ON mysql.* to u1; +GRANT DELETE ON mysql.* to r1; +GRANT INSERT ON mysql.user to u1; +GRANT INSERT ON mysql.user to r1; +GRANT UPDATE (host) ON mysql.user to u1; +GRANT UPDATE (host) ON mysql.user to r1; + +GRANT r1 to u1; +GRANT r2 to r1; + +delimiter |; +create procedure mysql.test_proc (OUT param1 INT) +begin + select COUNT(*) into param1 from mysql.roles_mapping; +end| +delimiter ;| +GRANT EXECUTE ON PROCEDURE mysql.test_proc TO r1; +GRANT EXECUTE ON PROCEDURE mysql.test_proc TO u1; + +CREATE FUNCTION mysql.test_func (param INT) RETURNS INT + RETURN (SELECT COUNT(*) FROM mysql.user); +GRANT EXECUTE ON FUNCTION mysql.test_func TO r1; +GRANT EXECUTE ON FUNCTION mysql.test_func TO u1; +# Extra grant to differentiate procedure from function grants. +GRANT EXECUTE ON FUNCTION mysql.test_func TO r2; + +# Recheck how statistics are updated. Make sure that both the information +# schema and the actualy physical rows are the same. +SHOW STATUS LIKE 'Acl%'; +SELECT count(*) COLUMN_GRANTS from mysql.columns_priv; +SELECT count(*) DATABASE_GRANTS from mysql.db; +SELECT count(*) FUNCTION_GRANTS from mysql.procs_priv where routine_type='FUNCTION'; +SELECT count(*) PROCEDURE_GRANTS from mysql.procs_priv where routine_type='PROCEDURE'; +SELECT count(*) PROXY_USERS from mysql.proxies_priv; +SELECT count(*) ROLE_GRANTS from mysql.roles_mapping; +SELECT count(*) ROLES from mysql.user where is_role='Y'; +SELECT count(*) TABLE_GRANTS from mysql.tables_priv; +SELECT count(*) USERS from mysql.user where is_role='N'; + +DROP PROCEDURE mysql.test_proc; +DROP FUNCTION mysql.test_func; +DROP ROLE r2; +DROP ROLE r1; +DROP USER u1; diff --git a/mysql-test/suite/roles/create_and_drop_role.result b/mysql-test/suite/roles/create_and_drop_role.result index 2974dd20e22..79c6f412111 100644 --- a/mysql-test/suite/roles/create_and_drop_role.result +++ b/mysql-test/suite/roles/create_and_drop_role.result @@ -46,3 +46,21 @@ Note 1449 The user specified as a definer ('u1'@'%') does not exist create user foo@bar; drop user foo@bar; drop role r1; +CREATE USER u1; +CREATE ROLE r1; +CREATE USER r1@localhost; +CREATE ROLE r2; +GRANT r2 to r1; +GRANT r2 to r1@localhost; +DROP ROLE r1; +SELECT * FROM mysql.roles_mapping; +Host User Role Admin_option +localhost r1 r2 N +localhost root r2 Y +SHOW GRANTS FOR r1@localhost; +Grants for r1@localhost +GRANT r2 TO 'r1'@'localhost' +GRANT USAGE ON *.* TO 'r1'@'localhost' +DROP USER u1; +DROP ROLE r2; +DROP USER r1@localhost; diff --git a/mysql-test/suite/roles/create_and_drop_role.test b/mysql-test/suite/roles/create_and_drop_role.test index 38f040789e0..0bf5b744e6b 100644 --- a/mysql-test/suite/roles/create_and_drop_role.test +++ b/mysql-test/suite/roles/create_and_drop_role.test @@ -68,3 +68,22 @@ create user foo@bar; drop user foo@bar; drop role r1; +# +# MDEV-7774 Assertion `status == 0' fails when dropping in this order: +# +CREATE USER u1; +CREATE ROLE r1; +CREATE USER r1@localhost; +CREATE ROLE r2; +GRANT r2 to r1; +GRANT r2 to r1@localhost; +# MDEV-7774: Dropping in this order caused the crash. +DROP ROLE r1; +--sorted_result +SELECT * FROM mysql.roles_mapping; +SHOW GRANTS FOR r1@localhost; # Related to MDEV-7774, also caused a crash, by + # not updating the internal acl_roles_mapping + # data structure correctly; +DROP USER u1; +DROP ROLE r2; +DROP USER r1@localhost; diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result index 0010853be78..a2d84244c38 100644 --- a/mysql-test/suite/roles/definer.result +++ b/mysql-test/suite/roles/definer.result @@ -86,6 +86,7 @@ Warnings: Note 1449 The user specified as a definer ('role4'@'') does not exist select * from test.v5; ERROR HY000: The user specified as a definer ('role4'@'') does not exist +flush tables; show create view test.v5; View Create View character_set_client collation_connection v5 CREATE ALGORITHM=UNDEFINED DEFINER=`role4`@`%` SQL SECURITY DEFINER VIEW `test`.`v5` AS select (`mysqltest1`.`t1`.`a` + `mysqltest1`.`t1`.`b`) AS `a+b`,`mysqltest1`.`t1`.`c` AS `c` from `mysqltest1`.`t1` latin1 latin1_swedish_ci diff --git a/mysql-test/suite/roles/definer.test b/mysql-test/suite/roles/definer.test index 3c069105c8c..995c36a8511 100644 --- a/mysql-test/suite/roles/definer.test +++ b/mysql-test/suite/roles/definer.test @@ -115,6 +115,7 @@ open(F, '>', $f) or die "open(>$f): $!"; syswrite F, $_ or die "syswrite($f): $!" EOF +flush tables; show create view test.v5; select * from test.v5; drop user role4; |