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 { privilege[,... ] | ALL [ PRIVILEGES ] }
... ON {
... [ TABLE ] [[database.]schema.]table [,... ]
... | ALL TABLES IN SCHEMA [database.]schema [,...] }
... TO { username | role | PUBLIC } [,... ] 
... [ 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]

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 the 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 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

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