ALTER DEFAULT PRIVILEGES

Description

ALTER DEFAULT PRIVILEGES is used to modify the default permissions of a user on a specific object in the database. It does not affect the permissions assigned to existing objects.

Precautions

Currently, you can change only the permissions for tables (including views), sequences, functions, types, client master keys of encrypted databases, and column encryption keys.

Syntax

1
2
3
4

ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke;

• abbreviated_grant_or_revoke grants or revokes permissions on some objects.

  1 2 3 4 5 6 7 8 9 10 11 12

  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 grants permissions on tables.

  1 2 3 4 5

  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 grants permissions on sequences.

  1 2 3 4 5

  GRANT { { SELECT | UPDATE | USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON SEQUENCES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

• grant_on_functions_clause grants permissions on functions.

  1 2 3 4

  GRANT { { EXECUTE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON FUNCTIONS TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

• grant_on_types_clause grants permissions on types.

  1 2 3 4

  GRANT { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TYPES TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

• grant_on_client_master_keys_clause grants permissions on CMKs.

  GRANT { { USAGE  | DROP } [, ...] | ALL [ PRIVILEGES ] }
      ON CLIENT_MASTER_KEYS
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ]

• grant_on_column_encryption_keys_clause grants permissions on column encryption keys.

  GRANT { { USAGE | DROP } [, ...] | ALL [ PRIVILEGES ] }
      ON COLUMN_ENCRYPTION_KEYS 
      TO { [ GROUP ] role_name | PUBLIC } [, ...]
      [ WITH GRANT OPTION ]

• revoke_on_tables_clause revokes permissions on tables.

  1 2 3 4 5 6

  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 revokes permissions on sequences.

  1 2 3 4 5 6

  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 revokes permissions on functions.

  1 2 3 4 5

  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 revokes permissions on types.

  1 2 3 4 5

  REVOKE [ GRANT OPTION FOR ] { { USAGE | ALTER | DROP | COMMENT } [, ...] | ALL [ PRIVILEGES ] } ON TYPES FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]

Parameters

• target_role

  Specifies the name of an existing role. If FOR ROLE/USER is omitted, the current role is assumed.

  Value range: an existing role name

• schema_name

  Specifies the name of an existing schema.

  target_role must have the CREATE permission for schema_name.

  Value range: an existing schema name

• role_name

  Specifies the name of an existing role to grant or revoke permissions for.

  Value range: an existing role name

To drop a role for which the default permissions have been granted, reverse the changes in its default permissions or use DROP OWNED BY to get rid of the default permission entry for the role.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

-- Create a schema.
gaussdb=# CREATE SCHEMA tpcds;

-- Grant the SELECT permission on all the tables (and views) in tpcds to every user.
gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT SELECT ON TABLES TO PUBLIC;

-- Create a common user jack.
gaussdb=# CREATE USER jack PASSWORD '******';

-- Grant the INSERT permission on all the tables in tpcds to the user jack.
gaussdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT INSERT ON TABLES TO jack;

-- Grant the INSERT permission on all the tables created by jack in tpcds to the user 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;

-- Revoke the preceding permissions.
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;

-- Delete user jack.
gaussdb=# DROP USER jack CASCADE;

-- Delete the schema.
gaussdb=# DROP SCHEMA tpcds;

Helpful Links

GRANT and REVOKE