This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

GRANT statements

GRANT statements grant privileges on database objects to users and roles.

1: GRANT (authentication)
2: GRANT (data loader)
3: GRANT (database)
4: GRANT (key)
5: GRANT (library)
6: GRANT (model)
7: GRANT (procedure)
8: GRANT (Resource pool)
9: GRANT (Role)
10: GRANT (schema)
11: GRANT (sequence)
12: GRANT (storage location)
13: GRANT (table)
14: GRANT (TLS configuration)
15: GRANT (user defined extension)
16: GRANT (view)
17: GRANT (workload)

GRANT statements grant privileges on database objects to users and roles.

Important

In a database with trust authentication, GRANT statements appear to work as expected but have no real effect on database security.

1 - GRANT (authentication)

Associates an authentication record to one or more users and roles.

Syntax

GRANT AUTHENTICATION auth-method-name TO grantee[,...]

Parameters

auth-method-name

Name of the authentication method to associate with one or more users or roles.

grantee

Specifies who is associated with the authentication method, one of the following:

User name
Role
PUBLIC: The default role of all users.

Privileges

Superuser

Examples

Associate v_ldap authentication with user jsmith:
```
=> GRANT AUTHENTICATION v_ldap TO jsmith;
```

Associate v_gss authentication to the role DBprogrammer:


=> CREATE ROLE DBprogrammer;
=> GRANT AUTHENTICATION v_gss TO DBprogrammer;

Associate client authentication method v_localpwd with role PUBLIC, which is assigned by default to all users:
```
=> GRANT AUTHENTICATION v_localpwd TO PUBLIC;
```

2 - GRANT (data loader)

Grants data-loader privileges to users and roles.

Grants privileges on automatic data loaders to users and roles. By default, only superusers and the owner can execute or alter a data loader.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] }
   ON DATA LOADER [schema.]name
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Arguments

privilege

One of the following privileges:

EXECUTE: Enables EXECUTE DATA LOADER.
ALTER: Enables ALTER DATA LOADER.
DROP: Enables DROP DATA LOADER.

ALL [PRIVILEGES]

Grants all privileges. Inherited privileges must be granted explicitly.

schema

Schema containing the data loader. The default schema is public.

name

Name of the data loader.

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

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

3 - GRANT (database)

Grants database privileges to users and roles.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] }
   ON DATABASE db-spec
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

privilege

The following privileges are valid for a database:

CREATE: Create schemas.
TEMP: Create temporary tables. By default, all users are granted this privilege through their DEFAULT role.

ALL [PRIVILEGES]

Grants all database privileges that also belong to the grantor. Grantors cannot grant privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

db-spec

Specifies the current database, set to the database name or DEFAULT.

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-superuser: Privileges grantee given the option (WITH GRANT OPTION) of granting privileges to other users or roles.

Examples

The following example grants user Fred the right to create schemas in the current database.

=> GRANT CREATE ON DATABASE DEFAULT TO Fred;

4 - GRANT (key)

Grants privileges on a cryptographic key to a user or role.

Important

Because certificates depend on their underlying key, DROP privileges on a key effectively act as DROP privileges on its associated certificate when used with DROP KEY...CASCADE.

To revoke granted privileges, see REVOKE (key).

Superusers have limited access to cryptographic objects that they do not own. For details, see Database object privileges.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] [ EXTEND ] } ON KEY
    key_name[,...]
    TO grantee[,...]
    [ WITH GRANT OPTION ]

Parameters

privilege

A privilege, one of the following:

USAGE: Allows a user to perform the following actions:
- View the contents of the key.
- Create or sign certificates using the key.
USAGE on the key also gives implicit USAGE privileges on a certificate that uses it as its private key. Users can also get these privileges from ownership of the key or certificate. USAGE privileges on a certificate allow a user to perform the following actions:
- View the contents of the certificate.
- Add (with CREATE or ALTER) the certificate to a TLS Configuration.
- Reuse the CA certificate when importing certificates signed by it. For example, if a user imports a chain of certificates A > B > C and have USAGE on B, the database reuses B (as opposed to creating a duplicate of B).
- Specify that the CA certificate signed an imported certificate. For example, if certificate B signed certificate C, USAGE on B allows a user to import C and specify that it was SIGNED BY B.
DROP
ALTER: Allows a user to see the key and its associated certificates in their respective system tables, but not their contents.

