Hologres如何對使用者授權及撤銷授權 - Hologres

Hologres相容PostgreSQL，採用與標準PostgreSQL語句相同的授權體系（簡稱專家模式）。本文為您介紹Hologres如何使用專家許可權模型對使用者授權及撤銷授權。

專家許可權模型授權

在Hologres執行個體串連開發工具後，可以使用SQL語句通過專家許可權模型授權，使該使用者具有執行個體的相關許可權。

建立使用者。

一個帳號必須先被建立成為Hologres的使用者，才能訪問Hologres並進行開發。

建立使用者的語句格式如下：

--建立具有登入Hologres執行個體許可權的使用者，如果是為RAM使用者授權，帳號格式請使用RAM使用者的表達格式。
CREATE USER "雲帳號ID/雲郵箱"; 
--建立使用者並授予Superuser許可權。
CREATE USER "雲帳號ID/雲郵箱" SUPERUSER;

您可以參照如下樣本建立使用者，其中更多關於阿里雲帳號和RAM使用者的格式說明，請參見帳號概述。

--使用阿里雲帳號ID建立使用者。
CREATE USER "11822780xxx";
--授予RAM使用者Superuser許可權。
CREATE USER "p4_1822780xxx" SUPERUSER;

更多關於建立角色的說明，請參見CREATE ROLE。

授予許可權。

將帳號建立為Hologres的使用者後，您需要授予使用者一定的許可權，該使用者才能在許可權範圍內使用Hologres。在專家許可權模型下可以控制使用者在資料庫、表、視圖、列層級的許可權。Hologres中常用的授權操作如下表所示。

說明

目前專家模式只能對現有執行個體對象進行授權，對授權後建立的內容不生效。例如，使用者A對使用者B授予了public schema中所有表的查看許可權。當使用者A建立一張新表，則使用者B不具有對這張表的查看許可權，需要重新授權。

許可權描述	文法樣本	是否必須
建立具有登入Hologres執行個體許可權的使用者	`CREATE USER "雲帳號/雲郵箱";`	是
建立使用者並授予使用者Superuser的許可權	`CREATE USER "雲帳號/雲郵箱" SUPERUSER ;`	可選
授予在Schema下建立表的許可權	`GRANT CREATE ON SCHEMA schema_name TO "雲帳號/雲郵箱";`	可選
授予Schema存取權限	`GRANT USAGE ON SCHEMA schema_name TO "雲帳號/雲郵箱";`	必須說明必須授予Schema的存取權限才能有表的查詢許可權。
授予所有使用者public schema中所有表的查看、寫入、及修改許可權	`GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA public to PUBLIC;`	可選
授予使用者某個表的SELECT許可權	`GRANT SELECT ON TABLE <tablename> TO "雲帳號/雲郵箱";`	可選
授予使用者某個表的SELECT許可權，並允許該使用者授予此許可權給其他使用者	`GRANT SELECT ON TABLE <tablename> TO "雲帳號/雲郵箱" WITH GRANT OPTION;`	可選
授予使用者public schema中所有表的SELECT許可權	`GRANT SELECT ON ALL TABLES IN SCHEMA public TO "雲帳號/雲郵箱";`	可選
設定當前授權人在`public`Schema下建立的未來表對所有人可讀。	`ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;`	可選
修改普通使用者為Superuser	`ALTER USER "雲帳號/雲郵箱" SUPERUSER;`	可選
修改Superuser為普通使用者	`ALTER USER "雲帳號/雲郵箱" NOSUPERUSER;`	可選
授予其他使用者表的Owner許可權	`ALTER TABLE <tablename> OWNER TO "雲帳號/雲郵箱";`	可選
建立沒有登入Hologres執行個體許可權的角色	`CREATE ROLE "雲帳號/雲郵箱";`	可選
授予某個使用者某個角色的許可權	`GRANT <rolename> TO "雲帳號/雲郵箱" ;`	可選
授予某個使用者某個表的某些列的查詢許可權	`GRANT SELECT (<column1>,<column2>,<column3>,...) ON TABLE <tablename> TO "雲帳號/雲郵箱" ;`	可選
授予某個使用者某個視圖的查詢許可權說明專家許可權模型下訪問視圖需要授予視圖的查詢許可權。 SPM/SLPM許可權模型下訪問視圖需要有viewer及以上使用者組的許可權。	`--專家許可權模型給某個使用者授予view的查詢許可權 GRANT SELECT ON <viewname> TO "雲帳號/雲郵箱" ;`	可選

在專家模型下，您可以參照以下樣本給一個新使用者授予某張表的查詢許可權。

CREATE USER "雲帳號/雲郵箱";
GRANT USAGE ON SCHEMA <schema_name>  TO "雲帳號/雲郵箱";
GRANT SELECT ON TABLE <tablename> TO "雲帳號/雲郵箱";

CREATE ROLE用於建立沒有登入Hologres執行個體許可權的角色，例如代表一類具體使用者的使用者組或虛擬角色等。更多關於許可權的授予的說明，請參見GRANT。

刪除表。
只有Superuser或表Owner才可以刪除表。您可以使用如下幾種方法授予某個使用者或多個使用者刪除表的許可權：
- 替換新使用者為表的Owner。
```
ALTER TABLE TABLENAME OWNER TO "雲帳號/雲郵箱";
```
- 授予新使用者Superuser許可權。
```
ALTER USER "雲帳號/雲郵箱" SUPERUSER;
```
- 添加多個使用者至使用者組並授予表Owner許可權。
```
CREATE USER "雲帳號ID/雲郵箱";
CREATE ROLE <rolename>;
GRANT <rolename> TO "雲帳號/雲郵箱";
ALTER TABLE <tablename> OWNER TO <rolename>;
```

