ALTER DEFAULT PRIVILEGES
功能描述
ALTER DEFAULT PRIVILEGES语句用于修改数据库中用户在特定对象上默认拥有的权限，不会影响到分配已有对象中的权限。
注意事项
目前只支持表（包括视图）、序列、函数，类型，密态数据库客户端主密钥和列加密密钥的权限更改。
语法格式
| ``` ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ] [ IN SCHEMA schema_name [, ...] ] abbreviated_grant_or_revoke;
| -------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中abbreviated_grant_or_revoke子句用于指定对哪些对象进行授权或回收权限。
| ```
grant_on_tables_clause | grant_on_sequences_clause | grant_on_functions_clause | grant_on_types_clause | grant_on_client_master_keys_clause | grant_on_column_encryption_keys_clause | revoke_on_tables_clause | revoke_on_sequences_clause | revoke_on_functions_clause | revoke_on_types_clause | revoke_on_client_master_keys_clause | revoke_on_column_encryption_keys_clause
``` |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
<!---->
- 其中grant_on_tables_clause子句用于对表授权。
| ```
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] } ON TABLES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中grant_on_sequences_clause子句用于对序列授权。
| ```
GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON SEQUENCES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中grant_on_functions_clause子句用于对函数授权。
| ```
GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON FUNCTIONS TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中grant_on_types_clause子句用于对类型授权。
| ```
GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TYPES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中grant_on_client_master_keys_clause子句用于对客户端主密钥授权。
```
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
ON CLIENT_MASTER_KEYS
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ]
```
- 其中grant_on_column_encryption_keys_clause子句用于对列加密密钥授权。
```
GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
ON COLUMN_ENCRYPTION_KEYS
TO { [ GROUP ] role_name | PUBLIC } [, ...]
[ WITH GRANT OPTION ]
```
- 其中revoke_on_tables_clause子句用于回收表对象的权限。
| ```
REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ALTER | DROP | COMMENT | INDEX | VACUUM } [, ...] | ALL [ PRIVILEGES ] } ON TABLES FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
``` |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中revoke_on_sequences_clause子句用于回收序列的权限。
| ```
REVOKE [ GRANT OPTION FOR ] { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON SEQUENCES FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中revoke_on_functions_clause子句用于回收函数的权限。
| ```
REVOKE [ GRANT OPTION FOR ] { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON FUNCTIONS FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
- 其中revoke_on_types_clause子句用于回收类型的权限。
| ```
REVOKE [ GRANT OPTION FOR ] { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TYPES FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
``` |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
<!---->
- 其中revoke_on_client_master_keys_clause子句用于回收客户端主密钥的权限。
```
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
ON CLIENT_MASTER_KEYS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
```
- 其中revoke_on_column_encryption_keys_clause子句用于回收列加密密钥的权限。
```
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
ON COLUMN_ENCRYPTION_KEYS
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
```
#### 参数说明
- **target_role**
已有角色的名称。如果省略FOR ROLE/USER，则缺省值为当前角色/用户。
取值范围：已有角色的名称。
- **schema_name**
现有模式的名称。
target_role必须有schema_name的CREATE权限。
取值范围：现有模式的名称。
- **role_name**
被授予或者取消权限角色的名称。
取值范围：已存在的角色名称。
NOTICE:
如果想删除一个被赋予了默认权限的角色，有必要恢复改变的缺省权限或者使用DROP OWNED BY来为角色脱离缺省的权限记录。
#### 示例
| ```
--创建SCHEMA。 gaussdb=# CREATE SCHEMA tpcds; --将创建在模式tpcds里的所有表（和视图）的SELECT权限授予每一个用户。 gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT SELECT ON TABLES TO PUBLIC; --创建用户普通用户jack。 gaussdb=# CREATE USER jack PASSWORD '******'; --将tpcds下的所有表的插入权限授予用户jack。 gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack; --将tpcds下由jack创建的所有表的插入权限授予用户jack。 gaussdb=# GRANT USAGE,CREATE ON SCHEMA tpcds TO jack; gaussdb=# ALTER DEFAULT PRIVILEGES FOR ROLE jack IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack; --撤销上述权限。 gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE SELECT ON TABLES FROM PUBLIC; gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE INSERT ON TABLES FROM jack; --删除用户jack。 gaussdb=# DROP USER jack CASCADE; --删除SCHEMA。 gaussdb=# DROP SCHEMA tpcds;
``` |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
#### 相关链接
[GRANT](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0639.html#ZH-CN_TOPIC_0000001911585305)，[REVOKE](https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/devg-dist/gaussdb-12-0668.html#ZH-CN_TOPIC_0000001911585905)
更多详情请参考GaussDB 文档中心：<https://doc.hcs.huawei.com/db/zh-cn/gaussdbqlh/24.1.30/productdesc/qlh_03_0001.html>
