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