215 lines
6.4 KiB
Plaintext
215 lines
6.4 KiB
Plaintext
call mtr.add_suppression("Can't set mandatory_role.*");
|
|
SHOW VARIABLES LIKE 'mandatory_roles';
|
|
CREATE ROLE PUBLIC;
|
|
CREATE DATABASE test2;
|
|
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON test2.* TO PUBLIC@`%`;
|
|
SHOW GRANTS FOR PUBLIC;
|
|
CREATE USER u1@localhost IDENTIFIED BY 'foo';
|
|
SET DEFAULT ROLE PUBLIC TO u1@localhost;
|
|
SHOW GRANTS FOR u1@localhost USING PUBLIC;
|
|
connect(con1,localhost,u1,foo,test2,,,);
|
|
connection con1;
|
|
--echo # Current default role should be the mandatory role PUBLIC
|
|
SELECT current_role();
|
|
--echo # SHOW GRANTS should show the granted roles
|
|
SHOW GRANTS;
|
|
use test2;
|
|
CREATE TABLE t1 (c1 INT);
|
|
SET ROLE PUBLIC;
|
|
DROP TABLE t1;
|
|
--echo # Make sure we're actually checking for privileges
|
|
--error ER_DBACCESS_DENIED_ERROR
|
|
CREATE DATABASE test3;
|
|
connection default;
|
|
CREATE ROLE team_share;
|
|
SET GLOBAL mandatory_roles='PUBLIC@%,team_share';
|
|
disconnect con1;
|
|
--source include/restart_mysqld.inc
|
|
--echo # Restarting the server will reset the mandatory_roles variable
|
|
SHOW VARIABLES LIKE 'mandatory_roles';
|
|
SET global mandatory_roles='PUBLIC@%,team_share';
|
|
connect(con1,localhost,u1,foo,test2,,,);
|
|
connection con1;
|
|
--echo # Active role is still PUBLIC; this is determined by default role
|
|
--echo # settings.
|
|
SELECT CURRENT_ROLE();
|
|
--echo # But u1 is also granted team_share
|
|
SHOW GRANTS;
|
|
use test2;
|
|
CREATE TABLE t1 (c1 INT);
|
|
SET ROLE PUBLIC;
|
|
DROP TABLE t1;
|
|
connection default;
|
|
disconnect con1;
|
|
--error ER_MANDATORY_ROLE
|
|
REVOKE PUBLIC FROM u1@localhost;
|
|
--error ER_MANDATORY_ROLE
|
|
DROP ROLE PUBLIC;
|
|
DROP USER u1@localhost;
|
|
SET GLOBAL mandatory_roles= '';
|
|
DROP ROLE team_share;
|
|
DROP DATABASE test2;
|
|
SET GLOBAL mandatory_roles= 'PUBLIC';
|
|
--echo #
|
|
--echo # Tests for activate_all_roles_on_login
|
|
--echo #
|
|
CREATE USER u1@localhost IDENTIFIED BY 'foo';
|
|
CREATE ROLE r1;
|
|
GRANT r1 TO u1@localhost;
|
|
GRANT SELECT ON *.* TO r1;
|
|
SET GLOBAL activate_all_roles_on_login= 'ON';
|
|
connect(con1,localhost,u1,foo,test,,,);
|
|
connection con1;
|
|
--echo # Active role are PUBLIC, r1
|
|
SELECT CURRENT_ROLE();
|
|
SHOW GRANTS;
|
|
connection default;
|
|
CREATE ROLE r2,r3,r4,r5;
|
|
GRANT r2,r3,r4,r5 TO u1@localhost;
|
|
disconnect con1;
|
|
connect(con1,localhost,u1,foo,test,,,);
|
|
connection con1;
|
|
--echo # Grants for u1@localhost should include PUBLIC.
|
|
SELECT CURRENT_ROLE();
|
|
SHOW GRANTS;
|
|
SET ROLE NONE;
|
|
SHOW GRANTS;
|
|
connection default;
|
|
--echo # SHOW GRANTS FOR won't show mandatory roles (PUBLIC in this case)
|
|
--echo # because we need to avoid breaking the mysqlpump protocol which rely
|
|
--echo # on SHOW GRANTS FOR to dump user privileges.
|
|
SHOW GRANTS FOR u1@localhost;
|
|
SHOW GRANTS FOR u1@localhost USING `PUBLIC`@`%`,`r1`@`%`,`r2`@`%`,`r3`@`%`,`r4`@`%`,`r5`@`%`;
|
|
SET GLOBAL mandatory_roles= '';
|
|
DROP ROLE PUBLIC;
|
|
SET GLOBAL mandatory_roles= 'PUBLIC';
|
|
SET GLOBAL activate_all_roles_on_login= 'OFF';
|
|
connection default;
|
|
REVOKE ALL ON *.* from r1,r2;
|
|
GRANT SELECT ON *.* TO r1;
|
|
GRANT INSERT ON *.* TO r2;
|
|
GRANT r2 TO u1@localhost;
|
|
SET GLOBAL mandatory_roles='r1';
|
|
FLUSH PRIVILEGES;
|
|
SHOW GRANTS FOR u1@localhost USING r1;
|
|
SHOW GRANTS FOR u1@localhost USING r2;
|
|
SHOW GRANTS FOR u1@localhost USING r1,r2;
|
|
--echo # We need to disconnect here to be sure that the u1 session has correct
|
|
--echo # values for activate_all_roles_on_login and mandatory_roles.
|
|
disconnect con1;
|
|
connect(con1,localhost,u1,foo,test,,,);
|
|
--echo # Current role should be None because ther are no activate roles per
|
|
--echo # default even though r1 is granted as a mandatory role.
|
|
SELECT CURRENT_ROLE();
|
|
SHOW GRANTS;
|
|
SET ROLE r1;
|
|
SHOW GRANTS;
|
|
SET ROLE r2;
|
|
SHOW GRANTS;
|
|
SET ROLE r1,r2;
|
|
SHOW GRANTS;
|
|
SET ROLE ALL;
|
|
SHOW GRANTS;
|
|
connection default;
|
|
--echo #
|
|
--echo # Reparse the mandatory role after each CREATE/DROP USER/ROLE
|
|
--echo #
|
|
CREATE USER u2@localhost IDENTIFIED BY 'foo';
|
|
SET GLOBAL mandatory_roles="rr1";
|
|
FLUSH PRIVILEGES;
|
|
--error ER_CANNOT_USER
|
|
DROP ROLE rr1;
|
|
--echo # Creating the role will cause mandatory_roles to be reparsed
|
|
CREATE ROLE rr1;
|
|
GRANT SELECT ON test.* TO rr1;
|
|
SET GLOBAL activate_all_roles_on_login = 'ON';
|
|
connect(con2,localhost,u2,foo,test,,,);
|
|
SELECT CURRENT_USER();
|
|
--echo # SHOW GRANTS should show that rr1 is granted and effective privileges
|
|
--echo # should contain SELECT ON test.*
|
|
SHOW GRANTS;
|
|
connection default;
|
|
SET GLOBAL activate_all_roles_on_login = 'OFF';
|
|
--echo # Don't show the same role twice.
|
|
SET GLOBAL mandatory_roles="r1, r1";
|
|
SHOW GRANTS;
|
|
disconnect con1;
|
|
disconnect con2;
|
|
|
|
DROP USER u1@localhost;
|
|
DROP USER u2@localhost;
|
|
SET GLOBAL mandatory_roles='PUBLIC';
|
|
DROP ROLE r1,r2,r3,r4,r5,rr1;
|
|
|
|
CREATE USER u1@localhost IDENTIFIED BY 'foo';
|
|
CREATE ROLE r1, r2, r3;
|
|
SET GLOBAL mandatory_roles="aa ; bb : r3, r1, (r2), *r2,";
|
|
connect(con1,localhost,u1,foo,test,,,);
|
|
SHOW GRANTS;
|
|
connection default;
|
|
SET GLOBAL mandatory_roles="PUBLIC";
|
|
DROP USER u1@localhost, r1, r2, r3;
|
|
|
|
--echo
|
|
--echo # Anonymous roles aren't allowed
|
|
SET GLOBAL mandatory_roles='';
|
|
CREATE USER ''@'localhost';
|
|
GRANT ALL ON sys.* TO ''@'localhost';
|
|
--error ER_WRONG_VALUE_FOR_VAR
|
|
SET GLOBAL mandatory_roles='``@`localhost`';
|
|
SHOW GRANTS;
|
|
CREATE USER u1@localhost identified by 'foo';
|
|
GRANT SELECT ON *.* To u1@localhost;
|
|
connect(con2,localhost,u1,foo,test,,,);
|
|
SELECT CURRENT_USER();
|
|
--echo # Should not show any anonymous mandatory role
|
|
SHOW GRANTS;
|
|
connection default;
|
|
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO u1@localhost;
|
|
connection con2;
|
|
SELECT CURRENT_USER();
|
|
SHOW GRANTS;
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
SET GLOBAL mandatory_roles='';
|
|
--error ER_SPECIFIC_ACCESS_DENIED_ERROR
|
|
SET GLOBAL mandatory_roles='foo@localhost';
|
|
connection default;
|
|
disconnect con2;
|
|
--error ER_FAILED_ROLE_GRANT
|
|
GRANT ''@'localhost' TO u1@localhost;
|
|
SET GLOBAL mandatory_roles='';
|
|
DROP USER u1@localhost, ''@'localhost';
|
|
|
|
--echo # SET ROLE DEFAULT ALL should include mandatory roles
|
|
CREATE ROLE r1, r2, r3;
|
|
GRANT SELECT ON *.* TO r1;
|
|
CREATE USER paul@localhost IDENTIFIED BY 'foo';
|
|
GRANT r3 TO paul@localhost;
|
|
GRANT INSERT ON test.* TO r3;
|
|
SET GLOBAL mandatory_roles='r1,r2,r3';
|
|
SET DEFAULT ROLE ALL TO paul@localhost;
|
|
connect(con3,localhost,paul,foo,test,,,);
|
|
--echo # Should show roles r1,r2,r3
|
|
SELECT CURRENT_ROLE();
|
|
SET ROLE ALL;
|
|
--echo # Should still show all mandatory roles
|
|
SELECT CURRENT_ROLE();
|
|
connection default;
|
|
ALTER USER paul@localhost DEFAULT ROLE ALL;
|
|
connection con3;
|
|
SET ROLE DEFAULT;
|
|
--echo # Should show mandatory roles
|
|
SELECT CURRENT_ROLE();
|
|
SET ROLE ALL;
|
|
--echo # Should show roles r1,r2,r3
|
|
SELECT CURRENT_ROLE();
|
|
--echo # no duplicates
|
|
SET ROLE r1,r1,r1,r1,r1,r2,r2,r2,r2,r3,r3,r3,r3;
|
|
SELECT CURRENT_ROLE();
|
|
connection default;
|
|
SET GLOBAL mandatory_roles="PUBLIC";
|
|
DROP USER paul@localhost;
|
|
DROP ROLE r1,r2,r3;
|
|
disconnect con3;
|
|
|