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）

類別

標志

INSERT

'a'

SELECT

'r'

UPDATE

'w'

DELETE

'd'

TRUNCATE

'D'

CREATE

'C'

CONNECT

'c'

TEMPORARY

'T'

EXECUTE

'X'

USAGE

'U'

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。

查看特殊系統表權限

權限作用

能否GRANT/REVOKE

SYSADMIN

查看特殊系統表權限

是

CREATEDB

創建數據庫DATABASE

否

CREATEROLE

創建用戶與角色

否

AUTITADMIN

是否可以查看審計日志

否

LOGIN

是否有連接數據庫權限

否

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??? 系統最小授權規劃原則

步驟

描述

規劃原則

1

規劃系統權限

默認情況下，只有系統管理員具備系統權限。在數據庫安裝成功后，可以使用系統管理員給其他用戶分配系統權限。從安全性考慮，系統權限應該分別賦予可信賴的用戶。

2

規劃對象權限

對象權限的規劃比較靈活，系統管理員可以將某些數據庫對象的所有權限賦予某個用戶，也可以將某些數據庫對象的部分權限（比如：SELECT權限和UPDATE權限等）分別賦予不同的用戶。

3

規劃角色

在實際工作中，如果有兩個以上的用戶具有相同的對象權限，則建議將這幾個用戶規劃為一個角色，并將這些權限賦予此角色。

4

賦予用戶權限

根據以上規劃：

l? 通過語句CREATE/ALTER USER將系統權限賦予指定用戶。

l? 通過語句GRANT/REVOKE將對象權限賦予指定用戶。

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數據庫權限管理相關知識，從而為數據庫管理和業務開發提供技術支撐。通過更加有效和細致的權限管理，制定完善的數據安全機制，保證數據安全。

數據庫 HUAWEI CONNECT

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

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