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

     1  set global enable_privilege_cache = off;
     2  -- @setup
     3  --alter account auth_option: password
     4  create account if not exists Abc ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment 'comment test';
     5  alter account abc admin_name='admin'  IDENTIFIED BY '1WERDFT3YG';
     6  -- @session:id=1&user=abc:admin&password=1WERDFT3YG
     7  select user_name,authentication_string from mo_catalog.mo_user;
     8  create database testdb;
     9  drop database testdb;
    10  -- @session
    11  alter account abc admin_name='admin'  IDENTIFIED BY 'yyyy_34lifel';
    12  -- @session:id=1&user=abc:admin&password=yyyy_34lifel
    13  select user_name,authentication_string from mo_catalog.mo_user;
    14  -- @session
    15  alter account abc admin_name='admin'  IDENTIFIED BY 'abcddddddfsfafaffsefsfsefljofiseosfjosissssssssssssssssssssssssssssssssssssssssssssssssssssssssssss';
    16  -- @session:id=1&user=abc:admin&password=abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff` admin_name `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffffdddddd
    17  select user_name,authentication_string from mo_catalog.mo_user;
    18  show databases;
    19  -- @session
    20  alter account abc admin_name='admin'  IDENTIFIED BY 'Ni7893';
    21  -- @session:id=1&user=abc:admin&password=Ni7893
    22  select user_name,authentication_string from mo_catalog.mo_user;
    23  -- @session
    24  alter account abc admin_name='admin'  IDENTIFIED BY '_1234';
    25  -- @session:id=1&user=abc:admin%&password=_1234
    26  select user_name,authentication_string from mo_catalog.mo_user;
    27  -- @session
    28  alter account abc admin_name='admin'  IDENTIFIED BY 'nnnn@12.fef';
    29  -- @session:id=1&user=abc:admin&password=nnnn@12.fef
    30  select user_name,authentication_string from mo_catalog.mo_user;
    31  -- @session
    32  alter account abc admin_name='admin'  IDENTIFIED BY '密码';
    33  -- @session:id=1&user=abc:admin&password=密码
    34  select user_name,authentication_string from mo_catalog.mo_user;
    35  -- @session
    36  alter account abc admin_name='admin'  IDENTIFIED BY '123 456';
    37  -- @session:id=1&user=abc:admin&password=123 456
    38  select user_name,authentication_string from mo_catalog.mo_user;
    39  -- @session
    40  alter account abc admin_name='admin'  IDENTIFIED BY 'test:aaa';
    41  -- @session:id=1&user=abc:admin&password=test:aaa
    42  select user_name,authentication_string from mo_catalog.mo_user;
    43  -- @session
    44  drop account abc;
    45  
    46  --alter admin_name /password is null
    47  create account if not exists test ADMIN_NAME '1WERDFT3YG' IDENTIFIED BY '123456';
    48  alter account test admin_name='1WERDFT3YG'  IDENTIFIED BY '';
    49  
    50  --alter not exist account ,alter if exists,admin_name not exist
    51  alter account not_exist_account ADMIN_NAME 'admin' IDENTIFIED BY '123456';
    52  alter account if exists not_exist_account ADMIN_NAME 'admin' IDENTIFIED BY '123456';
    53  alter account test ADMIN_NAME 'testaaa' IDENTIFIED BY '123456';
    54  alter account if exists test ADMIN_NAME 'testaaa' IDENTIFIED BY '123456';
    55  drop account test;
    56  
    57  --alter account same admin_name and password
    58  create account if not exists test ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment 'account comment';
    59  alter account test admin_name='admin'  IDENTIFIED BY '123456';
    60  -- @session:id=2&user=test:admin&password=123456
    61  select user_name,authentication_string from mo_catalog.mo_user;
    62  -- @session
    63  drop account test;
    64  
    65  --after create new user role ,alter account admin_name
    66  create account if not exists alaccount ADMIN_NAME 'WERTY12ERT' IDENTIFIED BY '123456' comment 'account comment';
    67  -- @session:id=3&user=alaccount:WERTY12ERT&password=123456
    68  create user 'al_user_1' identified by '123456';
    69  create role if not exists al_role;
    70  grant all on account * to al_role;
    71  grant al_role to al_user_1;
    72  create database al_db;
    73  -- @session
    74  alter account alaccount ADMIN_NAME 'WERTY12ERT' IDENTIFIED BY 'abc@123';
    75  -- @session:id=3&user=alaccount:WERTY12ERT&password=abc@123
    76  select user_name,authentication_string from mo_catalog.mo_user where user_name='al_user_1';
    77  select role_name,comments from mo_catalog.mo_role;
    78  -- @session
    79  -- @session:id=3&user=alaccount:WERTY12ERT&password=abc@123
    80  show databases;
    81  drop database al_db;
    82  -- @session
    83  drop account alaccount;
    84  
    85  --alter account comment
    86  create account if not exists testcomment ADMIN_NAME 'test_user' IDENTIFIED BY 'Asd1235' comment 'account comment';
    87  alter account testcomment comment 'new account comment';
    88  select account_name,comments from mo_catalog.mo_account where account_name='testcomment';
    89  -- @session:id=8&user=testcomment:test_user&password=Asd1235
    90  show databases;
    91  -- @session
    92  alter account testcomment comment '';
    93  select account_name,comments from mo_catalog.mo_account where account_name='testcomment';
    94  alter account testcomment comment 'abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff';
    95  select account_name,comments from mo_catalog.mo_account where account_name='testcomment';
    96  alter account testcomment comment '周三下午18：00';
    97  select account_name,comments from mo_catalog.mo_account where account_name='testcomment';
    98  alter account if exists testcomment comment '177634853$%^&*!@()';
    99  select account_name,comments from mo_catalog.mo_account where account_name='testcomment';
   100  alter account if exists testcomment1 comment '177634853$%^&*!@()';
   101  alter account testcomment1 comment '177634853$%^&*!@()';
   102  drop account testcomment;
   103  
   104  --alter account status_option: OPEN|SUSPEND
   105  create account if not exists testsuspend ADMIN_NAME 'admin' IDENTIFIED BY '123456' comment 'account comment';
   106  -- @session:id=4&user=testsuspend:admin&password=123456
   107  select user_name, authentication_string from mo_catalog.mo_user;
   108  -- @session
   109  alter account testsuspend suspend;
   110  select account_name,status from mo_catalog.mo_account order by account_name;
   111  alter account testsuspend OPEN;
   112  select account_name,status from mo_catalog.mo_account order by account_name;
   113  --suspend status alter ADMIN_NAME/comment,drop account
   114  alter account testsuspend suspend;
   115  select account_name,status from mo_catalog.mo_account order by account_name;
   116  alter account testsuspend ADMIN_NAME 'admin' IDENTIFIED BY '1234567890';
   117  alter account testsuspend comment 'aaaaaaa';
   118  select account_name,status,comments from mo_catalog.mo_account where account_name='testsuspend';
   119  drop account testsuspend;
   120  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   121  alter account testsuspend open;
   122  
   123  --reopen an already opened account or resuspend an already suspended account, there is no error
   124  create account if not exists testsuspend ADMIN_NAME 'user01' IDENTIFIED BY 'fffff' comment 'account comment';
   125  alter account testsuspend OPEN;
   126  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   127  alter account testsuspend OPEN;
   128  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   129  alter account testsuspend suspend;
   130  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   131  alter account testsuspend suspend;
   132  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   133  drop account testsuspend;
   134  select account_name,status from mo_catalog.mo_account where account_name='testsuspend';
   135  
   136  --Illegal syntax
   137  create account if not exists test ADMIN_NAME 'adminuser' IDENTIFIED BY '123456' comment 'account comment';
   138  alter account test admin_name='adminuser'  IDENTIFIED BY '123456' comment 'new comment ' ;
   139  alter account test admin_name='adminuser'  IDENTIFIED BY '123456' suspend comment 'new comment';
   140  alter account test suspend comment 'new comment';
   141  alter account test admin_name='adminuser';
   142  drop account test;
   143  
   144  --Executed in a non moadmin role
   145  drop user if exists al_user_2;
   146  create user 'al_user_2' identified by '123456';
   147  create role if not exists al_role2;
   148  grant all on account * to al_role2;
   149  grant al_role2 to al_user_2;
   150  create account if not exists test ADMIN_NAME '123ERTYU' IDENTIFIED BY '123ERTYU' comment 'account comment';
   151  -- @session:id=5&user=sys:al_user_2:al_role2&password=123456
   152  alter account test admin_name='adminuser'  IDENTIFIED BY '123456';
   153  alter account test comment 'ccccccc';
   154  alter account test suspend;
   155  -- @session
   156  drop role if exists al_role2;
   157  drop user if exists al_user_2;
   158  drop account test;
   159  set global enable_privilege_cache = on;