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.

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

1 - GRANT (authentication)

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

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:

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;
    

See also

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

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.

See also

3 - GRANT (database)

Grants database privileges to users and roles.

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:

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;

See also

4 - GRANT (key)

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

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

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:

    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:

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.

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.

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

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;

See also

6 - GRANT (model)

Grants usage privileges on a model to users and roles.

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

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;

See also

7 - GRANT (procedure)

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

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

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:

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;

See also

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:

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:

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

See also

9 - GRANT (Role)

Assigns roles to users or other roles.

Assigns roles to users or other roles.

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

See also

REVOKE (Role)

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.

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

11 - GRANT (sequence)

Grants sequence privileges to users and roles.

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

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

See also

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:

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:

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

See also

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

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

14 - GRANT (TLS configuration)

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

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:

tls_configuration
The target TLS Configuration.
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-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.

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

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;

See also

16 - GRANT (view)

Grants view privileges to users and roles.

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:

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 view ship.

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

See also

REVOKE (view)

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.