DWS如何實現業務隔離

業務隔離

DWS中可以使用Database和Schema實現業務的隔離，區別在于：

• Database之間無法直接互訪，通過連接隔離實現徹底的權限隔離。各個Database之間共享資源極少，可實現連接隔離、權限隔離等。
• Schema隔離的方式共用資源較多，可以通過GRANT與REVOKE語法便捷地控制不同用戶對各Schema及其下屬對象的權限，從而賦給業務更多的靈活性。

從便捷性和資源共享效率上考慮，推薦使用Schema進行業務隔離。建議系統管理員創建Schema和Database，再賦予相關用戶對應的權限。

權限控制

DATABASE

數據庫Database是數據庫對象的物理集合，不同Database之間資源完全隔離(除部分共享對象之外)。即Database是對業務的物理隔離，不同Database的之間的對象不能相互訪問。比如在Database A中無法訪問Databse B中的對象。因此登錄集群的時候必須顯示指定要連接的Databse。

SCHEMA

數據庫里面通過Schema把數據庫對象進行邏輯劃分，在Database中，通過Schema實現對數據庫對象的邏輯隔離。

通過權限管理實現在同一個session下對不同Schema下對象的訪問和操作權限。Schema下則是各種應用程序會接觸到的對象，比如表，索引，數據類型，函數，操作符等。

同一個Schema下，不能存在同名的數據庫對象；但是不同Schema下的對象名可以重復。

gaussdb=> CREATE SCHEMA myschema; 
CREATE SCHEMA 
gaussdb=> CREATE SCHEMA myschema_1; 
CREATE SCHEMA 
 
gaussdb=> CREATE TABLE myschema.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE 
gaussdb=> CREATE TABLE myschema.t1(a int, b int) DISTRIBUTE BY HASH(b); 
ERROR:  relation "t1" already exists 
gaussdb=> CREATE TABLE myschema_1.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE

Schema實現了對業務的邏輯劃分，反過來這些業務對象也對Schema形成一種依賴關系，因此當Schema下存在對象時，刪除Schema的時候會報錯，并提示具體的依賴信息。

gaussdb=> DROP SCHEMA myschema_1; 
ERROR: cannot drop schema myschema_1 because other objects depend on it  
Detail: table myschema_1.t1 depends on schema myschema_1  
Hint: Use DROP ... CASCADE to drop the dependent objects too.

當刪除Schema的時候加上CASCADE選項，把Schema以及依賴此Schema的選項連帶刪除。

gaussdb=> DROP SCHEMA myschema_1 CASCADE; 
NOTICE:  drop cascades to table myschema_1.t1 
gaussdb=> DROP SCHEMA

USER/ROLE

用戶或角色是數據庫服務器(集群)全局范圍內的權限控制系統，是集群業務的所有者和執行者，用于各種集群范圍內所有的對象權限管理。因此角色不特定于某個單獨的數據庫，但角色登錄集群的時候必須要顯式指定登錄的用戶名，以保證當前連接執行的操作者的透明性。同時數據庫也會通過權限管理限定用戶的訪問和操作權限。

用戶是權限的最終體現者，所有的權限管理最終都體現在用戶對數據庫對象的操作權限是否被允許。

權限管理

DWS中的權限管理分為三種場景：

• 系統權限

系統權限又稱為用戶屬性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。

系統權限一般通過CREATE/ALTER ROLE語法來指定。其中，SYSADMIN權限可以通過GRANT/REVOKE ALL PRIVILEGE授予或撤銷。但系統權限無法通過ROLE和USER的權限被繼承，也無法授予PUBLIC。

• 用戶權限

將一個角色或用戶的權限授予一個或多個其他角色或用戶。在這種情況下，每個角色或用戶都可視為擁有一個或多個數據庫權限的集合。

當聲明了WITH ADMIN OPTION，被授權的用戶可以將該權限再次授予其他角色或用戶，以及撤銷所有由該角色或用戶繼承到的權限。當授權的角色或用戶發生變更或被撤銷時，所有繼承該角色或用戶權限的用戶擁有的權限都會隨之發生變更。

