GRANT

使用 GRANT 命令向用户授予特定的权限。

句法

向用户授予数据库权限。

GRANT {{CONNECT | CREATE}[, ...]| ALL [PRIVILEGES]} 
ON DATABASE database_name [, ...]
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

向用户授予 schema 权限。

GRANT {CREATE | ALL [PRIVILEGES]} 
ON SCHEMA schema_name [, ...]
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

向用户授予表权限。

GRANT {{CREATE | SELECT | UPDATE | INSERT | DELETE} [, ...]| ALL [PRIVILEGES]} 
ON { TABLE table_name [, ...]
    | ALL TABLES IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

对于 source 和物化视图，只能分配和撤销 SELECT 权限。

向用户授予 source 权限。

GRANT { SELECT | ALL [PRIVILEGES]} 
ON {  source_or_table_name [, ...]
    | ALL SOURCES IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

向用户授予物化视图权限。

GRANT {SELECT | ALL [PRIVILEGES]} 
ON {MATERIALIZED VIEW mv_name [, ...] 
    | ALL MATERIALIZED VIEWS IN SCHEMA schema_name [, ...] }
TO user_name [WITH GRANT OPTION] [GRANTED BY user_name];

参数

参数或子句	描述
WITH GRANT OPTION 子句	WITH GRANT OPTION 子句允许被授予权限的用户将权限授予其他用户。
GRANTED BY 子句	GRANTED BY 子句后面指定的用户必须是当前用户。默认情况下，当前用户是 root。

示例

授予用户 user1 对 schema1 中所有 source 的所有权限。

GRANT ALL PRIVILEGES 
ON ALL SOURCES IN SCHEMA schema1 
TO user1 GRANTED BY user;

将位于数据库 db1 的 schema1 中的物化视图 mv1 的 SELECT 权限授予用户 user1。user1 能够将 SELECT 权限授予其他用户。

GRANT SELECT
ON MATERIALIZED VIEW mv1 IN SCHEMA db1.schema1
TO user1 WITH GRANT OPTION GRANTED BY user;

将用户 user1 的 SELECT 权限授予 source s1。

GRANT SELECT
ON SOURCE s1
TO user1;