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

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

a_grant_seq

GRANT,grant_seq_privALLPRIVILEGESONSEQUENCESTO,grant_role_specWITHGRANTOPTION

a_grant_func

GRANTEXECUTEALLPRIVILEGESONFUNCTIONSROUTINESTO,grant_role_specWITHGRANTOPTION

a_grant_type

GRANTUSAGEALLPRIVILEGESONTYPESTO,grant_role_specWITHGRANTOPTION

a_grant_schema

GRANTUSAGECREATEALLPRIVILEGESONSCHEMASTO,grant_role_specWITHGRANTOPTION

a_revoke_table

REVOKEGRANTOPTIONFOR,grant_table_privALLPRIVILEGESONTABLESFROM,grant_role_specCASCADERESTRICT

a_revoke_seq

REVOKEGRANTOPTIONFOR,grant_seq_privALLPRIVILEGESONSEQUENCESFROM,grant_role_specCASCADERESTRICT

a_revoke_func

REVOKEGRANTOPTIONFOREXECUTEALLPRIVILEGESONFUNCTIONSROUTINESFROM,grant_role_specCASCADERESTRICT

a_revoke_type

REVOKEGRANTOPTIONFORUSAGEALLPRIVILEGESONTYPESFROM,grant_role_specCASCADERESTRICT

a_revoke_schema

REVOKEGRANTOPTIONFORUSAGECREATEALLPRIVILEGESONSCHEMASFROM,grant_role_specCASCADERESTRICT

grant_table_priv

SELECTINSERTUPDATEDELETETRUNCATEREFERENCESTRIGGER

grant_seq_priv

USAGESELECTUPDATE

grant_role_spec

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