github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/privileges_database (about)

     1  # LogicTest: !3node-tenant
     2  # Test default database-level permissions.
     3  # Default user is root.
     4  statement ok
     5  CREATE DATABASE a
     6  
     7  statement ok
     8  DROP DATABASE a
     9  
    10  statement ok
    11  CREATE DATABASE a
    12  
    13  statement ok
    14  SHOW DATABASES
    15  
    16  statement ok
    17  SET DATABASE = a
    18  
    19  statement ok
    20  CREATE TABLE t (id INT PRIMARY KEY)
    21  
    22  statement ok
    23  SHOW TABLES
    24  
    25  statement ok
    26  SHOW GRANTS ON DATABASE a
    27  
    28  statement ok
    29  CREATE USER bar
    30  
    31  statement ok
    32  GRANT ALL ON DATABASE a TO bar
    33  
    34  statement ok
    35  REVOKE ALL ON DATABASE a FROM bar
    36  
    37  # Switch to a user without any privileges.
    38  user testuser
    39  
    40  statement error only users with the admin role are allowed to CREATE DATABASE
    41  CREATE DATABASE b
    42  
    43  statement error user testuser does not have DROP privilege on database a
    44  DROP DATABASE a CASCADE
    45  
    46  statement ok
    47  SHOW DATABASES
    48  
    49  statement ok
    50  SET DATABASE = a
    51  
    52  statement error user testuser does not have CREATE privilege on database a
    53  CREATE TABLE t2 (id INT PRIMARY KEY)
    54  
    55  statement ok
    56  SHOW TABLES
    57  
    58  statement ok
    59  SHOW GRANTS ON DATABASE a
    60  
    61  statement error user testuser does not have GRANT privilege on database a
    62  GRANT ALL ON DATABASE a TO bar
    63  
    64  statement error user testuser does not have GRANT privilege on database a
    65  REVOKE ALL ON DATABASE a FROM bar
    66  
    67  # Grant read-only privileges.
    68  user root
    69  
    70  statement ok
    71  GRANT SELECT ON DATABASE a TO testuser
    72  
    73  user testuser
    74  
    75  statement error only users with the admin role are allowed to CREATE DATABASE
    76  CREATE DATABASE b
    77  
    78  statement error user testuser does not have DROP privilege on database a
    79  DROP DATABASE a CASCADE
    80  
    81  statement ok
    82  SHOW DATABASES
    83  
    84  statement ok
    85  SET DATABASE = a
    86  
    87  statement error user testuser does not have CREATE privilege on database a
    88  CREATE TABLE t2 (id INT PRIMARY KEY)
    89  
    90  statement ok
    91  SHOW TABLES
    92  
    93  statement ok
    94  SHOW GRANTS ON DATABASE a
    95  
    96  statement error user testuser does not have GRANT privilege on database a
    97  GRANT ALL ON DATABASE a TO bar
    98  
    99  statement error user testuser does not have GRANT privilege on database a
   100  REVOKE ALL ON DATABASE a FROM bar
   101  
   102  # Grant all privileges.
   103  user root
   104  
   105  statement ok
   106  GRANT ALL ON DATABASE a TO testuser
   107  
   108  user testuser
   109  
   110  statement error only users with the admin role are allowed to CREATE DATABASE
   111  CREATE DATABASE b
   112  
   113  statement ok
   114  SHOW DATABASES
   115  
   116  statement ok
   117  SET DATABASE = a
   118  
   119  statement ok
   120  CREATE TABLE t2 (id INT PRIMARY KEY)
   121  
   122  statement ok
   123  SHOW TABLES
   124  
   125  statement ok
   126  SHOW GRANTS ON DATABASE a
   127  
   128  statement ok
   129  GRANT ALL ON DATABASE a TO bar
   130  
   131  statement ok
   132  REVOKE ALL ON DATABASE a FROM bar
   133  
   134  statement error user testuser does not have DROP privilege on relation t
   135  DROP DATABASE a CASCADE
   136  
   137  user root
   138  
   139  statement ok
   140  GRANT DROP ON TABLE a.t TO testuser
   141  
   142  user testuser
   143  
   144  statement ok
   145  DROP DATABASE a CASCADE
   146  
   147  user root
   148  
   149  statement ok
   150  CREATE DATABASE d45697
   151  
   152  statement ok
   153  GRANT CREATE,GRANT ON DATABASE d45697 TO testuser
   154  
   155  user testuser
   156  
   157  statement ok
   158  GRANT CREATE ON DATABASE d45697 TO bar
   159  
   160  statement error user testuser does not have DROP privilege on database d45697
   161  GRANT DROP ON DATABASE d45697 TO bar