GRANT (Table)

Grants table privileges to users and roles. Users must also be granted USAGE on the schema that contains the table.

Syntax

GRANT { privilege[,…] | ALL [ PRIVILEGES ] }
   ON { 
     [ TABLE ] [[database.]schema.]table[,…] 
     | ALL TABLES IN SCHEMA [database.]schema[,…] }
   TO grantee[,…] 
   [ WITH GRANT OPTION ]

Parameters

privilege

One of the following privileges:

Important: Only SELECT privileges can be granted on system tables.

  • SELECT: Query tables of this schema. SELECT privileges are granted by default to the PUBLIC role.

  • INSERT: Insert rows into tables of this schema, or and load data into tables with COPY.

    Note: COPY FROM STDIN is allowed for users with INSERT privileges, while COPY FROM file requires admin privileges.

  • UPDATE: Update rows of tables of this schema.
  • DELETE: Delete rows of tables of this schema.
  • REFERENCES: Create foreign key constraints for tables of this schema. This privilege must be set on both referencing and referenced tables.

  • TRUNCATE: Truncate table contents. Non-owners of the tables can also execute the following partition operations on them:
ALL [PRIVILEGES]

Invalid for system tables, grants all table privileges that also belong to the grantor. Grantors cannot grant privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.
[database.]schema

Specifies a schema, by default public. If schema is any schema other than public, you must supply the schema name. For example:

myschema.thisDbObject

One exception applies: you can specify system tables without their schema name.

If you specify a database, it must be the current database.
table

The table on which to grant privileges.

Note: The table can be a global temporary table, but not a local temporary table. See Creating Temporary Tables in the Administrator's Guide.
ON ALL TABLES IN SCHEMA

Grants privileges on all tables and views in the specified schema.
grantee

Specifies who is granted privileges, one of the following:
WITH GRANT OPTION

Gives grantee the privilege to grant the same privileges to other users or roles. For details, see How to Grant Privileges in the Administrator's Guide.

Privileges

One of the following:

Examples

Grant user Joe all privileges on table customer_dimension:

=> CREATE USER Joe;
CREATE USER
=> GRANT ALL PRIVILEGES ON TABLE customer_dimension TO Joe;
GRANT PRIVILEGE

Grant user Joe SELECT privileges on all system tables:

=> GRANT SELECT on all tables in schema V_MONITOR, V_CATALOG TO Joe;
GRANT PRIVILEGE

See Also