1CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user1; 2Warnings: 3Note 1449 The user specified as a definer ('user1'@'%') does not exist 4SELECT * FROM mysql.roles_mapping; 5Host User Role Admin_option 6% user1 role1 Y 7DROP ROLE role1; 8CREATE OR REPLACE ROLE role1 WITH ADMIN user2; 9Warnings: 10Note 1449 The user specified as a definer ('user2'@'%') does not exist 11SELECT * FROM mysql.roles_mapping WHERE Role='role1'; 12Host User Role Admin_option 13% user2 role1 Y 14CREATE OR REPLACE ROLE role1 WITH ADMIN user3; 15Warnings: 16Note 1449 The user specified as a definer ('user3'@'%') does not exist 17SELECT * FROM mysql.roles_mapping WHERE Role='role1'; 18Host User Role Admin_option 19% user3 role1 Y 20CREATE ROLE IF NOT EXISTS role1 WITH ADMIN user4; 21Warnings: 22Note 1449 The user specified as a definer ('user4'@'%') does not exist 23Note 1975 Can't create role 'role1'; it already exists 24SELECT * FROM mysql.roles_mapping WHERE Role='role1'; 25Host User Role Admin_option 26% user3 role1 Y 27DROP ROLE IF EXISTS role1; 28SELECT * FROM mysql.roles_mapping WHERE Role='role1'; 29Host User Role Admin_option 30DROP ROLE IF EXISTS role1; 31Warnings: 32Note 1976 Can't drop role 'role1'; it doesn't exist 33CREATE ROLE role_1; 34CREATE ROLE IF NOT EXISTS role_1; 35Warnings: 36Note 1975 Can't create role 'role_1'; it already exists 37CREATE OR REPLACE ROLE role_1; 38CREATE OR REPLACE ROLE IF NOT EXISTS role_1; 39ERROR HY000: Incorrect usage of OR REPLACE and IF NOT EXISTS 40CREATE ROLE role_1; 41ERROR HY000: Operation CREATE ROLE failed for 'role_1' 42CREATE USER u1@localhost; 43REVOKE SHOW DATABASES ON *.* FROM 'u1'@'localhost'; 44GRANT SHOW DATABASES ON *.* TO role_1; 45GRANT role_1 TO u1@localhost; 46connect user_a, localhost, u1,,; 47connection user_a; 48SELECT CURRENT_USER; 49CURRENT_USER 50u1@localhost 51SHOW DATABASES; 52Database 53information_schema 54test 55SELECT CURRENT_ROLE; 56CURRENT_ROLE 57NULL 58SET ROLE role_1; 59SELECT CURRENT_ROLE; 60CURRENT_ROLE 61role_1 62SHOW DATABASES; 63Database 64information_schema 65mtr 66mysql 67performance_schema 68test 69SET ROLE NONE; 70connect user_b, localhost, root,,; 71connection user_b; 72# Clearing up 73DROP ROLE role_1; 74DROP ROLE IF EXISTS role_1; 75Warnings: 76Note 1976 Can't drop role 'role_1'; it doesn't exist 77DROP ROLE role_1; 78ERROR HY000: Operation DROP ROLE failed for 'role_1' 79DROP USER u1@localhost; 80CREATE ROLE r; 81GRANT SHOW DATABASES ON *.* TO r; 82CREATE USER foo; 83CREATE USER bar; 84GRANT r TO foo; 85CREATE OR REPLACE USER foo IDENTIFIED WITH non_existing_plugin; 86ERROR HY000: Plugin 'non_existing_plugin' is not loaded 87DROP ROLE r; 88DROP USER bar; 89