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

     1  --env prepare statement
     2  drop role if exists use_role_1,use_role_2,use_role_3,use_role_4,use_role_5;
     3  drop user if exists use_user_1,use_user_2;
     4  drop database if exists use_db_1;
     5  create role use_role_1,use_role_2,use_role_3,use_role_4,use_role_5;
     6  create database use_db_1;
     7  
     8  --default指定主要角色，切换角色set secondary role all/none,use role来回切换，set role public
     9  create user use_user_1 identified by '123456' default role use_role_1;
    10  grant select ,insert ,update on table *.* to use_role_1;
    11  grant all on database * to use_role_2;
    12  grant use_role_2 to use_user_1;
    13  -- @session:id=2&user=sys:use_user_1&password=123456
    14  create table use_db_1.use_table_1(a int,b varchar(20),c double );
    15  set secondary role all;
    16  create table use_db_1.use_table_1(a int,b varchar(20),c double );
    17  select * from use_db_1.use_table_1;
    18  insert into use_db_1.use_table_1 values(34,'kelly',90.3);
    19  set role use_role_2;
    20  create table use_db_1.use_table_2(a int,b varchar(20),c double );
    21  insert into use_db_1.use_table_2 values(34,'kelly',90.3);
    22  select * from use_db_1.use_table_2;
    23  set secondary role none;
    24  create table use_db_1.use_table_3(a int,b varchar(20),c double );
    25  insert into use_db_1.use_table_3 values(34,'kelly',90.3);
    26  select * from use_db_1.use_table_3;
    27  drop table use_db_1.use_table_3;
    28  set role use_role_1;
    29  create table use_db_1.use_table_4(a int,b varchar(20),c double );
    30  insert into use_db_1.use_table_2 values(10,'yellow',99.99);
    31  select * from use_db_1.use_table_2;
    32  drop table use_db_1.use_table_2;
    33  set role public;
    34  create table use_db_1.use_table_4(a int,b varchar(20),c double );
    35  insert into use_db_1.use_table_2 values(10,'yellow',99.99);
    36  select * from use_db_1.use_table_2;
    37  drop table use_db_1.use_table_2;
    38  set secondary role all;
    39  create table use_db_1.use_table_5(a int,b varchar(20),c double );
    40  insert into use_db_1.use_table_5 values(10,'yellow',99.99);
    41  select * from use_db_1.use_table_5;
    42  drop table use_db_1.use_table_5;
    43  set role moadmin;
    44  -- @session
    45  
    46  --缺省default指定主要角色，grant/alter指定主要角色，切换角色set secondary role all/none,use role来回切换，切换角色/不存在角色，set 用户没有被授权的role
    47  create user use_user_2 identified by '123456';
    48  grant create user, drop user, alter user, create role, drop role, create database,drop database,show databases on account *  to use_role_3;
    49  grant all on table *.* to use_role_4;
    50  grant create table,drop table on database * to use_role_5;
    51  grant use_role_3,use_role_4,use_role_5 to use_user_2;
    52  -- @session:id=2&user=sys:use_user_2&password=123456
    53  set role use_not_exists;
    54  set role use_role_3;
    55  create role use_role_test;
    56  set role use_role_test;
    57  drop role  use_role_test;
    58  set secondary role all;
    59  create table use_db_1.use_table_6(a int,b varchar(20),c double);
    60  insert into use_db_1.use_table_6 values(10,'yellow',99.99);
    61  create database use_db_test;
    62  drop database use_db_test;
    63  set secondary role none;
    64  insert into use_db_1.use_table_6 values (10, 'yellow', 99.99);
    65  drop table use_db_1.use_table_6;
    66  create role if not exists use_role_test;
    67  drop role use_role_test;
    68  set role use_role_5;
    69  drop table use_db_1.use_table_6;
    70  create database if not exists use_db_test;
    71  -- @session
    72  
    73  drop role if exists use_role_1,use_role_2,use_role_3,use_role_4,use_role_5;
    74  drop user if exists use_user_1,use_user_2;
    75  drop database if exists use_db_1;