未來表授權

由於專家模式授權不包含對未來表的授權，因此需要使用ALTER DEFAULT PRIVILEGES語句對未來表進行授權。具體操作步驟如下：

說明

該命令語句不影響已有的邏輯對象。
該命令語句只能設定TABLE、SCHEMA、FUNCTION、SEQUENCE或TYPE的預設許可權。

授權。

預設執行授權語句的人，在某個Schema下建立的表對所有人或指定人進行查詢。樣本語句如下。
- 執行授權的人在public schema下，建立的表可以被子帳號p4_123xxx查詢。
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "p4_123xxx";
```
- 執行授權的人在test schema下，建立的表可以被子帳號p4_123xxx查詢。
```
ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT SELECT ON TABLES TO "p4_123xxx";
```
- 執行授權的人在public schema下，建立的表可以被所有人查詢。
```
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
```
預設授權後，其他人建立表時可被指定所有人或指定人進行查詢。樣本語句如下。
- 授權後，p4_id1建立的表可以被public schema下的所有使用者訪問。
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO PUBLIC;
```
- 授權後，p4_id1建立的表可以被public schema下的子帳號p4_id2訪問。
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA public GRANT SELECT ON TABLES TO "p4_id2";
```
- 授權後，p4_id1建立的表可以被test schema下的所有使用者訪問。
```
ALTER DEFAULT PRIVILEGES FOR ROLE "p4_id1" IN SCHEMA test GRANT SELECT ON TABLES TO PUBLIC;
```

撤銷設定的預設授權，有的情境您需要撤銷您設定的預設授權，樣本語句如下。

--撤銷執行授權的人在public schema下未來建立的表可以被子帳號p4_123xxx查詢的預設授權
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM "p4_123xxx";

--撤銷執行授權的人在test schema下未來建立的表可以被子帳號p4_123xxx查詢的預設授權
ALTER DEFAULT PRIVILEGES IN SCHEMA test REVOKE SELECT ON TABLES FROM "p4_123xxx";

--撤銷執行授權的人在public schema下未來建立的表可以被所有人查詢的預設授權
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;

查看預設許可權是否設定成功。
- 使用\ddp命令在Psql用戶端查看ALTER DEFAULT PRIVILEGES是否設定成功。
- 使用如下SQL命令在Hologres中直接查詢。
```
SELECT pg_catalog.pg_get_userbyid(d.defaclrole) AS "Owner",
  n.nspname AS "Schema",
  CASE d.defaclobjtype WHEN 'r' THEN 'table' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'function' WHEN 'T' THEN 'type' WHEN 'n' THEN 'schema' END AS "Type",
  pg_catalog.array_to_string(d.defaclacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_default_acl d
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace
ORDER BY 1, 2, 3;
```
建立新表時，Hologres會使用目前使用者和模式去匹配系統資料表pg_catalog.pg_default_acl。如果檢查到匹配項ALTER DEFAULT PRIVILEGES，則為使用者添加匹配項規則。目前使用者說明如下：
- 如果目前使用者是User，則建立表時使用User進行匹配。
- 如果使用者User建立表之前執行了SET SESSION ROLE GROUP1;語句，此時目前使用者就變為GROUP1，則建立表時使用GROUP1進行匹配。
匹配規則只在建立表時執行，在建立表之後執行ALTER TABLE SET OWNER TO語句修改表Owner，不會觸發對應匹配項規則。

專家模式撤銷授權

使用REVOKE語句撤銷使用者權限的樣本如下，更多關於許可權的撤銷操作，請參見REVOKE。

REVOKE SELECT ON TABLE tablename FROM "雲帳號ID/雲郵箱" ; --如果是RAM使用者，帳號格式請使用RAM使用者的表達格式。

查看許可權

通過以下SQL命令查看使用者的角色及許可權。

SELECT ROLNAME FROM pg_roles;
SELECT user_display_name(ROLNAME) FROM pg_roles;

刪除使用者

若您的執行個體已經串連開發工具，您可以使用SQL語句進行刪除子帳號，分為如下兩種情況。

刪除普通使用者
如果是刪除普通使用者，並且該帳號沒有建立其他對象（如表、視圖、extension等），可以執行以下命令語句或者直接在HoloWeb刪除使用者。
```
drop user "雲帳號ID/雲郵箱";
```
刪除Superuser等管理員
若是要刪除Superuser、Admin等管理員，但是該帳號建立過表、視圖、extension等執行個體內對象，並且是這些對象的管理員（尤其是專家許可權模型下），若是直接刪除使用者會報錯，需要先將該帳號下的對象進行轉移，執行以下命令語句。
```
-- 將A帳號下的對象轉給B
reassign owned by "A雲帳號ID" to "B雲帳號ID";     
-- 刪除A帳號
drop user "A雲帳號ID";
```

您可以使用以下方式刪除執行個體中的RAM使用者：

DROP USER "雲帳號ID/雲郵箱";

重要

RAM使用者被刪除後，將不能串連執行個體並訪問執行個體內的任何對象，請您謹慎操作。

標準的PostgreSQL對於許可權有著非常嚴格的劃分，對此我們提供最佳實務供您根據業務需求選擇和參考，詳情請參見基於PostgreSQL標準許可權模型授權。