數據庫系統管理員可以給任何角色或用戶授予/撤銷任何權限。擁有CREATEROLE權限的角色可以賦予或者撤銷任何非系統管理員角色的權限。

• 數據對象權限

將數據庫對象（表和視圖、指定字段、數據庫、函數、模式等）的相關權限授予特定角色或用戶。GRANT命令將數據庫對象的特定權限授予一個或多個角色。這些權限會追加到已有的權限上。

SCHEMA隔離應用示例

示例一：

Schema的owner默認擁有該Schema下對象的所有權限，包括刪除權限；Database的owner默認擁有該Database下對象的所有權限，包括刪除權限。因此建議對Database和Schema的創建要做比較嚴格的控制，一般建議使用管理員創建Database和Schema，然后把相關的權限控制賦給業務用戶。

1.dbadmin在數據庫testdb下把創建Schema的權限賦給普通用戶user_1。

testdb=> GRANT CREATE ON DATABASE testdb to user_1; 
GRANT

2.切換到普通用戶user_1。

testdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

用戶user_1在數據庫testdb下創建名為myschema_2的Schema。

testdb=> CREATE SCHEMA myschema_2; 
CREATE SCHEMA

3.切換到管理員dbadmin。

testdb=> RESET SESSION AUTHORIZATION; 
RESET

管理員dbadmin在模式myschema_2下創建表t1。

testdb=> CREATE TABLE myschema_2.t1(a int, b int) DISTRIBUTE BY HASH(b); 
CREATE TABLE

4.切換到普通用戶user_1。

testdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

普通用戶user_1刪除管理員dbadmin在模式myschema_2下創建的表t1。

testdb=> drop table myschema_2.t1; 
DROP TABLE

示例二：

因為Schema的邏輯隔離的功能，訪問數據庫對象實際上要通過Schema和具體對象的兩層校驗。

1.把表myschema.t1的權限賦給用戶user_1。

gaussdb=> GRANT SELECT ON TABLE myschema.t1 TO user_1; 
GRANT

2.切換到用戶user_1。

SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

查詢表myschema.t1。

gaussdb=> SELECT * FROM myschema.t1; 
ERROR:  permission denied for schema myschema 
LINE 1: SELECT * FROM myschema.t1;

3.切換到管理員dbadmin。

gaussdb=> RESET SESSION AUTHORIZATION; 
RESET

把myschema.t1的權限賦給用戶user_1。

gaussdb=> GRANT USAGE ON SCHEMA myschema TO user_1; 
GRANT

4.切換到普通用戶user_1。

gaussdb=> SET SESSION AUTHORIZATION user_1 PASSWORD '********'; 
SET

查詢表myschema.t1。

gaussdb=> SELECT * FROM myschema.t1; 
 a | b 
---+--- 
(0 rows)

數據庫密碼到期了，如何修改？

數據庫管理員dbadmin的密碼，可登錄管理控制臺選擇集群所在行右邊的“更多 > 重置密碼”進行修改。

出于安全機制考慮，DWS在集群參數中通過以下2個關鍵參數管理帳戶密碼，在管理控制臺，單擊集群名稱，切換到“參數修改”可進行參數修改。

• failed_login_attempts：輸入密碼錯誤的次數，超出設置值，數據庫帳戶會被自動鎖定，可通過dbadmin管理帳戶執行以下語句解鎖。

ALTER USER user_name ACCOUNT UNLOCK;

• password_effect_time：帳戶密碼的有效期，單位為天，默認為90。

如何給指定用戶賦予某張表的權限？

給指定用戶賦予某張表的權限主要通過以下語法實現，本章主要介紹常見的幾種場景，包括只讀（SELECT）、插入（INSERT）、改寫（UPDATE）和擁有所有權限。

語法格式

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ANALYZE | ANALYSE } [, ...]  
      | ALL [ PRIVILEGES ] } 
    ON { [ TABLE ] table_name [, ...] 
       | ALL TABLES IN SCHEMA schema_name [, ...] } 
    TO { [ GROUP ] role_name | PUBLIC } [, ...]  
    [ WITH GRANT OPTION ];

