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

     1  set global enable_privilege_cache = off;
     2  drop account if exists  acc1;
     3  create account if not exists acc1 ADMIN_NAME 'admin' IDENTIFIED BY '123';
     4  alter account acc1 restricted;
     5  
     6  -- @session:id=1&user=acc1:admin&password=123
     7  show databases;
     8  create table r_test(c1 int);
     9  insert into r_test values(3);
    10  update r_test set c1=5;
    11  truncate table r_test;
    12  drop table r_test;
    13  -- @session
    14  drop account if exists acc1;
    15  
    16  create account if not exists acc1 ADMIN_NAME 'admin' IDENTIFIED BY '123efg' comment 'account comment';
    17  -- @session:id=5&user=acc1:admin&password=123efg
    18  create database res_test;
    19  use res_test;
    20  create table r_test(c1 int,c2 varchar(20), unique index ui(c1));
    21  insert into r_test values(3,'a'),(4,'b'),(7,'h');
    22  update r_test set c1=2 where c2='a';
    23  -- @session
    24  alter account acc1 restricted;
    25  -- @session:id=5&user=acc1:admin&password=123efg
    26  create database rdb;
    27  drop database rdb;
    28  
    29  create table r1(c1 int,c2 varchar(20));
    30  insert into r_test values(8,'c');
    31  load data infile '$resources/load_data/integer_numbers_1.csv' into table r_test fields terminated by ',';
    32  update r_test set c1=5 where c2='h';
    33  delete from r_test where c1=4;
    34  select * from r_test;
    35  truncate table r_test;
    36  create view r_view as select * from r_test;
    37  drop view r_view;
    38  
    39  create  table ti2(a INT primary key AUTO_INCREMENT, b INT, c INT);
    40  create  table tm2(a INT primary key AUTO_INCREMENT, b INT, c INT);
    41  insert into ti1 values (1,1,1), (2,2,2);
    42  insert into ti2 values (1,1,1), (2,2,2);
    43  alter table ti1 add constraint fi1 foreign key (b) references ti2(a);
    44  
    45  show databases;
    46  use res_test;
    47  desc r_test;
    48  show tables;
    49  show create table r_test;
    50  show columns from r_test;
    51  show full columns from r_test;
    52  show variables where value = 'MatrixOne';
    53  show grants;
    54  show grants for 'admin'@'localhost';
    55  SHOW CREATE TABLE information_schema.columns;
    56  show index from r_test;
    57  show node list;
    58  show locks;
    59  show table_values from r_test;
    60  show column_number from r_test;
    61  show TRIGGERS;
    62  show TRIGGERS like '*%';
    63  show collation like 'utf8mb4_general_ci%';
    64  show full tables;
    65  show full tables from res_test;;
    66  
    67  select version();
    68  alter database test set mysql_compatibility_mode = '8.0.30-MatrixOne-v0.7.0';
    69  select privilege_name, obj_type, privilege_level from mo_catalog.mo_role_privs where privilege_name = 'values';
    70  select user_name from mo_catalog.mo_user;
    71  
    72  create account abc ADMIN_NAME 'admin' IDENTIFIED BY '123456';
    73  
    74  alter table r_test drop index ui;
    75  create role role1;
    76  grant all on table *.* to role1;
    77  grant create table, drop table on database *.* to role1;
    78  create user user1 identified by 'pass1';
    79  grant role1 to user1;
    80  drop user user1;
    81  drop role role1;
    82  drop database account_res;
    83  -- @session
    84  
    85  alter account acc1 suspend;
    86  select account_name,status,comments from mo_catalog.mo_account where account_name='acc1';
    87  
    88  alter account acc1 open;
    89  select account_name,status,comments from mo_catalog.mo_account where account_name='acc1';
    90  -- @session:id=6&user=acc1:admin&password=123efg
    91  create database rdb;
    92  use rdb;
    93  create table r1(c1 int,c2 varchar(20));
    94  insert into res_test.r_test values(8,'c');
    95  update res_test.r_test set c1=5 where c2='h';
    96  delete from res_test.r_test where c1=4;
    97  delete from system.statement_info;
    98  select * from res_test.r_test;
    99  truncate table res_test.r_test;
   100  create view r_view as select * from res_test.r_test;
   101  drop view r_view;
   102  
   103  show databases;
   104  use res_test;
   105  show tables;
   106  show create table r_test;
   107  show columns from r_test;
   108  show full columns from r_test;
   109  show variables where value = 'MatrixOne';
   110  show grants for 'hnadmin'@'localhost';
   111  SHOW CREATE TABLE information_schema.columns;
   112  show index from r_test;
   113  show node list;
   114  show locks;
   115  show table_values from r_test;
   116  show column_number from r_test;
   117  show TRIGGERS;
   118  show TRIGGERS like '*%';
   119  show collation like 'utf8mb4_general_ci%';
   120  show full tables;
   121  show full tables from account_res;
   122  
   123  select privilege_name, obj_type, privilege_level from mo_catalog.mo_role_privs where privilege_name = 'values';
   124  select user_name from mo_catalog.mo_user;
   125  
   126  create role role1;
   127  grant all on table *.* to role1;
   128  grant create table, drop table on database *.* to role1;
   129  create user user1 identified by 'pass1';
   130  grant role1 to user1;
   131  drop user user1;
   132  drop role role1;
   133  drop database rdb;
   134  drop database res_test;
   135  -- @session
   136  drop account if exists acc1;
   137  set global enable_privilege_cache = on;