GRANT (table)

Grants table privileges to users and roles.

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 ] [[database.]schema.]table[,...]
     | ALL TABLES IN SCHEMA [database.]schema[,...] }
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

privilege
The following privileges are valid for tables:
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.]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 table
Specifies the table on which to grant privileges.
ON ALL TABLES IN SCHEMA schema
Grants the specified privileges on all tables and views in schema 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, and also revoke them. 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