帶你學習DWS資料庫使用者許可權設計與管理

前言

本文將介紹DWS基於RBAC（Role-Based Access Control，基於角色的訪問控制）的資料庫使用者許可權管理。簡單地說，一個使用者擁有若干角色，每一個角色擁有若干許可權。這樣，就構造成“使用者-角色-許可權”的授權模型。在這種模型中，使用者之間，角色與許可權之間，一般是多對多的關係。

透過本節，我們將學習到DWS資料庫許可權管理的相關知識並進一步學會如何進行許可權管理。

1      物件基本概念

叢集：叢集是由一組伺服器和其它資源組成的一個單獨的系統，可以實現高可用性。有的情況下，可以實現負載均衡及並行處理。

資料庫：資料庫是儲存在一起的相關資料的集合，這些資料可以被訪問，管理以及更新。一套叢集包含一個或多個已命名資料庫。

使用者和角色：使用者和角色在整個叢集範圍內是共享的，但是其資料並不共享。即使用者可以連線任何資料庫，但當連線成功後，任何使用者都只能訪問連線請求裡宣告的那個資料庫。

模式：資料庫物件集，包括邏輯結構，例如表、檢視、序、儲存過程、同義名、索引、叢集及資料庫連結。

表：表是由行與列組合成的。每一列被當作是一個欄位。每個欄位中的值代表一種型別的資料。

它們之間的關係如下：                                             

叢集中可以建立多個庫，庫與庫之間物理隔離，叢集中的使用者和角色是唯一併且全域性共用的，訪問庫的許可權透過使用者進行控制，同一個庫中，schema是唯一的，不同schema下可以建立同名表，表與表之間透過schema進行區分，不同使用者之間的資料訪問透過許可權控制進行隔離，不同使用者間表的訪問許可權透過使用者進行維護，透過角色進行許可權統一管理，一個使用者下可建立不同schema區別不同的業務模組，透過不同使用者提供給不同業務使用。

使用CREATE USER和ALTER USER可以建立和管理資料庫使用者。資料庫叢集包含一個或多個已命名資料庫。使用者和角色在整個叢集範圍內是共享的，但是其資料並不共享。即使用者可以連線任何資料庫，但當連線成功後，任何使用者都只能訪問連線請求裡宣告的那個資料庫。

非 下，DWS使用者帳戶只能由系統管理員或擁有CREATEROLE屬性的安全管理員建立和刪除。三權分立時，使用者帳戶只能由初始使用者員和安全管理員建立。

在使用者登入DWS時會對其進行身份驗證。使用者可以擁有資料庫和資料庫物件（例如表），並且可以向使用者和角色授予對這些物件的許可權以控制誰可以訪問哪個物件。除系統管理員外，具有CREATEDB屬性的使用者可以建立資料庫並授予對這些資料庫的許可權。

角色是一組使用者的集合。透過GRANT把角色授予使用者後，使用者即具有了角色的所有許可權。推薦使用角色進行高效許可權分配。例如，可以為設計、開發和維護人員建立不同的角色，將角色GRANT給使用者後，再向每個角色中的使用者授予其工作所需資料的差異許可權。在角色級別授予或撤消許可權時，這些更改將作用到角色下的所有成員。

Schema又稱作模式。透過管理Schema，允許多個使用者使用同一資料庫而不相互干擾，可以將資料庫物件組織成易於管理的邏輯組，同時便於將第三方應用新增到相應的Schema下而不引起衝突。

每個資料庫包含一個或多個Schema。資料庫中的每個Schema包含表和其他型別的物件。資料庫建立初始，預設具有一個名為public的Schema，且所有使用者都擁有此Schema的許可權。可以透過Schema分組資料庫物件。Schema類似於作業系統目錄，但Schema不能巢狀。

相同的資料庫物件名稱可以應用在同一資料庫的不同Schema中，而沒有衝突。例如，a_schema和b_schema都可以包含名為mytable的表。具有所需許可權的使用者可以訪問資料庫的多個Schema中的物件。

2      角色、使用者與使用者組

Ø  角色（ROLE）本質上是一組許可權的集合，通常情況下使用ROLE來組織許可權，使用使用者進行許可權的管理和業務操作。

Ø  之間的許可權可以繼承，使用者組的所有使用者可自動繼承對應角色的許可權。

Ø  資料庫中USER與ROLE的關係為，USER的許可權來自於ROLE。

Ø  使用者組包含了具有相同許可權的使用者集合。

Ø  使用者可以看作是具有登入許可權的角色。

Ø  角色可以看作是沒有登入許可權的使用者。

