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

     1  set global enable_privilege_cache = on;
     2  drop user if exists u1;
     3  drop role if exists r1;
     4  
     5  -- setup
     6  create user u1 identified by '111';
     7  create role r1;
     8  --r1 just have CONNECT privilege. r1 does not have the SHOW DATABASES privilege.
     9  grant r1 to u1;
    10  
    11  -----------------------------------------
    12  --TEST 1: the privilege cache is enabled.
    13  -----------------------------------------
    14  
    15  -- @session:id=2&user=sys:u1:r1&password=111
    16  show variables like 'enable_privilege_cache';
    17  -- fail. has cache. the SHOW DATABASES privilege is not granted to r1.
    18  show databases;
    19  -- @session
    20  
    21  -- give the SHOW DATABASES privilege to r1
    22  grant show databases on account * to r1;
    23  
    24  -- @session:id=2&user=sys:u1:r1&password=111
    25  -- success. has cache. the SHOW DATABASES privilege is granted to r1.
    26  show databases;
    27  -- @session
    28  
    29  -- revoke the SHOW DATABASES privilege from r1
    30  revoke show databases on account * from r1;
    31  
    32  -- @session:id=2&user=sys:u1:r1&password=111
    33  show variables like 'enable_privilege_cache';
    34  -- success. has cache. the SHOW DATABASES privilege is in cache.
    35  show databases;
    36  show databases;
    37  
    38  -- clear the cache.
    39  set clear_privilege_cache = on;
    40  show variables like 'clear_privilege_cache';
    41  show variables like 'enable_privilege_cache';
    42  -- fail. has cache. the SHOW DATABASES privilege is not granted to r1.
    43  show databases;
    44  -- fail too. has cache. we do not cache fail.
    45  show databases;
    46  -- @session
    47  
    48  -- give the SHOW DATABASES privilege to r1
    49  grant show databases on account * to r1;
    50  
    51  -- @session:id=2&user=sys:u1:r1&password=111
    52  -- success. has cache. the SHOW DATABASES privilege is granted to r1.
    53  show databases;
    54  -- success. has cache. the SHOW DATABASES privilege is in cache.
    55  show databases;
    56  -- @session
    57  
    58  ------------------------------------------
    59  --TEST 2: the privilege cache is disabled.
    60  ------------------------------------------
    61  revoke show databases on account * from r1;
    62  
    63  -- @session:id=2&user=sys:u1:r1&password=111
    64  set enable_privilege_cache = off;
    65  show variables like 'clear_privilege_cache';
    66  show variables like 'enable_privilege_cache';
    67  -- fail. no cache. the logic of the privilege check judges
    68  -- the SHOW DATABASES privilege is not granted to r1.
    69  show databases;
    70  -- fail. no cache.
    71  show databases;
    72  -- @session
    73  
    74  -- give the SHOW DATABASES privilege to r1
    75  grant show databases on account * to r1;
    76  
    77  -- @session:id=2&user=sys:u1:r1&password=111
    78  -- success. no cache. the logic of the privilege check judges
    79  -- the SHOW DATABASES privilege is granted to r1.
    80  show databases;
    81  -- @session
    82  
    83  revoke show databases on account * from r1;
    84  
    85  -- @session:id=2&user=sys:u1:r1&password=111
    86  -- fail. no cache. the logic of the privilege check judges
    87  -- the SHOW DATABASES privilege is not granted to r1.
    88  show databases;
    89  show databases;
    90  -- @session
    91  
    92  drop user if exists u1;
    93  drop user if exists r1;
    94  set clear_privilege_cache = on;
    95  set global enable_privilege_cache = on;