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

     1  set global enable_privilege_cache = off;
     2  -- env prepare statement
     3  drop user if exists drop_user_1,drop_user_2,drop_user_3,drop_user_4,drop_user_5,drop_user_6,drop_user_7,drop_user_8,drop_user_9,drop_user_10,drop_user_11;
     4  drop user if exists drop_user_111,drop_user_112;
     5  drop role if exists drop_u_role_1,drop_u_role_2;
     6  -- 1.drop user存在/不存在
     7  create user drop_user_1 identified by '111';
     8  drop user drop_user_1;
     9  select user_name,authentication_string from mo_catalog.mo_user where user_name='drop_user_1';
    10  drop user drop_user_1;
    11  
    12  --2.drop if exists存在/不存在
    13  drop user if exists drop_user_2;
    14  create user drop_user_2 identified by '111' comment '';
    15  select user_name,authentication_string from mo_catalog.mo_user where user_name='drop_user_2';
    16  drop user if exists drop_user_2;
    17  select user_name,authentication_string from mo_catalog.mo_user where user_name='drop_user_2';
    18  
    19  --3.异常测试：空值，内置user，语法错误
    20  drop user "";
    21  drop user root;
    22  drop user dump;
    23  drop if not exists d;
    24  drop user if not exists d;
    25  
    26  --4.一次删除多个user情况，覆盖多个用户中有不存在用户，无权限删除用户
    27  create user drop_user_3 identified by '12345678',drop_user_4 identified by '12345678',drop_user_5 identified by '12345678',drop_user_6 identified by '12345678',drop_user_7 identified by '12345678',drop_user_8 identified by '12345678',drop_user_9 identified by '12345678',drop_user_10 identified by '12345678';
    28  select user_name,authentication_string from mo_catalog.mo_user where user_name like 'drop_user_%';
    29  drop user drop_user_3,drop_user_4,drop_user_5;
    30  select user_name,authentication_string from mo_catalog.mo_user where user_name like 'drop_user_%';
    31  drop user drop_user_3,drop_user_4,drop_user_5,drop_user_6,drop_user_7;
    32  select user_name,authentication_string from mo_catalog.mo_user where user_name like 'drop_user_%';
    33  drop user if exists drop_user_3,drop_user_4,drop_user_5,drop_user_6,drop_user_7;
    34  select user_name,authentication_string from mo_catalog.mo_user where user_name like 'drop_user_%';
    35  drop user if exists drop_user_8;
    36  select user_name,authentication_string from mo_catalog.mo_user where user_name in ('drop_user_8');
    37  drop user drop_user_8,drop_user_user;
    38  
    39  --5.role to user with grant option后drop user
    40  create role drop_u_role_1,drop_u_role_2;
    41  create user drop_user_111 identified by '111',drop_user_112 identified by '111';
    42  grant all on table *.* to drop_u_role_1 with grant option;
    43  grant drop_u_role_1 to drop_user_111 with grant option;
    44  grant drop_u_role_2 to drop_user_112;
    45  select role_name from mo_catalog.mo_role where role_name in ('drop_u_role_1','drop_u_role_2');
    46  grant drop_u_role_1 to drop_u_role_2;
    47  drop user drop_user_111;
    48  select user_name,authentication_string from mo_catalog.mo_user where  user_name='drop_user_111';
    49  select role_name from mo_catalog.mo_role where role_name ='drop_u_role_1';
    50  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_u_role_1','drop_u_role_2');
    51  
    52  drop user if exists drop_user_1,drop_user_2,drop_user_3,drop_user_4,drop_user_5,drop_user_6,drop_user_7,drop_user_8,drop_user_9,drop_user_10,drop_user_11;
    53  drop user if exists drop_user_111,drop_user_112;
    54  drop role if exists drop_u_role_1,drop_u_role_2;
    55  set global enable_privilege_cache = on;