diff options
Diffstat (limited to 'mysql-test/suite/roles')
43 files changed, 1286 insertions, 119 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/admin.result b/mysql-test/suite/roles/admin.result index ad3d4125252..838f2ea165d 100644 --- a/mysql-test/suite/roles/admin.result +++ b/mysql-test/suite/roles/admin.result @@ -1,3 +1,4 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; create role role1; create role role2 with admin current_user; @@ -72,13 +73,13 @@ show grants for role4; Grants for role4 GRANT USAGE ON *.* TO 'role4' select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -role1 role2 NO -role1 role3 YES -role3 role4 YES -root@localhost role1 YES -root@localhost role2 YES -root@localhost role4 YES +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +role1 role3 YES NULL +role3 role4 YES NULL +root@localhost role1 YES NO +root@localhost role2 YES NO +root@localhost role4 YES NO grant role2 to role1 with admin option; revoke role1 from foo@localhost; revoke admin option for role4 from role3; @@ -131,13 +132,13 @@ show grants for role4; Grants for role4 GRANT USAGE ON *.* TO 'role4' select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -role1 role2 YES -role1 role3 YES -role3 role4 NO -root@localhost role1 NO -root@localhost role2 YES -root@localhost role4 YES +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 YES NULL +role1 role3 YES NULL +role3 role4 NO NULL +root@localhost role1 NO NO +root@localhost role2 YES NO +root@localhost role4 YES NO grant role1 to role4; ERROR 28000: Access denied for user 'root'@'localhost' grant role1 to role4 with admin option; diff --git a/mysql-test/suite/roles/admin.test b/mysql-test/suite/roles/admin.test index 03f3449200b..242518eb13d 100644 --- a/mysql-test/suite/roles/admin.test +++ b/mysql-test/suite/roles/admin.test @@ -1,5 +1,6 @@ source include/not_embedded.inc; +create user foo@localhost; grant create user on *.* to foo@localhost; ######################################## @@ -99,4 +100,3 @@ grant role4 to current_user; ######################################## drop role role1, role2, role3, role4, role5, role6; drop user foo@localhost; - diff --git a/mysql-test/suite/roles/create_and_drop_current.result b/mysql-test/suite/roles/create_and_drop_current.result index 382e5d10248..7e847677364 100644 --- a/mysql-test/suite/roles/create_and_drop_current.result +++ b/mysql-test/suite/roles/create_and_drop_current.result @@ -1,3 +1,4 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; create user current_user; ERROR HY000: Operation CREATE USER failed for CURRENT_USER diff --git a/mysql-test/suite/roles/create_and_drop_current.test b/mysql-test/suite/roles/create_and_drop_current.test index 5250ef78f43..7ca8161a30c 100644 --- a/mysql-test/suite/roles/create_and_drop_current.test +++ b/mysql-test/suite/roles/create_and_drop_current.test @@ -12,6 +12,7 @@ --source include/not_embedded.inc +create user foo@localhost; grant create user on *.* to foo@localhost; --change_user foo diff --git a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result index 2d5891f996e..27149c4bf17 100644 --- a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result +++ b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.result @@ -1,5 +1,7 @@ use mysql; alter table user drop column is_role; +alter table user drop column default_role; +alter table user drop column max_statement_time; flush privileges; create role test_role; ERROR HY000: Column count of mysql.user is wrong. Expected 44, found 43. Created with MariaDB MYSQL_VERSION_ID, now running MYSQL_VERSION_ID. Please use mysql_upgrade to fix this error. @@ -8,6 +10,18 @@ ERROR HY000: Operation DROP ROLE failed for 'test_role' alter table user add column is_role enum('N', 'Y') default 'N' not null COLLATE utf8_general_ci after password_expired; +create role test_role; +create user test_user@localhost; +grant test_role to test_user@localhost; +set default role test_role for root@localhost; +ERROR HY000: Column count of mysql.user is wrong. Expected 45, found 44. Created with MariaDB MYSQL_VERSION_ID, now running MYSQL_VERSION_ID. Please use mysql_upgrade to fix this error. +drop role test_role; +drop user test_user@localhost; +alter table user add column default_role char(80) binary default '' not null +COLLATE utf8_general_ci +after is_role; +alter table user add max_statement_time decimal(12,6) default 0 not null +after default_role; update user set is_role='N'; flush privileges; create role test_role; diff --git a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test index fddab717245..ebd75c34ca1 100644 --- a/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test +++ b/mysql-test/suite/roles/create_and_drop_role_invalid_user_table.test @@ -1,9 +1,18 @@ +# +# Test that SET DEFAULT ROLE doesn't work on old privilege tables +# that don't have 'default_role' column +# source include/not_embedded.inc; connect (mysql, localhost, root,,); use mysql; +# +# downgrade the table to pre-default-role structure +# alter table user drop column is_role; +alter table user drop column default_role; +alter table user drop column max_statement_time; flush privileges; @@ -16,11 +25,24 @@ alter table user add column is_role enum('N', 'Y') default 'N' not null COLLATE utf8_general_ci after password_expired; -update user set is_role='N'; - -flush privileges; +# Test default role column create role test_role; +create user test_user@localhost; +grant test_role to test_user@localhost; +--replace_regex /10\d\d\d\d/MYSQL_VERSION_ID/ +--error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE +set default role test_role for root@localhost; drop role test_role; +drop user test_user@localhost; +alter table user add column default_role char(80) binary default '' not null + COLLATE utf8_general_ci +after is_role; +alter table user add max_statement_time decimal(12,6) default 0 not null +after default_role; +update user set is_role='N'; +flush privileges; +create role test_role; +drop role test_role; diff --git a/mysql-test/suite/roles/definer.result b/mysql-test/suite/roles/definer.result index f11b5565ffe..f2fca91f0ea 100644 --- a/mysql-test/suite/roles/definer.result +++ b/mysql-test/suite/roles/definer.result @@ -9,6 +9,7 @@ grant role1 to current_user; create role role2; grant insert,select on mysqltest1.t1 to role2; grant event,execute,trigger on mysqltest1.* to role2; +create user foo@localhost; grant create view on mysqltest1.* to foo@localhost; create role role4; grant select on mysqltest1.t1 to role4; @@ -78,6 +79,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 +create user role4; grant select on mysqltest1.t1 to role4; show create view test.v5; View Create View character_set_client collation_connection @@ -86,6 +88,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 @@ -103,7 +106,7 @@ create definer=current_role trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333); show create trigger tr1; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr1 CREATE DEFINER=`role1` trigger tr1 before insert on t2 for each row +tr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci set role none; insert t2 values (11,22,33); @@ -120,7 +123,7 @@ create definer=role2 trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333); show create trigger tr2; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr2 CREATE DEFINER=`role2` trigger tr2 before delete on t2 for each row +tr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci delete from t2 where a=1; select * from t1; @@ -138,7 +141,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create trigger tr3; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr3 CREATE DEFINER=`role3`@`%` trigger tr3 before update on t2 for each row +tr3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` trigger tr3 before update on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci update t2 set b=2 where a=2; ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -152,7 +155,7 @@ a b c flush tables; show create trigger tr2; Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation -tr2 CREATE DEFINER=`role2`@`` trigger tr2 before delete on t2 for each row +tr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2`@`` trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci delete from t2 where a=2; ERROR HY000: The user specified as a definer ('role2'@'%') does not exist @@ -169,7 +172,7 @@ set role role1; create definer=current_role procedure pr1() insert t1 values (111, 222, 333); show create procedure pr1; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr1 CREATE DEFINER=`role1` PROCEDURE `pr1`() +pr1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr1`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci set role none; call pr1(); @@ -181,7 +184,7 @@ a b c create definer=role2 procedure pr2() insert t1 values (111, 222, 333); show create procedure pr2; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr2 CREATE DEFINER=`role2` PROCEDURE `pr2`() +pr2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` PROCEDURE `pr2`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci call pr2(); select * from t1; @@ -195,7 +198,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create procedure pr3; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr3 CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() +pr3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() insert t1 values (111, 222, 333) latin1 latin1_swedish_ci latin1_swedish_ci call pr3(); ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -212,7 +215,7 @@ set role role1; create definer=current_role function fn1() returns int return (select sum(a+b) from t1); show create function fn1; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn1 CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) +fn1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci set role none; select fn1(); @@ -224,7 +227,7 @@ a b c create definer=role2 function fn2() returns int return (select sum(a+b) from t1); show create function fn2; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn2 CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) +fn2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci select fn2(); fn2() @@ -234,7 +237,7 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create function fn3; Function sql_mode Create Function character_set_client collation_connection Database Collation -fn3 CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) +fn3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) return (select sum(a+b) from t1) latin1 latin1_swedish_ci latin1_swedish_ci select fn3(); ERROR HY000: The user specified as a definer ('role3'@'%') does not exist @@ -247,7 +250,7 @@ create definer=current_role event e1 on schedule every 1 second starts '2000-01- insert t1 values (111, 2, 0); show create event e1; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e1 SYSTEM CREATE DEFINER=`role1` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e1 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role1` EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) latin1 latin1_swedish_ci latin1_swedish_ci set role none; create definer=role3 event e3 on schedule every 1 second starts '2000-01-01' do insert t1 values (111, 3, 0); @@ -255,12 +258,12 @@ Warnings: Note 1449 The user specified as a definer ('role3'@'%') does not exist show create event e3; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e3 SYSTEM CREATE DEFINER=`role3`@`%` EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e3 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role3`@`%` EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) latin1 latin1_swedish_ci latin1_swedish_ci create definer=role2 event e2 on schedule every 1 second starts '2000-01-01' do insert t1 values (111, 4, 0); show create event e2; Event sql_mode time_zone Create Event character_set_client collation_connection Database Collation -e2 SYSTEM CREATE DEFINER=`role2` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) latin1 latin1_swedish_ci latin1_swedish_ci +e2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SYSTEM CREATE DEFINER=`role2` EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) latin1 latin1_swedish_ci latin1_swedish_ci set global event_scheduler=off; select distinct * from t1; a b c @@ -336,7 +339,7 @@ INSERT INTO `t2` VALUES (2,20,200); /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`role1`*/ /*!50003 trigger tr1 before insert on t2 for each row insert t1 values (111, 222, 333) */;; @@ -352,7 +355,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`role3`@`%`*/ /*!50003 trigger tr3 before update on t2 for each row insert t1 values (111, 222, 333) */;; @@ -368,7 +371,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50017 DEFINER=`role2`@``*/ /*!50003 trigger tr2 before delete on t2 for each row insert t1 values (111, 222, 333) */;; @@ -386,7 +389,7 @@ DELIMITER ;; /*!50003 SET character_set_results = latin1 */ ;; /*!50003 SET collation_connection = latin1_swedish_ci */ ;; /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; -/*!50003 SET sql_mode = '' */ ;; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role1`*/ /*!50106 EVENT `e1` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 2, 0) */ ;; @@ -403,7 +406,7 @@ DELIMITER ;; /*!50003 SET character_set_results = latin1 */ ;; /*!50003 SET collation_connection = latin1_swedish_ci */ ;; /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; -/*!50003 SET sql_mode = '' */ ;; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role2`*/ /*!50106 EVENT `e2` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 4, 0) */ ;; @@ -420,7 +423,7 @@ DELIMITER ;; /*!50003 SET character_set_results = latin1 */ ;; /*!50003 SET collation_connection = latin1_swedish_ci */ ;; /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; -/*!50003 SET sql_mode = '' */ ;; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;; /*!50003 SET @saved_time_zone = @@time_zone */ ;; /*!50003 SET time_zone = 'SYSTEM' */ ;; /*!50106 CREATE*/ /*!50117 DEFINER=`role3`@`%`*/ /*!50106 EVENT `e3` ON SCHEDULE EVERY 1 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO insert t1 values (111, 3, 0) */ ;; @@ -438,7 +441,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role1` FUNCTION `fn1`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -454,7 +457,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role2` FUNCTION `fn2`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -470,7 +473,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role3`@`%` FUNCTION `fn3`() RETURNS int(11) return (select sum(a+b) from t1) ;; @@ -486,7 +489,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role1` PROCEDURE `pr1`() insert t1 values (111, 222, 333) ;; @@ -502,7 +505,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role2`@`%` PROCEDURE `pr2`() insert t1 values (111, 222, 333) ;; @@ -518,7 +521,7 @@ DELIMITER ; /*!50003 SET character_set_results = latin1 */ ; /*!50003 SET collation_connection = latin1_swedish_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; -/*!50003 SET sql_mode = '' */ ; +/*!50003 SET sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE DEFINER=`role3`@`%` PROCEDURE `pr3`() insert t1 values (111, 222, 333) ;; @@ -646,7 +649,7 @@ END;// set role r2; show create procedure user1_proc; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -user1_proc CREATE DEFINER=`r2` PROCEDURE `user1_proc`() +user1_proc NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r2` PROCEDURE `user1_proc`() SQL SECURITY INVOKER BEGIN SELECT NOW(), VERSION(); @@ -666,7 +669,7 @@ SELECT NOW(), VERSION(); END;// show create procedure user1_proc2; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -user1_proc2 CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() +user1_proc2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() SQL SECURITY INVOKER BEGIN SELECT NOW(), VERSION(); @@ -678,7 +681,7 @@ END latin1 latin1_swedish_ci latin1_swedish_ci set role r2; show create procedure user1_proc2; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -user1_proc2 CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() +user1_proc2 NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`r1` PROCEDURE `user1_proc2`() SQL SECURITY INVOKER BEGIN SELECT NOW(), VERSION(); @@ -713,7 +716,7 @@ END;// use rtest; show create procedure sensitive_proc; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -sensitive_proc CREATE DEFINER=`user_like_role`@`%` PROCEDURE `sensitive_proc`() +sensitive_proc NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`user_like_role`@`%` PROCEDURE `sensitive_proc`() SQL SECURITY INVOKER BEGIN SELECT NOW(), VERSION(); diff --git a/mysql-test/suite/roles/definer.test b/mysql-test/suite/roles/definer.test index 1a8be78fea8..89a1c90ee24 100644 --- a/mysql-test/suite/roles/definer.test +++ b/mysql-test/suite/roles/definer.test @@ -26,6 +26,7 @@ grant insert,select on mysqltest1.t1 to role2; grant event,execute,trigger on mysqltest1.* to role2; # create a non-priv user and a priv role granted to him +create user foo@localhost; grant create view on mysqltest1.* to foo@localhost; create role role4; grant select on mysqltest1.t1 to role4; @@ -99,6 +100,7 @@ show create view test.v5; --error ER_NO_SUCH_USER select * from test.v5; +create user role4; grant select on mysqltest1.t1 to role4; show create view test.v5; --error ER_NO_SUCH_USER @@ -115,6 +117,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; diff --git a/mysql-test/suite/roles/drop_current_user-5176.result b/mysql-test/suite/roles/drop_current_user-5176.result index 8e01bb10bd5..ea9f4fa4f92 100644 --- a/mysql-test/suite/roles/drop_current_user-5176.result +++ b/mysql-test/suite/roles/drop_current_user-5176.result @@ -1,7 +1,8 @@ +create user foo@localhost; grant create user on *.* to foo@localhost; drop user foo@localhost; select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT show grants; ERROR 42000: There is no such grant defined for user 'foo' on host 'localhost' select current_user(); diff --git a/mysql-test/suite/roles/drop_current_user-5176.test b/mysql-test/suite/roles/drop_current_user-5176.test index e022d3e8a86..27051345e8b 100644 --- a/mysql-test/suite/roles/drop_current_user-5176.test +++ b/mysql-test/suite/roles/drop_current_user-5176.test @@ -3,6 +3,7 @@ # --source include/not_embedded.inc +create user foo@localhost; grant create user on *.* to foo@localhost; --connect (foo,localhost,foo,,) drop user foo@localhost; diff --git a/mysql-test/suite/roles/grant-5771.result b/mysql-test/suite/roles/grant-5771.result index 4921641f3a5..5d79820e7a0 100644 --- a/mysql-test/suite/roles/grant-5771.result +++ b/mysql-test/suite/roles/grant-5771.result @@ -1,5 +1,6 @@ create database mysqltest1; create database mysqltest2; +create user foo@localhost; create role r1, r2; grant all on mysqltest1.* to r1; grant all on mysqltest2.* to r2; diff --git a/mysql-test/suite/roles/grant-5771.test b/mysql-test/suite/roles/grant-5771.test index 84c625a6067..3c8f5d2f7c0 100644 --- a/mysql-test/suite/roles/grant-5771.test +++ b/mysql-test/suite/roles/grant-5771.test @@ -6,6 +6,7 @@ create database mysqltest1; create database mysqltest2; +create user foo@localhost; create role r1, r2; grant all on mysqltest1.* to r1; grant all on mysqltest2.* to r2; diff --git a/mysql-test/suite/roles/grant_revoke_current.result b/mysql-test/suite/roles/grant_revoke_current.result index 644454685b4..d9798463965 100644 --- a/mysql-test/suite/roles/grant_revoke_current.result +++ b/mysql-test/suite/roles/grant_revoke_current.result @@ -24,4 +24,19 @@ revoke all, grant option from current_role; show grants for current_role; Grants for r1 GRANT USAGE ON *.* TO 'r1' +set password=password('foobar'); +show grants; +Grants for root@localhost +GRANT r1 TO 'root'@'localhost' WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*9B500343BC52E2911172EB52AE5CF4847604C6E5' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO 'r1' +grant r1 to current_user() identified by 'barfoo'; +show grants; +Grants for root@localhost +GRANT r1 TO 'root'@'localhost' WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*343915A8181B5728EADBDC73E1F7E6B0C3998483' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO 'r1' +set password=''; drop role r1; diff --git a/mysql-test/suite/roles/grant_revoke_current.test b/mysql-test/suite/roles/grant_revoke_current.test index 96a27fd5697..0ebe0170782 100644 --- a/mysql-test/suite/roles/grant_revoke_current.test +++ b/mysql-test/suite/roles/grant_revoke_current.test @@ -20,5 +20,10 @@ show grants for current_role; revoke all, grant option from current_role; show grants for current_role; -drop role r1; +set password=password('foobar'); +show grants; +grant r1 to current_user() identified by 'barfoo'; +show grants; +set password=''; +drop role r1; diff --git a/mysql-test/suite/roles/grant_role_auto_create_user.result b/mysql-test/suite/roles/grant_role_auto_create_user.result index 81b25e5f527..dee07aa6b3c 100644 --- a/mysql-test/suite/roles/grant_role_auto_create_user.result +++ b/mysql-test/suite/roles/grant_role_auto_create_user.result @@ -1,8 +1,11 @@ create database db; create role auto_create; +create user auto_create; grant all on db.* to auto_create; +create user foo@localhost; grant auto_create to foo@localhost; -grant auto_create to bar@localhost identified by 'baz'; +create user bar@localhost identified by 'baz'; +grant auto_create to bar@localhost; set role 'auto_create'; use db; create table t1 (i int); @@ -60,4 +63,5 @@ ERROR 42000: You are not allowed to create a user with GRANT drop user foo@localhost; drop user bar@localhost; drop role auto_create; +drop user auto_create; drop database db; diff --git a/mysql-test/suite/roles/grant_role_auto_create_user.test b/mysql-test/suite/roles/grant_role_auto_create_user.test index d2489a4ea74..e673934775e 100644 --- a/mysql-test/suite/roles/grant_role_auto_create_user.test +++ b/mysql-test/suite/roles/grant_role_auto_create_user.test @@ -5,10 +5,12 @@ create database db; create role auto_create; +create user auto_create; grant all on db.* to auto_create; - +create user foo@localhost; grant auto_create to foo@localhost; -grant auto_create to bar@localhost identified by 'baz'; +create user bar@localhost identified by 'baz'; +grant auto_create to bar@localhost; # Test if the users have been created and the role has been granted to them --connect (con1,localhost,foo,,) @@ -117,5 +119,5 @@ grant auto_create to foo2@localhost identified by 'pass'; drop user foo@localhost; drop user bar@localhost; drop role auto_create; +drop user auto_create; drop database db; - diff --git a/mysql-test/suite/roles/i_s_applicable_roles_is_default.result b/mysql-test/suite/roles/i_s_applicable_roles_is_default.result new file mode 100644 index 00000000000..63127f8b176 --- /dev/null +++ b/mysql-test/suite/roles/i_s_applicable_roles_is_default.result @@ -0,0 +1,80 @@ +create user foo; +create role role1; +create role role2; +create role role3; +grant role1 to foo; +grant role2 to role1; +grant role3 to foo; +connect foo, localhost, foo; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@% role1 NO NO +foo@% role3 NO NO +role1 role2 NO NULL +set default role role3; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@% role1 NO NO +foo@% role3 NO YES +role1 role2 NO NULL +set default role role1; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@% role1 NO YES +foo@% role3 NO NO +role1 role2 NO NULL +disconnect foo; +connection default; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES NO +root@localhost role2 YES NO +root@localhost role3 YES NO +set default role none for foo; +connect foo, localhost, foo; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@% role1 NO NO +foo@% role3 NO NO +role1 role2 NO NULL +disconnect foo; +connection default; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES NO +root@localhost role2 YES NO +root@localhost role3 YES NO +set default role role1; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES YES +root@localhost role2 YES NO +root@localhost role3 YES NO +set default role role2; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES NO +root@localhost role2 YES YES +root@localhost role3 YES NO +set default role role3; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES NO +root@localhost role2 YES NO +root@localhost role3 YES YES +set default role none; +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +role1 role2 NO NULL +root@localhost role1 YES NO +root@localhost role2 YES NO +root@localhost role3 YES NO +drop role role3; +drop role role2; +drop role role1; +drop user foo; diff --git a/mysql-test/suite/roles/i_s_applicable_roles_is_default.test b/mysql-test/suite/roles/i_s_applicable_roles_is_default.test new file mode 100644 index 00000000000..4a844e39b87 --- /dev/null +++ b/mysql-test/suite/roles/i_s_applicable_roles_is_default.test @@ -0,0 +1,63 @@ +--source include/not_embedded.inc +--enable_connect_log +create user foo; +create role role1; +create role role2; +create role role3; + +grant role1 to foo; +grant role2 to role1; +grant role3 to foo; + + +connect (foo, localhost, foo); +--sorted_result +select * from information_schema.applicable_roles; + +set default role role3; +--sorted_result +select * from information_schema.applicable_roles; + +set default role role1; +--sorted_result +select * from information_schema.applicable_roles; + + +disconnect foo; +connection default; + +--sorted_result +select * from information_schema.applicable_roles; + +set default role none for foo; +connect (foo, localhost, foo); +--sorted_result +select * from information_schema.applicable_roles; + +disconnect foo; +connection default; + +--sorted_result +select * from information_schema.applicable_roles; + +set default role role1; +--sorted_result +select * from information_schema.applicable_roles; + +set default role role2; +--sorted_result +select * from information_schema.applicable_roles; + +set default role role3; +--sorted_result +select * from information_schema.applicable_roles; + + +set default role none; +--sorted_result +select * from information_schema.applicable_roles; + +drop role role3; +drop role role2; +drop role role1; +drop user foo; diff --git a/mysql-test/suite/roles/password.result b/mysql-test/suite/roles/password.result index 041a049ccec..2d54db2ca64 100644 --- a/mysql-test/suite/roles/password.result +++ b/mysql-test/suite/roles/password.result @@ -1,3 +1,4 @@ +set sql_mode=''; create role r1; grant select on *.* to r1 identified by 'foobar'; drop user r1; @@ -10,25 +11,25 @@ ERROR HY000: Plugin 'plugin' is not loaded grant select on mysql.user to r1 identified via plugin using 'param'; ERROR HY000: Plugin 'plugin' is not loaded grant select on *.* to r1 require subject 'foobar'; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on mysql.user to r1 require issuer 'foobar'; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on *.* to r1 require cipher 'foobar'; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on mysql.user to r1 require ssl; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on *.* to r1 require x509; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on mysql.user to r1 require none; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on *.* to r1 with max_queries_per_hour 10; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on mysql.user to r1 with max_updates_per_hour 10; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on *.* to r1 with max_connections_per_hour 10; -ERROR 28000: Can't find any matching row in the user table +drop user r1; grant select on mysql.user to r1 with max_user_connections 10; -ERROR 28000: Can't find any matching row in the user table +drop user r1; set password for r1 = '00000000000000000000000000000000000000000'; ERROR 28000: Can't find any matching row in the user table drop role r1; diff --git a/mysql-test/suite/roles/password.test b/mysql-test/suite/roles/password.test index f638c5f39be..e5fff01ddbc 100644 --- a/mysql-test/suite/roles/password.test +++ b/mysql-test/suite/roles/password.test @@ -10,6 +10,7 @@ # max_queries_per_hour | max_updates_per_hour | max_connections_per_hour | max_user_connections #set password for ... = ... +set sql_mode=''; create role r1; # IDENTIFIED does not apply to roles, using it forces username context @@ -24,26 +25,28 @@ grant select on *.* to r1 identified via plugin; --error ER_PLUGIN_IS_NOT_LOADED grant select on mysql.user to r1 identified via plugin using 'param'; ---error ER_PASSWORD_NO_MATCH +# same for REQUIRE and mqh grant select on *.* to r1 require subject 'foobar'; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on mysql.user to r1 require issuer 'foobar'; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on *.* to r1 require cipher 'foobar'; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on mysql.user to r1 require ssl; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on *.* to r1 require x509; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on mysql.user to r1 require none; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on *.* to r1 with max_queries_per_hour 10; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on mysql.user to r1 with max_updates_per_hour 10; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on *.* to r1 with max_connections_per_hour 10; ---error ER_PASSWORD_NO_MATCH +drop user r1; grant select on mysql.user to r1 with max_user_connections 10; +drop user r1; + --error ER_PASSWORD_NO_MATCH set password for r1 = '00000000000000000000000000000000000000000'; diff --git a/mysql-test/suite/roles/prepare_stmt_with_role.result b/mysql-test/suite/roles/prepare_stmt_with_role.result new file mode 100644 index 00000000000..e859b2e304b --- /dev/null +++ b/mysql-test/suite/roles/prepare_stmt_with_role.result @@ -0,0 +1,107 @@ +# +# Test user to check if we can grant the created role to it. +# +create user test_user; +# +# First create the role. +# +SET @createRole = 'CREATE ROLE developers'; +PREPARE stmtCreateRole FROM @createRole; +EXECUTE stmtCreateRole; +# +# Test to see if the role is created. +# +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +user host is_role +developers Y +SHOW GRANTS; +Grants for root@localhost +GRANT developers TO 'root'@'localhost' WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +# Test reexecution. +EXECUTE stmtCreateRole; +ERROR HY000: Operation CREATE ROLE failed for 'developers' +# +# Now grant the role to the test user. +# +SET @grantRole = 'GRANT developers to test_user'; +PREPARE stmtGrantRole FROM @grantRole; +EXECUTE stmtGrantRole; +# Test reexecution. +EXECUTE stmtGrantRole; +# +# We should see 2 entries in the roles_mapping table. +# +SELECT * FROM mysql.roles_mapping; +Host User Role Admin_option +% test_user developers N +localhost root developers Y +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT developers TO 'test_user'@'%' +GRANT USAGE ON *.* TO 'test_user'@'%' +# +# Test revoking a role. +# +SET @revokeRole = 'REVOKE developers FROM test_user'; +PREPARE stmtRevokeRole FROM @revokeRole; +EXECUTE stmtRevokeRole; +EXECUTE stmtRevokeRole; +ERROR HY000: Cannot revoke role 'developers' from: 'test_user'@'%'. +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT USAGE ON *.* TO 'test_user'@'%' +EXECUTE stmtGrantRole; +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT developers TO 'test_user'@'%' +GRANT USAGE ON *.* TO 'test_user'@'%' +EXECUTE stmtRevokeRole; +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT USAGE ON *.* TO 'test_user'@'%' +# +# Now drop the role. +# +SET @dropRole = 'DROP ROLE developers'; +PREPARE stmtDropRole FROM @dropRole; +EXECUTE stmtDropRole; +# +# Check both user and roles_mapping table for traces of our role. +# +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +user host is_role +SELECT * FROM mysql.roles_mapping; +Host User Role Admin_option +SHOW GRANTS; +Grants for root@localhost +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT USAGE ON *.* TO 'test_user'@'%' +# +# Test reexecution. +# +EXECUTE stmtCreateRole; +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +user host is_role +developers Y +SELECT * FROM mysql.roles_mapping; +Host User Role Admin_option +localhost root developers Y +SHOW GRANTS; +Grants for root@localhost +GRANT developers TO 'root'@'localhost' WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +SHOW GRANTS FOR test_user; +Grants for test_user@% +GRANT USAGE ON *.* TO 'test_user'@'%' +EXECUTE stmtDropRole; +# Cleanup. +DROP USER test_user; diff --git a/mysql-test/suite/roles/prepare_stmt_with_role.test b/mysql-test/suite/roles/prepare_stmt_with_role.test new file mode 100644 index 00000000000..516e9ddab34 --- /dev/null +++ b/mysql-test/suite/roles/prepare_stmt_with_role.test @@ -0,0 +1,85 @@ +--source include/not_embedded.inc + + +--echo # +--echo # Test user to check if we can grant the created role to it. +--echo # +create user test_user; +--echo # +--echo # First create the role. +--echo # +SET @createRole = 'CREATE ROLE developers'; +PREPARE stmtCreateRole FROM @createRole; +EXECUTE stmtCreateRole; +--echo # +--echo # Test to see if the role is created. +--echo # +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +SHOW GRANTS; + +--echo # Test reexecution. +--error ER_CANNOT_USER +EXECUTE stmtCreateRole; + +--echo # +--echo # Now grant the role to the test user. +--echo # +SET @grantRole = 'GRANT developers to test_user'; +PREPARE stmtGrantRole FROM @grantRole; +EXECUTE stmtGrantRole; +--echo # Test reexecution. +EXECUTE stmtGrantRole; + +--echo # +--echo # We should see 2 entries in the roles_mapping table. +--echo # +--sorted_result +SELECT * FROM mysql.roles_mapping; +SHOW GRANTS FOR test_user; + +--echo # +--echo # Test revoking a role. +--echo # +SET @revokeRole = 'REVOKE developers FROM test_user'; +PREPARE stmtRevokeRole FROM @revokeRole; +EXECUTE stmtRevokeRole; +--error ER_CANNOT_REVOKE_ROLE +EXECUTE stmtRevokeRole; +SHOW GRANTS FOR test_user; + +EXECUTE stmtGrantRole; +SHOW GRANTS FOR test_user; +EXECUTE stmtRevokeRole; +SHOW GRANTS FOR test_user; + +--echo # +--echo # Now drop the role. +--echo # +SET @dropRole = 'DROP ROLE developers'; +PREPARE stmtDropRole FROM @dropRole; +EXECUTE stmtDropRole; + +--echo # +--echo # Check both user and roles_mapping table for traces of our role. +--echo # +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +SELECT * FROM mysql.roles_mapping; +SHOW GRANTS; +SHOW GRANTS FOR test_user; + +--echo # +--echo # Test reexecution. +--echo # +EXECUTE stmtCreateRole; +SELECT user, host,is_role FROM mysql.user +WHERE user = 'developers'; +SELECT * FROM mysql.roles_mapping; + +SHOW GRANTS; +SHOW GRANTS FOR test_user; +EXECUTE stmtDropRole; + +--echo # Cleanup. +DROP USER test_user; diff --git a/mysql-test/suite/roles/recursive.result b/mysql-test/suite/roles/recursive.result index ef40c97865f..7abbd01240e 100644 --- a/mysql-test/suite/roles/recursive.result +++ b/mysql-test/suite/roles/recursive.result @@ -28,18 +28,18 @@ Grants for foo@localhost GRANT USAGE ON *.* TO 'foo'@'localhost' GRANT role10 TO 'foo'@'localhost' select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -foo@localhost role10 NO -role10 role9 NO -role2 role1 NO -role4 role2 NO -role5 role2 NO -role5 role3 NO -role6 role4 NO -role6 role5 NO -role7 role5 NO -role9 role6 NO -role9 role7 NO +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@localhost role10 NO NO +role10 role9 NO NULL +role2 role1 NO NULL +role4 role2 NO NULL +role5 role2 NO NULL +role5 role3 NO NULL +role6 role4 NO NULL +role6 role5 NO NULL +role7 role5 NO NULL +role9 role6 NO NULL +role9 role7 NO NULL show status like 'debug%'; Variable_name Value grant select on *.* to role1; diff --git a/mysql-test/suite/roles/recursive_dbug.result b/mysql-test/suite/roles/recursive_dbug.result index 1d857a5c888..ea65a842eb0 100644 --- a/mysql-test/suite/roles/recursive_dbug.result +++ b/mysql-test/suite/roles/recursive_dbug.result @@ -32,18 +32,18 @@ Grants for foo@localhost GRANT USAGE ON *.* TO 'foo'@'localhost' GRANT role10 TO 'foo'@'localhost' select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -foo@localhost role10 NO -role10 role9 NO -role2 role1 NO -role4 role2 NO -role5 role2 NO -role5 role3 NO -role6 role4 NO -role6 role5 NO -role7 role5 NO -role9 role6 NO -role9 role7 NO +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +foo@localhost role10 NO NO +role10 role9 NO NULL +role2 role1 NO NULL +role4 role2 NO NULL +role5 role2 NO NULL +role5 role3 NO NULL +role6 role4 NO NULL +role6 role5 NO NULL +role7 role5 NO NULL +role9 role6 NO NULL +role9 role7 NO NULL show status like 'debug%'; Variable_name Value Debug_role_merges_global 11 diff --git a/mysql-test/suite/roles/rpl_definer.result b/mysql-test/suite/roles/rpl_definer.result index eadb6315d9b..c9e4f66a267 100644 --- a/mysql-test/suite/roles/rpl_definer.result +++ b/mysql-test/suite/roles/rpl_definer.result @@ -21,19 +21,19 @@ create definer=current_role procedure pcr() select current_role; create definer=role1 procedure pr() select "role1"; show create procedure pcu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcu CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() +pcu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() select current_user latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pu CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() +pu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() select "root@localhost" latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pcr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcr CREATE DEFINER=`role1` PROCEDURE `pcr`() +pcr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pcr`() select current_role latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr CREATE DEFINER=`role1` PROCEDURE `pr`() +pr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr`() select "role1" latin1 latin1_swedish_ci latin1_swedish_ci [connection slave] set role role1; @@ -49,19 +49,19 @@ GRANT role2 TO 'role1' GRANT role2 TO 'root'@'localhost' WITH ADMIN OPTION show create procedure pcu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcu CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() +pcu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pcu`() select current_user latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pu; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pu CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() +pu NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `pu`() select "root@localhost" latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pcr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pcr CREATE DEFINER=`role1` PROCEDURE `pcr`() +pcr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pcr`() select current_role latin1 latin1_swedish_ci latin1_swedish_ci show create procedure pr; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -pr CREATE DEFINER=`role1` PROCEDURE `pr`() +pr NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`role1` PROCEDURE `pr`() select "role1" latin1 latin1_swedish_ci latin1_swedish_ci drop procedure pcu; drop procedure pu; diff --git a/mysql-test/suite/roles/set_default_role_clear.result b/mysql-test/suite/roles/set_default_role_clear.result new file mode 100644 index 00000000000..382d3d27c70 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_clear.result @@ -0,0 +1,35 @@ +create user test_user@localhost; +create role test_role; +grant select on *.* to test_role; +grant test_role to test_user@localhost; +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +set default role test_role; +select user, host, default_role from mysql.user; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost test_role +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +GRANT SELECT ON *.* TO 'test_role' +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost test_role +set default role NONE; +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost +set default role invalid_role; +ERROR OP000: Invalid role specification `invalid_role`. +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost +select user, host, default_role from mysql.user; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +drop role test_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_clear.test b/mysql-test/suite/roles/set_default_role_clear.test new file mode 100644 index 00000000000..32c9661c573 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_clear.test @@ -0,0 +1,52 @@ +source include/not_embedded.inc; + +# This test checks clearing a default role from a user. + +# Create a user with no privileges +create user test_user@localhost; + +create role test_role; + +grant select on *.* to test_role; +grant test_role to test_user@localhost; + +change_user 'test_user'; +show grants; +set default role test_role; + +# Even though a user has the default role set, without reconnecting, we should +# not already have the roles privileges. +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user; + +change_user 'root'; +select user, host, default_role from mysql.user where user='test_user'; + +change_user 'test_user'; +# This should show that the new test_user has the role's grants enabled. +show grants; +select user, host, default_role from mysql.user where user='test_user'; + +set default role NONE; + +# We should still have the role set right now. +select user, host, default_role from mysql.user where user='test_user'; + +# Make sure we do not somehow get privileges to set an invalid role +--error ER_INVALID_ROLE +set default role invalid_role; + +change_user 'root'; +select user, host, default_role from mysql.user where user='test_user'; + +change_user 'test_user'; +# The user does not have a default role set anymore. Make sure we don't still +# get the privileges. +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user; + +change_user 'root'; + +# Cleanup +drop role test_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_for.result b/mysql-test/suite/roles/set_default_role_for.result new file mode 100644 index 00000000000..7289319a428 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_for.result @@ -0,0 +1,65 @@ +create user user_a@localhost; +create user user_b@localhost; +create role role_a; +create role role_b; +grant role_a to user_a@localhost; +grant role_b to user_b@localhost; +grant role_a to user_a@localhost; +grant select on *.* to role_a; +grant role_b to user_b@localhost; +grant insert, update on *.* to role_b; +set default role role_a for user_b@localhost; +ERROR 42000: Access denied for user 'user_a'@'localhost' to database 'mysql' +set default role role_a for user_a@localhost; +set default role invalid_role for user_a@localhost; +ERROR OP000: Invalid role specification `invalid_role`. +set default role role_b for user_a@localhost; +ERROR OP000: Invalid role specification `role_b`. +set default role role_b for user_b@localhost; +show grants; +Grants for user_a@localhost +GRANT role_a TO 'user_a'@'localhost' +GRANT USAGE ON *.* TO 'user_a'@'localhost' +GRANT SELECT ON *.* TO 'role_a' +select user, host, default_role from mysql.user where user like 'user_%'; +user host default_role +user_a localhost role_a +user_b localhost role_b +set default role NONE for current_user; +select user, host, default_role from mysql.user where user like 'user_%'; +user host default_role +user_a localhost +user_b localhost role_b +set default role current_role for current_user; +select user, host, default_role from mysql.user where user like 'user_%'; +user host default_role +user_a localhost role_a +user_b localhost role_b +set default role role_b for current_user; +ERROR OP000: Invalid role specification `role_b`. +show grants; +Grants for user_b@localhost +GRANT role_b TO 'user_b'@'localhost' +GRANT USAGE ON *.* TO 'user_b'@'localhost' +GRANT INSERT, UPDATE ON *.* TO 'role_b' +select user, host, default_role from mysql.user where user like 'user_%'; +ERROR 42000: SELECT command denied to user 'user_b'@'localhost' for table 'user' +insert into mysql.user (user, host) values ('someuser', 'somehost'); +Warnings: +Warning 1364 Field 'ssl_cipher' doesn't have a default value +Warning 1364 Field 'x509_issuer' doesn't have a default value +Warning 1364 Field 'x509_subject' doesn't have a default value +Warning 1364 Field 'authentication_string' doesn't have a default value +set default role NONE for user_a@localhost; +show grants; +Grants for user_a@localhost +GRANT role_a TO 'user_a'@'localhost' +GRANT USAGE ON *.* TO 'user_a'@'localhost' +GRANT INSERT, UPDATE ON *.* TO 'role_b' +select user, host, default_role from mysql.user where user like 'user_%'; +ERROR 42000: SELECT command denied to user 'user_a'@'localhost' for table 'user' +drop role role_a; +drop role role_b; +delete from mysql.user where user = 'someuser' && host = 'somehost'; +drop user user_a@localhost; +drop user user_b@localhost; diff --git a/mysql-test/suite/roles/set_default_role_for.test b/mysql-test/suite/roles/set_default_role_for.test new file mode 100644 index 00000000000..de35b529ce6 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_for.test @@ -0,0 +1,87 @@ +source include/not_embedded.inc; + +# This test checks setting a default role to a different user; + + +create user user_a@localhost; +create user user_b@localhost; + +create role role_a; +create role role_b; + +grant role_a to user_a@localhost; +grant role_b to user_b@localhost; + +grant role_a to user_a@localhost; +grant select on *.* to role_a; + +grant role_b to user_b@localhost; +grant insert, update on *.* to role_b; + +change_user 'user_a'; + +# A user should not be a able to set a default role for someone else, +# if he hasn't got write access to the database. +--error ER_DBACCESS_DENIED_ERROR +set default role role_a for user_b@localhost; + +# Should have the same effect as set default role role_a. +set default role role_a for user_a@localhost; + +change_user 'root'; + +# Not even a 'root' user should be able to set an invalid role for a user. +--error ER_INVALID_ROLE +set default role invalid_role for user_a@localhost; + +--error ER_INVALID_ROLE +set default role role_b for user_a@localhost; + +# Make sure we can set a default role for a different user than the one that +# is actually running the command. +set default role role_b for user_b@localhost; + +change_user 'user_a'; + +show grants; +--sorted_result +select user, host, default_role from mysql.user where user like 'user_%'; + +set default role NONE for current_user; +--sorted_result +select user, host, default_role from mysql.user where user like 'user_%'; + +set default role current_role for current_user; +--sorted_result +select user, host, default_role from mysql.user where user like 'user_%'; + +# Make sure we can't set a default role not granted to us, using current_user +--error ER_INVALID_ROLE +set default role role_b for current_user; + +change_user 'user_b'; + +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user like 'user_%'; + +# Make sure the default role setting worked from root. +insert into mysql.user (user, host) values ('someuser', 'somehost'); +# Since we have update privileges on the mysql.user table, we should +# be able to set a default role for a different user. +set default role NONE for user_a@localhost; + +change_user 'user_a'; + +# There is no default role set any more. +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user like 'user_%'; + +change_user 'root'; + +drop role role_a; +drop role role_b; +delete from mysql.user where user = 'someuser' && host = 'somehost'; +drop user user_a@localhost; +drop user user_b@localhost; diff --git a/mysql-test/suite/roles/set_default_role_invalid.result b/mysql-test/suite/roles/set_default_role_invalid.result new file mode 100644 index 00000000000..3916bacfd4c --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_invalid.result @@ -0,0 +1,40 @@ +create user test_user@localhost; +create role test_role; +create role not_granted_role; +grant select on *.* to test_role; +grant test_role to test_user@localhost; +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +select user, host, default_role from mysql.user; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +set default role invalid_role; +ERROR OP000: Invalid role specification `invalid_role`. +set default role not_granted_role; +ERROR OP000: Invalid role specification `not_granted_role`. +set default role test_role; +select user, host, default_role from mysql.user; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost test_role +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +GRANT SELECT ON *.* TO 'test_role' +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost test_role +set default role invalid_role; +ERROR OP000: Invalid role specification `invalid_role`. +select user, host, default_role from mysql.user where user='test_user'; +user host default_role +test_user localhost test_role +revoke test_role from test_user@localhost; +select user, host, default_role from mysql.user where user='test_user'; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +drop role test_role; +drop role not_granted_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_invalid.test b/mysql-test/suite/roles/set_default_role_invalid.test new file mode 100644 index 00000000000..8e72e316d4b --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_invalid.test @@ -0,0 +1,62 @@ +source include/not_embedded.inc; + +# This test checks the error paths possible during set default role. + +# Create a user with no privileges +create user test_user@localhost; + +create role test_role; +create role not_granted_role; + +grant select on *.* to test_role; +grant test_role to test_user@localhost; + +change_user 'test_user'; +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user; + +# A user can not set a default role that does not exist in the database. +--error ER_INVALID_ROLE +set default role invalid_role; + +# A user can not set a default role if he can not call set role <role>. +--error ER_INVALID_ROLE +set default role not_granted_role; + +set default role test_role; + +# Even though a user has the default role set, without reconnecting, we should +# not already have the roles privileges. +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user; + +change_user 'root'; +select user, host, default_role from mysql.user where user='test_user'; + +change_user 'test_user'; +# This should show that the new test_user has the role's grants enabled. +show grants; +select user, host, default_role from mysql.user where user='test_user'; + +# If we have a failed set default role attempt, don't change the already set +# default role. +--error ER_INVALID_ROLE +set default role invalid_role; +select user, host, default_role from mysql.user where user='test_user'; + +change_user 'root'; +# Now, even though a default role is still set for test_user, make sure the +# user does not get the rights, if he can not set the role. +revoke test_role from test_user@localhost; + +change_user 'test_user'; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user='test_user'; + +change_user 'root'; + +# Cleanup +drop role test_role; +drop role not_granted_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_new_connection.result b/mysql-test/suite/roles/set_default_role_new_connection.result new file mode 100644 index 00000000000..d88ead70038 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_new_connection.result @@ -0,0 +1,48 @@ +create user test_user@localhost; +create role test_role; +grant select on *.* to test_role; +grant test_role to test_user@localhost; +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +select user, host, default_role from mysql.user where user = 'test_user'; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +set default role test_role; +select user, host, default_role from mysql.user where user = 'test_user'; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +select user, host, default_role from mysql.user where user = 'test_user'; +user host default_role +test_user localhost test_role +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +GRANT SELECT ON *.* TO 'test_role' +select user, host, default_role from mysql.user where user = 'test_user'; +user host default_role +test_user localhost test_role +set default role NONE; +select user, host, default_role from mysql.user where user = 'test_user'; +user host default_role +test_user localhost +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +select user, host, default_role from mysql.user where user = 'test_user'; +ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'user' +select user, host, default_role from mysql.user where user = 'test_user'; +user host default_role +test_user localhost +set default role test_role for test_user@localhost; +show grants; +Grants for test_user@localhost +GRANT test_role TO 'test_user'@'localhost' +GRANT USAGE ON *.* TO 'test_user'@'localhost' +GRANT SELECT ON *.* TO 'test_role' +select user, host, default_role from mysql.user where user = 'test_user'; +user host default_role +test_user localhost test_role +drop role test_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_new_connection.test b/mysql-test/suite/roles/set_default_role_new_connection.test new file mode 100644 index 00000000000..81f7f2ffb58 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_new_connection.test @@ -0,0 +1,47 @@ +source include/not_embedded.inc; + +create user test_user@localhost; +create role test_role; +grant select on *.* to test_role; +grant test_role to test_user@localhost; + +--connect (c1, localhost, test_user,,) +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user = 'test_user'; +set default role test_role; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user = 'test_user'; +disconnect c1; + +connection default; +select user, host, default_role from mysql.user where user = 'test_user'; + + +--connect (c1, localhost, test_user,,) +show grants; +select user, host, default_role from mysql.user where user = 'test_user'; +set default role NONE; +disconnect c1; + +connection default; +select user, host, default_role from mysql.user where user = 'test_user'; + +--connect (c1, localhost, test_user,,) +show grants; +--error ER_TABLEACCESS_DENIED_ERROR +select user, host, default_role from mysql.user where user = 'test_user'; +disconnect c1; + +connection default; +select user, host, default_role from mysql.user where user = 'test_user'; +set default role test_role for test_user@localhost; + +--connect (c1, localhost, test_user,,) +show grants; +select user, host, default_role from mysql.user where user = 'test_user'; +disconnect c1; + +connection default; +drop role test_role; +drop user test_user@localhost; diff --git a/mysql-test/suite/roles/set_default_role_ps-6960.result b/mysql-test/suite/roles/set_default_role_ps-6960.result new file mode 100644 index 00000000000..60210d7f92c --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_ps-6960.result @@ -0,0 +1,8 @@ +create role r1; +prepare stmt from "set password = '11111111111111111111111111111111111111111'"; +execute stmt; +prepare stmt from "set default role r1"; +execute stmt; +set password = ''; +set default role NONE; +drop role r1; diff --git a/mysql-test/suite/roles/set_default_role_ps-6960.test b/mysql-test/suite/roles/set_default_role_ps-6960.test new file mode 100644 index 00000000000..8ac520e1776 --- /dev/null +++ b/mysql-test/suite/roles/set_default_role_ps-6960.test @@ -0,0 +1,15 @@ +# +# MDEV-6960 Server crashes in check_alter_user on setting a default role via PS +# + +--source include/not_embedded.inc + +create role r1; +prepare stmt from "set password = '11111111111111111111111111111111111111111'"; +execute stmt; +prepare stmt from "set default role r1"; +execute stmt; + +set password = ''; +set default role NONE; +drop role r1; diff --git a/mysql-test/suite/roles/set_role-recursive.result b/mysql-test/suite/roles/set_role-recursive.result index 0bec8026ab5..9e62558fc14 100644 --- a/mysql-test/suite/roles/set_role-recursive.result +++ b/mysql-test/suite/roles/set_role-recursive.result @@ -16,11 +16,11 @@ Host User Role Admin_option test_role1 test_role2 N grant select on *.* to test_role2; select * from mysql.user where user like 'test_role1'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role - test_role1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role1 N N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.user where user like 'test_role2'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role - test_role2 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role2 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.roles_mapping; ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping' show grants; diff --git a/mysql-test/suite/roles/set_role-simple.result b/mysql-test/suite/roles/set_role-simple.result index ea416835017..4fb705368b7 100644 --- a/mysql-test/suite/roles/set_role-simple.result +++ b/mysql-test/suite/roles/set_role-simple.result @@ -11,8 +11,8 @@ localhost root test_role1 Y localhost test_user test_role1 N grant select on *.* to test_role1; select * from mysql.user where user='test_role1'; -Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role - test_role1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y +Host User Password Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Reload_priv Shutdown_priv Process_priv File_priv Grant_priv References_priv Index_priv Alter_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Create_user_priv Event_priv Trigger_priv Create_tablespace_priv ssl_type ssl_cipher x509_issuer x509_subject max_questions max_updates max_connections max_user_connections plugin authentication_string password_expired is_role default_role max_statement_time + test_role1 Y N N N N N N N N N N N N N N N N N N N N N N N N N N N N 0 0 0 0 N Y 0.000000 select * from mysql.roles_mapping; ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table 'roles_mapping' show grants; @@ -45,3 +45,12 @@ delete from mysql.user where user='test_role1'; delete from mysql.roles_mapping where Role='test_role1'; flush privileges; drop user 'test_user'@'localhost'; +create user user1; +select current_user; +current_user +user1@% +show grants; +Grants for user1@% +GRANT USAGE ON *.* TO 'user1'@'%' +set role none; +drop user user1; diff --git a/mysql-test/suite/roles/set_role-simple.test b/mysql-test/suite/roles/set_role-simple.test index 152a1d0eb25..ed884fa2407 100644 --- a/mysql-test/suite/roles/set_role-simple.test +++ b/mysql-test/suite/roles/set_role-simple.test @@ -37,3 +37,18 @@ delete from mysql.user where user='test_role1'; delete from mysql.roles_mapping where Role='test_role1'; flush privileges; drop user 'test_user'@'localhost'; + +# +# MDEV-9898 SET ROLE NONE can crash mysqld. +# + +create user user1; + +--connect (con1,localhost,user1,,) +select current_user; +show grants; +set role none; + +connection default; +drop user user1; + diff --git a/mysql-test/suite/roles/show_grants.result b/mysql-test/suite/roles/show_grants.result index e7b7860c74f..5d46b038cf8 100644 --- a/mysql-test/suite/roles/show_grants.result +++ b/mysql-test/suite/roles/show_grants.result @@ -23,15 +23,15 @@ user host grant select on mysql.* to test_role2; flush privileges; select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -root@localhost test_role1 YES -root@localhost test_role2 YES -test_role1 test_role2 NO +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +root@localhost test_role1 YES NO +root@localhost test_role2 YES NO +test_role1 test_role2 NO NULL select * from information_schema.applicable_roles; -GRANTEE ROLE_NAME IS_GRANTABLE -test_role1 test_role2 NO -test_user@localhost test_role1 NO -test_user@localhost test_role2 NO +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +test_role1 test_role2 NO NULL +test_user@localhost test_role1 NO NO +test_user@localhost test_role2 NO NO show grants; Grants for test_user@localhost GRANT USAGE ON *.* TO 'test_user'@'localhost' diff --git a/mysql-test/suite/roles/show_grants_replicated.result b/mysql-test/suite/roles/show_grants_replicated.result new file mode 100644 index 00000000000..0b2e38d3630 --- /dev/null +++ b/mysql-test/suite/roles/show_grants_replicated.result @@ -0,0 +1,57 @@ +include/master-slave.inc +[connection master] +create user u1; +create role r1; +# +# On master SHOW GRANTS work both for the user and the role: +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +show grants for r1; +Grants for r1 +GRANT USAGE ON *.* TO 'r1' +# +connection slave; +# +# The role has been replicated, +# it's visible in mysql.user and I_S: +# +select user, host, is_role from mysql.user where user in ('u1', 'r1'); +user host is_role +r1 Y +u1 % N +select * from information_schema.applicable_roles; +GRANTEE ROLE_NAME IS_GRANTABLE IS_DEFAULT +root@localhost r1 YES NO +# +# Check show grants for the new user. +show grants for u1; +Grants for u1@% +GRANT USAGE ON *.* TO 'u1'@'%' +# +# Check show grants for the new role. +show grants for r1; +Grants for r1 +GRANT USAGE ON *.* TO 'r1' +# +# Check if flushing privileges preserves the state. +flush privileges; +show grants for r1; +Grants for r1 +GRANT USAGE ON *.* TO 'r1' +# +# Check SHOW GRANTS after setting the role. +set role r1; +show grants; +Grants for root@localhost +GRANT r1 TO 'root'@'localhost' WITH ADMIN OPTION +GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION +GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION +GRANT USAGE ON *.* TO 'r1' +show grants for r1; +Grants for r1 +GRANT USAGE ON *.* TO 'r1' +connection master; +drop role r1; +drop user u1; +include/rpl_end.inc diff --git a/mysql-test/suite/roles/show_grants_replicated.test b/mysql-test/suite/roles/show_grants_replicated.test new file mode 100644 index 00000000000..1d4572bb297 --- /dev/null +++ b/mysql-test/suite/roles/show_grants_replicated.test @@ -0,0 +1,41 @@ +--source include/master-slave.inc + +--enable_connect_log + +create user u1; +create role r1; +--echo # +--echo # On master SHOW GRANTS work both for the user and the role: +show grants for u1; +show grants for r1; +--echo # +--sync_slave_with_master +--echo # +--echo # The role has been replicated, +--echo # it's visible in mysql.user and I_S: +--echo # +--sorted_result +select user, host, is_role from mysql.user where user in ('u1', 'r1'); +select * from information_schema.applicable_roles; +--echo # +--echo # Check show grants for the new user. +show grants for u1; +--echo # +--echo # Check show grants for the new role. +show grants for r1; +--echo # +--echo # Check if flushing privileges preserves the state. +flush privileges; +show grants for r1; +--echo # +--echo # Check SHOW GRANTS after setting the role. +set role r1; +show grants; +show grants for r1; + +connection master; +drop role r1; +drop user u1; +--disable_connect_log +--sync_slave_with_master +--source include/rpl_end.inc |