key_name

The target key.

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

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

Examples

The following example grants USAGE privileges on a private key to a user, which then allows the user to add the self-signed CA certificate to the server TLS Configuration:

=> CREATE KEY new_ca_key TYPE 'RSA' LENGTH 2048;
=> CREATE CA CERTIFICATE new_ca_cert
    SUBJECT '/C=US/ST=Massachusetts/L=Cambridge/O=Micro Focus/OU=Vertica/CN=Vertica example CA'
    VALID FOR 3650
    EXTENSIONS 'authorityKeyIdentifier' = 'keyid:always,issuer', 'nsComment' = 'new CA'
    KEY new_ca_key;

=> CREATE USER u1;
=> GRANT USAGE ON KEY new_ca_key TO u1;
=> GRANT ALTER ON TLS CONFIGURATION data_channel TO u1;

=> \c - u1

=> ALTER TLS CONFIGURATION data_channel ADD CA CERTIFICATES new_ca_cert;

-- clean up:
=> \c
=> ALTER TLS CONFIGURATION data_channel REMOVE CA CERTIFICATES new_ca_cert;
=> DROP KEY new_ca_key CASCADE;
=> DROP USER u1;

5 - GRANT (library)

Grants privileges on one or more libraries to users and roles.

For example, when working with the Connector Framework Service, you might need to grant a user usage privileges to a library to be able to set UDSession parameters. For more information see Implementing CFS.

Syntax

GRANT privilege
   ON LIBRARY [[database.]schema.]library[,...]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Arguments

privilege

Privilege to grant, one of:

USAGE: Grants access to functions in the specified libraries.
DROP: Grants permission to drop libraries that the grantee created.
ALL [PRIVILEGES] [EXTEND]: Grants all library 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 DROP privileges. An unqualified ALL excludes this privilege. This option enables backward compatibility with GRANT ALL usage in pre-9.2.1 Vertica releases.

Important

To execute functions inside the library, users must also have separate EXECUTE privileges on them, and USAGE privileges on their respective schemas.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

library

The target library.

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 USAGE privileges on the MyFunctions library to Fred:

=> GRANT USAGE ON LIBRARY MyFunctions TO Fred;

6 - GRANT (model)

Grants usage privileges on a model to users and roles.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] [ EXTEND ] }
   ON MODEL [[database.]schema.]model-name[,...]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

privilege

The following privileges are valid for models:

USAGE
ALTER
DROP

ALL [PRIVILEGES][EXTEND]

Grants all model 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

Database and schema. The default schema is public. If you specify a database, it must be the current database.

model-name

The model on which to grant the privilege.

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

This example grants USAGE privileges on the mySvmClassModel model to user1:

=> GRANT USAGE ON MODEL mySvmClassModel TO user1;

7 - GRANT (procedure)

Grants privileges on a stored procedure or external procedure to a user or role.

Important

External procedures that you create with CREATE PROCEDURE (external) are always run with Linux dbadmin privileges. If a dbadmin or pseudosuperuser grants a non-dbadmin permission to run a procedure using GRANT (procedure), be aware that the non-dbadmin user runs the procedure with full Linux dbadmin privileges.

Syntax

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
   ON PROCEDURE [[database.]schema.]procedure( [arg-list] )[,...]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

EXECUTE

Enables grantees to run the specified procedure.

ALL [PRIVILEGES]

Grants all procedure 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

Database and schema. The default schema is public. If you specify a database, it must be the current database.

procedure

The target procedure.

arg-list

A comma-delimited list of procedure arguments, where each argument is specified as follows:

[ argname ] argtype

If the procedure is defined with no arguments, supply an empty argument list.

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

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

