> ## Documentation Index > Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. # ALTER DEFAULT PRIVILEGES export const InternalLink = ({version, path = "", children, ...props}) => { let detectedVersion = version || "stable"; if (typeof window !== 'undefined' && !version) { const match = window.location.pathname.match(/\/docs\/([^/]+)/); if (match) { detectedVersion = match[1]; } } const normalizedPath = path.startsWith("/") ? path.slice(1) : path; return {children} ; }; The `ALTER DEFAULT PRIVILEGES` statement changes the default privileges on objects created by users/roles in the current database. The creator of an object is also the object's owner. Any roles that are members of the owner role have `ALL` privileges on the object. Altering the default privileges of objects created by a role does not affect that role's privileges as the object's owner. The default privileges granted to other users/roles are always in addition to the ownership (i.e., `ALL`) privileges given to the creator of the object. If you grant a default privilege to a user/role for all objects created by a specific user/role, neither of the users/roles can be dropped until the default privilege is revoked. For an example, see [Grant default privileges to a specific role](#grant-default-privileges-to-a-specific-role). ## Synopsis alter_default_privileges syntax diagram ### Parameters | Parameter | Description | | ------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `FOR ROLE name`/`FOR USER name` | Alter the default privileges on objects created by a specific role/user, or a list of roles/users. | | `FOR ALL ROLES` | Alter the default privileges on objects created by all users/roles. | | `GRANT ...` | Grant a default privilege or list of privileges on all objects of the specified type to a role/user, or a list of roles/users. | | `REVOKE ...` | Revoke a default privilege or list of privileges on all objects of the specified type from a role/user, or a list of roles/users. | | `IN SCHEMA qualifiable\_schema\_name` | If specified, the default privileges are altered for objects created in that schema. If an object has default privileges specified at the database and at the schema level, the union of the default privileges is taken. | If you do not specify a `FOR...` clause, CockroachDB alters the default privileges on objects created by the current user. ## Required privileges * To run `ALTER DEFAULT PRIVILEGES FOR ALL ROLES`, the user must be a member of the `admin` role. * To alter the default privileges on objects created by a specific role, the user must be a member of that role. ## Examples ### Grant default privileges to a specific role Run the following statements as a member of the `admin` role, with `ALL` privileges: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE ROLE cockroachlabs WITH LOGIN; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > GRANT CREATE ON DATABASE defaultdb TO cockroachlabs; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE USER max WITH LOGIN; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW DEFAULT PRIVILEGES FOR ROLE cockroachlabs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} role | for_all_roles | object_type | grantee | privilege_type ----------------+---------------+-------------+---------------+----------------- cockroachlabs | false | schemas | cockroachlabs | ALL cockroachlabs | false | sequences | cockroachlabs | ALL cockroachlabs | false | tables | cockroachlabs | ALL cockroachlabs | false | types | cockroachlabs | ALL cockroachlabs | false | types | public | USAGE (5 rows) ``` In the same database, run the following statements as the `cockroachlabs` user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER DEFAULT PRIVILEGES FOR ROLE cockroachlabs GRANT SELECT ON TABLES TO max; ``` Because `cockroachlabs` is the current user, the previous statement is equivalent to `ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO max;`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW DEFAULT PRIVILEGES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} role | for_all_roles | object_type | grantee | privilege_type ----------------+---------------+-------------+---------------+----------------- cockroachlabs | false | schemas | cockroachlabs | ALL cockroachlabs | false | sequences | cockroachlabs | ALL cockroachlabs | false | tables | cockroachlabs | ALL cockroachlabs | false | tables | max | SELECT cockroachlabs | false | types | cockroachlabs | ALL cockroachlabs | false | types | public | USAGE (6 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE albums ( id UUID PRIMARY KEY, title STRING, length DECIMAL, tracklist JSONB ); ``` In the same database, run the following statements as the `max` user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP TABLE albums; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: user max does not have DROP privilege on relation albums SQLSTATE: 42501 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM albums; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-----------+------------ id | UUID | false | NULL | | {primary} | false title | STRING | true | NULL | | {primary} | false length | DECIMAL | true | NULL | | {primary} | false tracklist | JSONB | true | NULL | | {primary} | false (4 rows) ``` Because `max` has default `SELECT` privileges on all tables created by `cockroachlabs`, neither user/role can be dropped until all privileges are revoked. To see this, run the following statements as a member of the `admin` role, with `ALL` privileges: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP USER max; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: cannot drop role/user max: grants still exist on defaultdb.public.albums SQLSTATE: 2BP01 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP USER cockroachlabs; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: cannot drop role/user cockroachlabs: grants still exist on defaultdb, defaultdb.public.albums SQLSTATE: 2BP01 ``` ### Revoke default privileges from a specific role Run the following statements as the `cockroachlabs` user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW DEFAULT PRIVILEGES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} role | for_all_roles | object_type | grantee | privilege_type ----------------+---------------+-------------+---------------+----------------- cockroachlabs | false | schemas | cockroachlabs | ALL cockroachlabs | false | sequences | cockroachlabs | ALL cockroachlabs | false | tables | cockroachlabs | ALL cockroachlabs | false | tables | max | SELECT cockroachlabs | false | types | cockroachlabs | ALL cockroachlabs | false | types | public | USAGE (6 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER DEFAULT PRIVILEGES FOR ROLE cockroachlabs REVOKE SELECT ON TABLES FROM max; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW DEFAULT PRIVILEGES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} role | for_all_roles | object_type | grantee | privilege_type ----------------+---------------+-------------+---------------+----------------- cockroachlabs | false | schemas | cockroachlabs | ALL cockroachlabs | false | sequences | cockroachlabs | ALL cockroachlabs | false | tables | cockroachlabs | ALL cockroachlabs | false | types | cockroachlabs | ALL cockroachlabs | false | types | public | USAGE (5 rows) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE tracks ( id UUID PRIMARY KEY, album_id UUID, title STRING, length DECIMAL ); ``` In the same database, run the following statements as the `max` user: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP TABLE albums; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: user max does not have DROP privilege on relation albums SQLSTATE: 42501 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM albums; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden --------------+-----------+-------------+----------------+-----------------------+-----------+------------ id | UUID | false | NULL | | {primary} | false title | STRING | true | NULL | | {primary} | false length | DECIMAL | true | NULL | | {primary} | false tracklist | JSONB | true | NULL | | {primary} | false (4 rows) ``` `max` still has `SELECT` privileges on `albums` because when `cockroachlabs` created `albums`, `max` was granted default `SELECT` privileges on all tables created by `cockroachlabs`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > REVOKE SELECT ON TABLE albums FROM max; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP TABLE tracks; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: user max does not have DROP privilege on relation tracks SQLSTATE: 42501 ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM tracks; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} ERROR: user max has no privileges on relation tracks SQLSTATE: 42501 ``` `cockroachlabs` created the `tracks` table after revoking default `SELECT` privileges from `max`. As a result, `max` never had `SELECT` privileges on `tracks`. Because `max` has no default privileges, the user can now be dropped: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP USER max; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW USERS; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} username | options | member_of | estimated_last_login_time ----------------+---------+-----------+------------------------------ admin | {} | {} | NULL cockroachlabs | {} | {} | NULL root | {} | {admin} | NULL (3 rows) ``` ### Grant default privileges for all roles Run the following statements as a member of the `admin` role, with `ALL` privileges: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER DEFAULT PRIVILEGES FOR ALL ROLES GRANT SELECT ON TABLES TO public; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW DEFAULT PRIVILEGES FOR ALL ROLES; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} role | for_all_roles | object_type | grantee | privilege_type -------+---------------+-------------+---------+----------------- NULL | true | tables | public | SELECT NULL | true | types | public | USAGE (2 rows) ``` In the same database, run the following statements as any two different users: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE TABLE discographies ( id UUID PRIMARY KEY, artist STRING, total_length DECIMAL ); ``` `CREATE TABLE` requires the `CREATE` privilege on the database. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW COLUMNS FROM discographies; ``` ```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden ---------------+-----------+-------------+----------------+-----------------------+-----------+------------ id | UUID | false | NULL | | {primary} | false artist | STRING | true | NULL | | {primary} | false total_length | DECIMAL | true | NULL | | {primary} | false (3 rows) ``` ## See also * `SHOW DEFAULT PRIVILEGES` * SQL Statements * Default Privileges