用户管理优秀实践
更新时间 2023-11-24 10:05:46
最近更新时间: 2023-11-24 10:05:46
本章节主要介绍用户管理优秀实践。
DWS集群中，常用的用户分别是系统管理员和普通用户。本节简述了系统管理员和普通用户的权限，如何创建以及如何查询用户相关信息。
系统管理员
在启动DWS集群时创建的用户dbadmin是系统管理员，其拥有系统的最高权限，能够执行所有的操作（表空间，表，索引，模式，函数，自定义视图的操作权限及系统表和系统视图的查看权限）。
要创建新的数据库管理员，则以管理员用户身份连接数据库，并使用带SYSADMIN选项的CREATE USER语句或 ALTER USER语句进行设置。
例如：
创建用户Jim为系统管理员。
CREATE USER Jim WITH SYSADMIN password 'password';
修改用户Tom为系统管理员。（ALTER USER时，要求用户已存在。）
ALTER USER Tom SYSADMIN;
普通用户
普通用户由SQL语句CREATE USER创建。不具有对表空间创建，修改，删除，分配权限，访问需要被赋权；仅对自己创建的表/模式/函数/自定义视图有所有权限，仅可以在自己的表上建索引，仅可查看部分系统表和系统视图。
数据库集群包含一个或多个已命名数据库。用户在整个集群范围内是共享的，但是其数据并不共享。
常见用户相关操作如下，此处使用的密码需要用户自定义：
1.创建用户。
CREATE USER Tom PASSWORD 'password';
2.修改用户密码。
将用户Tom的登录密码由password修改为newpassword。
ALTER USER Tom IDENTIFIED BY 'newpassword' REPLACE 'password';
3.给用户授权。
−要创建有“创建数据库”权限的用户，需要加CREATEDB关键字。
CREATE USER Tom CREATEDB PASSWORD 'password';
−为用户追加CREATEROLE权限。
ALTER USER Tom CREATEROLE;
4.撤销权限。
REVOKE ALL PRIVILEGES FROM Tom;
5.锁定或解锁用户。
−锁定Tom帐户：
ALTER USER Tom ACCOUNT LOCK;
−解锁Tom用户：
ALTER USER Tom ACCOUNT UNLOCK;
6.删除用户。
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）和用户权限。
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视图存储记录数据库中所有用户，但不对用户信息进行详细的描述。
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存储访问数据库角色的相关信息。
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，并非每个数据库一份。需要有系统管理员权限才可以访问此系统表。
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.查询所有用户的资源限额和资源使用情况。
SELECT * FROM PG_TOTAL_USER_RESOURCE_INFO;
例如，当前所有用户的资源使用情况如下：
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.查询具体某个用户的资源限额和资源使用情况。
SELECT * FROM GS_WLM_USER_RESOURCE_INFO('username');
例如，查询Tom的资源使用情况：
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资源使用情况。
SELECT * FROM pg_user_iostat('username');
例如，查询Tom的IO资源使用情况：
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)
