GRANT (User Defined Extension)

Grants privileges on a user-defined extension (UDx) to a database user or role. Optionally grants all privileges on the user-defined extension within one or more schemas. You can grant privileges on the following user-defined extension types:

Syntax

GRANT { EXECUTE | ALL } 
... ON FUNCTION [ schema.]function-name [, ...]
... | ON AGGREGATE FUNCTION [ schema.]function-name [, ...]
... | ON ANALYTIC FUNCTION [ schema.]function-name [, ...]
... | ON TRANSFORM FUNCTION [schema.]function-name [, ...]
... | ON FILTER [ schema.]filter-name [, ...]
... | ON PARSER [ schema.]parser-name [, ...]
... | ON SOURCE [ schema.]source-name [, ...]
... | ON ALL FUNCTIONS IN SCHEMA schema-name [, ...]
... ( [ argname ] argtype [, ...] )
... TO { username | role | PUBLIC } [, ...]

Parameters

{ EXECUTE | ALL }

The type of privilege to grant the UDx:

  • EXECUTE grants permission to call a user-defined extension.
  • ALL grants all privileges on the user-defined extension
 
schema

Specifies a schema. If multiple schemas are defined in the database, include the schema name. For example:

myschema.thisDbObject
 
function-name
filter-name
parser-name
source-name

The name of the UDx on which to grant the privilege. If you use more than one schema, you must specify the schema that contains the UDx, as noted in the previous row.

 
ON ALL FUNCTIONS IN SCHEMA

Grants privileges on all UDx's within one or more schemas to a user, role, or all users and roles.

 
argname

[Optional] The argument name for the UDx.

When you GRANT, REVOKE, or DROP privileges for a polymorphic function, you must include an argument with the command. 

argtype

The argument data type of the UDx. 

{ username | role | PUBLIC } [,...]

The recipient of the UDx privileges, which can be one or more users, one or more roles, or all users and roles (PUBLIC).

  • username - Indicates a specific user
  • role - Specifies a particular role
  • PUBLIC - Indicates that all users and roles have granted privileges to the UDx.

Privileges

Only a superuser and owner can grant privileges on a UDx. To grant privileges to a specific schema UDx or to all UDx's within one or more schemas, grantees must have USAGE privileges on the schema. See GRANT (Schema).

Examples

The following command grants EXECUTE privileges on the myzeroifnull SQL function to users Bob and Jules, and to the Operator role. The function takes one integer argument:

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

The following command grants EXECUTE privileges on all functions in the zero-schema schema to user Bob:

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

The following command grants EXECUTE privileges on the tokenize transform function to user Bob and to the Operator role:

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

The following command grants 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;

The next command grants ALL privileges on the ExampleSource() source to user Alice:

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

The following command grants ALL privileges on the polymorphic function Pagerank to the dbadmin role:

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

See Also