You want to grant someone the ability to create and execute stored PL/SQL programs.
To grant the ability for a user to create a procedure, function, or package, you must log in to the Oracle 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.