GRANT (table)

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

Syntax

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

Parameters

privilege

The following privileges are valid for tables:

Important

Only SELECT privileges are valid for system tables.

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

• INSERT: Insert table rows with INSERT, and load data with COPY.

  Note

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

• UPDATE: Update table rows.

• DELETE: Delete table rows.

• REFERENCES: Create foreign key constraints on this table. This privilege must be set on both referencing and referenced tables.

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

  • DROP_PARTITIONS
  • SWAP_PARTITIONS_BETWEEN_TABLES
  • MOVE_PARTITIONS_TO_TABLE

• ALTER: Modify a table's DDL with ALTER TABLE.

• DROP: Drop a table.

ALL [PRIVILEGES][EXTEND]

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

You can qualify ALL with two optional keywords:

• PRIVILEGES conforms with the SQL standard.

• EXTEND extends the semantics of ALL to include ALTER and DROP privileges. An unqualified ALL excludes these two privileges. This option enables backward compatibility with GRANT ALL usage in pre-9.2.1 Vertica releases.

{ database | namespace }

Name of the database or namespace that contains table:

• Database name: If specified, it must be the current database.

• Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

TABLE table

Specifies 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.

ON ALL TABLES IN SCHEMA schema

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

grantee

Who is granted privileges, one of the following:

• user-name
• role

• PUBLIC: Default role of all users

WITH GRANT OPTION

Allows the grantee to grant and revoke the same privileges to other users or roles. For details, see Granting privileges.

Privileges

Non-superusers require USAGE on the schema and one of the following:

• Owner

• Privileges grantee given the option (WITH GRANT OPTION) of granting privileges to other users or roles.

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

• REVOKE (table)
• Granting and revoking privileges