Help Center > > Developer Guide> SQL Reference> SQL Syntax> ALTER DEFAULT PRIVILEGES

ALTER DEFAULT PRIVILEGES

Updated at: Mar 13, 2020 GMT+08:00

Function

ALTER DEFAULT PRIVILEGES allows you to set the permissions that will be used for objects created in the future. (It does not affect permissions assigned to existing objects.) To isolate permissions, DWS disables the WITH GRANT OPTION syntax.

Precautions

Only the permissions for tables (including views), functions, and types (including domains) can be altered.

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 certain objects.
    1
    2
    3
    4
    5
    6
    grant_on_tables_clause
      | grant_on_functions_clause
      | grant_on_types_clause
      | revoke_on_tables_clause
      | revoke_on_functions_clause
      | revoke_on_types_clause
    
  • grant_on_tables_clause grants permissions on tables.
    1
    2
    3
    4
    5
    GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES } 
        [, ...] | ALL [ PRIVILEGES ] }
        ON TABLES 
        TO { [ GROUP ] role_name | PUBLIC } [, ...]
        [ WITH GRANT OPTION ]
    
  • grant_on_functions_clause grants permissions on functions.
    1
    2
    3
    4
    GRANT { EXECUTE | 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 | ALL [ PRIVILEGES ] }
        ON TYPES 
        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 } 
        [, ...] | ALL [ PRIVILEGES ] }
        ON TABLES 
        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 | 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 | ALL [ PRIVILEGES ] }
        ON TYPES 
        FROM { [ GROUP ] role_name | PUBLIC } [, ...]
        [ CASCADE | RESTRICT | CASCADE CONSTRAINTS ]
    

Parameter Description

  • target_role

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

    Value range: An existing role name.

  • schema_name

    Specifies the name of an existing schema.

    target_role must have the CREATE permissions for schema_name.

    Value range: An existing schema name.

  • role_name

    Specifies the name of an existing role whose permissions are to be granted or revoked.

    Value range: An existing role name.

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

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- Grant the SELECT permission on all the tables (and views) in tpcds to every user:
ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds GRANT SELECT ON TABLES TO PUBLIC;

-- Create a common user jack:
CREATE USER jack PASSWORD 'Bigdata123@';

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

-- Revoke the preceding permissions:
ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE SELECT ON TABLES FROM PUBLIC; 
ALTER DEFAULT PRIVILEGES IN SCHEMA tpcds REVOKE INSERT ON TABLES FROM jack;

-- Delete the user jack:
DROP USER jack;

Helpful Links

GRANT, REVOKE

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel