Case Study: SQL Procedures - Analytics Database - Teradata Vantage

The following GRANT request must be submitted so that user_1 can create SQL procedures in the accounting database:

     GRANT CREATE PROCEDURE
     ON accounting
     TO user_1;

To grant CREATE PROCEDURE, DROP PROCEDURE, and EXECUTE PROCEDURE privileges to user_1 on the accounting database, submit the following GRANT request:

     GRANT CREATE PROCEDURE, EXECUTE PROCEDURE, DROP PROCEDURE
     ON accounting
     TO user_1;

In a GRANT request, the keyword PROCEDURE without CREATE or DROP confers both CREATE PROCEDURE and DROP PROCEDURE privileges at the user or database level.

The following example assumes that the grantor has the privileges required to grant CREATE PROCEDURE and DROP PROCEDURE privileges to user_1 on the accounting database:

     GRANT PROCEDURE
     ON accounting
     TO user_1;

Assume user_1 needs the EXECUTE PROCEDURE privilege WITH GRANT OPTION on the procedure daily_updates in the database accounting. Assume the grantor has the EXECUTE PROCEDURE WITH GRANT OPTION privilege on the procedure or its containing database. The following GRANT request must be submitted:

     GRANT EXECUTE ON PROCEDURE accounting.daily_updates
     TO user_1
     WITH GRANT OPTION;

This request can also be specified using the following syntax:

     GRANT EXECUTE PROCEDURE ON accounting.daily_updates
     TO user_1
     WITH GRANT OPTION;

The following GRANT request returns an error because the syntax is valid only for macros, and daily_update is not a macro:

     GRANT EXECUTE ON accounting.daily_update
     TO user_1
     WITH GRANT OPTION;

To grant ALTER PROCEDURE, EXECUTE PROCEDURE, and DROP PROCEDURE privileges to user_1 on the SQL procedure weekly_update, the necessary GRANT request looks like either of these requests, assuming the grantor has the privileges to perform the grant:

     GRANT ALTER PROCEDURE, EXECUTE, DROP PROCEDURE
     ON PROCEDURE weekly_update
     TO user_1;

     GRANT ALL ON PROCEDURE accounting.weekly_update
     TO user_1;