ALTER DEFAULT PRIVILEGES
Synopsis
Use the ALTER DEFAULT PRIVILEGES statement to define the default access privileges.
Syntax
Grammar
Diagramalter_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
grantee_role [ , ... ] [ WITH GRANT OPTION ]
a_grant_seq ::= GRANT { grant_seq_priv [ , ... ]
| ALL [ PRIVILEGES ] } ON SEQUENCES TO
grantee_role [ , ... ] [ WITH GRANT OPTION ]
a_grant_func ::= GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON
{ FUNCTIONS | ROUTINES } TO grantee_role [ , ... ]
[ WITH GRANT OPTION ]
a_grant_type ::= GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPES TO
grantee_role [ , ... ] [ WITH GRANT OPTION ]
a_grant_schema ::= GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } ON
SCHEMAS TO grantee_role [ , ... ]
[ WITH GRANT OPTION ]
a_revoke_table ::= REVOKE [ GRANT OPTION FOR ]
{ grant_table_priv [ , ... ] | ALL [ PRIVILEGES ] }
ON TABLES FROM grantee_role [ , ... ]
[ CASCADE | RESTRICT ]
a_revoke_seq ::= REVOKE [ GRANT OPTION FOR ]
{ grant_seq_priv [ , ... ] | ALL [ PRIVILEGES ] } ON
SEQUENCES FROM grantee_role [ , ... ]
[ CASCADE | RESTRICT ]
a_revoke_func ::= REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] } ON
{ FUNCTIONS | ROUTINES } FROM grantee_role
[ , ... ] [ CASCADE | RESTRICT ]
a_revoke_type ::= REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] } ON TYPES FROM
grantee_role [ , ... ] [ CASCADE | RESTRICT ]
a_revoke_schema ::= REVOKE [ GRANT OPTION FOR ]
{ USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS
FROM grantee_role [ , ... ]
[ CASCADE | RESTRICT ]
grant_table_priv ::= SELECT
| INSERT
| UPDATE
| DELETE
| TRUNCATE
| REFERENCES
| TRIGGER
grant_seq_priv ::= USAGE | SELECT | UPDATE
grantee_role ::= [ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
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;