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

     1  # LogicTest: local
     2  
     3  statement ok
     4  CREATE USER user1
     5  
     6  query TTT colnames
     7  SHOW USERS
     8  ----
     9  username  options     member_of
    10  admin     CREATEROLE  {}
    11  root      CREATEROLE  {admin}
    12  testuser  ·           {}
    13  user1     ·           {}
    14  
    15  statement ok
    16  DROP USER user1
    17  
    18  query TTT colnames
    19  SHOW USERS
    20  ----
    21  username  options     member_of
    22  admin     CREATEROLE  {}
    23  root      CREATEROLE  {admin}
    24  testuser  ·           {}
    25  
    26  statement ok
    27  CREATE USER user1
    28  
    29  query TTT colnames
    30  SHOW USERS
    31  ----
    32  username  options     member_of
    33  admin     CREATEROLE  {}
    34  root      CREATEROLE  {admin}
    35  testuser  ·           {}
    36  user1     ·           {}
    37  
    38  statement ok
    39  DROP USER USEr1
    40  
    41  query TTT colnames
    42  SHOW USERS
    43  ----
    44  username  options     member_of
    45  admin     CREATEROLE  {}
    46  root      CREATEROLE  {admin}
    47  testuser  ·           {}
    48  
    49  statement error user user1 does not exist
    50  DROP USER user1
    51  
    52  statement error user user1 does not exist
    53  DROP USER usER1
    54  
    55  statement ok
    56  DROP USER IF EXISTS user1
    57  
    58  statement error username "node" reserved
    59  DROP USER node
    60  
    61  statement error pq: username "foo☂" invalid
    62  DROP USER foo☂
    63  
    64  statement ok
    65  CREATE USER user1
    66  
    67  statement ok
    68  CREATE USER user2
    69  
    70  statement ok
    71  CREATE USER user3
    72  
    73  statement ok
    74  CREATE USER user4
    75  
    76  query TTT colnames
    77  SHOW USERS
    78  ----
    79  username  options     member_of
    80  admin     CREATEROLE  {}
    81  root      CREATEROLE  {admin}
    82  testuser  ·           {}
    83  user1     ·           {}
    84  user2     ·           {}
    85  user3     ·           {}
    86  user4     ·           {}
    87  
    88  statement ok
    89  DROP USER user1,user2
    90  
    91  query TTT colnames
    92  SHOW USERS
    93  ----
    94  username  options     member_of
    95  admin     CREATEROLE  {}
    96  root      CREATEROLE  {admin}
    97  testuser  ·           {}
    98  user3     ·           {}
    99  user4     ·           {}
   100  
   101  statement error user user1 does not exist
   102  DROP USER user1,user3
   103  
   104  query TTT colnames
   105  SHOW USERS
   106  ----
   107  username  options     member_of
   108  admin     CREATEROLE  {}
   109  root      CREATEROLE  {admin}
   110  testuser  ·           {}
   111  user3     ·           {}
   112  user4     ·           {}
   113  
   114  statement ok
   115  CREATE USER user1
   116  
   117  statement ok
   118  CREATE TABLE foo(x INT);
   119   GRANT SELECT ON foo TO user3;
   120   GRANT SELECT ON DATABASE test TO user1
   121  
   122  statement error cannot drop roles/users user1, user3: grants still exist on test, test.public.foo
   123  DROP USER IF EXISTS user1,user3
   124  
   125  statement error cannot drop roles/users user1, user3: grants still exist on test
   126  REVOKE SELECT ON foo FROM user3;
   127    DROP USER IF EXISTS user1,user3
   128  
   129  statement ok
   130  REVOKE SELECT ON DATABASE test FROM user1;
   131    DROP USER IF EXISTS user1,user3
   132  
   133  statement ok
   134  PREPARE du AS DROP USER $1;
   135   EXECUTE du('user4')
   136  
   137  query TTT colnames
   138  SHOW USERS
   139  ----
   140  username  options     member_of
   141  admin     CREATEROLE  {}
   142  root      CREATEROLE  {admin}
   143  testuser  ·           {}
   144  
   145  user testuser
   146  
   147  statement error pq: user testuser does not have CREATEROLE privilege
   148  DROP USER user2
   149  
   150  user root
   151  
   152  statement error pq: cannot drop role/user root: grants still exist on .*
   153  DROP USER root
   154  
   155  statement error pq: cannot drop role/user admin: grants still exist on .*
   156  DROP USER admin