Examples

Grant EXECUTE privileges on the tokenize procedure to users Bob and Jules, and to the role Operator:

=> GRANT EXECUTE ON PROCEDURE tokenize(varchar) TO Bob, Jules, Operator;

8 - GRANT (Resource pool)

Grants USAGE privileges on resource pools to users and roles.

Grants USAGE privileges on resource pools to users and roles. Users can access their resource pools with ALTER USER or SET SESSION RESOURCE POOL.

Syntax

GRANT USAGE
   ON RESOURCE POOL resource-pool[,...]
   [FOR SUBCLUSTER subcluster | FOR CURRENT SUBCLUSTER]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

USAGE

Enables grantees to acess the specified resource pools.

ALL [PRIVILEGES]

Grants all resource pool privileges that also belong to the grantor. Grantors cannot grant privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

resource-pool

A resource pool on which to grant the specified privileges.

subcluster

The subcluster for the resource pool.

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

Ownership
GRANT OPTION on the object

Examples

Grant user Joe USAGE privileges on resource pool Joe_pool.

=> CREATE USER Joe;
CREATE USER
=> CREATE RESOURCE POOL Joe_pool;
CREATE RESOURCE POOL
=> GRANT USAGE ON RESOURCE POOL Joe_pool TO Joe;
GRANT PRIVILEGE

Grant user Joe USAGE privileges on resource pool Joe_pool for subcluster sub1.

=> GRANT USAGE on RESOURCE POOL Joe_pool FOR SUBCLUSTER sub1 TO Joe;
GRANT PRIVILEGE

9 - GRANT (Role)

Assigns roles to users or other roles.

Note

Granting a role does not activate the role automatically; you must enable it with the SET ROLE statement or specify it as a default role to enable it automatically.

Syntax

GRANT role[,...] TO grantee[,...] [ WITH ADMIN OPTION ]

Arguments

role

A role to grant

grantee

User or role to be granted the specified roles, one of the following:

User name
Role
PUBLIC: The default role of all users.

WITH ADMIN OPTION

Gives grantee the privilege to grant the specified roles to other users or roles. For details, see Granting privileges.

Privileges

Non-superuser: If WITH GRANT OPTION is used, can grant the same roles to other users or roles.

Examples

See Granting database roles.

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

Note

By default, new users cannot access schema PUBLIC. You must explicitly grant all new users USAGE privileges on the PUBLIC 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.

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

{ namespace. | database. }

Name of the database or namespace that contains schema:

Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
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.

You cannot specify both a database and namespace name.

schema

Name of the schema, by default public.

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

11 - GRANT (sequence)

Grants sequence privileges to users and roles.

Syntax

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

Parameters

privilege

The following privileges are valid for sequences:

SELECT: Execute functions CURRVAL and NEXTVAL on the specified sequences.
ALTER: Modify a sequence's DDL with ALTER SEQUENCE
DROP: Drop this sequence with DROP SEQUENCE.

ALL [PRIVILEGES][EXTEND]

Grants all sequence 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

Database and schema. The default schema is public. If you specify a database, it must be the current database.

SEQUENCE sequence

Specifies the sequence on which to grant privileges.

ALL SEQUENCES IN SCHEMA schema

Grants the specified privileges on all sequences 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

This example shows how to grant user Joe all privileges on sequence my_seq.

=> CREATE SEQUENCE my_seq START 100;
CREATE SEQUENCE
=> GRANT ALL PRIVILEGES ON SEQUENCE my_seq TO Joe;
GRANT PRIVILEGE

12 - GRANT (storage location)

Grants privileges to users and roles on a USER-defined storage location.

Grants privileges to users and roles on a USER-defined storage location. For details, see Creating storage locations.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] }
   ON LOCATION 'path' [ ON node ]
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Parameters

privilege

The following privileges are valid for storage locations:

READ: Copy data from files in the storage location into a table.
WRITE: Export data from the database to the storage location. With WRITE privileges, grantees can also save COPY statement rejected data and exceptions files to the storage location.

