Privileges and Procedures - Teradata Vantage - Analytics Database

SQL Data Control Language
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2025-11-21
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 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

Privilege Level
  • CREATE PROCEDURE
  • CREATE EXTERNAL PROCEDURE
 Database or user
  • ALTER PROCEDURE
  • ALTER EXTERNAL PROCEDURE
  • DROP PROCEDURE
  • EXECUTE PROCEDURE
 Database, user, or specified procedure

You can use DROP and EXECUTE 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
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 run mode for a particular external procedure to run 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 run 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. Restrict this privilege to your most trusted programmers. Make sure the external procedure is thoroughly tested to verify that it does not compromise the system.

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