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

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