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
|
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;
|