場景介紹

假設當前有用戶u1u5，在系統中有對應的同名Schema u1u5，各用戶的權限管控如下：

• u2作為只讀用戶，需要表u1.t1的SELECT權限。
• u3作為插入用戶，需要表u1.t1的INSERT權限。
• u4作為改寫用戶，需要表u1.t1的UPDATE權限。
• u5作為擁有所有權限的用戶，需要表u1.t1的所有權限。

表u1.t1的表權限分類

用戶名	用戶類型	Grant授權語句	查詢	插入	修改	刪除
u1	所有者	-	√	√	√	√
u2	只讀用戶	GRANT SELECT ON u1.t1 TO u2;	√	x	x	x
u3	插入用戶	GRANT INSERT ON u1.t1 TO u3;	x	√	x	x
u4	改寫用戶	GRANT SELECT,UPDATE ON u1.t1 TO u4; 須知 授予UPDATE權限必須同時授予SELECT權限，否則會出現信息泄露。	√	x	√	x
u5	擁有所有權限的用戶	GRANT ALL PRIVILEGES ON u1.t1 TO u5;	√	√	√	√

操作步驟

以下將演示不同權限的授權方法和驗證過程。

1.打開窗口1（即dbadmin連接會話窗口，后續不再提示），使用系統管理員dbadmin連接DWS數據庫，創建用戶u1u5（系統默認會創建u1u5的同名SCHEMA）。

CREATE USER u1 PASSWORD '{password}'; 
CREATE USER u2 PASSWORD '{password}'; 
CREATE USER u3 PASSWORD '{password}'; 
CREATE USER u4 PASSWORD '{password}'; 
CREATE USER u5 PASSWORD '{password}';

2.在SCHEMA u1下創建表u1.t1。

CREATE TABLE u1.t1 (c1 int, c2 int);

3.為表中插入兩條數據。

INSERT INTO u1.t1 VALUES (1,2); 
INSERT INTO u1.t1 VALUES (1,2);

4. DWS中引入了SCHEMA層概念，如果有SCHEMA，需要先給用戶賦予SCHEMA的使用權限。

GRANT USAGE ON SCHEMA u1 TO u2,u3,u4,u5;

5.給只讀用戶u2賦予表u1.t1的查詢權限。

GRANT SELECT ON u1.t1 TO u2;

6.打開窗口2（即用戶u2連接會話窗口，后續不再提示），使用用戶u2連接DWS數據庫，驗證u2可以查詢u1.t1表，但是不能寫入和修改，此時u2為只讀用戶。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

7.切回窗口1，分別給u3、u4、u5賦予對應的權限。

GRANT INSERT ON u1.t1 TO u3;    --插入用戶u3，可以插入數據 
GRANT SELECT,UPDATE ON u1.t1 TO u4;   --改寫用戶u4，可以修改表 
GRANT ALL PRIVILEGES ON u1.t1 TO u5;   --擁有所有權限的用戶u5，可以對表進行查詢、插入、改寫和刪除

8.打開窗口3，使用用戶u3連接DWS數據庫，驗證u3可以插入u1.t1，但是不能查詢和修改，此時u3為插入用戶。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

9.打開窗口4，使用用戶u4連接DWS數據庫，驗證u4可以修改u1.t1，同時還可以查詢，但是不能插入，此時u4為改寫用戶。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1;

10.打開窗口5，使用用戶u5連接DWS數據庫，驗證u5可以查詢、插入、修改和刪除u1.t1，此時u5為擁有所有權限的用戶。

SELECT * FROM u1.t1; 
INSERT INTO u1.t1 VALUES (1,20); 
UPDATE u1.t1 SET c2 = 3 WHERE c1 =1; 
DELETE FROM u1.t1;

11.最后切回窗口1，通過函數has_table_privilege分別查詢每個用戶的權限。

SELECT * FROM pg_class WHERE relname = 't1';

