Privileges and Procedures - Analytics Database - Teradata Vantage

SQL Data Control Language
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
sgu1628111251052.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
dvv1472243528022
lifecycle
latest
Product Category
Teradata Vantage™

Granting Privileges on Procedures

The following table describes the privileges of various types of users or grantors with respect to procedure-specific privileges:

Privilege Granted to Granted By
ALTER PROCEDURE and CREATE PROCEDURE user DBC implicitly. user DBC.
other users, roles, databases, or PUBLIC explicitly.
  • user DBC.
  • an owner of the user or database where the owner has this privilege WITH GRANT OPTION on itself.
  • a database or user having this privilege WITH GRANT OPTION on the database or user for which the privilege is being granted.
ALTER EXTERNAL PROCEDURE and CREATE EXTERNAL PROCEDURE user DBC implicitly. user DBC.
other users explicitly.
  • user DBC.
  • a database or user having this privilege WITH GRANT OPTION on itself.
DROP PROCEDURE all users, roles, databases, or PUBLIC explicitly. default.
EXECUTE PROCEDURE or EXEC PROCEDURE user DBC implicitly. user DBC.
the creator of a procedure automatically. the creator.

Except for user DBC, owners do not implicitly have this privilege.

If the immediate owner of the procedure is different from its creator, the owner does not receive this privilege automatically.
other users, roles, databases, and PUBLIC explicitly.
  • user DBC.
  • an owner of the user or database where the owner has this privilege WITH GRANT OPTION on itself.
  • a database or user having this privilege WITH GRANT OPTION on the database or user for which the privilege is being granted.

Usage Notes for Procedure-Specific Privileges

The following rules apply to privileges specific to procedures:
  • CREATE PROCEDURE and CREATE EXTERNAL PROCEDURE are database- or user-level privilege only.
  • ALTER PROCEDURE, ALTER EXTERNAL PROCEDURE, DROP PROCEDURE, and EXECUTE PROCEDURE are allowed on databases, users, or specified procedures.
  • DROP and EXECUTE can be used as abbreviations for DROP PROCEDURE and EXECUTE PROCEDURE while granting privileges, if you specify the object type PROCEDURE as a qualifier for the procedure name.

If PROCEDURE is not specified before the object name:

Request Made Effect of the Request
GRANT EXECUTE Object is assumed to be a macro. If a macro by that name does not exist, an error is returned.
GRANT DROP Error is returned.

ALTER EXTERNAL PROCEDURE and CREATE EXTERNAL PROCEDURE Privileges

External procedures also require the ALTER EXTERNAL PROCEDURE and CREATE EXTERNAL PROCEDURE privileges. You need the ALTER EXTERNAL PROCEDURE privilege to use the ALTER PROCEDURE (External Form) statement.

The ALTER PROCEDURE (External Form) statement can be used to recompile existing external procedures. The purpose of the ALTER EXTERNAL PROCEDURE privilege is to enable DBAs to change the execution mode or to recompile an existing external procedure in situations where the current library is corrupt or the system has been reloaded. Do not grant this privilege to any user other than a DBA.

The ALTER EXTERNAL PROCEDURE privilege enables DBAs to use ALTER PROCEDURE (External Form) requests to change the execution mode for a particular external procedure to execute either directly in unprotected mode (EXECUTE NOT PROTECTED) or as a separate process in protected mode (EXECUTE PROTECTED).

ALTER EXTERNAL PROCEDURE is not an automatic privilege for a user when you create a database or user. The DBA retains the privilege (initially, only user DBC holds the privilege implicitly) and assigns it only for external procedures that are completely debugged and production certified, and can have their execution mode changed from EXECUTE PROTECTED to EXECUTE NOT PROTECTED.

Do not grant users the ability to perform the ALTER PROCEDURE (External Form) statement without considering the implications, because doing so could easily compromise the integrity of the system if the privilege is misused.

You can grant the ALTER EXTERNAL PROCEDURE privilege on either a specific external procedure or to an entire database or user.

See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

CREATE EXTERNAL PROCEDURE Privilege

Do not grant the CREATE EXTERNAL PROCEDURE privilege to any user unless they are explicitly assigned to code an external procedure for your site. Even then, you should restrict this privilege to only your most trusted programmers. You must also ensure that the external procedure is thoroughly tested to verify that it does not compromise the system in any way.

The system does not grant the CREATE EXTERNAL PROCEDURE privilege automatically when you create a user or database. Nor is CREATE EXTERNAL PROCEDURE granted on a database unless an owner explicitly has this privilege on itself WITH GRANT OPTION.

This functionality is different from the CREATE MACRO privilege, where the system grants CREATE MACRO automatically when you create a database or user, as well as the privilege held implicitly by an owner.

External procedures execute as part of the system when running in unprotected mode, while protected mode external procedures run in a separate process as an ordinary user named tdatuser.

For more information, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Example: Granting the CREATE EXTERNAL PROCEDURE Privilege

The following example grants user asst_dba the privilege to create an external procedure:

     GRANT CREATE EXTERNAL PROCEDURE
     ON DATABASE classify
     TO asst_dba;