GLOP Privileges | GRANT (SQL Form) | Teradata Vantage - 17.05 - GLOP Privileges - Teradata Database

Teradata Vantage™ - SQL Data Control Language

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1149-170K

Teradata Database does not grant any of the GLOP privileges automatically when a database or user is created. All GLOP privileges must be explicitly granted, even when the privileges have been granted by a user who possesses the WITH GRANT OPTION privilege.

There are three GLOP privileges:
  • CREATE GLOP enables the user to whom it is granted to perform the CREATE GLOP SET statement to create a new GLOP set.
  • DROP GLOP enables the user to whom it is granted to perform the DROP GLOP SET statement to drop an existing GLOP set. DROP GLOP is granted automatically to the creator and owner of a GLOP set.
  • GLOP MEMBER enables an external routine to access a GLOP set that is not contained within its containing user or database. Specifically, GLOP MEMBER enables the GLOP set clause referenced in the function, method, or procedure definition to use the GLOP set specified in the MEMBER OF GLOP SET clause.

You can also specify the keyword GLOP by itself to signify both the CREATE GLOP and DROP GLOP privileges. See Multiple Privileges with a Single Keyword.

Granting GLOP Privileges

The enterprise wants the sales_bonus UDF to access the special sales GLOP. User Joe creates both the UDF and the GLOP in SYSLIB. The following requests must be executed to make this happen:

A user WITH GRANT OPTION must execute the following GRANT request:

GRANT GLOP ON syslib TO Joe;

Assuming that Joe already has privileges to create a function in SYSLIB, Joe then executes the following requests:

Joe creates the GLOP set object first to enable a GLOP to be added.

CREATE GLOP SET syslib.sales;

Joe then calls the procedure GLOP_Add to add the sales GLOP and associates it with SYSLIB.

CALL DBCExtension.GLOP_Add('syslib.sales',...);

Finally, Joe creates the function sales_bonus in SYSLIB.

CREATE FUNCTION syslib.sales_bonus( … )
  RETURNS DECIMAL(6,2)
    LANGUAGE C
    MEMBER OF GLOP SET syslib.sales
    PARAMETER STYLE SQL
    EXTERNAL;

SYSLIB is a public database. For this reason, the Security Administrator instead decides to place the sales GLOP in the finance database, and to make the Finance department responsible for creating the GLOP data. She also wants the UDF to be contained within the finance database.

The following requests must be executed to make this happen:

A user WITH GRANT OPTION must execute the following GRANT request:

GRANT GLOP ON finance TO finance;

User finance must then execute the following requests:

CREATE GLOP SET sales;
CALL DBCExtension.GLOP_Add('sales', … );
GRANT GLOP MEMBER ON finance.sales TO finance;

User Joe creates the function in the finance database. Joe must first be granted the privilege to create the UDF in the finance database by someone with the privileges to enable him to do that:

CREATE FUNCTION finance.sales_bonus( … )
  RETURNS DECIMAL(6,2)
    LANGUAGE C
    MEMBER OF GLOP SET finance.sales
    PARAMETER STYLE SQL
    EXTERNAL;