用户管理优秀实践

系统管理员

在启动GaussDB(DWS)集群时创建的用户dbadmin是系统管理员，其拥有系统的最高权限，能够执行所有的操作（表空间，表，索引，模式，函数，自定义视图的操作权限及系统表和系统视图的查看权限）。

要创建新的数据库管理员，则以管理员用户身份连接数据库，并使用带SYSADMIN选项的CREATE USER语句或ALTER USER语句进行设置。

例如：

创建用户Jim为系统管理员。

1

CREATE USER Jim WITH SYSADMIN password '{Password}';

修改用户Tom为系统管理员。（ALTER USER时，要求用户已存在。）

1

ALTER USER Tom SYSADMIN;

普通用户

普通用户由SQL语句CREATE USER创建。不具有对表空间创建，修改，删除，分配权限，访问需要被赋权；仅对自己创建的表/模式/函数/自定义视图有所有权限，仅可以在自己的表上建索引，仅可查看部分系统表和系统视图。

数据库集群包含一个或多个已命名数据库。用户在整个集群范围内是共享的，但是其数据并不共享。

常见用户相关操作如下，此处使用的密码需要用户自定义：

1. 创建用户。

   1	CREATE USER Tom PASSWORD '{Password}';

2. 修改用户密码。
   将用户Tom的登录密码由password修改为newpassword。

   1	ALTER USER Tom IDENTIFIED BY 'newpassword' REPLACE '{Password}';

3. 给用户授权。
   • 要创建有“创建数据库”权限的用户，需要加CREATEDB关键字。

   1	CREATE USER Tom CREATEDB PASSWORD '{Password}';

   • 为用户追加CREATEROLE权限。

   1	ALTER USER Tom CREATEROLE;

4. 撤销权限。

   1	REVOKE ALL PRIVILEGES FROM Tom;

5. 锁定或解锁用户。
   • 锁定Tom账户：

   1	ALTER USER Tom ACCOUNT LOCK;

   • 解锁Tom用户：

   1	ALTER USER Tom ACCOUNT UNLOCK;

6. 删除用户。

   1	DROP USER Tom CASCADE;

用户信息查询

涉及用户、角色及权限相关的系统视图有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)

用户资源查询

1. 查询所有用户的资源限额和资源使用情况。

   1	SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;

   例如，当前所有用户的资源使用情况如下：

   1 2 3 4 5 6 7

   username | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed ----------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- perfadm | 0 | 17250 | 0 | 0 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 usern | 0 | 17250 | 0 | 48 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 userg | 34 | 15525 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814955731 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42678 | 8001 userg1 | 34 | 13972 | 23.53 | 48 | 0 | -1 | 0 | -1 | 814972419 | -1 | 6111952 | 1145864 | 763994 | 143233 | 42710 | 8007 (4 rows)

2. 查询具体某个用户的资源限额和资源使用情况。

   1	SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');

   例如，查询Tom的资源使用情况：

   1 2 3 4 5

   SELECT * FROM GS_WLM_USER_RESOURCE_INFO('Tom'); userid | used_memory | total_memory | used_cpu | total_cpu | used_space | total_space | used_temp_space | total_temp_space | used_spill_space | total_spill_space | read_kbytes | write_kbytes | read_counts | write_counts | read_speed | write_speed -------+-------------+--------------+----------+-----------+------------+-------------+-----------------+------------------+------------------+-------------------+-------------+--------------+-------------+--------------+------------+------------- 16523 | 18 | 2831 | 0 | 19 | 0 | -1 | 0 | -1 | 0 | -1 | 0 | 0 | 0 | 0 | 0 | 0 (1 row)

3. 查询具体某个用户的IO资源使用情况。

   1	SELECT * FROM pg_user_iostat('username');

   例如，查询Tom的IO资源使用情况：

   1 2 3 4 5

   SELECT * FROM pg_user_iostat('Tom'); userid | min_curr_iops | max_curr_iops | min_peak_iops | max_peak_iops | io_limits | io_priority -------+---------------+---------------+---------------+---------------+-----------+------------- 16523 | 0 | 0 | 0 | 0 | 0 | None (1 row)