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

     1  set global enable_privilege_cache = off;
     2  drop user if exists anne,bill;
     3  create user anne identified by '111' , bill identified by '111';
     4  drop role if exists intern,lead,newrole,rolex,dev,test,rx;
     5  create role intern,lead,newrole,rolex,dev,test,rx;
     6  
     7  -- basci privilege
     8  grant connect on account * to intern,lead,newrole,rolex;
     9  
    10  grant intern to anne;
    11  grant dev to intern;
    12  grant test to dev;
    13  grant create table on database * to intern with grant option;
    14  grant create database on account * to dev;
    15  grant drop database on account * to dev with grant option;
    16  grant drop table on database * to test with grant option;
    17  
    18  grant lead to anne with grant option;
    19  grant dev to lead with grant option;
    20  grant create table on database * to lead with grant option;
    21  
    22  grant newrole to anne;
    23  grant create table on database * to newrole with grant option;
    24  grant dev to newrole with grant option;
    25  grant newrole to lead with grant option;
    26  grant newrole to anne;
    27  grant newrole to rolex with grant option;
    28  grant rolex to anne;
    29  
    30  -- @session:id=2&user=sys:anne:intern&password=111
    31  drop database if exists t;
    32  create database t;
    33  use t;
    34  create table A(a int);
    35  drop table A;
    36  
    37  -- intern
    38  grant create table on database * to rx;
    39  grant create database on account * to rx;
    40  grant drop database on account * to rx;
    41  grant drop table on database * to rx;
    42  
    43  grant test to rx,bill;
    44  grant dev to rx,bill;
    45  grant intern to rx,bill;
    46  grant lead to rx,bill;
    47  grant newrole to rx,bill;
    48  grant rolex to rx,bill;
    49  
    50  -- intern with secondary all
    51  set secondary role all;
    52  grant test to rx,bill;
    53  grant dev to rx,bill;
    54  grant intern to rx,bill;
    55  grant lead to rx,bill;
    56  grant newrole to rx,bill;
    57  grant rolex to rx,bill;
    58  
    59  set role lead;
    60  
    61  drop database if exists t;
    62  create database t;
    63  use t;
    64  create table A(a int);
    65  drop table A;
    66  
    67  -- lead
    68  grant create table on database * to rx;
    69  grant create database on account * to rx;
    70  grant drop database on account * to rx;
    71  grant drop table on database * to rx;
    72  
    73  grant test to rx,bill;
    74  grant dev to rx,bill;
    75  grant intern to rx,bill;
    76  grant lead to rx,bill;
    77  grant newrole to rx,bill;
    78  grant rolex to rx,bill;
    79  
    80  -- lead with secondary all
    81  set secondary role all;
    82  grant test to rx,bill;
    83  grant dev to rx,bill;
    84  grant intern to rx,bill;
    85  grant lead to rx,bill;
    86  grant newrole to rx,bill;
    87  grant rolex to rx,bill;
    88  
    89  set role newrole;
    90  
    91  drop database if exists t;
    92  create database t;
    93  use t;
    94  create table A(a int);
    95  drop table A;
    96  
    97  -- newrole
    98  grant create table on database * to rx;
    99  grant create database on account * to rx;
   100  grant drop database on account * to rx;
   101  grant drop table on database * to rx;
   102  
   103  grant test to rx,bill;
   104  grant dev to rx,bill;
   105  grant intern to rx,bill;
   106  grant lead to rx,bill;
   107  grant newrole to rx,bill;
   108  grant rolex to rx,bill;
   109  
   110  -- newrole with secondary all
   111  set secondary role all;
   112  grant test to rx,bill;
   113  grant dev to rx,bill;
   114  grant intern to rx,bill;
   115  grant lead to rx,bill;
   116  grant newrole to rx,bill;
   117  grant rolex to rx,bill;
   118  
   119  set role rolex;
   120  
   121  drop database if exists t;
   122  create database t;
   123  use t;
   124  create table A(a int);
   125  drop table A;
   126  
   127  -- rolex
   128  grant create table on database * to rx;
   129  grant create database on account * to rx;
   130  grant drop database on account * to rx;
   131  grant drop table on database * to rx;
   132  
   133  grant test to rx,bill;
   134  grant dev to rx,bill;
   135  grant intern to rx,bill;
   136  grant lead to rx,bill;
   137  grant newrole to rx,bill;
   138  grant rolex to rx,bill;
   139  
   140  -- rolex with secondary all
   141  set secondary role all;
   142  grant test to rx,bill;
   143  grant dev to rx,bill;
   144  grant intern to rx,bill;
   145  grant lead to rx,bill;
   146  grant newrole to rx,bill;
   147  grant rolex to rx,bill;
   148  
   149  drop database t;
   150  -- @session
   151  
   152  drop user anne,bill;
   153  drop role intern,lead,newrole,rolex,dev,test,rx;
   154  set global enable_privilege_cache = on;