GRANT (Table)
Grants privileges on a table to a user or role. Optionally grants privileges on all tables within one or more schemas.
Note: Granting privileges on all tables in a schema also includes privileges on all views in the same schema.
Syntax
GRANT { { SELECT | INSERT | UPDATE | DELETE | REFERENCES | TRUNCATE } [ ,... ] ... | ALL [ PRIVILEGES ] }
... ON [ TABLE ] [ [ db-name.]schema.]tablename [ , ... ]
... | ON ALL TABLES IN SCHEMA schema-name [, ...]
... TO { username | role | PUBLIC } [ , ... ]
... [ WITH GRANT OPTION ]
Parameters
SELECT |
Allows the user to SELECT from any column of the specified table. |
INSERT |
Allows the user to INSERT tuples into the specified table and to use the COPY command to load the table. Note: COPY FROM STDIN is allowed to any user granted the INSERT privilege, while COPY FROM <file> is an admin-only operation. |
UPDATE |
Allows the user to UPDATE tuples in the specified table. |
DELETE |
Allows the user to DELETE a row from the specified table. |
REFERENCES |
You must have this privilege on both the referencing and referenced tables to create a foreign key constraint. You also need USAGE on the schema that contains the table. |
TRUNCATE |
Grants TRUNCATE TABLE privileges to non-owners of a table. It also grants the non-owner of the table the following partition functions: |
ALL |
Applies to all privileges. |
PRIVILEGES |
Is for SQL standard compatibility and is ignored. |
[db-name.]schema |
Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example: myschema.thisDbObject |
tablename |
Specifies the table on which to grant the privileges. |
ON ALL TABLES IN SCHEMA |
Grants privileges on all tables (and by default all views) within one or more schemas to a user and/or role. |
username |
Grants the privilege to the specified user. |
role |
Grants the privilege to the specified role. |
PUBLIC |
Grants the privilege to all users. |
WITH GRANT OPTION |
Allows the user to grant the same privileges to other users. |
Notes
- The user must also be granted USAGE on the schema that contains the table. See GRANT (Schema).
- To use the DELETE or UPDATE commands with a WHERE Clause, a user must have both SELECT and UPDATE and DELETE privileges on the table.
- The user can be granted privileges on a global temporary table, but not a local temporary table.
Examples
This example shows how to grant user Joe all privileges on table customer_table.
=> CREATE USER Joe; CREATE USER
=> GRANT ALL PRIVILEGES ON TABLE customer_dimension TO Joe; GRANT PRIVILEGE
