Oracle: Granting the Ability to Create & Execute PL/SQL Stored Programs

Granting the Ability to Create & Execute PL/SQL

Problem

You want to grant someone the ability to create and execute stored PL/SQL programs.

Solution

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.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 1723 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 1376 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 1628 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 3307 views Гвен Sun, 03 Jun 2018, 11:54:40
Comments (0)
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Suggested Locations