ALTER DEFAULT PRIVILEGES

Synopsis

Use the ALTER DEFAULT PRIVILEGES statement to define the default access privileges.

Syntax

  1. alter_default_priv ::= ALTER DEFAULT PRIVILEGES 
  2.                        [ FOR { ROLE | USER } role_name [ , ... ] ] 
  3.                        [ IN SCHEMA schema_name [ , ... ] ] 
  4.                        abbr_grant_or_revoke
  6. abbr_grant_or_revoke ::= a_grant_table
  7.                          | a_grant_seq
  8.                          | a_grant_func
  9.                          | a_grant_type
  10.                          | a_grant_schema
  11.                          | a_revoke_table
  12.                          | a_revoke_seq
  13.                          | a_revoke_func
  14.                          | a_revoke_type
  15.                          | a_revoke_schema
  17. a_grant_table ::= GRANT { grant_table_priv [ , ... ]
  18.                           | ALL [ PRIVILEGES ] } ON TABLES TO 
  19.                   grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
  21. a_grant_seq ::= GRANT { grant_seq_priv [ , ... ]
  22.                         | ALL [ PRIVILEGES ] } ON SEQUENCES TO 
  23.                 grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
  25. a_grant_func ::= GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON 
  26.                  { FUNCTIONS | ROUTINES } TO grant_role_spec [ , ... ] 
  27.                  [ WITH GRANT OPTION ]
  29. a_grant_type ::= GRANT { USAGE | ALL [ PRIVILEGES ] } ON TYPES TO 
  30.                  grant_role_spec [ , ... ] [ WITH GRANT OPTION ]
  32. a_grant_schema ::= GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } ON 
  33.                    SCHEMAS TO grant_role_spec [ , ... ] 
  34.                    [ WITH GRANT OPTION ]
  36. a_revoke_table ::= REVOKE [ GRANT OPTION FOR ] 
  37.                    { grant_table_priv [ , ... ] | ALL [ PRIVILEGES ] } 
  38.                    ON TABLES FROM grant_role_spec [ , ... ] 
  39.                    [ CASCADE | RESTRICT ]
  41. a_revoke_seq ::= REVOKE [ GRANT OPTION FOR ] 
  42.                  { grant_seq_priv [ , ... ] | ALL [ PRIVILEGES ] } ON 
  43.                  SEQUENCES FROM grant_role_spec [ , ... ] 
  44.                  [ CASCADE | RESTRICT ]
  46. a_revoke_func ::= REVOKE [ GRANT OPTION FOR ] 
  47.                   { EXECUTE | ALL [ PRIVILEGES ] } ON 
  48.                   { FUNCTIONS | ROUTINES } FROM grant_role_spec 
  49.                   [ , ... ] [ CASCADE | RESTRICT ]
  51. a_revoke_type ::= REVOKE [ GRANT OPTION FOR ] 
  52.                   { USAGE | ALL [ PRIVILEGES ] } ON TYPES FROM 
  53.                   grant_role_spec [ , ... ] [ CASCADE | RESTRICT ]
  55. a_revoke_schema ::= REVOKE [ GRANT OPTION FOR ] 
  56.                     { USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS 
  57.                     FROM grant_role_spec [ , ... ] 
  58.                     [ CASCADE | RESTRICT ]
  60. grant_table_priv ::= SELECT
  61.                      | INSERT
  62.                      | UPDATE
  63.                      | DELETE
  64.                      | TRUNCATE
  65.                      | REFERENCES
  66.                      | TRIGGER
  68. grant_seq_priv ::= USAGE | SELECT | UPDATE
  70. grant_role_spec ::= [ GROUP ] role_name
  71.                     | PUBLIC
  72.                     | CURRENT_USER
  73.                     | SESSION_USER

alter_default_priv

ALTER DEFAULT PRIVILEGES - 图1

abbr_grant_or_revoke

ALTER DEFAULT PRIVILEGES - 图2

a_grant_table

ALTER DEFAULT PRIVILEGES - 图3

a_grant_seq

ALTER DEFAULT PRIVILEGES - 图4

a_grant_func

ALTER DEFAULT PRIVILEGES - 图5

a_grant_type

ALTER DEFAULT PRIVILEGES - 图6

a_grant_schema

ALTER DEFAULT PRIVILEGES - 图7

a_revoke_table

ALTER DEFAULT PRIVILEGES - 图8

a_revoke_seq

ALTER DEFAULT PRIVILEGES - 图9

a_revoke_func

ALTER DEFAULT PRIVILEGES - 图10

a_revoke_type

ALTER DEFAULT PRIVILEGES - 图11

a_revoke_schema

ALTER DEFAULT PRIVILEGES - 图12

grant_table_priv

ALTER DEFAULT PRIVILEGES - 图13

grant_seq_priv

ALTER DEFAULT PRIVILEGES - 图14

grant_role_spec

ALTER DEFAULT PRIVILEGES - 图15

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.
  1. yugabyte=# ALTER DEFAULT PRIVILEGES IN SCHEMA marketing GRANT SELECT ON TABLES TO PUBLIC;
  • Revoke INSERT privilege on all tables from user john.
  1. yugabyte=# ALTER DEFAULT PRIVILEGES REVOKE INSERT ON TABLES FROM john;

See also