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

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