详解GuassDB数据库权限命令：GRANT和REVOKE

2023-10-26 10:14 由华为云开发者联盟发表于 #数据库

本文分享自华为云社区《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云数据库中非常重要的一个命令，它可以用于撤销和管理数据库中的不同对象的访问权限，从而保证数据库的安全性和可靠性。

点击关注，第一时间了解华为云新鲜技术~