ALL [PRIVILEGES]

Grants all storage location privileges that also belong to the grantor. Grantors cannot grant privileges that they themselves lack.

The optional keyword PRIVILEGES conforms with the SQL standard.

ON LOCATION 'path' [ ON node]

Specifies the path name mount point of the storage location. If qualified by ON NODE, Vertica grants access to the storage location residing on node.

If no node is specified, the grant operation applies to all nodes on the specified path. All nodes must be on the specified path; otherwise, the entire grant operation rolls back.

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

Ownership
GRANT OPTION on the object

Note

Only a superuser can add, alter, retire, drop, and restore a location.

Examples

Create a storage location:

=> CREATE LOCATION '/home/dbadmin/UserStorage/BobStore' NODE 'v_mcdb_node0007' USAGE 'USER';
CREATE LOCATION

Grant user Bob all available privileges to the /BobStore location:

=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' TO Bob;
GRANT PRIVILEGE

Revoke all storage location privileges from Bob:

=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' FROM Bob;
REVOKE PRIVILEGE

Grant privileges to Bob on the BobStore location again, specifying a node:

=> GRANT ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 TO Bob;
GRANT PRIVILEGE

Revoke all storage location privileges from Bob:

=> REVOKE ALL ON LOCATION '/home/dbadmin/UserStorage/BobStore' ON v_mcdb_node0007 FROM Bob;
REVOKE PRIVILEGE

