本文分享自華為雲社群《GuassDB資料庫的GRANT & REVOKE》，作者： Gauss松鼠會小助手2 。
一、GaussDB的許可權概述
在資料庫中，物件的建立者將成為該物件的所有者，具有對該物件進行查詢、修改和刪除等操作的許可權。同時，系統管理員也擁有與所有者相同的許可權。因此，如果要讓其他使用者能夠使用某個物件，必須向該使用者或包含該使用者的角色授予必要的許可權。
GaussDB資料庫物件許可權：
|
物件 |
許可權 |
說明 |
|
資料庫 DATABASE |
CONNECT |
允許使用者連線到指定的資料庫 |
|
CREATE |
允許在資料庫裡建立新的模式 |
|
|
模式 SCHEMA |
CREATE |
允許在模式中建立新的物件 |
|
USAGE |
允許訪問包含在指定模式中的物件，若沒有該許可權，則只能看到這些物件的名字 |
|
|
函式 FUNCTION |
EXECUTE |
允許使用指定的函式，以及利用這些函式實現的運算子 |
|
表空間 TABLESPACE |
CREATE |
允許在表空間中建立表，允許在建立資料庫和模式的時候把該表空間指定為預設表空間。 |
|
表 TABLE |
INSERT DELETE UPDATE SELECT |
允許使用者對指定表進行增刪改查操作 |
|
TRUNCATE |
允許執行TRUNCATE語句刪除指定表中的所有記錄。 |
|
|
REFERENCES |
建立一個外來鍵約束，必須擁有參考表和被參考表的REFERENCES許可權 |
要撤消已經授予的許可權，可以使用REVOKE。
物件所有者的許可權（例如ALTER、DROP、COMMENT、INDEX、VACUUM、GRANT和REVOKE）是隱式擁有的，即只要擁有物件就可以執行物件所有者的這些隱式許可權。物件所有者可以撤消自己的普通許可權。
系統表和系統檢視要麼只對系統管理員可見，要麼對所有使用者可見。標識了需要系統管理員許可權的系統表和檢視只有系統管理員可以查詢。
資料庫提供物件隔離的特性，物件隔離特性開啟時，使用者只能檢視有許可權訪問的物件(表、檢視、欄位、函式)，系統管理員不受影響。
不建議使用者修改系統表和系統檢視的許可權。
二、GaussDB許可權設計建議
在進行業務使用前，必須由系統管理員(root使用者)建立資料庫、模式(SCHEMA)和使用者(USER)。然後，需要為相關使用者授予訪問物件所需的許可權。如果該使用者不是該模式的所有者，則要訪問該模式下的物件，還需要同時向該使用者授予模式的usage許可權和物件的相應許可權。
DATABASE、SCHEMA和USER名使用小寫。資料庫會預設把其名轉為小寫，連線串裡面如果出現大寫的物件名無法連線到資料庫。
對角色和使用者賦權時，應使用最小化許可權原則。
優先透過角色來管理許可權。使用角色管理許可權，再將角色賦予使用者。例如：
- 角色和使用者為多對多關係，一個角色可以賦予多個使用者，修改角色中的許可權，被賦予角色的使用者許可權就可以同時更新。
- 刪除使用者時，不會影響到角色。
- 新建使用者後可以透過賦予角色快速獲取所需許可權。
在刪除指定資料庫時，應回收使用者對該資料庫的CONNECT許可權，避免刪除時仍然存在活躍的資料庫連線而失敗。
三、GaussDB的GRANT命令
1.功能說明
1）將系統許可權授權給角色或使用者
系統許可權又稱為使用者屬性，包括SYSADMIN、CREATEDB、CREATEROLE、AUDITADMIN、MONADMIN、OPRADMIN、POLADMIN、INHERIT、REPLICATION、VCADMIN和LOGIN等。
系統許可權一般透過CREATE/ALTER ROLE語法來指定。其中，SYSADMIN許可權可以透過GRANT/REVOKE ALL PRIVILEGE授予或撤銷。但系統許可權無法透過ROLE和USER的許可權被繼承，也無法授予PUBLIC。
2）將資料庫物件授權給角色或使用者
將資料庫物件（表和檢視、指定欄位、資料庫、函式、模式、表空間等）的相關許可權授予特定角色或使用者；
GRANT命令將資料庫物件的特定許可權授予一個或多個角色。這些許可權會追加到已有的許可權上。
關鍵字PUBLIC表示該許可權要賦予所有角色，包括以後建立的使用者。PUBLIC可以看做是一個隱含定義好的組，它總是包括所有角色。任何角色或使用者都將擁有透過GRANT直接賦予的許可權和所屬的許可權，再加上PUBLIC的許可權。
如果宣告瞭WITH GRANT OPTION，則被授權的使用者也可以將此許可權賦予他人。這個選項不能賦予PUBLIC（GaussDB特有的屬性）。
GaussDB會將某些型別的物件上的許可權授予PUBLIC。預設情況下，對錶、表欄位、序列、外部資料來源、外部伺服器、模式或表空間物件的許可權不會授予PUBLIC，而以下這些物件的許可權會授予PUBLIC：資料庫的CONNECT許可權和CREATE TEMP TABLE許可權、函式的EXECUTE特權、語言和資料型別（包括域）的USAGE特權。當然，物件擁有者可以撤銷預設授予PUBLIC的許可權並專門授予許可權給其他使用者。為了更安全，建議在同一個事務中建立物件並設定許可權，這樣其他使用者就沒有時間視窗使用該物件。
物件的所有者預設具有該物件上的所有許可權，出於安全考慮所有者可以捨棄部分許可權，但ALTER、DROP、COMMENT、INDEX、VACUUM以及物件的可再授予許可權屬於所有者固有的許可權，隱式擁有。
3）將角色或使用者的許可權授權給其他角色或使用者
將一個角色或使用者的許可權授予一個或多個其他角色或使用者。在這種情況下，每個角色或使用者都可視為擁有一個或多個資料庫許可權的集合。
如果宣告瞭WITH ADMIN OPTION，被授權的使用者可以將該許可權再次授予其他角色或使用者，以及撤銷所有由該角色或使用者繼承到的許可權。當授權的角色或使用者發生變更或被撤銷時，所有繼承該角色或使用者許可權的使用者擁有的許可權都會隨之發生變更。
資料庫系統管理員可以給任何角色或使用者授予/撤銷任何許可權。擁有CREATEROLE許可權的角色可以賦予或者撤銷任何非系統管理員角色的許可權。
4）將ANY許可權授予給角色或使用者
將ANY許可權授予特定的角色和使用者。當宣告瞭WITH ADMIN OPTION，被授權的使用者可以將該ANY許可權再次授予其他角色/使用者，或從其他角色/使用者處回收該ANY許可權。ANY許可權可以透過角色被繼承，但不能賦予PUBLIC。初始使用者和三權分立關閉時的系統管理員使用者可以給任何角色/使用者授予或撤銷ANY許可權。
目前支援以下ANY許可權：
|
CREATE ANY TABLE |
CREATE ANY SEQUENCE |
|
ALTER ANY TABLE |
CREATE ANY INDEX |
|
DROP ANY TABLE |
CREATE ANY FUNCTION |
|
SELECT ANY TABLE |
EXECUTE ANY FUNCTION |
|
INSERT ANY TABLE |
CREATE ANY PACKAGE |
|
UPDATE ANY TABLE |
EXECUTE ANY PACKAGE |
|
DELETE ANY TABLE |
CREATE ANY TYPE |
2.注意事項
1）不允許將ANY許可權授予PUBLIC，也不允許從PUBLIC回收ANY許可權。
2）ANY許可權屬於資料庫內的許可權，只對授予該許可權的資料庫內的物件有效，例如SELECT ANY TABLE只允許使用者檢視當前資料庫內的所有使用者表資料，對其他資料庫內的使用者表無檢視許可權。
3）即使使用者被授予ANY許可權，也不能對私有使用者下的物件進行訪問操作（INSERT、DELETE、UPDATE、SELECT）。
4）ANY許可權與原有的許可權相互無影響。
5）如果使用者被授予CREATE ANY TABLE許可權，在同名schema下建立表的屬主是該schema的建立者，使用者對錶進行其他操作時，需要授予相應的操作許可權。
6）需要謹慎授予使用者CREATE ANY FUNMCTION的許可權，以免其他使用者利用SECURITY DEFINER型別的函式進行許可權提升。
3.常用語法
1）將表或檢視的訪問許可權賦予指定的使用者或角色
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...]
| ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
2）將表中欄位的訪問許可權賦予指定的使用者或角色
GRANT { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )} [, ...]
| ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
3)將資料庫的訪問許可權賦予指定的使用者或角色
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...]
| ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
4）將函式的訪問許可權賦予給指定的使用者或角色
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
5）將儲存過程的訪問許可權賦予給指定的使用者或角色
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] }
ON { PROCEDURE {proc_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ];
……
四、GaussDB的REVOKE命令用法
1.功能說明
REVOKE用於撤銷一個或多個使用者或角色的許可權。
2.注意事項
非物件所有者REVOKE許可權時，按照以下規則執行：
1）如果授權使用者沒有該物件上的許可權，則命令立即失敗。
2）如果授權使用者有部分許可權，則只撤銷那些有授權選項的許可權。
3）如果授權使用者沒有授權選項，REVOKE ALL PRIVILEGES形式將發出一個錯誤資訊，而對於其他形式的命令而言，如果是命令中指定名稱的許可權沒有相應的授權選項，該命令將發出一個警告。
3.常用語法
1）回收指定表或檢視上的許可權
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM }[, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ];
2）回收表上指定欄位的許可權
REVOKE [ GRANT OPTION FOR ] { {{ SELECT | INSERT | UPDATE | REFERENCES | COMMENT } ( column_name [, ...] )}[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) } ON [ TABLE ] table_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ];
3）回收指定資料庫上的許可權
REVOKE [ GRANT OPTION FOR ] { { CREATE | CONNECT | TEMPORARY | TEMP | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON DATABASE database_name [, ...] FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ];
4）回收指定函式上的許可權
REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON { FUNCTION {function_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ];
5）回收指定儲存過程上的許可權
REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON { PROCEDURE {proc_name ( [ {[ argmode ] [ arg_name ] arg_type} [, ...] ] )} [, ...] | ALL PROCEDURE IN SCHEMA schema_name [, ...] } FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ];
……
五、GaussDB示例
1.GRANT 語句示例
1）授予使用者 user_name 對 database_name.table_name 表的 SELECT、INSERT、UPDATE、DELETE 許可權。
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO user_name;
2）授予使用者 user_name 對 database_name.table_name 的所有許可權。
GRANT ALL PRIVILEGES ON database_name.table_name TO user_name;
3）授予使用者 user_name 對 database_name.table_name 的 SELECT、INSERT、UPDATE、DELETE 許可權，並允許他將該許可權傳遞給其他使用者。
GRANT SELECT,INSERT,UPDATE,DELETE ON database_name.table_name TO user_name WITH GRANT OPTION;
2.REVOKE 語句示例：
1）撤銷使用者 user_name 對 database_name.table_name 表的 SELECT、INSERT、UPDATE、DELETE 許可權。
REVOKE SELECT,INSERT,UPDATE,DELETE on database_name.table_name FROM user_name;
2）撤銷使用者 user_name 對 database_name.table_name 的所有許可權。
REVOKE ALL PRIVILEGES ON database_name.table_name FROM user_name;
3）撤銷使用者 user_name 對 database_name.table_name 表的 SELECT、INSERT、UPDATE、DELETE 許可權。
Tip:一個使用者只能撤銷由它自己直接賦予的許可權，依賴性許可權仍然存在，但如果宣告瞭CASCADE，則所有依賴性許可權都被撤銷.
REVOKE SELECT,INSERT,UPDATE,DELETE ON database_name.table_name FROM user_name WITH GRANT OPTION;
小結：資料庫的GRANT & REVOKE命令是用於管理資料庫使用者許可權的命令。這些命令通常用於在資料庫中為使用者分配許可權，以便使用者可以訪問和運算元據庫中的資料。GRANT & REVOKE是GaussDB雲資料庫中非常重要的一個命令，它可以用於撤銷和管理資料庫中的不同物件的訪問許可權，從而保證資料庫的安全性和可靠性。