GRANT (schema)

Grants schema privileges to users and roles.

Grants schema privileges to users and roles. By default, only superusers and the schema owner have the following schema privileges:

  • Create objects within a schema.

  • Alter and drop a schema.

Syntax

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

Parameters

privilege
One of the following privileges:

  • USAGE: Enables access to objects in the specified schemas. Grantees can then be granted privileges on individual objects in these schemas in order to access them, for example, with GRANT TABLE and GRANT VIEW.

  • CREATE: Create and rename objects in the specified schemas, and move objects from other schemas.

You can also grant the following privileges on a schema, to be inherited by tables and their projections, and by views of that schema. If inheritance is enabled for the database and schema, these privileges are automatically granted to those objects on creation:

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

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

  • 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:

  • ALTER: Modify the DDL of tables and views with ALTER TABLE and ALTER VIEW, respectively.

  • DROP: Drop tables and views.

ALL [PRIVILEGES][EXTEND]
Grants USAGE AND CREATE privileges. Inherited privileges must be granted explicitly.

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 schema:

  • 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 a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

grantee

Who is granted privileges, one of the following:

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, one of the following:

  • Schema owner

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

Examples

Grant user Joe USAGE privilege on schema online_sales.

=> CREATE USER Joe;
CREATE USER
=> GRANT USAGE ON SCHEMA online_sales TO Joe;
GRANT PRIVILEGE

See also