13 - 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 ] [[{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:
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.

{ namespace. | database. }

Name of the database or namespace that contains table:

Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
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.

You cannot specify both a database and namespace name.

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.

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

14 - GRANT (TLS configuration)

Grants privileges on a TLS Configuration to a user or role.

To revoke granted privileges, see REVOKE (TLS configuration).

Superusers have limited access to cryptographic objects that they do not own. For details, see Database object privileges.

Syntax

GRANT { privilege[,...] } ON TLS CONFIGURATION
    tls_configuration[,...]
    TO grantee[,...]
    [ WITH GRANT OPTION ]

Parameters

privilege

A privilege, one of the following:

USAGE: Allows the user to set the TLS Configuration for a type of connection and view its contents in the system table TLS_CONFIGURATIONS. For details, see Security parameters.
DROP
ALTER

tls_configuration

The target TLS Configuration.

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

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

Examples

You can grant ALTER on a TLS Configuration to a user or role to delegate management of that TLS context, which includes adding and removing certificates, setting the TLSMODE, etc. For example, the following statement grants ALTER privileges on the TLS CONFIGURATION server to the role client_server_tls_manager:

=> GRANT ALTER ON TLS CONFIGURATION server TO client_server_tls_manager;

15 - GRANT (user defined extension)

Grants privileges on a user-defined extensions (UDx) to users and roles.

Syntax

GRANT { privilege[,...] | ALL [ PRIVILEGES ] [ EXTEND ] }
   ON {
       UDx-type [[database.]schema.]function( [arg-list] )[,...]
       | ALL FUNCTIONS IN SCHEMA schema[,...] }
   TO grantee[,...]
   [ WITH GRANT OPTION ]

Arguments

privilege

The following privileges are valid for user-defined extensions:

EXECUTE
ALTER
DROP

Note

Users can only call a UDx function on which they have EXECUTE privilege, and USAGE privilege on its schema.

ALL [PRIVILEGES] [EXTEND]

Grants all function 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.

ON UDx-type

Type of the user-defined extension (UDx), one of the following:

FUNCTION (scalar function)
AGGREGATE FUNCTION
ANALYTIC FUNCTION
TRANSFORM FUNCTION
FILTER
PARSER
SOURCE

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

function

Name of the user-defined function on which to grant privileges.

ON ALL FUNCTIONS IN SCHEMAschema

Grants privileges on all functions in the specified schema.

arg-list

Required for all polymorphic functions, a comma-delimited list of function arguments, where each argument is specified as follows:

[ argname ] argtype

If the procedure is defined with no arguments, supply an empty argument list.

grantee

Who is granted privileges, one of the following:

user-name
role
PUBLIC: Default role of all users

Note

Grantees must have USAGE privileges on the schema.

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 EXECUTE privileges on the myzeroifnull SQL function to users Bob and Jules, and to the role Operator. The function takes one integer argument:

=> GRANT EXECUTE ON FUNCTION myzeroifnull (x INT) TO Bob, Jules, Operator;

Grant EXECUTE privileges on all functions in the zero-schema schema to user Bob:

=> GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA zero-schema TO Bob;

Grant EXECUTE privileges on the tokenize transform function to user Bob and the role Operator:

=> GRANT EXECUTE ON TRANSFORM FUNCTION tokenize(VARCHAR) TO Bob, Operator;

Grant EXECUTE privileges on the ExampleSource() source to user Alice:

=> CREATE USER Alice;
=> GRANT USAGE ON SCHEMA hdfs TO Alice;
=> GRANT EXECUTE ON SOURCE ExampleSource() TO Alice;

Grant all privileges on the ExampleSource() source to user Alice:

=> GRANT ALL ON SOURCE ExampleSource() TO Alice;

Grant all privileges on polymorphic function Pagerank to the dbadmin role:

=> GRANT ALL ON TRANSFORM FUNCTION Pagerank(z varchar) to dbadmin;

16 - GRANT (view)

Grants view privileges to users and roles.

Syntax

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

Parameters

privilege``

The following privileges are valid for views:

ALL [PRIVILEGES][EXTEND]

Grants all view 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

Database and schema. The default schema is public. If you specify a database, it must be the current database.

view

The target view.

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.

Note

As view owner, you can grant other users SELECT privilege on the view only if one of the following is true:

You own the view's base table.
You have SELECT...WITH GRANT OPTION privilege on the view's base table.

Examples

Grant user Joe all privileges on view ship.

=> CREATE VIEW ship AS SELECT * FROM public.shipping_dimension;
CREATE VIEW
=> GRANT ALL PRIVILEGES ON ship TO Joe;
GRANT PRIVILEGE

17 - GRANT (workload)

Grants privileges to a role to use a workload, routing queries to its associated subclusters.

Grants privileges to a user or role to use a workload, routing its queries to its associated subclusters.

Syntax

GRANT USAGE ON WORKLOAD workload TO {role | user}

Parameters

workload: A workload defined by a routing rule. For details, see Workload routing.
user | role: The user or role permitted to use the workload.

Privileges

Superuser

Examples

The following example grants the USAGE privilege on the analytics workload to users with the analytics_role role:

=> GRANT USAGE ON WORKLOAD analytics TO analytics_role;

Similarly, this example grants the USAGE privilege on the analytics workload to the user jacob:

=> GRANT USAGE ON WORKLOAD analytics TO jacob;

For more examples, see Workload routing.

GRANT statements

Important

1 - GRANT (authentication)

Syntax

Parameters

Privileges

Examples

See also

2 - GRANT (data loader)

Syntax

Arguments

Privileges

See also

3 - GRANT (database)

Syntax

Parameters

Privileges

Examples

See also

4 - GRANT (key)

Important

Syntax

Parameters

Privileges

Examples

5 - GRANT (library)

Syntax

Arguments

Important

Privileges

Examples

See also

6 - GRANT (model)

Syntax

Parameters

Privileges

Examples

See also

7 - GRANT (procedure)

Important

Syntax

Parameters

Privileges

Examples

See also

8 - GRANT (Resource pool)

Syntax

Parameters

Privileges

Examples

See also

9 - GRANT (Role)

Note

Syntax

Arguments

Privileges

Examples

See also

10 - GRANT (schema)

Note

Syntax

Parameters

Note

Privileges

Examples

See also

11 - GRANT (sequence)

Syntax

Parameters

Privileges

Examples

See also

12 - GRANT (storage location)

Syntax

Parameters

Privileges

Note

Examples

See also

13 - GRANT (table)