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 
                  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

alter_default_priv

ALTERDEFAULTPRIVILEGESFORROLEUSER,role_nameINSCHEMA,schema_nameabbr_grant_or_revoke

abbr_grant_or_revoke

a_grant_tablea_grant_seqa_grant_funca_grant_typea_grant_schemaa_revoke_tablea_revoke_seqa_revoke_funca_revoke_typea_revoke_schema

a_grant_table

GRANT,grant_table_privALLPRIVILEGESONTABLESTO,grantee_roleWITHGRANTOPTION

a_grant_seq

GRANT,grant_seq_privALLPRIVILEGESONSEQUENCESTO,grantee_roleWITHGRANTOPTION

a_grant_func

GRANTEXECUTEALLPRIVILEGESONFUNCTIONSROUTINESTO,grantee_roleWITHGRANTOPTION

a_grant_type

GRANTUSAGEALLPRIVILEGESONTYPESTO,grantee_roleWITHGRANTOPTION

a_grant_schema

GRANTUSAGECREATEALLPRIVILEGESONSCHEMASTO,grantee_roleWITHGRANTOPTION

a_revoke_table

REVOKEGRANTOPTIONFOR,grant_table_privALLPRIVILEGESONTABLESFROM,grantee_roleCASCADERESTRICT

a_revoke_seq

REVOKEGRANTOPTIONFOR,grant_seq_privALLPRIVILEGESONSEQUENCESFROM,grantee_roleCASCADERESTRICT

a_revoke_func

REVOKEGRANTOPTIONFOREXECUTEALLPRIVILEGESONFUNCTIONSROUTINESFROM,grantee_roleCASCADERESTRICT

a_revoke_type

REVOKEGRANTOPTIONFORUSAGEALLPRIVILEGESONTYPESFROM,grantee_roleCASCADERESTRICT

a_revoke_schema

REVOKEGRANTOPTIONFORUSAGECREATEALLPRIVILEGESONSCHEMASFROM,grantee_roleCASCADERESTRICT

grant_table_priv

SELECTINSERTUPDATEDELETETRUNCATEREFERENCESTRIGGER

grant_seq_priv

USAGESELECTUPDATE

grantee_role

GROUProle_namePUBLICCURRENT_USERSESSION_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;

See also