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

     1  --env prepare statement
     2  drop role if exists newrole, role_1234,12role,`role@hhhh123`,`role.123`,_newrole,role222;
     3  drop role if exists role1,role2,role3,role4,role5,role6,role_7,user_role,u_role;
     4  drop role if exists role_1,role_2,role_3,role_4,role_5,role_6,role_7,'中文','12345','default';;
     5  drop role if exists `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff`;
     6  drop user if exists role_user,user_role;
     7  drop role if exists role_role1,role_role2,role_role3,role_role4,role_role5,role_role6,role_role7,role_role8,u_role;
     8  drop database  if exists p_db;
     9  --1.rolename支持字符，数字，特殊字符混合,中文,大小写不敏感,覆盖"",'',``,及去除头尾空格
    10  create role newrole, role_1234,12role,`role@hhhh123`,`role.123`,_newrole,RoLe222;
    11  create role NewRole;
    12  select role_name from mo_catalog.mo_role where role_name in ('newrole', 'role_1234','12role','role@hhhh123','role.123','_newrole','role222');
    13  create role "role_1",'role_2',`role_3`;
    14  select role_name from mo_catalog.mo_role where role_name in ('role_1','role_2','role_3');
    15  create role " role_4 ",' role_5 ',` role_6 `;
    16  select role_name from mo_catalog.mo_role where role_name in ('role_4','role_5','role_6');
    17  create role '中文','12345';
    18  select role_name from mo_catalog.mo_role where role_name in ('中文','12345');
    19  create role 'abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff';
    20  select role_name from mo_catalog.mo_role where role_name in ('abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff');
    21  create role 'default';
    22  select role_name from mo_catalog.mo_role where role_name in ('default');
    23  
    24  --2.异常测试:语法错误,rolename包含冒号，关键字，rolename和rolename同名,rolename为空
    25  create if not exists role select;
    26  create role a b c;
    27  create role 'test:abc';
    28  create role "test:abc";
    29  create role `test:abc`;
    30  create role default;
    31  create user user_role identified by '12345678';
    32  create role user_role;
    33  create role '';
    34  
    35  --3.rolename已存在/不存在，包含初始化moadmin，public,if not exits存在/不存在
    36  create role moadmin,public;
    37  create role if not exists moadmin,public;
    38  select role_name from mo_catalog.mo_role where role_name in ('moadmin','public');
    39  create role if not exists role_7;
    40  select role_name from mo_catalog.mo_role where role_name = 'role_7';
    41  
    42  --4.一次性创建多个role都不存在，部分存在，全部存在，名字非法，管理员角色，if not exits
    43  use mo_catalog;
    44  create role if not exists role_role1,role_role2,role_role3,role_role4,role_role5,role_role6;
    45  select role_name from mo_role where role_name like 'role_role%' order by role_name;
    46  create role role_role1,role_role2,role_role3,role_role7,role_role8;
    47  create role if not exists role_role1,role_role2,role_role3,role_role7,role_role8;
    48  select role_name from mo_role where role_name like 'role_role%' order by role_name;
    49  create role role_role9,role_role:10,role_role11;
    50  select role_name from mo_role where role_name like 'role_role%' order by role_name;
    51  
    52  --6.覆盖CREATE-grant-DROP-CREATE,CREATE-grant-grant场景
    53  create role if not exists role2;
    54  create user role_user identified by '111111';
    55  grant insert,select on table *.* to role2;
    56  use mo_catalog;
    57  select role_name,obj_type,privilege_name,privilege_level from mo_role_privs where role_name='role2';
    58  grant role2 to role_user;
    59  select user_name,role_name,obj_type,privilege_name,privilege_level from mo_user_grant,mo_user,mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role2';
    60  drop role role2;
    61  select count(*) from mo_role_privs where role_name='role2';
    62  select count(*) from mo_user_grant,mo_user,mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role2';
    63  create role role2;
    64  grant insert,select on table *.* to role2;
    65  select role_name,obj_type,privilege_name,privilege_level from mo_role_privs where role_name='role2';
    66  create role if not exists role3;
    67  grant create database on account * to role3;
    68  select role_name,obj_type,privilege_name,privilege_level from mo_role_privs where role_name='role3';
    69  grant role3 to role_user;
    70  grant ownership on table *.* to role3;
    71  grant role3 to role_user;
    72  select user_name,role_name,obj_type,privilege_name,privilege_level from mo_user_grant,mo_user,mo_role_privs where mo_user_grant.user_id=mo_user.user_id and mo_role_privs.role_id=mo_user_grant.role_id and role_name='role3';
    73  
    74  --prepare,set,execute权限验证
    75  drop user if exists user_prepare_01;
    76  drop role if exists role_prepare_1;
    77  create user user_prepare_01 identified by '123456';
    78  create role role_prepare_1;
    79  create database if not exists p_db;
    80  grant create table ,drop table on database *.*  to role_prepare_1;
    81  grant connect on account * to role_prepare_1;
    82  grant insert,select on table *.* to role_prepare_1;
    83  grant role_prepare_1 to user_prepare_01;
    84  -- @session:id=17&user=sys:user_prepare_01:role_prepare_1&password=123456
    85  use p_db;
    86  prepare stmtt from 'drop table if exists  grant_table_30';
    87  execute stmtt;
    88  prepare stmt from 'create table grant_table_30(a int)';
    89  execute stmt;
    90  prepare stmt1 from 'insert into grant_table_30 values(?)';
    91  set @a=55;
    92  execute stmt1 using @a;
    93  prepare stmt2 from 'select * from grant_table_30';
    94  execute stmt2;
    95  prepare stmt2 from 'update grant_table_30 set a=60';
    96  execute stmt2;
    97  prepare stmt2 from 'insert into grant_table_30 select 89';
    98  execute stmt2;
    99  prepare stmt3 from 'insert into grant_table_30 select * from grant_table_30';
   100  execute stmt3;
   101  prepare stmt4 from 'show databases';
   102  execute stmt4;
   103  prepare stmt5 from 'select "abc"';
   104  execute stmt5;
   105  -- @session
   106  
   107  drop role if exists newrole, role_1234,12role,`role@hhhh123`,`role.123`,_newrole,role222;
   108  drop role if exists role1,role2,role3,role4,role5,role6,role_7,user_role,u_role;
   109  drop role if exists role_1,role_2,role_3,role_4,role_5,role_6,role_7,'中文','12345','default';;
   110  drop role if exists `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff`;
   111  drop user if exists role_user,user_role;
   112  drop role if exists role_role1,role_role2,role_role3,role_role4,role_role5,role_role6,role_role7,role_role8,u_role;
   113  drop database  if exists p_db;