Case Study: SQL Procedures - Analytics Database - Teradata Vantage

Assume that user_1 is to be granted CREATE PROCEDURE privilege on the accounting database. Assume that the grantor is an owner of the database and that she has the explicit CREATE PROCEDURE and EXECUTE PROCEDURE privileges WITH GRANT OPTION on herself. This grant cannot be made unless one of the following conditions is true:

• The grantor has both the CREATE PROCEDURE and EXECUTE PROCEDURE privileges WITH GRANT OPTION on itself and on the user or database WITH GRANT OPTION.
• The grantor is user DBC.

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;

If the keyword PROCEDURE is specified without CREATE or DROP in a GRANT request, it 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 that user_1 needs to be granted EXECUTE PROCEDURE privilege WITH GRANT OPTION on the procedure daily_updates in the database accounting. Assume that the grantor has the EXECUTE PROCEDURE WITH GRANT OPTION privilege on the procedure or its containing database. The following GRANT request needs to 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;

Note that 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;