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.||
|ALTER EXTERNAL PROCEDURE and CREATE EXTERNAL PROCEDURE||user DBC implicitly.||user DBC.|
|other users explicitly.||
|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.||
Usage Notes for Procedure-Specific Privileges
- 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 “ALTER PROCEDURE (External Form)” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.
CREATE EXTERNAL PROCEDURE Privilege
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.
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 “CREATE PROCEDURE (External Form)/REPLACE PROCEDURE (External Form)” in 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;