github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/tenant/privilege/create_user_default_role.sql (about)

     1  set global enable_privilege_cache = off;
     2  drop role if exists r1;
     3  create role r1;
     4  drop user if exists u1;
     5  create user u1 identified by '111' default role r1;
     6  
     7  -- @session:id=2&user=sys:u1:r1&password=111
     8  show tables;
     9  use mo_catalog;
    10  create database t;
    11  -- @session
    12  
    13  grant show databases on account * to r1;
    14  grant show tables on database * to r1;
    15  
    16  -- @session:id=2&user=sys:u1:r1&password=111
    17  -- r1 without the privilege CONNECT
    18  use mo_catalog;
    19  show tables;
    20  create database t;
    21  -- @session
    22  
    23  grant connect on account * to r1;
    24  
    25  -- @session:id=2&user=sys:u1:r1&password=111
    26  use mo_catalog;
    27  show tables;
    28  create database t;
    29  -- @session
    30  
    31  grant create database on account * to r1;
    32  
    33  -- @session:id=2&user=sys:u1:r1&password=111
    34  create database t;
    35  use t;
    36  create table A(a int);
    37  drop table A;
    38  -- @session
    39  
    40  grant create table on database * to r1;
    41  
    42  -- @session:id=2&user=sys:u1:r1&password=111
    43  use t;
    44  create table A(a int);
    45  insert into A values (1),(1);
    46  -- @session
    47  
    48  grant insert on table t.* to r1;
    49  
    50  -- @session:id=2&user=sys:u1:r1&password=111
    51  use t;
    52  insert into A values (1),(1);
    53  select a from A;
    54  -- @session
    55  
    56  grant select on table t.* to r1;
    57  
    58  -- @session:id=2&user=sys:u1:r1&password=111
    59  use t;
    60  select a from A;
    61  update A set a = 2 where a = 1;
    62  update A set a = 2;
    63  -- @session
    64  
    65  grant update on table t.* to r1;
    66  
    67  -- @session:id=2&user=sys:u1:r1&password=111
    68  use t;
    69  update A set a = 2 where a = 1;
    70  update A set a = 2;
    71  delete from A where a = 2;
    72  delete from A;
    73  -- @session
    74  
    75  grant delete on table t.* to r1;
    76  
    77  -- @session:id=2&user=sys:u1:r1&password=111
    78  use t;
    79  delete from A where a = 2;
    80  delete from A;
    81  select a from A;
    82  drop table A;
    83  -- @session
    84  
    85  grant drop table on database t to r1;
    86  
    87  -- @session:id=2&user=sys:u1:r1&password=111
    88  use t;
    89  drop table A;
    90  create database s;
    91  use s;
    92  create table B(b int);
    93  insert into B values (1),(1);
    94  -- @session
    95  
    96  grant select,insert,update,delete on table s.* to r1;
    97  
    98  -- @session:id=2&user=sys:u1:r1&password=111
    99  use s;
   100  insert into B values (1),(1);
   101  select b from B;
   102  update B set b = 2 where b=1;
   103  update B set b = 2;
   104  delete from B where b = 1;
   105  delete from B;
   106  drop table B;
   107  -- @session
   108  
   109  grant drop table on database s to r1;
   110  
   111  -- @session:id=2&user=sys:u1:r1&password=111
   112  use s;
   113  drop table B;
   114  drop database t;
   115  drop database s;
   116  -- @session
   117  
   118  -- multi tables in multi database
   119  
   120  -- @session:id=2&user=sys:u1:r1&password=111
   121  create database v;
   122  use v;
   123  -- @session
   124  
   125  grant create table,drop table on database v to r1;
   126  
   127  -- @session:id=2&user=sys:u1:r1&password=111
   128  use v;
   129  create table A(a int);
   130  create table B(b int);
   131  create table C(c int);
   132  create table D(d int);
   133  create table E(e int);
   134  create table F(f int);
   135  create table G(g int);
   136  create table H(h int);
   137  -- @session
   138  
   139  grant select on table v.A to r1;
   140  
   141  -- @session:id=2&user=sys:u1:r1&password=111
   142  use v;
   143  select * from A,B;
   144  select * from A,B where A.a = B.b;
   145  -- @session
   146  
   147  grant select on table v.B to r1;
   148  
   149  -- @session:id=2&user=sys:u1:r1&password=111
   150  use v;
   151  select * from A,B;
   152  select * from A,B where A.a = B.b;
   153  update C,D set c = d+1 where c = d;
   154  -- @session
   155  
   156  grant update on table v.C to r1;
   157  
   158  -- @session:id=2&user=sys:u1:r1&password=111
   159  use v;
   160  update C,D set c = d+1 where c = d;
   161  -- @session
   162  
   163  grant update on table v.D to r1;
   164  
   165  -- @session:id=2&user=sys:u1:r1&password=111
   166  use v;
   167  update C,D set c = d+1 where c = d;
   168  -- @session
   169  
   170  -- @session:id=2&user=sys:u1:r1&password=111
   171  use v;
   172  delete E,F from E,F where E.e = F.f;
   173  -- @session
   174  
   175  grant update on table v.E to r1;
   176  grant delete on table v.F to r1;
   177  
   178  -- @session:id=2&user=sys:u1:r1&password=111
   179  use v;
   180  delete E,F from E,F where E.e = F.f;
   181  -- @session
   182  
   183  grant delete on table v.E to r1;
   184  
   185  -- @session:id=2&user=sys:u1:r1&password=111
   186  use v;
   187  delete E,F from E,F where E.e = F.f;
   188  -- @session
   189  
   190  -- @session:id=2&user=sys:u1:r1&password=111
   191  use v;
   192  insert into G select A.a from A,B where A.a = B.b;
   193  -- @session
   194  
   195  grant insert on table v.G to r1;
   196  
   197  -- @session:id=2&user=sys:u1:r1&password=111
   198  use v;
   199  insert into G select A.a from A,B where A.a = B.b;
   200  -- @session
   201  
   202  -- @session:id=2&user=sys:u1:r1&password=111
   203  drop database if exists t;
   204  drop database if exists s;
   205  drop database if exists v;
   206  -- @session
   207  
   208  grant drop database on account * to r1;
   209  
   210  -- @session:id=2&user=sys:u1:r1&password=111
   211  drop database if exists t;
   212  drop database if exists s;
   213  drop database if exists v;
   214  -- @session
   215  
   216  drop role if exists r1;
   217  drop user if exists u1;
   218  set global enable_privilege_cache = on;