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

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