数据仓库服务 GAUSSDB(DWS)-用户管理优秀实践:用户信息查询

时间:2024-09-14 17:38:48

用户信息查询

涉及用户、角色及权限相关的系统视图有ALL_USERS、PG_USER和PG_ROLES,系统表有PG_AUTHID和PG_AUTH_MEMBERS。

  • ALL_USERS视图存储记录数据库中所有用户,但不对用户信息进行详细的描述。
  • PG_USER视图存储用户信息,包含用户ID,是否可以创建数据库以及用户所在资源池等信息。
  • PG_ROLES视图存储数据库角色的相关信息。
  • PG_AUTHID系统表存储有关数据库认证标识符(角色)的信息,包含角色是否可以登录,创建数据库等信息。
  • PG_AUTH_MEMBERS存储角色的成员关系,即某个角色组包含了哪些其他角色。
  1. 通过PG_USER可以查看数据库中所有用户的列表,还可以查看用户ID(USESYSID)和用户权限。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    SELECT * FROM pg_user;
     usename | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valbegin | valuntil |   respool    | parent | spacelimit | useconfig | nodegroup | tempspacelimit | spillspacelim
    it
    ---------+----------+-------------+----------+-----------+---------+----------+----------+----------+--------------+--------+------------+-----------+-----------+----------------+--------------
    ---
     Ruby    |       10 | t           | t        | t         | t       | ******** |          |          | default_pool |      0 |            |           |           |                |
     kim     |    21661 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |
     u3      |    22662 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |
     u1      |    22666 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |
     dbadmin |    16396 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |
     u5      |    58421 | f           | f        | f         | f       | ******** |          |          | default_pool |      0 |            |           |           |                |
    (6 rows)
    
  2. ALL_USERS视图存储记录数据库中所有用户,但不对用户信息进行详细的描述。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    SELECT * FROM all_users;
     username | user_id
    ----------+---------
     Ruby     |      10
     manager  |   21649
     kim      |   21661
     u3       |   22662
     u1       |   22666
     u2       |   22802
     dbadmin  |   16396
     u5       |   58421
    (8 rows)
    
  3. 系统表PG_ROLES存储访问数据库角色的相关信息。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    SELECT * FROM pg_roles;
     rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit | rolpassword | rolvalidbegin | rolv
    aliduntil |  rolrespool  | rolparentid | roltabspace | rolconfig |  oid  | roluseft | rolkind | nodegroup | roltempspace | rolspillspace
    ---------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+-------------+---------------+-----
    ----------+--------------+-------------+-------------+-----------+-------+----------+---------+-----------+--------------+---------------
     Ruby    | t        | t          | t             | t           | t            | t           | t              | t             | t              |           -1 | ********    |               |
              | default_pool |           0 |             |           |    10 | t        | n       |           |              |
     manager | f        | t          | f             | f           | f            | f           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 21649 | f        | n       |           |              |
     kim     | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 21661 | f        | n       |           |              |
     u3      | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 22662 | f        | n       |           |              |
     u1      | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 22666 | f        | n       |           |              |
     u2      | f        | t          | f             | f           | f            | f           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 22802 | f        | n       |           |              |
     dbadmin | f        | t          | f             | f           | f            | t           | f              | f             | t              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 16396 | f        | n       |           |              |
     u5      | f        | t          | f             | f           | f            | t           | f              | f             | f              |           -1 | ********    |               |
              | default_pool |           0 |             |           | 58421 | f        | n       |           |              |
    (8 rows)
    
  4. 要查看用户属性,可查询系统表PG_AUTHID,它存储有关数据库认证标识符(角色)的信息。一个集群中只有一份pg_authid,并非每个数据库一份。需要有系统管理员权限才可以访问此系统表。
    1
    2
    3
    4
    5
    6
    7
    SELECT * FROM pg_authid;
    rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolauditadmin | rolsystemadmin | rolconnlimit |                                                                                                                                                                                     rolpassword                                                                                                                                                                                      | rolvalidbegin | rolvaliduntil |  rolrespool  | roluseft | rolparentid | roltabspace | rolkind | rolnodegroup | roltempspace | rolspillspace | rolexcpdata | rolauthinfo
    ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+---------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+---------------+--------------+----------+-------------+-------------+---------+--------------+--------------+---------------+-------------+-------------
     Ruby     | t        | t          | t             | t           | t            | t           | t              | t             | t              |           -1 | sha256366f1e665be208e6015bc3c5795d13e4dc297a148dca6c60346018c80e5c04c9ba170384ce44609b31baa741f09a3ea5bedc7dadb906286ca994067c3fbf672dc08c981929e326ca08c005d8df942994e146ed3302af47000b36e9852b50e39dmd585de11aafebd90ec620b201fc36f07a5ecdficefade3a1456ec0aca9a0ee01e3bf2971d1dbafd604e596149e2e2928be4060dec2bd8688776588b4cd8c64fd38f1b0beab1603129fa396556ba8aa4c7d6e137a04623 |               |               | default_pool | t        |           0 |             | n       |            0 |              |               |             |
     sysadmin | f        | t          | f             | f           | f            | t           | f              | f             | t              |           -1 | sha256ecaa7f0ca4436143af43074f16cdd825783ad1a5d659fd94f5e2fa5124e7da44045ecf40bda1a97975fcf5920dca0c8be375be5c71b51cb1eeeba0851fb3648cfa49f55989f83fd9baf1a9d5853ce19125f4fc29a7c709c095ed02d00638410dmd556d6e2dcc41594dc7ad8ee909ef81637ecdficefadefd7d9704ee06affef9581cd6a50a546607f88891198e96a5e84e7e83dccf56c5cd20a500bbc5248e8ea51f0bca70c5a8dcf00953f8b62c7a181368153abce760 |               |               | default_pool | f        |           0 |             | n       |              |              |               |             |
     Tom      | f        | t          | f             | t           | f            | t           | f              | f             | f              |           -1 | sha256f43c4f52ac51e297bc4dbdbc751fcf05319c15681dbf5a9c5777d2edce45cb592a948b25457a728e99a3e0608592f33b0a4312eba6124936522304ba298caa2002a04578860fecb0286d7c7baec09365eafd049b2b99f74f21a08864dd7d3f2amd515ee49f0b18ef8e7d0cd27d91ce2fa9decdficefade16bab5f05b6d7c86a19ae6406cc59c437506c3f6187bfdf3eefc7a7c7033afa076361b255cc8b6ccb6e19d4767effaec654b3308cc72cebb891d00a4a10362da |               |               | default_pool | f        |           0 |             | n       |              |              |               |             |
    (3 rows)
    
support.huaweicloud.com/bestpractice-dws/dws_05_0058.html