github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/grant_database (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE DATABASE a 5 6 query TTTT colnames 7 SHOW GRANTS ON DATABASE a 8 ---- 9 database_name schema_name grantee privilege_type 10 a crdb_internal admin ALL 11 a crdb_internal root ALL 12 a information_schema admin ALL 13 a information_schema root ALL 14 a pg_catalog admin ALL 15 a pg_catalog root ALL 16 a pg_extension admin ALL 17 a pg_extension root ALL 18 a public admin ALL 19 a public root ALL 20 21 statement error user root must have exactly ALL privileges on system object with ID=.* 22 REVOKE SELECT ON DATABASE a FROM root 23 24 statement error user admin must have exactly ALL privileges on system object with ID=.* 25 REVOKE SELECT ON DATABASE a FROM admin 26 27 statement ok 28 CREATE USER readwrite 29 30 statement error pq: user or role "test-user" does not exist 31 GRANT ALL ON DATABASE a TO readwrite, "test-user" 32 33 statement ok 34 INSERT INTO system.users VALUES('test-user',''); 35 36 statement ok 37 GRANT ALL ON DATABASE a TO readwrite, "test-user" 38 39 statement error syntax error 40 GRANT SELECT,ALL ON DATABASE a TO readwrite 41 42 statement error syntax error 43 REVOKE SELECT,ALL ON DATABASE a FROM readwrite 44 45 query TTTT 46 SHOW GRANTS ON DATABASE a 47 ---- 48 a crdb_internal admin ALL 49 a crdb_internal readwrite ALL 50 a crdb_internal root ALL 51 a crdb_internal test-user ALL 52 a information_schema admin ALL 53 a information_schema readwrite ALL 54 a information_schema root ALL 55 a information_schema test-user ALL 56 a pg_catalog admin ALL 57 a pg_catalog readwrite ALL 58 a pg_catalog root ALL 59 a pg_catalog test-user ALL 60 a pg_extension admin ALL 61 a pg_extension readwrite ALL 62 a pg_extension root ALL 63 a pg_extension test-user ALL 64 a public admin ALL 65 a public readwrite ALL 66 a public root ALL 67 a public test-user ALL 68 69 # Create table to inherit DB permissions. 70 statement ok 71 CREATE TABLE a.t (id INT PRIMARY KEY) 72 73 query TTTTT colnames 74 SHOW GRANTS ON a.t 75 ---- 76 database_name schema_name table_name grantee privilege_type 77 a public t admin ALL 78 a public t readwrite ALL 79 a public t root ALL 80 a public t test-user ALL 81 82 query TTTT 83 SHOW GRANTS ON DATABASE a FOR readwrite, "test-user" 84 ---- 85 a crdb_internal readwrite ALL 86 a crdb_internal test-user ALL 87 a information_schema readwrite ALL 88 a information_schema test-user ALL 89 a pg_catalog readwrite ALL 90 a pg_catalog test-user ALL 91 a pg_extension readwrite ALL 92 a pg_extension test-user ALL 93 a public readwrite ALL 94 a public test-user ALL 95 96 statement ok 97 REVOKE INSERT,UPDATE ON DATABASE a FROM "test-user",readwrite 98 99 query TTTT 100 SHOW GRANTS ON DATABASE a 101 ---- 102 a crdb_internal admin ALL 103 a crdb_internal readwrite CREATE 104 a crdb_internal readwrite DELETE 105 a crdb_internal readwrite DROP 106 a crdb_internal readwrite GRANT 107 a crdb_internal readwrite SELECT 108 a crdb_internal readwrite ZONECONFIG 109 a crdb_internal root ALL 110 a crdb_internal test-user CREATE 111 a crdb_internal test-user DELETE 112 a crdb_internal test-user DROP 113 a crdb_internal test-user GRANT 114 a crdb_internal test-user SELECT 115 a crdb_internal test-user ZONECONFIG 116 a information_schema admin ALL 117 a information_schema readwrite CREATE 118 a information_schema readwrite DELETE 119 a information_schema readwrite DROP 120 a information_schema readwrite GRANT 121 a information_schema readwrite SELECT 122 a information_schema readwrite ZONECONFIG 123 a information_schema root ALL 124 a information_schema test-user CREATE 125 a information_schema test-user DELETE 126 a information_schema test-user DROP 127 a information_schema test-user GRANT 128 a information_schema test-user SELECT 129 a information_schema test-user ZONECONFIG 130 a pg_catalog admin ALL 131 a pg_catalog readwrite CREATE 132 a pg_catalog readwrite DELETE 133 a pg_catalog readwrite DROP 134 a pg_catalog readwrite GRANT 135 a pg_catalog readwrite SELECT 136 a pg_catalog readwrite ZONECONFIG 137 a pg_catalog root ALL 138 a pg_catalog test-user CREATE 139 a pg_catalog test-user DELETE 140 a pg_catalog test-user DROP 141 a pg_catalog test-user GRANT 142 a pg_catalog test-user SELECT 143 a pg_catalog test-user ZONECONFIG 144 a pg_extension admin ALL 145 a pg_extension readwrite CREATE 146 a pg_extension readwrite DELETE 147 a pg_extension readwrite DROP 148 a pg_extension readwrite GRANT 149 a pg_extension readwrite SELECT 150 a pg_extension readwrite ZONECONFIG 151 a pg_extension root ALL 152 a pg_extension test-user CREATE 153 a pg_extension test-user DELETE 154 a pg_extension test-user DROP 155 a pg_extension test-user GRANT 156 a pg_extension test-user SELECT 157 a pg_extension test-user ZONECONFIG 158 a public admin ALL 159 a public readwrite CREATE 160 a public readwrite DELETE 161 a public readwrite DROP 162 a public readwrite GRANT 163 a public readwrite SELECT 164 a public readwrite ZONECONFIG 165 a public root ALL 166 a public test-user CREATE 167 a public test-user DELETE 168 a public test-user DROP 169 a public test-user GRANT 170 a public test-user SELECT 171 a public test-user ZONECONFIG 172 173 query TTTT 174 SHOW GRANTS ON DATABASE a FOR readwrite, "test-user" 175 ---- 176 a crdb_internal readwrite CREATE 177 a crdb_internal readwrite DELETE 178 a crdb_internal readwrite DROP 179 a crdb_internal readwrite GRANT 180 a crdb_internal readwrite SELECT 181 a crdb_internal readwrite ZONECONFIG 182 a crdb_internal test-user CREATE 183 a crdb_internal test-user DELETE 184 a crdb_internal test-user DROP 185 a crdb_internal test-user GRANT 186 a crdb_internal test-user SELECT 187 a crdb_internal test-user ZONECONFIG 188 a information_schema readwrite CREATE 189 a information_schema readwrite DELETE 190 a information_schema readwrite DROP 191 a information_schema readwrite GRANT 192 a information_schema readwrite SELECT 193 a information_schema readwrite ZONECONFIG 194 a information_schema test-user CREATE 195 a information_schema test-user DELETE 196 a information_schema test-user DROP 197 a information_schema test-user GRANT 198 a information_schema test-user SELECT 199 a information_schema test-user ZONECONFIG 200 a pg_catalog readwrite CREATE 201 a pg_catalog readwrite DELETE 202 a pg_catalog readwrite DROP 203 a pg_catalog readwrite GRANT 204 a pg_catalog readwrite SELECT 205 a pg_catalog readwrite ZONECONFIG 206 a pg_catalog test-user CREATE 207 a pg_catalog test-user DELETE 208 a pg_catalog test-user DROP 209 a pg_catalog test-user GRANT 210 a pg_catalog test-user SELECT 211 a pg_catalog test-user ZONECONFIG 212 a pg_extension readwrite CREATE 213 a pg_extension readwrite DELETE 214 a pg_extension readwrite DROP 215 a pg_extension readwrite GRANT 216 a pg_extension readwrite SELECT 217 a pg_extension readwrite ZONECONFIG 218 a pg_extension test-user CREATE 219 a pg_extension test-user DELETE 220 a pg_extension test-user DROP 221 a pg_extension test-user GRANT 222 a pg_extension test-user SELECT 223 a pg_extension test-user ZONECONFIG 224 a public readwrite CREATE 225 a public readwrite DELETE 226 a public readwrite DROP 227 a public readwrite GRANT 228 a public readwrite SELECT 229 a public readwrite ZONECONFIG 230 a public test-user CREATE 231 a public test-user DELETE 232 a public test-user DROP 233 a public test-user GRANT 234 a public test-user SELECT 235 a public test-user ZONECONFIG 236 237 statement ok 238 REVOKE SELECT ON DATABASE a FROM "test-user" 239 240 query TTTT 241 SHOW GRANTS ON DATABASE a 242 ---- 243 a crdb_internal admin ALL 244 a crdb_internal readwrite CREATE 245 a crdb_internal readwrite DELETE 246 a crdb_internal readwrite DROP 247 a crdb_internal readwrite GRANT 248 a crdb_internal readwrite SELECT 249 a crdb_internal readwrite ZONECONFIG 250 a crdb_internal root ALL 251 a crdb_internal test-user CREATE 252 a crdb_internal test-user DELETE 253 a crdb_internal test-user DROP 254 a crdb_internal test-user GRANT 255 a crdb_internal test-user ZONECONFIG 256 a information_schema admin ALL 257 a information_schema readwrite CREATE 258 a information_schema readwrite DELETE 259 a information_schema readwrite DROP 260 a information_schema readwrite GRANT 261 a information_schema readwrite SELECT 262 a information_schema readwrite ZONECONFIG 263 a information_schema root ALL 264 a information_schema test-user CREATE 265 a information_schema test-user DELETE 266 a information_schema test-user DROP 267 a information_schema test-user GRANT 268 a information_schema test-user ZONECONFIG 269 a pg_catalog admin ALL 270 a pg_catalog readwrite CREATE 271 a pg_catalog readwrite DELETE 272 a pg_catalog readwrite DROP 273 a pg_catalog readwrite GRANT 274 a pg_catalog readwrite SELECT 275 a pg_catalog readwrite ZONECONFIG 276 a pg_catalog root ALL 277 a pg_catalog test-user CREATE 278 a pg_catalog test-user DELETE 279 a pg_catalog test-user DROP 280 a pg_catalog test-user GRANT 281 a pg_catalog test-user ZONECONFIG 282 a pg_extension admin ALL 283 a pg_extension readwrite CREATE 284 a pg_extension readwrite DELETE 285 a pg_extension readwrite DROP 286 a pg_extension readwrite GRANT 287 a pg_extension readwrite SELECT 288 a pg_extension readwrite ZONECONFIG 289 a pg_extension root ALL 290 a pg_extension test-user CREATE 291 a pg_extension test-user DELETE 292 a pg_extension test-user DROP 293 a pg_extension test-user GRANT 294 a pg_extension test-user ZONECONFIG 295 a public admin ALL 296 a public readwrite CREATE 297 a public readwrite DELETE 298 a public readwrite DROP 299 a public readwrite GRANT 300 a public readwrite SELECT 301 a public readwrite ZONECONFIG 302 a public root ALL 303 a public test-user CREATE 304 a public test-user DELETE 305 a public test-user DROP 306 a public test-user GRANT 307 a public test-user ZONECONFIG 308 309 statement ok 310 REVOKE ALL ON DATABASE a FROM "test-user" 311 312 query TTTT 313 SHOW GRANTS ON DATABASE a FOR readwrite, "test-user" 314 ---- 315 a crdb_internal readwrite CREATE 316 a crdb_internal readwrite DELETE 317 a crdb_internal readwrite DROP 318 a crdb_internal readwrite GRANT 319 a crdb_internal readwrite SELECT 320 a crdb_internal readwrite ZONECONFIG 321 a information_schema readwrite CREATE 322 a information_schema readwrite DELETE 323 a information_schema readwrite DROP 324 a information_schema readwrite GRANT 325 a information_schema readwrite SELECT 326 a information_schema readwrite ZONECONFIG 327 a pg_catalog readwrite CREATE 328 a pg_catalog readwrite DELETE 329 a pg_catalog readwrite DROP 330 a pg_catalog readwrite GRANT 331 a pg_catalog readwrite SELECT 332 a pg_catalog readwrite ZONECONFIG 333 a pg_extension readwrite CREATE 334 a pg_extension readwrite DELETE 335 a pg_extension readwrite DROP 336 a pg_extension readwrite GRANT 337 a pg_extension readwrite SELECT 338 a pg_extension readwrite ZONECONFIG 339 a public readwrite CREATE 340 a public readwrite DELETE 341 a public readwrite DROP 342 a public readwrite GRANT 343 a public readwrite SELECT 344 a public readwrite ZONECONFIG 345 346 statement ok 347 REVOKE ALL ON DATABASE a FROM readwrite,"test-user" 348 349 query TTTT 350 SHOW GRANTS ON DATABASE a 351 ---- 352 a crdb_internal admin ALL 353 a crdb_internal root ALL 354 a information_schema admin ALL 355 a information_schema root ALL 356 a pg_catalog admin ALL 357 a pg_catalog root ALL 358 a pg_extension admin ALL 359 a pg_extension root ALL 360 a public admin ALL 361 a public root ALL 362 363 query TTTT 364 SHOW GRANTS ON DATABASE a FOR readwrite, "test-user" 365 ---- 366 367 # Verify that the table privileges have not changed. 368 query TTTTT colnames 369 SHOW GRANTS ON a.t 370 ---- 371 database_name schema_name table_name grantee privilege_type 372 a public t admin ALL 373 a public t readwrite ALL 374 a public t root ALL 375 a public t test-user ALL