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

     1  # LogicTest: !3node-tenant
     2  # Disable automatic stats to avoid flakiness.
     3  statement ok
     4  SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false
     5  
     6  # Test default table-level permissions.
     7  # Default user is root.
     8  statement ok
     9  CREATE DATABASE a
    10  
    11  statement ok
    12  SET DATABASE = a
    13  
    14  statement ok
    15  CREATE TABLE t (k INT PRIMARY KEY, v int)
    16  
    17  statement ok
    18  SELECT * from [54 as num_ref]
    19  
    20  statement ok
    21  SHOW GRANTS ON t
    22  
    23  statement ok
    24  CREATE USER bar
    25  
    26  statement ok
    27  GRANT ALL ON t TO bar
    28  
    29  statement ok
    30  REVOKE ALL ON t FROM bar
    31  
    32  statement ok
    33  INSERT INTO t VALUES (1, 1), (2, 2)
    34  
    35  statement ok
    36  SELECT * from t
    37  
    38  statement ok
    39  DELETE FROM t
    40  
    41  statement ok
    42  DELETE FROM t where k = 1
    43  
    44  statement ok
    45  UPDATE t SET v = 0
    46  
    47  statement ok
    48  UPDATE t SET v = 2 WHERE k = 2
    49  
    50  statement ok
    51  TRUNCATE t
    52  
    53  statement ok
    54  DROP TABLE t
    55  
    56  statement ok
    57  CREATE TABLE t (k INT PRIMARY KEY, v int)
    58  
    59  # Switch to a user without any privileges.
    60  user testuser
    61  
    62  # This needs to be repeated since session variables are per client.
    63  statement ok
    64  SET DATABASE = a
    65  
    66  statement ok
    67  SHOW GRANTS ON t
    68  
    69  statement error pq: user testuser has no privileges on relation t
    70  SHOW COLUMNS FROM t
    71  
    72  statement error pq: user testuser does not have SELECT privilege on relation t
    73  SELECT r FROM t
    74  
    75  statement error pq: user testuser does not have SELECT privilege on relation t
    76  SELECT * from [56 as num_ref]
    77  
    78  statement error user testuser does not have GRANT privilege on relation t
    79  GRANT ALL ON t TO bar
    80  
    81  statement error user testuser does not have GRANT privilege on relation t
    82  REVOKE ALL ON t FROM bar
    83  
    84  statement error user testuser does not have INSERT privilege on relation t
    85  INSERT INTO t VALUES (1, 1), (2, 2)
    86  
    87  statement error user testuser does not have SELECT privilege on relation t
    88  SELECT * FROM t
    89  
    90  statement ok
    91  SELECT 1
    92  
    93  statement error user testuser does not have DELETE privilege on relation t
    94  DELETE FROM t
    95  
    96  statement error user testuser does not have DELETE privilege on relation t
    97  DELETE FROM t where k = 1
    98  
    99  statement error user testuser does not have UPDATE privilege on relation t
   100  UPDATE t SET v = 0
   101  
   102  statement error user testuser does not have UPDATE privilege on relation t
   103  UPDATE t SET v = 2 WHERE k = 2
   104  
   105  statement error user testuser does not have DROP privilege on relation t
   106  TRUNCATE t
   107  
   108  statement error user testuser does not have DROP privilege on relation t
   109  DROP TABLE t
   110  
   111  # Grant SELECT privilege.
   112  user root
   113  
   114  statement ok
   115  GRANT SELECT ON t TO testuser
   116  
   117  user testuser
   118  
   119  query TTBTTTB
   120  SHOW COLUMNS FROM t
   121  ----
   122  k  INT8  false  NULL  ·  {primary}  false
   123  v  INT8  true   NULL  ·  {}         false
   124  
   125  statement error user testuser does not have GRANT privilege on relation t
   126  GRANT ALL ON t TO bar
   127  
   128  statement error user testuser does not have GRANT privilege on relation t
   129  REVOKE ALL ON t FROM bar
   130  
   131  statement error user testuser does not have INSERT privilege on relation t
   132  INSERT INTO t VALUES (1, 1), (2, 2)
   133  
   134  statement error user testuser does not have INSERT privilege on relation t
   135  UPSERT INTO t VALUES (1, 1), (2, 2)
   136  
   137  statement ok
   138  SELECT * FROM t
   139  
   140  statement ok
   141  SELECT 1
   142  
   143  statement error user testuser does not have DELETE privilege on relation t
   144  DELETE FROM t
   145  
   146  statement error user testuser does not have DELETE privilege on relation t
   147  DELETE FROM t where k = 1
   148  
   149  statement error user testuser does not have UPDATE privilege on relation t
   150  UPDATE t SET v = 0
   151  
   152  statement error user testuser does not have UPDATE privilege on relation t
   153  UPDATE t SET v = 2 WHERE k = 2
   154  
   155  statement error user testuser does not have DROP privilege on relation t
   156  TRUNCATE t
   157  
   158  statement error user testuser does not have DROP privilege on relation t
   159  DROP TABLE t
   160  
   161  # Grant all but SELECT privilege.
   162  user root
   163  
   164  statement ok
   165  GRANT ALL ON t TO testuser
   166  
   167  statement ok
   168  REVOKE SELECT ON t FROM testuser
   169  
   170  user testuser
   171  
   172  statement ok
   173  GRANT INSERT ON t TO bar
   174  
   175  statement ok
   176  REVOKE INSERT ON t FROM bar
   177  
   178  statement error user testuser does not have ALL privilege on relation t
   179  GRANT ALL ON t TO bar
   180  
   181  statement error user testuser does not have SELECT privilege on relation t
   182  GRANT SELECT ON t TO bar
   183  
   184  statement ok
   185  INSERT INTO t VALUES (1, 1), (2, 2)
   186  
   187  statement error user testuser does not have SELECT privilege on relation t
   188  SELECT * FROM t
   189  
   190  statement ok
   191  SELECT 1
   192  
   193  statement error user testuser does not have SELECT privilege on relation t
   194  DELETE FROM t
   195  
   196  statement error user testuser does not have SELECT privilege on relation t
   197  DELETE FROM t where k = 1
   198  
   199  statement error user testuser does not have SELECT privilege on relation t
   200  UPDATE t SET v = 0
   201  
   202  statement error user testuser does not have SELECT privilege on relation t
   203  UPDATE t SET v = 2 WHERE k = 2
   204  
   205  statement ok
   206  TRUNCATE t
   207  
   208  statement ok
   209  DROP TABLE t
   210  
   211  # Grant ALL privilege.
   212  user root
   213  
   214  statement ok
   215  CREATE TABLE t (k INT PRIMARY KEY, v int)
   216  
   217  statement ok
   218  GRANT ALL ON t TO testuser
   219  
   220  user testuser
   221  
   222  statement ok
   223  GRANT ALL ON t TO bar
   224  
   225  statement ok
   226  REVOKE ALL ON t FROM bar
   227  
   228  statement ok
   229  INSERT INTO t VALUES (1, 1), (2, 2)
   230  
   231  statement ok
   232  SELECT * FROM t
   233  
   234  statement ok
   235  SELECT 1
   236  
   237  statement ok
   238  DELETE FROM t
   239  
   240  statement ok
   241  DELETE FROM t where k = 1
   242  
   243  statement ok
   244  UPDATE t SET v = 0
   245  
   246  statement ok
   247  UPDATE t SET v = 2 WHERE k = 2
   248  
   249  statement ok
   250  TRUNCATE t
   251  
   252  statement ok
   253  DROP TABLE t
   254  
   255  # Grant INSERT privilege.
   256  user root
   257  
   258  statement ok
   259  CREATE TABLE t (k INT PRIMARY KEY, v int)
   260  
   261  statement ok
   262  GRANT INSERT ON t TO testuser
   263  
   264  user testuser
   265  
   266  statement ok
   267  INSERT INTO t VALUES (1, 2)
   268  
   269  statement error user testuser does not have SELECT privilege on relation t
   270  INSERT INTO t VALUES (1, 2) ON CONFLICT (k) DO NOTHING
   271  
   272  statement error user testuser does not have SELECT privilege on relation t
   273  INSERT INTO t VALUES (1, 2) ON CONFLICT (k) DO UPDATE SET v = excluded.v
   274  
   275  statement error user testuser does not have SELECT privilege on relation t
   276  UPSERT INTO t VALUES (1, 2)
   277  
   278  user root
   279  
   280  statement ok
   281  GRANT SELECT ON t TO testuser
   282  
   283  user testuser
   284  
   285  statement ok
   286  INSERT INTO t VALUES (1, 2) ON CONFLICT (k) DO NOTHING
   287  
   288  statement error user testuser does not have UPDATE privilege on relation t
   289  UPSERT INTO t VALUES (1, 2)
   290  
   291  statement error user testuser does not have UPDATE privilege on relation t
   292  INSERT INTO t VALUES (1, 2) ON CONFLICT (k) DO UPDATE SET v = excluded.v
   293  
   294  # Grant UPDATE privilege (in addition to INSERT).
   295  user root
   296  
   297  statement ok
   298  GRANT UPDATE ON t TO testuser
   299  
   300  user testuser
   301  
   302  statement ok
   303  UPSERT INTO t VALUES (1, 2)
   304  
   305  statement ok
   306  INSERT INTO t VALUES (1, 2) ON CONFLICT (k) DO UPDATE SET v = excluded.v
   307  
   308  user root
   309  
   310  statement ok
   311  DROP TABLE t
   312  
   313  # SHOW privileges.
   314  
   315  statement ok
   316  CREATE TABLE t (k INT PRIMARY KEY, v int)
   317  
   318  user testuser
   319  
   320  statement error user testuser has no privileges on relation t
   321  SHOW COLUMNS FROM t
   322  
   323  statement error user testuser has no privileges on relation t
   324  SHOW CREATE TABLE t
   325  
   326  statement error user testuser has no privileges on relation t
   327  SHOW INDEX FROM t
   328  
   329  statement error user testuser has no privileges on relation t
   330  SHOW CONSTRAINTS FROM t
   331  
   332  user root
   333  
   334  statement ok
   335  GRANT SELECT ON t TO testuser
   336  
   337  user testuser
   338  
   339  statement ok
   340  SHOW COLUMNS FROM t
   341  
   342  statement ok
   343  SHOW CREATE TABLE t
   344  
   345  statement ok
   346  SHOW INDEX FROM t
   347  
   348  statement ok
   349  SHOW CONSTRAINTS FROM t