GaussDB(DWS) SQL進階-database、schema、user和權限控制

【概要】

database是GaussDB(DWS)對象的物理集合，不同database之間資源完全隔離(除部分共享對象之外)。一個database里面通過schema把數據庫對象進行邏輯劃分，通過權限管理實現在同一個session下對不同schema下對象的訪問和操作權限。

【DATABASE】

在GaussDB(DWS)中，database是對業務的物理隔離，不同database的之間的對象不能相互訪問。比如在databaseA中無法訪問databse B中的對象。因此登錄集群的時候必須顯示指定要連接的databse。

在GaussDB(DWS)中創建database時，需要重點關注字符集編碼(ENCODING)和兼容性(DBCOMPATIBILITY)兩個配置項。ENCODING指明了數據庫存儲的數據的編碼格式，為了適應全球化，創建DATABASE的時候建議使用UTF-8編碼。DBCOMPATIBILITY 指明了DATABASE的兼容性選項，GaussDB(DWS)支持Oracle、Teradata和MySQL三種兼容模式，分別兼容Oracle、Teradata和MySQL語法；若不指定DBCOMPATIBILITY，則默認為ORA。需要注意的是, 數據庫一旦創建，這兩個屬性就不能修改，甚至語法層就沒有提供修改這兩個屬性的接口

【SCHEMA】

在GaussDB(DWS)中，schema是DATABASE下一個特殊的對象，實現對數據庫對象的邏輯隔離，從功能上類似于一些編程語言中namespace的概念。同一個schema下，不能存在同名的數據庫對象；但是不同scheam下的對象名可以重復。

postgres=# CREATE SCHEMA nsp_1; CREATE SCHEMA postgres=# CREATE SCHEMA nsp_2; CREATE SCHEMA postgres=# CREATE TABLE nsp_1.t1(a int, b int) DISTRIBUTE BY HASH(b); CREATE TABLE postgres=# CREATE TABLE nsp_1.t1(a int, b int) DISTRIBUTE BY HASH(b); ERROR: relation "t1" already exists postgres=# CREATE TABLE nsp_2.t1(a int, b int) DISTRIBUTE BY HASH(b); CREATE TABLE postgres=#

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

postgres=# -- 刪除失敗，提示依賴關系 postgres=# DROP SCHEMA nsp_1; ERROR: cannot drop schema nsp_1 because other objects depend on it DETAIL: table nsp_1.t1 depends on schema nsp_1 HINT: Use DROP ... CASCADE to drop the dependent objects too.

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

postgres=# -- 刪除成功，顯示級聯刪除對象 postgres=# DROP SCHEMA nsp_1 CASCADE; NOTICE: drop cascades to table nsp_1.t1 DROP SCHEMA

【search_path】

search_path又稱之為模式搜索路徑，本身是一個guc參數。對于未定義schema的對象，會根據search_path的配置賦予默認的schema或者默認的schema搜索范圍

1）創建對象時，會在search_path指定的schema列表中的第一個schema下創建對象

postgres=# DROP SCHEMA IF EXISTS nsp_1 CASCADE; NOTICE: drop cascades to table nsp_1.t1 DROP SCHEMA postgres=# DROP SCHEMA IF EXISTS nsp_2 CASCADE; NOTICE: drop cascades to table t1 DROP SCHEMA postgres=# CREATE SCHEMA nsp_1; CREATE SCHEMA postgres=# CREATE SCHEMA nsp_2; CREATE SCHEMA postgres=# postgres=# SET search_path TO nsp_1, nsp_2; SET postgres=# CREATE TABLE t1(a text, b int) DISTRIBUTE BY HASH(b); CREATE TABLE postgres=# INSERT INTO t1 VALUES('nsp_1', 1); INSERT 0 1 postgres=#-- 此處創建的表t1在schema nsp_1下 postgres=# SELECT schemaname, tablename, tablecreator, created FROM pg_tables WHERE tablename = 't1'; schemaname | tablename | tablecreator | created ------------+-----------+--------------+------------------------------- nsp_1 | t1 | jyh | 2021-03-30 16:50:12.930546+08 (1 row) postgres=# SET search_path to nsp_2, nsp_1; SET postgres=# CREATE TABLE t1(a text, b int) DISTRIBUTE BY HASH(b); CREATE TABLE postgres=# INSERT INTO t1 VALUES('nsp_2', 1); INSERT 0 1 postgres=#-- 此處創建的表 t1 在schema nsp_2下 postgres=# SELECT schemaname, tablename, tablecreator, created FROM pg_tables WHERE tablename = 't1'; schemaname | tablename | tablecreator | created ------------+-----------+--------------+------------------------------- nsp_1 | t1 | jyh | 2021-03-30 16:50:12.930546+08 nsp_2 | t1 | jyh | 2021-03-30 16:50:22.476881+08 (2 rows)

2) 查詢對象時，如果對象沒有顯式指明schema，GaussDB(DWS)會按照search_path中指明的schema列表，順序查找指定名稱的對象。如果遍歷完所有的schema都沒有查找到同名對象，數據庫會直接報錯。

