4-11. Granting the Ability to Create and Execute Stored Programs
Problem
You want to grant someone the ability to create and execute stored programs.
Solution
To grant the ability for a user to create a procedure, function, or package, you must log in to the database with a privileged account and grant the CREATE PROCEDURE privilege to the user. Here's an example:
GRANT CREATE PROCEDURE TO user;
Similarly, to grant permissions for execution of a procedure, package, or function, you must log in with a privileged account and grant the user EXECUTE permissions on a particular procedure, function, or package. Here's an example:
GRANT EXECUTE ON schema_name.program_name TO schema;
How It Works
Before a user can create stored code, the user must be given permission to do so. The solution shows the straightforward approach. The database administrator logs in and grants CREATE PROCEDURE to the schema owner. The schema owner can then log in and create stored code in their schema.
A schema owner can always execute stored code in the schema. However, application users do not generally log in as schema owners because of the security risks inherent in doing so. Thus, you will commonly be faced with the need to grant other users execute access on stored code. You do that by granting EXECUTE privileges, as shown in the second solution example.