Debian Codegraph @

mysql-test/suite/roles/role_case_sensitive-10744.result

raw
1 #
2 # MDEV-10744 Roles are not fully case-sensitive
3 #
4 #
5 # Test creating two case-different roles.
6 #
7 create user test_user@'%';
8 create role test_ROLE;
9 create role test_role;
10 #
11 # Test if mysql.user has the roles created.
12 #
13 select user, host from mysql.user where is_role='y' and user like 'test%';
14 User Host
15 test_ROLE
16 test_role
17 create database secret_db;
18 create table secret_db.t1 (secret varchar(100));
19 insert into secret_db.t1 values ("Some Secret P4ssw0rd");
20 grant select on secret_db.* to test_role;
21 grant test_role to test_user;
22 show grants for test_user;
23 Grants for test_user@%
24 GRANT `test_role` TO `test_user`@`%`
25 GRANT USAGE ON *.* TO `test_user`@`%`
26 #
27 # Now test the UPPER case role.
28 #
29 grant test_ROLE to test_user;
30 grant insert on secret_db.t1 to test_ROLE;
31 show grants for test_user;
32 Grants for test_user@%
33 GRANT `test_role` TO `test_user`@`%`
34 GRANT `test_ROLE` TO `test_user`@`%`
35 GRANT USAGE ON *.* TO `test_user`@`%`
36 connect test_user,localhost,test_user;
37 #
38 # Test users privileges when interacting with those roles;
39 #
40 show tables from secret_db;
41 ERROR 42000: Access denied for user 'test_user'@'%' to database 'secret_db'
42 set role test_ROLE;
43 show tables from secret_db;
44 Tables_in_secret_db
45 t1
46 select * from secret_db.t1;
47 ERROR 42000: SELECT command denied to user 'test_user'@'localhost' for table `secret_db`.`t1`
48 insert into secret_db.t1 values ("|-|4><");
49 set role test_role;
50 select * from secret_db.t1 order by secret;
51 secret
52 |-|4><
53 Some Secret P4ssw0rd
54 insert into secret_db.t1 values ("|_33T|-|4><");
55 ERROR 42000: INSERT command denied to user 'test_user'@'localhost' for table `secret_db`.`t1`
56 connection default;
57 drop role test_ROLE;
58 drop role test_role;
59 drop user test_user;
60 drop database secret_db;