postgres=# SET search_path TO nsp_1, nsp_2; SET postgres=# -- 查詢的表 t1 在schema nsp_1下 postgres=# SELECT * FROM t1; a | b -------+--- nsp_1 | 1 (1 row) postgres=# SET search_path TO nsp_2, nsp_1; SET postgres=# -- 查詢的表 t1 在schema nsp_2下 postgres=# SELECT * FROM t1; a | b -------+--- nsp_2 | 1 (1 row) postgres=# -- 在schema public下創建表t2 postgres=# CREATE TABLE public.t2(a text, b int) DISTRIBUTE BY HASH(b); CREATE TABLE postgres=# -- public在search_path指明的schema之外，查詢t2報錯 postgres=# SELECT * FROM t2; ERROR: relation "t2" does not exist LINE 1: SELECT * FROM t2; ^ postgres=#

【USER】

【權限管理】

GaussDB(DWS)中的權限管理分成三種場景

1）系統權限

系統權限又稱為用戶屬性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。系統權限一般通過CREATE/ALTER ROLE語法來指定。其中，SYSADMIN權限可以通過GRANT/REVOKE ALL PRIVILEGE授予或撤銷。但系統權限無法通過ROLE和USER的權限被繼承，也無法授予PUBLIC

2) 用戶權限

將一個用戶的權限授予一個或多個其他用戶。在這種情況下，每個用戶都可視為擁有一個或多個數據庫權限的集合。當聲明了WITH ADMIN OPTION，被授權的用戶可以將該權限再次授予其他角色或用戶，以及撤銷所有由該角色或用戶繼承到的權限。當授權的角色或用戶發生變更或被撤銷時，所有繼承該角色或用戶權限的用戶擁有的權限都會隨之發生變更

3) 數據對象權限

將數據庫對象（表和視圖、指定字段、數據庫、函數、模式、表空間等）的相關權限授予特定角色或用戶。

GRANT命令將數據庫對象的特定權限授予一個或多個角色。這些權限會追加到已有的權限上

關鍵字PUBLIC表示該權限要賦予所有角色，包括以后創建的用戶。PUBLIC可以看做是一個隱含定義好的組，它總是包括所有角色。任何角色或用戶都將擁有通過GRANT直接賦予的權限和所屬的權限，再加上PUBLIC的權限

如果聲明了WITH GRANT OPTION，則被授權的用戶也可以將此權限賦予他人，否則就不能授權給他人。但是WITH GRANT OPTION這個選項不能賦予PUBLIC

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

需要特別指出的是：

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

test=# -- 1. 超級用戶把在database test下把創建schema的權限賦給普通用戶dfm test=# GRANT CREATE ON DATABASE test TO dfm; GRANT test=# -- 切換到用戶dfm test=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123'; SET test=> -- 2. 普通用戶dfm在database test下創建schema nsp_3 test=> CREATE SCHEMA nsp_3; CREATE SCHEMA test=> -- 切換回原來的用戶 test=> RESET SESSION AUTHORIZATION; RESET test=# -- 3. 超級用戶在schema nsp_3下創建表t1 test=# CREATE TABLE nsp_3.t1(a int, b int) DISTRIBUTE BY HASH(b); CREATE TABLE test=# -- 切換到用戶dfm test=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123'; SET test=> -- 4. 普通用戶dfm刪除超級用戶在schema nsp_3下創建的表t1; test=> DROP TABLE nsp_3.t1; DROP TABLE test=>

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

postgres=# -- 把表nsp_1.t1的權限賦給用戶dfm postgres=# GRANT SELECT ON TABLE nsp_1.t1 TO dfm; GRANT postgres=# -- 切換到用戶dfm postgres=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123'; SET postgres=> -- 查詢表nsp_1.t1 postgres=> SELECT * FROM nsp_1.t1; ERROR: permission denied for schema nsp_1 LINE 1: SELECT * FROM nsp_1.t1; ^ postgres=> -- 切換回原來的用戶 postgres=> RESET SESSION AUTHORIZATION; RESET postgres=# -- 把schema nsp_1的權限賦給用戶dfm postgres=# GRANT USAGE ON SCHEMA nsp_1 TO dfm; GRANT postgres=# -- 切換到用戶dfm postgres=# SET SESSION AUTHORIZATION dfm PASSWORD 'Gauss@123'; SET postgres=> -- 查詢表nsp_1.t1 postgres=> SELECT * FROM nsp_1.t1; a | b -------+--- nsp_1 | 1 (1 row)

【業務隔離】

GaussDB(DWS)中可以使用database和Schema實現業務的隔離，區別在于database之間無法直接互訪，通過連接隔離實現徹底的權限隔離。schema隔離的方式共用資源較多，可以通過grant與revoke語法便捷地控制不同用戶對各schema及其下屬對象的權限，從而賦給業務更多的靈活性。

通常情況下我們建議使用schema進行業務隔離，除非我們新業務具有以下特征相對以前的業務，

新業務的數據源需要特殊的字符集編碼或者排序規則，導致舊的database無法承載業務

相對以前的業務，新業務需要滿足特殊的兼容性來降低業務遷移成本

安全上需要絕對的嚴格的隔離，這種場景一般需要結合pg_hba.conf配置文件進行會話級別的認證控制，只允許特定IP+特定用戶訪問這些數據庫

【趣知識】

1. DATABASE實現業務的嚴格隔離，甚至不同DATABASE的對應的數據文件都存儲在不同的文件目錄下面

2. SCHENA不僅含義上跟namespace相似，甚至存儲SCHENA元信息的系統表也叫pg_namespace

EI企業智能 Gauss AP 數據倉庫服務 GaussDB(DWS)

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。