Granting privileges for executing stored procedures and stored procedure packages

After you create a stored procedure, you need to grant EXECUTE privilege to users who plan to run the stored procedure and the stored procedure package. You can use the GRANT statement to grant the required privileges.

About this task

Invoking a stored procedure requires the EXECUTE privilege on the stored procedure. For external stored procedures (including external SQL procedures), additional authority is needed for the stored procedure package and for most packages that run in the stored procedure.

To grant privileges for executing stored procedures and stored procedure packages:

1. Issue the SQL GRANT statement with the EXECUTE ON PROCEDURE clause to the appropriate authorization ID or role.
   • To grant the EXECUTE privilege to an authorization ID, use the GRANT statement with the EXECUTE ON PROCEDURE clause. For example, to grant EXECUTE privilege for a stored procedure named SPNAME to a user whose authorization ID is PAOLORW, you can issue the following statement:

     GRANT EXECUTE ON PROCEDURE SPNAME TO PAOLORW;

   • To grant the EXECUTE privilege to a role, use the GRANT statement with the EXECUTE ON PROCEDURE clause and the ROLE clause. For example, to grant EXECUTE privilege for a stored procedure named SPNAME to a role named ADMINISTRATOR, you can issue the following statement:

     GRANT EXECUTE ON PROCEDURE SPNAME TO ROLE ADMINISTRATOR;

   The DYNAMICRULES behavior for the plan or package that contains the CALL statement determines which authorization ID or role holds the privilege.
2. Issue the SQL GRANT EXECUTE ON PACKAGE statement with the appropriate options, depending on whether you are granting the privilege to an authorization ID or a role:
   • To grant the EXECUTE privilege on the package to an authorization ID, issue the GRANT statement with the EXECUTE ON PACKAGE clause. For example, to grant the privilege to execute a package named PKGNAME to a user whose authorization ID is PAOLORW, you can issue this statement:

     GRANT EXECUTE ON PACKAGE PKGNAME TO PAOLORW;

   • To grant the EXECUTE privilege on the package to a role, issue the GRANT statement with the EXECUTE ON PACKAGE clause and the ROLE clause. For example, to grant this privilege to execute a package named PKGNAME to a role named ADMINISTRATOR, you can issue this statement:

     GRANT EXECUTE ON PACKAGE PKGNAME TO ROLE ADMINISTRATOR;

   The complete syntax of the GRANT statement that you should use depends on the type of package.