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
|
#
# Bug#28395115: permission denied if grants are given through role
#
CREATE DATABASE my_db;
CREATE table my_db.t1 (id int primary key);
CREATE ROLE foo_role;
CREATE USER foo, bar;
GRANT INSERT(id), UPDATE(id), SELECT(id) ON my_db.t1 to foo_role, bar;
GRANT EXECUTE, SYSTEM_VARIABLES_ADMIN ON *.* TO foo, bar;
GRANT foo_role TO foo;
SET DEFAULT ROLE foo_role TO foo;
SET DEBUG_SYNC='in_check_grant_all_columns SIGNAL s1 WAIT_FOR s2';
# Inserts are now allowed if grants are given through role
INSERT into my_db.t1 values(8) on duplicate key UPDATE id = values(id) + 80;
# Now revoke all privileges from the role
SET DEBUG_SYNC='now WAIT_FOR s1';
SET DEBUG_SYNC='after_table_grant_revoke SIGNAL s2';
REVOKE ALL ON my_db.t1 FROM foo_role;
# Despite all privileges are revoked current SQL statement will succeed.
Warnings:
Warning 1287 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead
SET DEBUG_SYNC= 'RESET';
# But the subsequent statement will fail.
INSERT into my_db.t1 values(9) on duplicate key UPDATE id = values(id) + 90;
ERROR 42000: INSERT, UPDATE command denied to user 'foo'@'localhost' for table 't1'
SET DEBUG_SYNC='in_check_grant_all_columns SIGNAL s1 WAIT_FOR s2';
# Inserts are now allowed if grants are given through role
INSERT into my_db.t1 values(6) on duplicate key UPDATE id = values(id) + 60;
# Now revoke all privileges from the user
SET DEBUG_SYNC='now WAIT_FOR s1';
SET DEBUG_SYNC='after_table_grant_revoke SIGNAL s2';
REVOKE ALL ON my_db.t1 FROM bar;
# Since all privileges are revoked therefore current SQL statement will fail.
ERROR 42000: INSERT command denied to user 'bar'@'localhost' for column 'id' in table 't1'
# Subsequent statement will fail as well.
INSERT into my_db.t1 values(9) on duplicate key UPDATE id = values(id) + 90;
ERROR 42000: INSERT, UPDATE command denied to user 'bar'@'localhost' for table 't1'
# Cleanup
SET DEBUG_SYNC= 'RESET';
DROP DATABASE my_db;
DROP USER foo, bar;
DROP ROLE foo_role;
#
# Bug #35471453: MySQL debug server stops when executing query
#
CREATE USER b35471453@localhost;
GRANT CREATE ROLE, DROP ROLE ON *.* TO b35471453@localhost;
CREATE TABLE t35471453(c1 INT);
# test1: should fail
CREATE OR REPLACE DEFINER = 'role_35471453' VIEW v35471453
AS TABLE t35471453;
ERROR 42000: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation
# Now grant SET_USER_ID and retry
GRANT SET_USER_ID ON *.* TO b35471453@localhost;
# test2: should complete with a warning
CREATE OR REPLACE DEFINER = 'role_35471453' VIEW v35471453
AS TABLE t35471453;
Warnings:
Note 1449 The user specified as a definer ('role_35471453'@'%') does not exist
# Now revoke SET_USER_ID and try dropping the role
REVOKE SET_USER_ID ON *.* FROM b35471453@localhost;
# test3: should fail
DROP ROLE IF EXISTS role_35471453;
ERROR HY000: Operation DROP ROLE failed for 'role_35471453'@'%' as it is referenced as a definer account in a view.
# Now grant SET_USER_ID and retry
GRANT SET_USER_ID ON *.* TO b35471453@localhost;
# test4: should pass with a warning
DROP ROLE IF EXISTS role_35471453;
Warnings:
Warning 4005 User 'role_35471453'@'%' is referenced as a definer account in a view.
Note 3162 Authorization ID 'role_35471453'@'%' does not exist.
DROP VIEW v35471453;
DROP TABLE t35471453;
REVOKE CREATE ROLE, DROP ROLE, SET_USER_ID ON *.* FROM b35471453@localhost;
DROP USER b35471453@localhost;
# End of 8.0 tests
|
