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‑name( [arg‑list] )[,…] 
       | ALL FUNCTIONS IN SCHEMA schema[,…] }
   TO grantee[,…]
   [ WITH GRANT OPTION ]

Parameters

privilege

The following privileges are valid for user‑defined extensions:

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

Specifies the function's user-defined extension (UDx) type, where UDx‑type is one of the following:

  • FUNCTION
  • AGGREGATE FUNCTION
  • ANALYTIC FUNCTION
  • TRANSFORM FUNCTION
  • FILTER
  • PARSER
  • SOURCE
[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

If you specify a database, it must be the current database.
function‑name Name of the user-defined function on which to grant privileges.
ON ALL FUNCTIONS IN SCHEMA schema 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

Specifies who is granted privileges, one of the following:

Grantees must have USAGE privileges on the schema.

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