16.20 - CREATE PROCEDURE and REPLACE PROCEDURE (SQL Form) - Teradata Database - Teradata Vantage NewSQL Engine

Purpose

Directs the SQL procedure compiler to create or replace a procedure from the SQL statements in the remainder of the source text and creates the SQL definition used to invoke the procedure.

REPLACE PROCEDURE directs the SQL procedure compiler to replace the definition of an existing SQL procedure.

If the specified SQL procedure does not exist, REPLACE PROCEDURE creates a new procedure by that name from the SQL statements in the remainder of the source text.

Required Privileges for CREATE PROCEDURE

You must have the CREATE PROCEDURE privilege on the containing database or user for the procedure. The creator of an SQL procedure is automatically granted the DROP PROCEDURE and EXECUTE privileges WITH GRANT OPTION.

Teradata Database checks SQL procedure privileges differently depending on the definition of the SQL SECURITY clause option for the procedure.

Note that you cannot specify the OWNER option for the SQL SECURITY clause unless you have the explicitly granted CREATE OWNER PROCEDURE privilege to permit you to create an SQL procedure in a database or user other than your default database or user. This is an extremely important privilege for the Security Administrator to safeguard, ensuring that only those users who absolutely require the privilege be granted it.

For more information, see Rules for SQL Procedure Privileges.

Required Privileges for REPLACE PROCEDURE

You must have the DROP PROCEDURE privilege on the procedure or its containing database or user to replace it.

You must have the CREATE PROCEDURE privilege on the procedure or its containing database or user if it does not already exist.

The user replacing a procedure must have the privileges for all objects it accesses.

Once a procedure has been replaced, its immediate owner is its containing database or user, not the user who replaced it. The immediately owning database must have all the appropriate privileges for executing the procedure, including WITH GRANT OPTION.

Privileges Granted Automatically

When you create a new procedure, Teradata Database automatically grants the following privileges on it.

• DROP PROCEDURE
• EXECUTE PROCEDURE

Syntax