返回結果，查看relacl字段，該字段回顯結果如下。" rolename =xxxx/yyyy" --表示rolename對該表有xxxx權限，且權限來自yyyy；

例如下圖，與以上驗證結果完全一致。

• u1=arwdDxtA/u1, 表示u1為owner，擁有所有權限。
• u2=r/u1,表示u2擁有讀權限。
• u3=a/u1,表示u3擁有插入權限。
• u4=rw/u1，表示u4擁有讀和修改權限。
• u5=arwdDxtA/u1,表示u5擁有所有權限。

如何給指定用戶賦予某個SCHEMA的權限？

給某個用戶授權某個SCHEMA的權限，包括三個場景（本章節針對SCHEMA層級權限，僅演示查詢權限，如果需要其他權限，可以參考《常見問題-操作類-帳戶、密碼、權限》中“如何給指定用戶賦予某張表的權限？”章節：

• SCHEMA下當前某張表權限。
• SCHEMA下當前所有表的權限。
• SCHEMA下未來創建的表的權限。

假設有兩個用戶u1和u2，對應的同名SCHEMA是u1和u2，用戶u2需要訪問SCHEMA u1的表（包括當前的某張表、所有表、未來創建的表）。

1.打開窗口1（即dbadmin連接會話窗口，后續不再提示），使用系統管理員dbadmin連接DWS數據庫，創建用戶u1和u2（系統默認會創建u1和u2的同名SCHEMA）。

CREATE USER u1 PASSWORD '{password}'; 
CREATE USER u2 PASSWORD '{password}';

2.在SCHEMA u1下創建表u1.t1和u1.t2。

CREATE TABLE u1.t1 (c1 int, c2 int); 
CREATE TABLE u1.t2 (c1 int, c2 int);

3.給用戶u2賦予SCHEMA u1的訪問權限。

GRANT USAGE ON SCHEMA u1 TO u2;

4.（ 某張表權限 ）給用戶u2賦予SCHEMA u1下某張表u1.t1的查詢權限。

GRANT SELECT ON u1.t1 TO u2;

5.打開窗口2（即用戶u2連接會話窗口，后續不再提示），使用用戶u2連接DWS數據庫，驗證u2可以查詢u1.t1表，但是不能查詢u1.t2表。

SELECT * FROM u1.t1; 
SELECT * FROM u1.t2;

6.（ 所有表權限 ）切回窗口1，使用dbadmin給用戶u2賦予SCHEMA u1下所有表的查詢權限。

GRANT SELECT ON ALL TABLES IN SCHEMA u1 TO u2;

7.切回窗口2，再次驗證u2可以查詢所有表。

SELECT * FROM u1.t1; 
SELECT * FROM u1.t2;

8.切回窗口1，創建一張新的表u1.t3。

CREATE TABLE u1.t3 (c1 int, c2 int);

9.切回窗口2，驗證發現用戶u2沒有u1.t3的查詢權限，說明u2雖然有SCHEMA u1下所有表的訪問權限，但是對于賦權后新創建的表還是沒有訪問權限（即u2對SCHEMA u1未來的表權限是沒有的）。

SELECT * FROM u1.t3;

10.（未來表權限） 切回窗口1，給用戶u2賦予SCHAMA u1未來創建的表的訪問權限，并創建一張新的表u1.t4。

ALTER DEFAULT PRIVILEGES FOR ROLE u1 IN SCHEMA u1 GRANT SELECT ON TABLES TO u2; 
CREATE TABLE u1.t4 (c1 int, c2 int);

11.切回窗口2，驗證發現用戶u2可以訪問u1.t4。但是對于之前的u1.t3還是沒有訪問權限（以上ALTER DEFAULT PRIVILEGES語句的授權范圍只是未來創建的表，對于已經創建的表，還是要使用GRANT 單張表重新授權），此時需要參考步驟4重新授權u1.t3即可。

SELECT * FROM u1.t4;

如何創建數據庫只讀用戶？

場景介紹

