github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/zz_accesscontrol/revoke_privs_role.sql (about)

     1  --env prepare statement
     2  drop database if exists revoke_db_01;
     3  drop role if exists revoke_role_1,revoke_role_2,revoke_role_3,revoke_role_4,revoke_role_5,revoke_role_6,revoke_role_7,revoke_role_8;
     4  drop user if exists revoke_user_1,revoke_user_2,revoke_user_3,revoke_user_4,revoke_user_5,revoke_user_6,revoke_user_7;
     5  create role if not exists revoke_role_1,revoke_role_2,revoke_role_3,revoke_role_4,revoke_role_5,revoke_role_6,revoke_role_7,revoke_role_8;
     6  create user if not exists revoke_user_1 identified by '12345678',revoke_user_2 identified by '12345678',revoke_user_3 identified by '12345678',revoke_user_4 identified by '12345678',revoke_user_5 identified by '12345678',revoke_user_6 identified by '12345678',revoke_user_7 identified by '12345678';
     7  create database revoke_db_01;
     8  
     9  --revoke多个权限from一个role，覆盖部分/全部，异常情况：无该权限，role不存在
    10  grant create user, drop user, alter user, create role, drop role, create database,drop database on account * to revoke_role_1 with grant option;
    11  grant revoke_role_1 to revoke_user_1;
    12  revoke create user, drop user, alter user on account * from revoke_role_1;
    13  select role_name, privilege_name, privilege_level from mo_catalog.mo_role_privs where role_name='revoke_role_1';
    14  -- @session:id=2&user=sys:revoke_user_1:revoke_role_1&password=12345678
    15  create user re_test_user identified by '12345678';
    16  drop user re_test_user;
    17  create role revoke_role_9;
    18  drop role revoke_role_9;
    19  -- @session
    20  revoke all on account * from revoke_role_3;
    21  revoke create user, drop user, show tables on account * from revoke_role_1;
    22  revoke create user, drop user, show tables on table *.* from revoke_role_1;
    23  revoke create user, drop user on account * from re_not_exists;
    24  revoke all on account * from revoke_role_1;
    25  
    26  --revoke多个权限from多个role，覆盖回收部分/全部，异常情况：无该权限，role不存在,if exists
    27  grant create table,drop table,alter table on database *.* to revoke_role_2,revoke_role_3 with grant option;
    28  grant all on account * to revoke_role_2;
    29  grant revoke_role_2 to revoke_user_2;
    30  grant revoke_role_3 to revoke_user_3;
    31  revoke drop table,create table on database *.* from revoke_role_2,revoke_role_3;
    32  select role_name, privilege_name, privilege_level from mo_catalog.mo_role_privs where role_name in ('revoke_role_2','revoke_role_3');
    33  -- @session:id=2&user=sys:revoke_user_2:revoke_role_2&password=12345678
    34  create table revoke_db_01.revoke_table_1(a int,b varchar(20),c double);
    35  drop table revoke_db_01.revoke_table_1;
    36  create database revoke_db_02;
    37  drop database revoke_db_02;
    38  -- @session
    39  -- @session:id=2&user=sys:revoke_user_3:revoke_role_3&password=12345678
    40  create table revoke_db_01.revoke_table_1(a int,b varchar(20),c double);
    41  drop table revoke_db_01.revoke_table_1;
    42  -- @session
    43  revoke create table,select,insert on database * from revoke_role_2,revoke_role_3;
    44  revoke if exists create table,select,insert on database * from revoke_role_2,revoke_role_3;
    45  revoke all on account * from revoke_role_2,revoke_role_3;
    46  revoke if exists all on account * from revoke_role_2,revoke_role_3;
    47  
    48  --revoke一个权限from多个role
    49  grant all on table *.* to revoke_role_4,revoke_role_5 with grant option;
    50  grant create table,drop table,alter table on database *.* to revoke_role_5;
    51  grant revoke_role_4 to revoke_user_4 with grant option;
    52  grant revoke_role_5 to revoke_user_5;
    53  revoke all on table *.* from revoke_role_4,revoke_role_5;
    54  select role_name, privilege_name, privilege_level from mo_catalog.mo_role_privs where role_name in ('revoke_role_4','revoke_role_5');
    55  -- @session:id=2&user=sys:revoke_user_4:revoke_role_4&password=12345678
    56  select * from mo_catalog.mo_user;
    57  -- @session
    58  -- @session:id=2&user=sys:revoke_user_5:revoke_role_5&password=12345678
    59  select * from mo_catalog.mo_user;
    60  create table revoke_db_01.revoke_table_2(a int,b varchar(20),c double);
    61  drop table revoke_db_01.revoke_table_2;
    62  -- @session
    63  
    64  --revoke ownership
    65  grant ownership on database revoke_db_01 to revoke_role_6;
    66  grant all on table *.* to revoke_role_6;
    67  grant revoke_role_6 to revoke_user_6;
    68  revoke ownership on database revoke_db_01 from revoke_role_6;
    69  -- @session:id=2&user=sys:revoke_user_6:revoke_role_6&password=12345678
    70  create table revoke_test_table_1(a int);
    71  drop table revoke_test_table_1;
    72  grant ownership on database revoke_db_01 to revoke_role_7;
    73  select * from revoke_db_01.revoke_table_1;
    74  -- @session
    75  
    76  drop database if exists revoke_db_01;
    77  drop role if exists revoke_role_1,revoke_role_2,revoke_role_3,revoke_role_4,revoke_role_5,revoke_role_6,revoke_role_7,revoke_role_8;
    78  drop user if exists revoke_user_1,revoke_user_2,revoke_user_3,revoke_user_4,revoke_user_5,revoke_user_6,revoke_user_7;