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