github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/zz_accesscontrol/drop_role.sql (about)

     1  set global enable_privilege_cache = off;
     2  -- env prepare statement
     3  drop role if exists drop_role1,drop_role2,drop_role3,drop_role4,drop_role5,drop_role6,drop_role7,drop_role8,drop_role9,drop_role10;
     4  drop role if exists drop_role_001,drop_role_002,drop_role_1,drop_role_2;
     5  drop user if exists drop_user_1,drop_user_2;
     6  
     7  -- 1.drop role存在/不存在
     8  create role drop_role_001;
     9  drop role drop_role_001;
    10  select role_name from mo_catalog.mo_role where role_name='drop_role_001';
    11  drop role role_name;
    12  
    13  --2.drop if exists存在/不存在
    14  drop role if exists drop_role_002;
    15  create role drop_role_002;
    16  drop role if exists drop_role_002;
    17  select role_name from mo_catalog.mo_role where role_name='drop_role_002';
    18  
    19  --3.异常测试：空值，内置role，语法错误
    20  drop role '';
    21  drop role moadmin;
    22  drop role public;
    23  drop if not exists d;
    24  drop role if not exists d;
    25  
    26  --4.一次删除多个role情况，覆盖多个role中有不存在role，无权限删除role
    27  create role if not exists drop_role1,drop_role2,drop_role3,drop_role4,drop_role5,drop_role6,drop_role7,drop_role8,drop_role9,drop_role10;
    28  drop role drop_role1,drop_role2,drop_role3,drop_role11;
    29  drop role if exists drop_role1,drop_role2,drop_role3,drop_role11;
    30  drop role if exists drop_role4,drop_role5,drop_role6,root,drop_role8;
    31  drop role if exists drop_role9,drop_role10;
    32  select role_name from mo_catalog.mo_role where role_name like 'drop_role%';
    33  
    34  --5.role with grant option 后drop role
    35  create role drop_role_1,drop_role_2;
    36  create user drop_user_1 identified by '111',drop_user_2 identified by '111';
    37  grant all on table *.* to drop_role_1 with grant option;
    38  grant drop_role_1 to drop_user_1;
    39  grant drop_role_2 to drop_user_2;
    40  select role_name from mo_catalog.mo_role where role_name in ('drop_role_1','drop_role_2');
    41  grant drop_role_1 to drop_role_2;
    42  drop role drop_role_1;
    43  select role_name from mo_catalog.mo_role where role_name in ('drop_role_1','drop_role_2');
    44  select role_name from mo_catalog.mo_user_grant mug ,mo_catalog.mo_role mr where mug.role_id=mr.role_id and mr.role_name in ('drop_role_1','drop_role_2');
    45  
    46  drop role if exists drop_role1,drop_role2,drop_role3,drop_role4,drop_role5,drop_role6,drop_role7,drop_role8,drop_role9,drop_role10;
    47  drop role if exists drop_role_001,drop_role_002,drop_role_1,drop_role_2;
    48  drop user if exists drop_user_1,drop_user_2;
    49  set global enable_privilege_cache = on;