用戶管理優秀實踐
更新時間 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)