根據不同業務場景需要，管理員使用 “管控面”建立並管理不同使用者組。使用者組透過繫結角色獲取操作許可權，使用者加入使用者組後，可獲得使用者組具有的操作許可權。使用者組同時可以達到對使用者進行分類並統一管理多個使用者。最大支援5000個使用者組（包括系統內建使用者組）。

DWS提供的許可權包括“管控面”和各元件的操作維護許可權，在實際應用時需根據業務場景為各使用者分別配置不同許可權。為了提升許可權管理的易用性，“管控面”引入角色的功能，透過選取指定的許可權並統一授予角色，以許可權集合的形式實現了許可權集中檢視和管理。

這樣一方面對普通使用者遮蔽了內部的許可權管理細節，另一方面對管理員簡化了許可權管理的操作方法，提升了許可權管理的易用性和使用者體驗。

集中許可權管理中許可權、角色和使用者的關係例如 圖1所示。

圖1 許可權管理與使用者關聯示意圖

DWS提供多種許可權，根據業務場景實際需要選擇指定的許可權授予不同角色，可能是一個或者多個許可權對應一個角色。

l  角色A：授予操作許可權A和B，使用者A和使用者B透過分配角色A取得對應的許可權。

l  角色 B：授予操作許可權C，使用者C透過分配角色 B取得對應的許可權。

l  角色C：授予操作許可權D和F，使用者C透過分配角色C取得對應的許可權。

透過GRANT把角色授予使用者後，使用者即具有了角色的所有許可權。推薦使用角色進行高效許可權分配。只對自己的表有所有許可權，對其他使用者放在屬於各自模式下的表無許可權。

3      基於角色的許可權管理模型

Ø  系統中的許可權：

     系統許可權：系統規定使用者使用資料庫的許可權

     物件許可權：在表、序列、函式等資料庫物件上執行特殊動作的許可權。

Ø  藉助角色機制：

    當給一組許可權相同的使用者授權時，不需對這些使用者逐一授權。

    透過將角色付給一個使用者可是的該使用者擁有這個角色中的所有許可權。

    一個使用者可以屬於不同的角色，擁有不同角色的許可權。

Ø  普通模式下：

資料庫管理員和業務使用者（只讀使用者、只寫使用者、讀寫使用者）

p 在角色機制下，角色被視為一個資料庫使用者或者一組資料庫使用者。

p 資料庫使用者主要用途是連線資料庫、訪問資料庫物件和執行SQL語句。

p 通常使用ROLE來組織許可權，使用使用者進行實際使用者操作。

p 之間的許可權可以繼承，使用者組的所有使用者自動繼承角色的許可權。

4      許可權種類及物件列表

4.1     角色許可權與物件許可權

Ø  角色是許可權的集合，許可權限制了使用者的行為

Ø  透過為使用者分配角色，限定使用者的權利範圍

Ø  使用角色管理許可權，更加有效

Ø  使用角色管理其所有使用者許可權，更加統一

Ø  角色可以被派生（開啟資源管控，層級兩層）

Ø  角色的物件許可權集合可以被繼承，系統許可權無法繼承

4.2     涉及許可權的資料庫物件

資料庫（DATABASE）、使用者（USER）、模式（SCHEMA）、表（TABLE）、函式（FUNCTION）、表空間（TABLESPACE）、型別（TYPE）、角色（ROLE）

4.3     檢視物件許可權

透過系統表欄位檢視物件許可權變化：

1、使用者mytbl1為使用者along1所擁有的表，且表along2對mytbl1有select(r)查詢許可權。

#select relname,relacl from pg_class where relname = 'mytbl1';

 relname |                 relacl                 

---------+-----------------------------------------

 mytbl1  | {along1=arwdDxt/along1,along2=r/along1}

2、將mytbl1的插入許可權賦給along2。

# grant insert on along1.mytbl1 to along2;

3、檢視到使用者along2已經擁有了對mytbl1的insert(a)許可權。

# select relname,relacl from pg_class where relname = 'mytbl1';

 relname |                  relacl                 

