Skip to main content

GRANT

Grants privileges on a database object or grants membership in a role.

Syntax​

GRANT { {SELECT | INSERT | UPDATE | DELETE |  
TRUNCATE } [, ...] | ALL [PRIVILEGES] }
    ON { [TABLE] [[[ONLY] <table_name> [, ...]] [, ...]]
         | ALL TABLES IN SCHEMA <schema_name> [, ...] }
    TO <role_spec> [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE } ( <col_name> [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( <col_name> [, ...] ) }
    ON [TABLE] [[[ONLY] <table_name> [, ...]] [, ...]]
    TO <role_spec> [, ...] [ WITH GRANT OPTION ]

GRANT { {CREATE | CONNECT | TEMPORARY | TEMP} [, ...] | ALL 
[PRIVILEGES] }
    ON DATABASE <database_name> [, ...]
    TO <role_spec> [, ...] [ WITH GRANT OPTION ]


GRANT { EXECUTE | ALL [PRIVILEGES] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } <routine_name> [ ( [ [ <arg_mode> ] [ <arg_name> ] <arg_type> [, ...] ] ) ] [, ...]
        | ALL { FUNCTIONS | PROCEDURES | ROUTINES }  IN SCHEMA <schema_name> [, ...] }
    TO <role_spec> [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [PRIVILEGES] }
    ON SCHEMA <schema_name> [, ...]
    TO <role_spec> [, ...] [ WITH GRANT OPTION ]


GRANT <role_name> [, ...] TO <role_spec> [, ...]
    [ GRANTED BY <role_spec> ]


where <role_spec> can be:

    [ GROUP ] <role_name>
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

Description​

Grant privileges on database objects

You can run GRANT to grant specific privileges on a database object to one or more roles. If you want to grant specific privileges to all roles including later created roles, set <role_spec> to keyword PUBLIC. PUBLIC can be regarded as a group-level role that includes all roles. All roles automatically have the privileges that are granted to PUBLIC.

If you include WITH GRANT OPTION in your GRANT statement, the specified roles in the statement can grant the privileges to other roles except PUBLIC.

note

  • The owner of a database object has full privileges on the database object, by default. However, they can revoke some privileges by themselves, for security purposes.

  • The privilege to drop or alter a database object cannot be granted or revoked, even by the owner of the database object.

You can run GRANT ON ... ROUTINE to grant privileges to a normal, aggregate, or procedure, use GRANT ... ON FUNCTION to grant privileges on a normal, aggregate, or window function, or GRANT ... ON PROCEDURE to a procedure.

In addition, Relyt allows you to run GRANT ON ... SCHEMA to grant privileges on all tables, sequences, functions, or procedures in the specified one or more schemas. If you want to grant privileges on all tables, set <role_spec> to ALL TABLES. The views and foreigns tables in the specified schemas are also affected. If you want to grant privileges on all functions, set <role_spec> to ALL FUNCTIONS. ALL FUNCTIONS do not include procedures; if you want include procedures, set <role_spec> to ALL ROUTINES. To grant privileges only on all procedures, set <role_spec> to PROCEDURES.

Grant privileges on roles

You can run GRANT to grant membership in a role to one or more roles. Role membership conveys privileges granted to a role to all its members. It is very imporant.

If you include GRANTED BY in your statement, the specified role is regarded as the one who performs the GRANT operation.

info
  • <role_spec> cannot be set to PUBLIC.
  • We recommend that you do not use noise word GROUP when specifying role_spec, since we do not use the concept user group in our system.

Usage notes​

If a user has the SELECT, UPDATE, or another privilege on a table and tries to revoke the privilege from a specific column, the table-level privilege may be affected.

If a user attempts to GRANT privileges to an object that is not owned by the user, the statement directly fail to be executed. As long as one privilege is available, the statement will proceed. GRANT ALL PRIVILIGES will issue a warning if no grant options are held, while other forms will issue a warning as long as there is one grant option is not held.

If the role that executes GRANT holds the required privileges indirectly from more than one role membership path, the containing role that are recorded as the grantee will not be specified. In such cases, we recommend that you use SET ROLE to specify the grantee.

Parameters​

  • SELECT

    The privilege to SELECT data from specified columns of the given database object. A role granted with this privilege is allowed to use COPY ... TO. This privilege is required to reference column values while performing UPDATE or DELETE operations.

  • INSERT

    The privilege to write rows to the given table. If you specify columns in the INSERT statement, values in the rows are written only to the specified columns, while other columns are filled up with their default values. A role granted with this privilege is allowed to use COPY ... FROM.

  • UPDATE

    The privilege to update values in the specified columns of the given table. To run SELECT ... FOR UPDATE and SELECT ... FOR SHARE, you also need this privilege and the SELECT privilege on at least one column. This privilege also allows you to use nextval() and setval() functions on sequences.

  • DELETE

    The privilege to delete rows from the specified table.

  • TRUNCATE

    The privilege to truncate all rows from the given table.

  • CREATE

    The privilege to create schemas in the given database or to create objects in the given schema.

  • CONNECT

    The privilege to connect to the given database.

  • EXECUTE

    The privilege to use the given function and all operators using the function. This is the only privilege that applies to functions.

  • USAGE

    The privilege to access objects in the given schema. For a schema, this privilege allows the grantee to check objects in the schema.

  • ALL PRIVILEGES

    The full privileges. You can use ALL instead.

  • PUBLIC

    The group-level role that includes all roles. The privileges assigned to PUBLIC is automatically assigned to all roles, including those created later.

Examples​

Execute the following statement to grant the UPDATE and INSERT privileges to all roles on table sales:

GRANT UPDATE ON sales TO PUBLIC;

Execute the following statement to grant membership in role [email protected] to user [email protected]:

GRANT "[email protected]" TO "[email protected]";

Note that while the above will indeed grant all privileges if run by a superuser or the owner of kinds, when run by someone else it will only grant those permissions for which the granting role has grant options.

SQL standard compatibility​

According to the SQL standard, the PRIVILEGES key word in ALL PRIVILEGES is required, but it is optional in Relyt. The SQL standard does not support setting the privileges on more than one object per command.

Relyt allows an object owner to revoke their own ordinary privileges: for example, a table owner can make the table read-only to themselves by revoking their own INSERT, UPDATE, and DELETE privileges. This is not possible according to the SQL standard. Relyt treats the owner's privileges as having been granted by the owner to the owner; therefore they can revoke them too. In the SQL standard, the owner's privileges are granted by an assumed system entity. Not being system, the owner cannot revoke these rights.

The SQL standard allows the GRANTED BY option to be used in all forms of GRANT. Relyt only supports it when granting role membership, and even then only superusers may use it in nontrivial ways.

The SQL standard provides for a USAGE privilege on other kinds of objects: character sets, collations, translations.

Privileges on databases and schemas are Relyt extensions.