mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
180 lines
5.2 KiB
Text
180 lines
5.2 KiB
Text
#
|
|
# MDEV-29458 Role grant commands do not propagate all grants
|
|
#
|
|
create user foo;
|
|
create database some_db;
|
|
create table some_db.t1 (a int, b int, secret int);
|
|
CREATE PROCEDURE some_db.p1 (OUT param1 INT)
|
|
BEGIN
|
|
SELECT COUNT(*) INTO param1 FROM some_db.t1;
|
|
END;
|
|
//
|
|
CREATE FUNCTION some_db.f1 (param1 INT) RETURNS INT
|
|
BEGIN
|
|
DECLARE c INT;
|
|
SET c = 100;
|
|
RETURN param1 + c;
|
|
END;
|
|
//
|
|
#
|
|
# These roles will form a two level hierarchy.
|
|
# The "select" role will have the select privilege, while
|
|
# the active role will inherit the select role.
|
|
#
|
|
# The active role will be granted a different privilege but on the same
|
|
# level (global, database, table, proc respectively) *after* the 'select'
|
|
# role has been granted its select privilege.
|
|
#
|
|
create role r_select_global;
|
|
create role r_active_global;
|
|
create role r_select_database;
|
|
create role r_active_database;
|
|
create role r_select_table;
|
|
create role r_active_table;
|
|
create role r_select_column;
|
|
create role r_active_column;
|
|
create role r_execute_proc;
|
|
create role r_active_proc;
|
|
create role r_execute_func;
|
|
create role r_active_func;
|
|
grant r_select_global to r_active_global;
|
|
grant r_select_database to r_active_database;
|
|
grant r_select_table to r_active_table;
|
|
grant r_select_column to r_active_column;
|
|
grant r_execute_proc to r_active_proc;
|
|
grant r_execute_func to r_active_func;
|
|
#
|
|
# These 3 roles form a chain, where only the upper level has select
|
|
# privileges and the middle level will receive a grant for the same level
|
|
# privilege, but different kind (for example select on upper and insert
|
|
# on middle).
|
|
#
|
|
# The lower level should inherit both rights.
|
|
#
|
|
create role upper_level;
|
|
create role middle_level;
|
|
create role lower_level;
|
|
grant upper_level to middle_level;
|
|
grant middle_level to lower_level;
|
|
grant r_active_global to foo;
|
|
grant r_active_database to foo;
|
|
grant r_active_table to foo;
|
|
grant r_active_column to foo;
|
|
grant r_active_proc to foo;
|
|
grant r_active_func to foo;
|
|
grant lower_level to foo;
|
|
grant select on *.* to r_select_global;
|
|
grant select on some_db.* to r_select_database;
|
|
grant select on some_db.t1 to r_select_table;
|
|
grant select(a) on some_db.t1 to r_select_column;
|
|
grant select on *.* to upper_level;
|
|
grant execute on procedure some_db.p1 to r_execute_proc;
|
|
grant execute on function some_db.f1 to r_execute_func;
|
|
#
|
|
# Granting a privilege different than select on the corresponding level.
|
|
# This tests that the base role correctly inherits its granted roles
|
|
# privileges.
|
|
#
|
|
grant insert on *.* to r_active_global;
|
|
grant insert on some_db.* to r_active_database;
|
|
grant insert on some_db.t1 to r_active_table;
|
|
grant insert(a) on some_db.t1 to r_active_column;
|
|
grant insert on *.* to middle_level;
|
|
grant alter routine on procedure some_db.p1 to r_active_proc;
|
|
grant alter routine on function some_db.f1 to r_active_func;
|
|
connect con1, localhost, foo,,;
|
|
select * from some_db.t1;
|
|
ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table `some_db`.`t1`
|
|
#
|
|
# Before MDEV-29458 fix, all these commands would return
|
|
# ER_TABLEACCESS_DENIED_ERROR
|
|
#
|
|
set role r_active_global;
|
|
select * from some_db.t1;
|
|
a b secret
|
|
set role r_active_database;
|
|
select * from some_db.t1;
|
|
a b secret
|
|
set role r_active_table;
|
|
select * from some_db.t1;
|
|
a b secret
|
|
set role r_active_column;
|
|
select a from some_db.t1;
|
|
a
|
|
set role lower_level;
|
|
select * from some_db.t1;
|
|
a b secret
|
|
set role r_active_proc;
|
|
set @var=100;
|
|
call some_db.p1(@var);
|
|
set role r_active_func;
|
|
select some_db.f1(10);
|
|
some_db.f1(10)
|
|
110
|
|
disconnect con1;
|
|
#
|
|
# Cleanup.
|
|
#
|
|
connection default;
|
|
drop database some_db;
|
|
drop role r_select_global, r_select_database, r_select_table, r_select_column;
|
|
drop role r_active_global, r_active_database, r_active_table, r_active_column;
|
|
drop role r_execute_proc, r_execute_func;
|
|
drop role r_active_proc, r_active_func;
|
|
drop role upper_level, middle_level, lower_level;
|
|
drop user foo;
|
|
#
|
|
# Test that dropping of roles clears the intermediate generated
|
|
# (such as an `acl_dbs` element with 0 init_access, but with access != 0)
|
|
# datastructures.
|
|
#
|
|
create role test_role1;
|
|
create role test_role2;
|
|
grant test_role2 to test_role1;
|
|
grant select on mysql.* to test_role2;
|
|
grant select on mysql.user to test_role2;
|
|
grant select(user) on mysql.user to test_role2;
|
|
drop role test_role1, test_role2;
|
|
create role test_role1;
|
|
drop role test_role1;
|
|
#
|
|
# MDEV-29851 Cached role privileges are not invalidated when needed
|
|
#
|
|
create role admin;
|
|
create role student;
|
|
create database crm;
|
|
grant create on crm.* to admin;
|
|
grant select on crm.* to student;
|
|
create user intern@localhost;
|
|
grant student to intern@localhost;
|
|
set default role student for intern@localhost;
|
|
connect con1, localhost, intern;
|
|
use crm;
|
|
disconnect con1;
|
|
connection default;
|
|
grant admin to student;
|
|
connect con1, localhost, intern;
|
|
use crm;
|
|
create table t1 (a int);
|
|
disconnect con1;
|
|
connection default;
|
|
drop user intern@localhost;
|
|
drop role student;
|
|
drop role admin;
|
|
drop database crm;
|
|
#
|
|
# MDEV-30526 Assertion `rights == merged->cols' failed in update_role_columns
|
|
#
|
|
create table t1 ( pk int, i int);
|
|
create role a;
|
|
grant select (i), update (pk) on t1 to a;
|
|
revoke update (pk) on t1 from a;
|
|
show grants for a;
|
|
Grants for a
|
|
GRANT USAGE ON *.* TO `a`
|
|
GRANT SELECT (`i`) ON `test`.`t1` TO `a`
|
|
drop role a;
|
|
drop table t1;
|
|
#
|
|
# End of 10.3 tests
|
|
#
|