---------+------------------------------------------

 mytbl1  | {along1=arwdDxt/along1,along2=ar/along1

5      許可權管理

5.1     許可權機制

資料庫物件建立後，進行物件建立的使用者就是該物件的所有者。叢集安裝後的預設情況下，未開啟 三權分立，資料庫系統管理員具有與物件所有者相同的許可權。也就是說物件建立後，預設只有物件所有者或者系統管理員可以查詢、修改和銷燬物件，以及透過 GRANT將物件的許可權授予其他使用者。

為使其他使用者能夠使用物件，必須向使用者或包含該使用者的角色授予必要的許可權。

DWS支援以下的許可權：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、CREATE、CONNECT、EXECUTE和USAGE。不同的許可權與不同的物件型別關聯。可以使用 GRANT。

要撤消已經授予的許可權，可以使用 REVOKE。物件所有者的許可權（例如ALTER、 DROP、GRANT和REVOKE）是隱式的，無法授予或撤消。即只要擁有物件就可以執行物件所有者的這些隱式許可權。物件所有者可以撤消自己的普通許可權，例如，使表對自己以及其他人只讀。

系統表和系統檢視要麼只對系統管理員可見，要麼對所有使用者可見。標識了需要系統管理員許可權的系統表和檢視只有系統管理員可以查詢。

5.2     系統管理員

系統管理員是指具有SYSADMIN屬性的帳戶。叢集安裝後，預設情況下系統管理員具有與物件所有者相同的許可權。

叢集安裝過程中自動生成的帳戶稱為初始使用者。初始使用者也是系統管理員，其擁有系統的最高許可權，能夠執行所有的操作。該帳戶與進行叢集安裝的作業系統使用者omm同名。

初始使用者會繞過所有許可權檢查。建議僅將此初始使用者作為DBA管理用途，而非業務應用。

5.3     系統許可權與物件許可權

許可權表示使用者訪問某個資料庫物件的操作是否被允許，資料庫物件包括表、函式、模式、序列等等，操作包括：建立、增、刪、改、查等等。許可權是使用者對一項功能的執行權利,在DWS中,根據系統管理方式的不同,可將許可權分為系統許可權與物件許可權兩類。

5.3.1    系統許可權

資料庫系統特定操作的能力，系統許可權又稱為使用者屬性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN和LOGIN。

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

5.3.2    物件許可權

資料庫物件操作的能力，如SELECT、INSERT、UPDATE、DELETE等。

物件許可權可以有物件所有者或者管理員透過GRANT/REVOKE對其他角色分配或撤銷。

5.4     授權操作

物件許可權管理主要透過GRANT/REVOKE賦予或收回使用者/角色在某個物件上的許可權，PUBLIC特質為所有角色賦權

許可權操作示例：

示例 1 ：將系統許可權授權給使用者或者角色。

建立名為joe的使用者，並將sysadmin許可權授權給他。

CREATE USER joe PASSWORD 'Bigdata123@';

GRANT ALL PRIVILEGES TO joe;

授權成功後，使用者joe會擁有sysadmin的所有許可權。

示例 2 ：將物件許可權授權給使用者或者角色。

1、    撤銷joe使用者的sysadmin許可權，然後將模式tpcds的使用許可權和表tpcds.reason的所有許可權授權給使用者joe。

注：將Schema中的表或者檢視物件授權給其他使用者或角色時，需要將表或檢視所屬Schema的USAGE許可權同時授予該使用者或角色。否則使用者或角色將只能看到這些物件的名字，並不能實際進行物件訪問。

REVOKE ALL PRIVILEGES FROM joe;

GRANT USAGE ON SCHEMA tpcds TO joe;

GRANT ALL PRIVILEGES ON tpcds.reason TO joe;

授權成功後，joe使用者就擁有了tpcds.reason表的所有許可權，包括增刪改查等許可權。

2、將tpcds.reason表中r_reason_sk、r_reason_id、r_reason_desc列的查詢許可權，r_reason_desc的更新許可權授權給joe。

GRANT select (r_reason_sk,r_reason_id,r_reason_desc),update (r_reason_desc) ON tpcds.reason TO joe;

授權成功後，使用者joe對tpcds.reason表中r_reason_sk，r_reason_id的查詢許可權會立即生效。如果joe使用者需要擁有將這些許可權授權給3、其他使用者的許可權，可以透過以下語法對joe使用者進行授權。

GRANT select (r_reason_sk, r_reason_id) ON tpcds.reason TO joe WITH GRANT OPTION;

4、將資料庫postgres的連線許可權授權給使用者joe，並給予其在postgres中建立schema的許可權，而且允許joe將此許可權授權給其他使用者。

GRANT create,connect on database postgres TO joe WITH GRANT OPTION;

5、建立角色tpcds_manager，將模式tpcds的訪問許可權授權給角色tpcds_manager，並授予該角色在tpcds下建立物件的許可權，不允許該角色中的使用者將許可權授權給其他人。

CREATE ROLE tpcds_manager PASSWORD 'Bigdata123@';

GRANT USAGE,CREATE ON SCHEMA tpcds TO tpcds_manager;

6、將表空間tpcds_tbspc的所有許可權授權給使用者joe，但使用者joe無法將許可權繼續授予其他使用者。

CREATE TABLESPACE tpcds_tbspc RELATIVE LOCATION 'tablespace/tablespace_1';

GRANT ALL ON TABLESPACE tpcds_tbspc TO joe;

示例 3 ：將使用者或者角色的許可權授權給其他使用者或角色。

1、建立角色manager，將joe的許可權授權給manager，並允許該角色將許可權授權給其他人。

CREATE ROLE manager PASSWORD 'Bigdata123@';

GRANT joe TO manager WITH ADMIN OPTION;

2、建立使用者senior_manager，將使用者manager的許可權授權給該使用者。

CREATE ROLE senior_manager PASSWORD 'Bigdata123@';

GRANT manager TO senior_manager;

3、撤銷許可權，並清理使用者。

REVOKE manager FROM joe;

REVOKE senior_manager FROM manager;

DROP USER manager;

5.5     許可權規劃

5.5.1    系統最小授權規劃原則

5.5.2    許可權授予使用建議

1）     許可權授予最小化，只需要SELECT許可權的不需要授予其他許可權。

2）     不要為了方便隨便授予ALL PRIVILEGES許可權。

3）     謹慎授予可能改變表內容的操作（update、insert）等許可權。

4）     管理好許可權週期，超過時間及時使用REVOKE回收許可權。

6      業務使用者許可權示例

6.1     業務操作許可權

步驟 1 ：先建一個角色

create role data_mgr password 'Gauss_234';

步驟 2 ：登陸schema所屬的使用者u2賦予角色data_mgr對所屬schema s2的使用許可權以及所有表的資料操作許可權

Ø  賦予角色data_mgr對s2的使用許可權

grant USAGE,CREATE on schema s2 to data_mgr;

Ø  角色data_mgr對u2已建立的表賦予查詢許可權

grant SELECT,insert,delete,update on all tables in schema s2 to data_mgr;

Ø  角色data_mgr對u2以後建立的新表賦予查詢許可權

alter default privileges in schema s2 grant SELECT,insert,delete,update on tables to data_mgr;

6.2     只讀操作許可權

步驟 1 ：建立只讀角色

create role read_only  password 'Gauss_234';

步驟 2 ：登陸schema所屬的使用者u2賦予角色 read_only 對schema  s1和s2的使用許可權以及所有表的查詢許可權

Ø  賦予角色read_only對s1,s2的使用許可權

grant USAGE on schema s1,s2 to read_only;     

Ø  --角色read_only擁有對s1,s2已建立的表賦予查詢許可權

grant SELECT on all tables in schema s1,s2 to read_only;     

Ø  角色read_only擁有對s1,s2以後建立的新表賦予查詢許可權

alter default privileges in schema s1,s2 grant SELECT on tables to read_only; 

6.3     賦權操作

Ø  給使用者u1賦予對使用者u2的schema s2的資料操作許可權。

grant data_mgr to u1;

Ø  給使用者u3賦予對使用者u2的schema s1和s2的只讀許可權。

grant read_only to u3;

6.4     許可權回收操作

6.4.1    角色的許可權回收

Ø  回收角色data_mgr對s2的使用許可權

revoke USAGE,CREATE on schema s2 from data_mgr;

Ø  回收角色data_mgr對u2已建立的表查詢許可權

revoke SELECT,insert,delete,update on all tables in schema s2 from data_mgr;

Ø  回收角色data_mgr對u2以後建立的新表賦予查詢許可權

alter default privileges in schema s2 revoke SELECT,insert,delete,update on tables from data_mgr;

6.4.2    使用者的許可權回收

Ø  回收使用者u1對u2的schema s2的資料操作許可權

revoke data_mgr to u1;

Ø  回收使用者u3對u2的schema s1、s2的只讀許可權

revoke read_only from u3;

6.5     賦權與回收

把當前使用者下某個schema下的表的查詢許可權賦給其他使用者，既需要把schema的usage許可權給其他使用者，也要賦予其他使用者對於表的查詢許可權（可以指定特定表來賦權查詢操作，也可以賦予一次性賦予所有表的查詢許可權）。

對於將來新建表的查詢許可權想要一次性的賦予許可權，則需要透過alter default privileges賦予預設許可權來操作。

回收許可權的話，需要回收schema的usage許可權，也要回收表的查詢許可權，還有回收預設許可權。

可以透過\ddp來檢視預設賦權資訊。

7      總結

  透過本文，可以系統瞭解DWS資料庫許可權管理相關知識，從而為資料庫管理和業務開發提供技術支撐。透過更加有效和細緻的許可權管理，制定完善的資料安全機制，保證資料安全。