Skip to main content
Modifies the default privileges that apply to objects created in the future. Use ALTER DEFAULT PRIVILEGES to grant or revoke permissions that automatically apply to new schemas.
Only schemas are supported at this time. Default privileges for databases and tables are not currently available.
For more information, see Role-based access control.

​
Syntax

ALTER DEFAULT PRIVILEGES
    { GRANT | REVOKE } <privilege>
    ON SCHEMAS
    { TO | FROM } <role_name>

​
Parameters

ParameterDescription
<privilege>The privilege to grant or revoke. Can be USAGE, CREATE, ALL, or other schema-level privileges.
<role_name>The role to grant the privilege to or revoke the privilege from.

​
Supported privileges

Only schema privileges can be granted using ALTER DEFAULT PRIVILEGES. The following privileges are supported:
PrivilegeDescriptionGRANT SyntaxREVOKE Syntax
USAGEAllows access to the schema and its objectsGRANT USAGE ON SCHEMA public IN <database_name> TO <role_name>;REVOKE USAGE ON SCHEMA public IN <database_name> FROM <role_name>;
MODIFYAllows altering the schema properties, including renaming or dropping the schema.GRANT MODIFY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE MODIFY ON SCHEMA public IN <database_name> FROM <role_name>;
CREATEAllows creating new objects, such as tables and views, within the schema.GRANT CREATE ON SCHEMA public IN <database_name> TO <role_name>;REVOKE CREATE ON SCHEMA public IN <database_name> FROM <role_name>;
DELETE ANYAllows deleting rows and partitions from all current and future tables.GRANT DELETE ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE DELETE ANY ON SCHEMA public IN <database_name> FROM <role_name>;
INSERT ANYAllows inserting rows into all current and future tables within the schema.GRANT INSERT ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE INSERT ANY ON SCHEMA public IN <database_name> FROM <role_name>;
UPDATE ANYAllows updating rows in all current and future tables within the schema.GRANT UPDATE ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE UPDATE ANY ON SCHEMA public IN <database_name> FROM <role_name>;
TRUNCATE ANYAllows truncating all current and future tables within the schema.GRANT TRUNCATE ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE TRUNCATE ANY ON SCHEMA public IN <database_name> FROM <role_name>;
VACUUM ANYAllows running the VACUUM operation on all current and future tables.GRANT VACUUM ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE VACUUM ANY ON SCHEMA public IN <database_name> FROM <role_name>;
MODIFY ANYAllows modifying or dropping all current and future objects in the schema.GRANT MODIFY ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE MODIFY ANY ON SCHEMA public IN <database_name> FROM <role_name>;
SELECT ANYAllows reading data from all current and future objects within the schema.GRANT SELECT ANY ON SCHEMA public IN <database_name> TO <role_name>;REVOKE SELECT ANY ON SCHEMA public IN <database_name> FROM <role_name>;
ALL [PRIVILEGES]Grants all direct privileges over the schema to a role.GRANT ALL ON SCHEMA public IN <database_name> TO <role_name>;REVOKE ALL ON SCHEMA public IN <database_name> FROM <role_name>;
For more information about schema privileges, see Schema permissions.

​
Limitations

  • Scope: Default privileges are granted at the account scope only.
  • Object types: Only schemas are supported.

​
Notes

  • Default privileges only apply to objects created after the ALTER DEFAULT PRIVILEGES command is executed. Existing objects are not affected.
  • You must have the privilege you are granting in order to set it as a default privilege.
  • Only account administrators and role owners can modify default privileges.
  • Default privileges are stored in the information_schema.object_default_privileges view.

​
Examples

Grant default USAGE privilege on future schemas The following example grants the USAGE privilege on all future schemas created by the current role to user_role: 
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO user_role;
Revoke default privileges The following example revokes the USAGE privilege on future schemas from user_role: 
ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM user_role;
Grant all default privileges The following example grants all available privileges on future schemas to power_user_role: 
ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO power_user_role;
  • RBAC guide - Provides overview of the system capabilities
  • GRANT - Grant privileges on existing objects
  • REVOKE - Revoke privileges from existing objects
  • CREATE ROLE - Create a new role