在業務開發場景中，數據庫管理員通過SCHEMA來劃分不同的業務，例如在金融行業中，負債業務屬于SCHEMA s1，資產業務屬于SCHEMA s2。

當前需要在數據庫中創建一個只讀用戶user1，允許這個用戶訪問負債業務SCHEMA s1下所有的表（包括未來創建的新表），供日常讀取，但是不允許做數據插入、修改或刪除。

實現原理

DWS有基于角色的用戶管理，需要先創建一個的只讀角色role1，再將對應的角色授權到實際的用戶user1即可。

操作步驟

1.使用系統管理員dbadmin連接DWS數據庫。

2.執行以下SQL語句創建角色role1。

CREATE ROLE role1 PASSWORD disable;

3.執行以下SQL語句，為角色role1進行授權。

GRANT usage ON SCHEMA s1 TO role1;     --賦予SCHEMA s1的訪問權限; 
GRANT select ON ALL TABLES IN SCHEMA s1 TO role1;    --賦予SHCEMA s1下所有表的查詢權限; 
ALTER DEFAULT PRIVILEGES FOR USER tom IN SCHEMA s1 GRANT select ON TABLES TO role1;     --賦予SCHEMA s1未來創建的表的權限，其中tom為SCHEMA s1的owner

4.執行以下SQL語句，將角色role1授權到實際用戶user1。

GRANT role1 TO user1;

5.使用只讀用戶user1進行SCHAMA s1下所有表數據的日常讀取。

如何創建數據庫私有用戶和私有表？

場景介紹

在業務開發場景中，普通用戶默認創建的表，系統管理員dbadmin還是有權訪問的，并沒有做到完全私有。而在三權分立開啟下，管理員dbadmin雖然對普通用戶的表沒有訪問權限，但同時也沒有控制權限（DROP、ALTER、TRUNCATE）。

當前業務開發場景中，需要創建一個私有用戶和私有表（即私有用戶創建的表），這個私有表只有私有用戶本身可以訪問，系統管理員dbadmin和其他普通用戶均無權訪問（INSERT、DELETE、UPDATE、SELECT、COPY），但也希望在未經私有用戶授權的情況下，可允許系統管理員dbadmin做DROP/ALTER/TRUNCATE操作。因此，私有用戶應運而生，私有用戶即擁有INDEPENDENT屬性的用戶。

實現原理

通過創建INDEPENDENT屬性的用戶來實現。

操作步驟

1.使用系統管理員dbadmin連接DWS數據庫。

2.執行以下SQL語句創建私有用戶u1。

CREATE USER u1 WITH INDEPENDENT IDENTIFIED BY "password";

3.使用u1重新連接DWS數據庫，創建測試表，并插入數據。

CREATE TABLE test (id INT, name VARCHAR(20)); 
INSERT INTO test VALUES (1, 'joe'); 
INSERT INTO test VALUES (2, 'jim');

4.切換成dbadmin用戶重新連接DWS數據庫，并執行以下SQL語句驗證是否可以訪問，結果提示無權訪問。

SELECT * FROM u1.test;

5.執行控制語句DROP，仍然可以刪除成功。

DROP TABLE u1.test;

如何REVOKE某用戶的connect on database權限？

DWS提供了一個隱式定義的擁有所有角色的組PUBLIC，所有創建的用戶和角色默認擁有PUBLIC所擁有的權限。要撤銷或重新授予用戶和角色對PUBLIC的權限， 可通過在GRANT和REVOKE指定關鍵字PUBLIC實現。

DWS會將某些類型的對象上的權限授予PUBLIC。默認情況下，對表、表字段、序列、外部數據源、外部服務器、模式或表空間對象的權限不會授予PUBLIC，而以下這些對象的權限會授予PUBLIC：數據庫的CONNECT權限和CREATE TEMP TABLE權限、函數的EXECUTE特權、語言和數據類型（包括域）的USAGE特權。當然，對象擁有者可以撤銷默認授予PUBLIC的權限并專門授予權限給其他用戶。為了更安全，建議在同一個事務中創建對象并設置權限，這樣其他用戶就沒有時間窗口使用該對象。另外，這些初始的默認權限可以使用ALTER DEFAULT PRIVILEGES命令修改。

