# 对象授权/取消授权管理 #### 对象支持的权限类型 - 对象支持的权限列表 | 对象类型 | READ | WRITE | DESCRIBE | EXECUTE | INSERT | SELECT | UPDATE | DELETE | ALTER | DROP | |:---|:---|:---|:---|:---|:---|:---|:---|:---|:---|:---| | DATABASE | ✓ | ✓ | ✓ | - | - | - | - | - | ✓ | ✓ | | SCHEMA | ✓ | ✓ | ✓ | - | - | - | - | - | ✓ | ✓ | | TABLE | - | - | ✓ | - | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | | FUNCTION | - | - | ✓ | ✓ | - | - | - | - | ✓ | ✓ | | PATH | ✓ | ✓ | - | - | - | - | - | - | - | - | - 对象授权支持的权限类型 | **Aura** **对象** | **Aura权限** | **LakeFormation** **对象** | **LakeFormation** **权限** | 说明 | |:---|:---|:---|:---|:---| | DATABASE | ALL | CATALOG | ALL | Database对象的所有权限。 | | DATABASE | READ | CATALOG | LIST_DATABASE | 查询Database中的Schema。 | | DATABASE | WRITE | CATALOG | CREATE_DATABASE | 在Database中创建Schema。 | | DATABASE | DESCRIBE | CATALOG | DESCRIBE | 查询Database元数据信息。 | | DATABASE | ALTER | CATALOG | ALTER | 修改Database权限，暂不支持。 | | DATABASE | DROP | CATALOG | DROP | 删除Database权限，暂不支持。 | | SCHEMA | ALL | DATABASE | ALL | Schema对象的所有权限。 | | SCHEMA | READ | DATABASE | LIST_TABLE LIST_FUNC | 查询Schema中的表。 查询Schema中的Function。 | | SCHEMA | WRITE | DATABASE | CREATE_TABLE CREATE_FUNC | 在Schema中创建表。 在Schema中创建Function。 | | SCHEMA | DESCRIBE | DATABASE | DESCRIBE | 查询Schema元数据信息。 | | SCHEMA | ALTER | DATABASE | ALTER | 修改Schema权限。 | | SCHEMA | DROP | DATABASE | DROP | 删除Schema权限。 | | TABLE | ALL | TABLE | ALL | 表对象的所有权限。 | | TABLE | DESCRIBE | TABLE | DESCRIBE | 查询表元数据信息。 | | TABLE | INSERT | TABLE | INSERT | 表数据导入权限。 | | TABLE | SELECT | TABLE | SELECT | 表数据查询权限。 | | TABLE | UPDATE | TABLE | UPDATE | 表数据更新权限。 | | TABLE | DELETE | TABLE | DELETE | 表数据删除权限。 | | TABLE | ALTER | TABLE | ALTER | 表修改权限。 | | TABLE | DROP | TABLE | DROP | 表删除权限。 | | FUNCTION | ALL | FUNCTION | ALL | Function对象的所有权限。 | | FUNCTION | DESCRIBE | FUNCTION | DESCRIBE | 查询Function元数据信息。 | | FUNCTION | EXECUTE | FUNCTION | EXEC | Function执行权限。 | | FUNCTION | ALTER | FUNCTION | ALTER | Function修改权限。 | | FUNCTION | DROP | FUNCTION | DROP | Function删除权限； | | PATH | READ | URI | READ | OBS路径读权限。 | | PATH | WRITE | URI | WRITE | OBS路径写权限。 | #### 约束与限制 对象授权使用约束 - **代理授权限制**：无法直接给代理对象进行赋权操作。 - **批量授权限制**：过多授权可能引发LakeFormation对OBS路径的联动授权问题。 - **列级权限** **限制**：暂不支持表的列级别权限管理（后续版本支持）。 - **Owner限制**：仅支持修改表的Owner。 #### 对象授权管理 - 对象授权语法 ``` GRANT privileges [ WITH GRANT ] ON privilege_target TO grantee_list; ``` **WITH GRANT**表示级联授权，被授权的用户可以给其他用户授予相同的权限。 其中： - **privileges** 可以为： ``` | [ READ, WRITE, DESCRIBE, EXECUTE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE ] [, …] | ALL | ALL PRIVILEGE | ALL PRIVILEGES ``` - **privilege_target** 可以为： ``` | DATABASE name_list | SCHEMA name_list | TABLE qualified_name_list | FUNCTION function_with_argtypes_list | PATH obs_path_list | ALL TABLES IN SCHEMA name_list | ALL FUNCTIONS IN SCHEMA name_list ``` 其中： - name_list为： ``` | name [, …] ``` - qualified_name_list为： ``` | qualified_name [, …] ``` - function_with_argtypes_list为： ``` | function_with_argtypes [, …] ``` - obs_path_list为： ``` | SCONST [, …] ``` - **grantee_list** 可以为： ``` | USER RoleId [, …] | USER GROUP RoleId [, …] | ROLE RoleId [, …] ``` 被授权对象类型支持**：** - USER *username* - USER GROUP *groupname* - ROLE *rolename* - DATABASE对象授权 - 授予用户数据库相关权限 ``` GRANT READ [ WITH GRANT ] ON DATABASE database1 TO USER user1; GRANT WRITE [ WITH GRANT ] ON DATABASE database1 TO USER user1; GRANT DESCRIBE [ WITH GRANT ] ON DATABASE database1 TO USER user1; GRANT ALTER [ WITH GRANT ] ON DATABASE database1 TO USER user1; GRANT DROP [ WITH GRANT ] ON DATABASE database1 TO USER user1; GRANT ALL [ WITH GRANT ] ON DATABASE database1 TO USER user1; ``` - 授予用户组数据库相关权限 ``` GRANT READ [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; GRANT WRITE [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; GRANT DESCRIBE [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; GRANT ALTER [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; GRANT DROP [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; GRANT ALL [ WITH GRANT ] ON DATABASE database1 TO USER GROUP group1; ``` - 授予角色数据库相关权限 ``` GRANT READ [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; GRANT WRITE [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; GRANT DESCRIBE [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; GRANT ALTER [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; GRANT DROP [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; GRANT ALL [ WITH GRANT ] ON DATABASE database1 TO ROLE role1; ``` - SCHEMA对象授权 - 授予用户SCHEMA相关权限 ``` GRANT READ [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; GRANT WRITE [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; GRANT DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; GRANT ALTER [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; GRANT DROP [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; GRANT ALL [ WITH GRANT ] ON SCHEMA schema1 TO USER user1; ``` - 授予用户组SCHEMA相关权限 ``` GRANT READ [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; GRANT WRITE [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; GRANT DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; GRANT ALTER [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; GRANT DROP [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; GRANT ALL [ WITH GRANT ] ON SCHEMA schema1 TO USER GROUP group1; ``` - 授予角色SCHEMA相关权限 ``` GRANT READ [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; GRANT WRITE [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; GRANT DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; GRANT ALTER [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; GRANT DROP [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; GRANT ALL [ WITH GRANT ] ON SCHEMA schema1 TO ROLE role1; ``` - TABLE对象授权 - 授予用户TABLE相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT INSERT [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT SELECT [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT UPDATE [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT DELETE [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT ALTER [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT DROP [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; GRANT ALL [ WITH GRANT ] ON TABLE s1.table1 TO USER user1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 TO USER user1; ``` - 授予用户组TABLE相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT INSERT [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT SELECT [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT UPDATE [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT DELETE [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT ALTER [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT DROP [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; GRANT ALL [ WITH GRANT ] ON TABLE s1.table1 TO USER GROUP group1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 TO USER GROUP group1; ``` - 授予角色TABLE相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT INSERT [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT SELECT [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT UPDATE [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT DELETE [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT ALTER [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT DROP [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; GRANT ALL [ WITH GRANT ] ON TABLE s1.table1 TO ROLE role1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 TO ROLE role1; ``` - FUNCTION对象授权 - 授予用户FUNCTION相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER user1; GRANT EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER user1; GRANT ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER user1; GRANT DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER user1; GRANT ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER user1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 TO USER user1; ``` - 授予用户组FUNCTION相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER GROUP group1; GRANT EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER GROUP group1; GRANT ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER GROUP group1; GRANT DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER GROUP group1; GRANT ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO USER GROUP group1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 TO USER GROUP group1; ``` - 授予角色FUNCTION相关权限 ``` GRANT DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO ROLE role1; GRANT EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO ROLE role1; GRANT ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO ROLE role1; GRANT DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO ROLE role1; GRANT ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) TO ROLE role1; ``` 还支持以下特殊授权操作： ``` GRANT DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 TO ROLE role1; ``` - PATH对象授权 - 授予用户PATH相关权限 ``` GRANT READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER user1; GRANT WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER user1; GRANT ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER user1; ``` - 授予用户组PATH相关权限 ``` GRANT READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER GROUP group1; GRANT WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER GROUP group1; GRANT ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO USER GROUP group1; ``` - 授予角色PATH相关权限 ``` GRANT READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO ROLE role1; GRANT WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO ROLE role1; GRANT ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ TO ROLE role1; ``` #### 取消授权 - 取消授权语法 ``` REVOKE privileges [ WITH GRANT ] ON privilege_target FROM grantee_list; ``` **WITH GRANT**表示级联授权，被授权的用户，可以给其它用户授予相同权限。 其中： - **privileges** 可以为： ``` | [ READ, WRITE, DESCRIBE, EXECUTE, ALTER, DROP, INSERT, SELECT, UPDATE, DELETE ] [, …] | ALL | ALL PRIVILEGE | ALL PRIVILEGES ``` - **privilege_target** 可以为： ``` | DATABASE name_list | SCHEMA name_list | TABLE qualified_name_list | FUNCTION function_with_argtypes_list | PATH obs_path_list | ALL TABLES IN SCHEMA name_list | ALL FUNCTIONS IN SCHEMA name_list ``` 其中： - name_list为： ``` | name [, …] ``` - qualified_name_list为： ``` | qualified_name [, …] ``` - function_with_argtypes_list为： ``` | function_with_argtypes [, …] ``` - obs_path_list为： ``` | SCONST [, …] ``` - **grantee_list** 可以为： ``` | USER RoleId [, …] | USER GROUP RoleId [, …] | ROLE RoleId [, …] ``` - DATABASE对象取消授权 - 取消用户DATABASE相关权限 ``` REVOKE READ [ WITH GRANT ] ON DATABASE database1 FROM USER user1; REVOKE WRITE [ WITH GRANT ] ON DATABASE database1 FROM USER user1; REVOKE DESCRIBE [ WITH GRANT ] ON DATABASE database1 FROM USER user1; REVOKE ALTER [ WITH GRANT ] ON DATABASE database1 FROM USER user1; REVOKE DROP [ WITH GRANT ] ON DATABASE database1 FROM USER user1; REVOKE ALL [ WITH GRANT ] ON DATABASE database1 FROM USER user1; ``` - 取消用户组DATABASE相关权限 ``` REVOKE READ [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; REVOKE WRITE [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; REVOKE DESCRIBE [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; REVOKE ALTER [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; REVOKE DROP [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; REVOKE ALL [ WITH GRANT ] ON DATABASE database1 FROM USER GROUP group1; ``` - 取消角色DATABASE相关权限 ``` REVOKE READ [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; REVOKE WRITE [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; REVOKE DESCRIBE [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; REVOKE ALTER [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; REVOKE DROP [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; REVOKE ALL [ WITH GRANT ] ON DATABASE database1 FROM ROLE role1; ``` - SCHEMA对象取消授权 - 取消用户SCHEMA相关权限 ``` REVOKE READ [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; REVOKE WRITE [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; REVOKE DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; REVOKE ALTER [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; REVOKE DROP [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; REVOKE ALL [ WITH GRANT ] ON SCHEMA schema1 FROM USER user1; ``` - 取消用户组SCHEMA相关权限 ``` REVOKE READ [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; REVOKE WRITE [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; REVOKE DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; REVOKE ALTER [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; REVOKE DROP [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; REVOKE ALL [ WITH GRANT ] ON SCHEMA schema1 FROM USER GROUP group1; ``` - 取消角色SCHEMA相关权限 ``` REVOKE READ [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; REVOKE WRITE [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; REVOKE DESCRIBE [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; REVOKE ALTER [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; REVOKE DROP [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; REVOKE ALL [ WITH GRANT ] ON SCHEMA schema1 FROM ROLE role1; ``` - TABLE对象取消授权 - 取消用户TABLE相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE INSERT [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE SELECT [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE UPDATE [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE DELETE [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE ALTER [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE DROP [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; REVOKE ALL [ WITH GRANT ] ON TABLE s1.table1 FROM USER user1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 FROM USER user1; ``` - 取消用户组TABLE相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE INSERT [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE SELECT [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE UPDATE [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE DELETE [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE ALTER [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE DROP [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; REVOKE ALL [ WITH GRANT ] ON TABLE s1.table1 FROM USER GROUP group1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 FROM USER GROUP group1; ``` - 取消角色TABLE相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE INSERT [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE SELECT [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE UPDATE [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE DELETE [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE ALTER [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE DROP [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; REVOKE ALL [ WITH GRANT ] ON TABLE s1.table1 FROM ROLE role1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,INSERT,SELECT,UPDATE,DELETE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL TABLES IN SCHEMA schema1 FROM ROLE role1; ``` - FUNCTION对象取消授权 - 取消用户FUNCTION相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER user1; REVOKE EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER user1; REVOKE ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER user1; REVOKE DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER user1; REVOKE ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER user1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 FROM USER user1; ``` - 取消用户组FUNCTION相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER GROUP group1; REVOKE EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER GROUP group1; REVOKE ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER GROUP group1; REVOKE DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER GROUP group1; REVOKE ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM USER GROUP group1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 FROM USER GROUP group1; ``` - 取消角色FUNCTION相关权限 ``` REVOKE DESCRIBE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM ROLE role1; REVOKE EXECUTE [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM ROLE role1; REVOKE ALTER [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM ROLE role1; REVOKE DROP [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM ROLE role1; REVOKE ALL [ WITH GRANT ] ON FUNCTION func1(s1 int[]) FROM ROLE role1; ``` 还支持以下特殊操作： ``` REVOKE DESCRIBE,EXECUTE,ALTER,DROP,ALL [ WITH GRANT ] ON ALL FUNCTIONS IN SCHEMA schema1 FROM ROLE role1; ``` - PATH对象取消授权 - 取消用户PATH相关权限 ``` REVOKE READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER user1; REVOKE WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER user1; REVOKE ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER user1; ``` - 取消用户组PATH相关权限 ``` REVOKE READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER GROUP group1; REVOKE WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER GROUP group1; REVOKE ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM USER GROUP group1; ``` - 取消角色PATH相关权限 ``` REVOKE READ [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM ROLE role1; REVOKE WRITE [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM ROLE role1; REVOKE ALL [ WITH GRANT ] ON PATH ‘obs://bucket/path1’ FROM ROLE role1; ``` #### 对象Owner管理 - 修改表Owner ``` ALTER TABLE table_name OWNER TO USER username; ALTER TABLE table_name OWNER TO USER GROUP groupname; ``` - 查询对象授权 - 查询用户授权 ``` SELECT * FROM pg_show_grants('USER', 'username'); ``` - 查询用户组授权 ``` SELECT * FROM pg_show_grants('GROUP', 'groupname'); ``` - 查询角色授权 ``` SELECT * FROM pg_show_grants('ROLE', 'rolename'); ``` - 查询数据库授权 ``` SELECT * FROM pg_show_grants('DATABASE', 'databasename'); ``` - 查询表授权 ``` SELECT * FROM pg_show_grants('TABLE', 'schemaname.tablename'); ``` - 查询函数授权 ``` SELECT * FROM pg_show_grants('FUNCTION', 'functionname'); ``` #### 授权/取消授权示例 - 表授权 ``` GRANT SELECT, INSERT ON TABLE schema1.table1 TO USER user1; GRANT ALL ON TABLE schema1.table1 TO ROLE role1; ``` - Schema授权 ``` GRANT READ ON SCHEMA schema1 TO USER GROUP group1; ``` - 数据库授权 ``` GRANT DESCRIBE ON DATABASE database1 TO USER user1; ``` - 函数授权 ``` GRANT EXECUTE ON FUNCTION func1(int[]) TO USER user1; ``` - OBS路径授权 ``` GRANT READ ON PATH 'obs://bucket/path1' TO ROLE role1; ``` - 批量授权 ``` GRANT ALL ON ALL TABLES IN SCHEMA schema1 TO USER user1; GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema1 TO ROLE role1; ``` - 级联授权（被授权用户可转授权其他用户） ``` GRANT SELECT ON TABLE t1 TO USER user1 WITH GRANT; ``` - 取消授权 ``` REVOKE SELECT ON TABLE schema1.table1 FROM USER user1; REVOKE ALL ON ALL TABLES IN SCHEMA schema1 FROM ROLE role1; ```