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

     1  -- env prepare statement
     2  drop account if exists account1;
     3  drop account if exists inner_account;
     4  drop role if exists revoke_role_1;
     5  
     6  --验证访问控制表中内置对象数据正确性
     7  select user_name,owner from mo_catalog.mo_user where user_name="root";
     8  select role_id,role_name,owner from mo_catalog.mo_role where role_name in ("moadmin","public");
     9  
    10  --验证moadminaccount初始化，sys租户root下创建普通租户下管理员用户查看
    11  create account account1 ADMIN_NAME 'admin' IDENTIFIED BY '123456';
    12  -- @session:id=2&user=account1:admin&password=123456
    13  select role_id,role_name,owner from mo_catalog.mo_role;
    14  show databases;
    15  show grants;
    16  use system;
    17  show triggers;
    18  use mo_catalog;
    19  show columns from mo_tables;
    20  select datname, dat_createsql from mo_database;
    21  select relname from mo_tables where relname="sql_statement_total";
    22  select relname from mo_tables where relname="mo_user";
    23  select relname from mo_tables where relname="tables";
    24  select user_name,authentication_string from mo_user;
    25  select role_name from mo_role;
    26  create database account_db;
    27  use account_db;
    28  show tables;
    29  create table a(col int);
    30  show create table a;
    31  show tables;
    32  -- @session
    33  
    34  --public只有连接权限
    35  -- @session:id=2&user=account1:admin:public&password=123456
    36  show databases;
    37  -- @session
    38  
    39  --内置表不能增删改
    40  update mo_catalog.mo_tables set relname='mo_aaaa';
    41  insert into mo_catalog.mo_role values (1763,'apple',0,1,'2022-09-22 06:53:34','');
    42  delete from mo_catalog.mo_user;
    43  drop table mo_catalog.mo_account;
    44  delete from mo_catalog.mo_user_grant;
    45  delete from mo_catalog.mo_role_grant;
    46  delete from mo_catalog.mo_role_privs;
    47  delete from mo_catalog.mo_database;
    48  delete from mo_catalog.mo_columns;
    49  
    50  --内置数据库不能删除
    51  drop database information_schema;
    52  drop database mo_catalog;
    53  drop database system;
    54  drop database system_metrics;
    55  
    56  --moadmin,public删除/回收
    57  revoke moadmin,public from root;
    58  select count(*) from mo_catalog.mo_role_privs where role_name in ('moadmin','public');
    59  drop role if exists moadmin,public;
    60  select role_name from mo_role where role_name in('moadmin','public');
    61  
    62  --root/admin user修改/删除/授权
    63  drop user if exists admin,root;
    64  
    65  --accountadmin删除/回收,切换到普通account验证
    66  create account inner_account ADMIN_NAME 'admin' IDENTIFIED BY '111';
    67  -- @session:id=2&user=inner_account:admin&password=123456
    68  revoke accountadmin from admin;
    69  select count(*) from mo_catalog.mo_role_privs where role_name in ('accountadmin');
    70  drop role if exists accountadmin;
    71  select role_name from mo_catalog.mo_role where role_name in('accountadmin');
    72  -- @session
    73  
    74  drop account if exists account1;
    75  drop account if exists inner_account;
    76  drop role if exists revoke_role_1;