可參考以下示例，REVOKE某用戶的connect on database權限：

1.執行以下命令連接DWS 集群的默認數據庫postgres：

gsql -d postgres -h 192.168.0.89 -U dbadmin -p 8000 -r

根據界面提示輸入密碼后，顯示如下信息表示gsql工具已經連接成功：

postgres=>

2.創建用戶u1。

CREATE USER u1 IDENTIFIED BY 'password'; 
CREATE USER

3.確認u1正常訪問。

gsql -d postgres -h 192.168.0.89 -U u1 -p 8000 -W password -r 
gsql ((GaussDB 8.1.0 build be03b9a0) compiled at 2021-03-12 14:18:02 commit 1237 last mr 2001 release) 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_128_GCM_SHA256, bits: 128) 
Type "help" for help.

4.撤銷public的connect on database權限。

gsql -d postgres -h 192.168.0.89 -U dbadmin -p 8000 -r 
postgres=> 
 
REVOKE CONNECT ON database gaussdb FROM public; 
REVOKE

                    
說明
                    
若直接使用revoke connect on database postgres from u1命令撤銷u1用戶的權限不會生效，因為數據庫的CONNECT權限授予了PUBLIC，需指定關鍵字PUBLIC實現。
                    
                  
                  

5.驗證結果，顯示如下內容表示用戶u1的connect on database權限已成功撤銷。

gsql -d postgres -h 192.168.0.89 -U u1 -p 8000 
gsql: FATAL:  permission denied for database "gaussdb" 
DETAIL:  User does not have CONNECT privilege.

如何查看某個用戶有哪些表的權限？

場景一：查看用戶有哪些表的權限 ，可以通過information_schema.table_privileges查看。例如

SELECT * FROM information_schema.table_privileges WHERE GRANTEE='user_name'; 

• grantor :賦權用戶
• grantee：被賦權用戶
• table_catalog：數據庫名
• table_schema：模式名
• table_name：對象名
• privilege_type：被賦予的權限

如上圖所示，表示用戶u2擁有Schema u2下的t2的所有權限和Schema u1下的t1的SELECT 權限 。

場景二：查看用戶是否有某張表的權限 ，可以通過以下方法。

1.執行以下語句查詢pg_class系統表。

select * from pg_class where relname = 'tablename';

查看relacl字段，該字段回顯結果如下，權限參數參見下表“權限的參數說明”。

• " rolename =xxxx/yyyy" --表示rolename對該表有xxxx權限，且權限來自yyyy；
• "=xxxx/yyyy" -- 表示public對該表有xxxx權限，且權限來自yyyy。

例如下圖：

joe=arwdDxtA，表示joe用戶有所有權限（ALL PRIVILEGES）。

leo=arw/joe，表示leo用戶擁有讀、寫、改權限，該權限來自joe授權。

權限的參數說明

參數	參數說明
r	SELECT（讀）
w	UPDATE（寫）
a	INSERT（插入）
d	DELETE
D	TRUNCATE
x	REFERENCES
t	TRIGGER
X	EXECUTE
U	USAGE
C	CREATE
c	CONNECT
T	TEMPORARY
A	ANALYZE
arwdDxtA	ALL PRIVILEGES（用于表）
*	給前面權限的授權選項

2.如果要查某用戶對某張表是否有某種權限，也可以通過訪問權限查詢函數has_table_privilege進行查詢。

select * from has_table_privilege('用戶名','表名','select');

例如，查詢joe對表t1是否有查詢權限。

select * from has_table_privilege('joe','t1','select');

Ruby是什么用戶？

在執行SELECT * FROM pg_user語句查看當前系統的用戶時，看到Ruby用戶且擁有很多權限。

Ruby用戶為官方運維使用帳戶，DWS數據庫創建后，默認生成Ruby帳戶，不涉及安全風險，請放心使用。