ALTER DEFAULT PRIVILEGES
Synopsis
Use the ALTER DEFAULT PRIVILEGES statement to define the default access privileges.
Syntax
alter_default_priv ::= ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } role_name [ , ... ] ]
[ IN SCHEMA schema_name [ , ... ] ]
abbr_grant_or_revoke
abbr_grant_or_revoke ::= a_grant_table
| a_grant_seq
| a_grant_func
| a_grant_type
| a_grant_schema
| a_revoke_table
| a_revoke_seq
| a_revoke_func
| a_revoke_type
| a_revoke_schema
a_grant_table ::= GRANT { grant_table_priv [ , ... ]
| ALL [ PRIVILEGES ] } ON TABLES TO
grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
a_grant_seq ::= GRANT { grant_seq_priv [ , ... ]
| ALL [ PRIVILEGES ] } ON SEQUENCES TO
grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
a_grant_func ::= GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON
{ FUNCTIONS | ROUTINES } TO grant_role_spec [ , ... ]
[ WITH GRANT OPTION ]
a_grant_type ::= GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPES TO
grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
a_grant_schema ::= GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } ON
SCHEMAS TO grant_role_spec [ , ... ]
[ WITH GRANT OPTION ]
a_revoke_table ::= REVOKE [ GRANT OPTION FOR ]
{ grant_table_priv [ , ... ] | ALL [ PRIVILEGES ] }
ON TABLES FROM grant_role_spec [ , ... ]
[ CASCADE | RESTRICT ]
a_revoke_seq ::= REVOKE [ GRANT OPTION FOR ]
{ grant_seq_priv [ , ... ] | ALL [ PRIVILEGES ] } ON
SEQUENCES FROM grant_role_spec [ , ... ]
[ CASCADE | RESTRICT ]
a_revoke_func ::= REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] } ON
{ FUNCTIONS | ROUTINES } FROM grant_role_spec
[ , ... ] [ CASCADE | RESTRICT ]
a_revoke_type ::= REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] } ON TYPES FROM
grant_role_spec [ , ... ] [ CASCADE | RESTRICT ]
a_revoke_schema ::= REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS
FROM grant_role_spec [ , ... ]
[ CASCADE | RESTRICT ]
grant_table_priv ::= SELECT
| INSERT
| UPDATE
| DELETE
| TRUNCATE
| REFERENCES
| TRIGGER
grant_seq_priv ::= USAGE | SELECT | UPDATE
grant_role_spec ::= [ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
alter_default_priv
abbr_grant_or_revoke
a_grant_table
a_grant_seq
a_grant_func
a_grant_type
a_grant_schema
a_revoke_table
a_revoke_seq
a_revoke_func
a_revoke_type
a_revoke_schema
grant_table_priv
grant_seq_priv
grant_role_spec
Semantics
ALTER DEFAULT PRIVILEGES defines the privileges for objects created in future. It does not affect objects that are already created.
Users can change default privileges only for objects that are created by them or by roles that they are a member of.
Examples
- Grant SELECT privilege to all tables that are created in schema marketing to all users.
yugabyte=# ALTER DEFAULT PRIVILEGES IN SCHEMA marketing GRANT SELECT ON TABLES TO PUBLIC;
- Revoke INSERT privilege on all tables from user john.
yugabyte=# ALTER DEFAULT PRIVILEGES REVOKE INSERT ON TABLES FROM john;