Privileges for PL/SQL Objects

Authorization in PL/SQL requires certain privileges.

• Privileges for PL/SQL Statements and Operations

• Invalidated Objects

• Definer's Rights and Invoker's Rights (AUTHID clause)

Note:

Also, see Privileges for SQL Objects.

Privileges for PL/SQL Statements and Operations

There are required privileges for PL/SQL statements and operations.

• Overview of Privileges for PL/SQL Statements and Operations

• Privileges Reference for PL/SQL Statements and Operations

• Granting Privileges for PL/SQL Statements and Operations

Overview of Privileges for PL/SQL Statements and Operations

For PL/SQL users, authorization through the granting of privileges is necessary to enable a user to create, alter, drop, or execute PL/SQL procedures and functions, including packages and their member procedures and functions.

You need the CREATE PROCEDURE privilege to create a procedure, function, package definition, or package body if it is being created in your own schema, or CREATE ANY PROCEDURE if it is being created in any other schema. To alter or drop a procedure, function, package definition, or package body, you must be the owner or have the ALTER ANY PROCEDURE privilege or DROP ANY PROCEDURE privilege, respectively.

For a user to execute PL/SQL functions, PL/SQL procedures or PL/SQL packages that they do not own, they must be granted the EXECUTE object privilege for the procedure or function or for the package to which it belong, or granted EXECUTE ANY PROCEDURE. When you grant a user EXECUTE privilege on a package, this automatically grants EXECUTE privilege on its component procedures and functions.

EXECUTE privilege authorizes the following:

• Execute the procedure or function.

• Access any program object declared in the specification of a package.

• Compile the object implicitly during a call to a currently invalid or uncompiled function or procedure.

To explicitly compile using ALTER PROCEDURE or ALTER FUNCTION, the user must be granted the ALTER ANY PROCEDURE system privilege.

Privileges Reference for PL/SQL Statements and Operations

There are required privileges for PL/SQL statements and operations.

Required privileges for PL/SQL statements and operations are summarized in Table 2-2.

Table 2-2 Privileges for Using PL/SQL Procedures and Functions

Action	SQL Statement or Operation	Required Privilege
Create a procedure, function, package definition, or package body.	CREATE [OR REPLACE] PROCEDURE CREATE [OR REPLACE] FUNCTION CREATE [OR REPLACE] PACKAGE CREATE [OR REPLACE] PACKAGE BODY	CREATE PROCEDURE in user's schema Or: CREATE ANY PROCEDURE in any other schema
Alter a procedure, function, or package.	ALTER PROCEDURE ALTER FUNCTION ALTER PACKAGE	Ownership of the procedure, function, or package Or: ALTER ANY PROCEDURE
Drop a procedure, function, package definition, or package body.	DROP PROCEDURE DROP FUNCTION DROP PACKAGE DROP PACKAGE BODY	Ownership of the procedure, function, or package Or: DROP ANY PROCEDURE
Execute a procedure or function.	Invoke the procedure or function.	Ownership of the procedure or function, or of the package to which it belongs (if applicable) Or: EXECUTE for the procedure or function, or for the package to which it belongs (if applicable) Or: EXECUTE ANY PROCEDURE

Granting Privileges for PL/SQL Statements and Operations

You can grant and then revoke EXECUTE privilege to user2 for a procedure and a package that user1 owns.

Command> grant execute on user1.myproc to user2;
Command> grant execute on user1.mypkg to user2;
...
Command> revoke execute on user1.myproc from user2;
Command> revoke execute on user1.mypkg from user2;

Note:

• A user who has been granted privilege to execute a procedure (or function) can execute the procedure even without privileges on other procedures that the procedure calls. For example, consider a stored procedure user2.proc1 that executes procedure user2.proc2. If user1 is granted privilege to execute proc1 but is not granted privilege to execute proc2, the user could not run proc2 directly but could still run proc1.

• Privilege to execute a procedure or function allows implicit compilation of the procedure or function if it is invalid or not compiled at the time of execution.

• To invoke a procedure or function through a synonym, a user must have privilege to execute the underlying procedure or function.

• A SQL statement executed in PL/SQL requires the same privilege as when executed directly.

• EXECUTE ANY PROCEDURE does not apply to TimesTen supplied packages; however, most are accessible through the PUBLIC role.

The following example shows a series of attempted operations by a user, user1, as follows:

1. The user attempts each operation before having the necessary privilege. The resulting error is shown.

2. The instance administrator grants the necessary privilege.

3. The user successfully performs the operation.

The ttIsql utility is used by user1 to perform (or attempt) the operations and by the instance administrator to grant privileges.

user1:

Initially the user does not have permission to create a procedure. That must be granted even in the user's own schema.

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE PROCEDURE
The command failed.

Instance administrator:

Command> grant create procedure to user1;

user1:

Once user1 can create a procedure in the user1 schema, that user owns it and can execute it.

Command> create procedure testproc is
         begin
         dbms_output.put_line('user1.testproc called');
         end;
         /
 
Procedure created.
 
Command> begin
         testproc();
         end;
         /
user1.testproc called
 
PL/SQL procedure successfully completed.

The user cannot yet create a procedure in another schema, though.

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
15100: User USER1 lacks privilege CREATE ANY PROCEDURE
The command failed.

user1:

Now user1 can create a procedure in another schema, but cannot execute it without owning it or having necessary privilege.

Instance administrator:

Command> grant create any procedure to user1;

Command> create procedure user2.testproc is
         begin
         dbms_output.put_line('user2.testproc called');
         end;
         /
 
Procedure created.

Command> begin
         user2.testproc();
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00904: insufficient privilege to access object USER2.TESTPROC
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

Instance administrator:

Command> grant execute any procedure to user1;

user1:

Now user1 can execute a procedure in another schema.

Command> begin
         user2.testproc();
         end;
         /
user2.testproc called
 
PL/SQL procedure successfully completed.

Invalidated Objects

When a privilege on an object is revoked from a user, all of that user's PL/SQL objects that refer to that object are temporarily invalidated. Once the privilege has been restored, a user can explicitly recompile and revalidate an object by executing ALTER PROCEDURE, ALTER FUNCTION, or ALTER PACKAGE, as applicable, on the object. Alternatively, each object is recompiled and revalidated automatically the next time it is executed.

For example, if user1 has a procedure user1.proc0 that calls user2.proc1, proc0 becomes invalid if EXECUTE privilege for proc1 is revoked from user1.

Use the following to see if any of your objects are invalid:

select * from user_objects where status='INVALID';

This example shows a series of actions resulting in an invalidated PL/SQL procedure:

1. A user is granted CREATE ANY PROCEDURE privilege, creates a procedure in another user's schema, then creates a procedure in their own schema that calls the procedure in the other user's schema.

2. The user is granted EXECUTE privilege to execute the procedure in the other user's schema.

3. The user executes the procedure in their schema that calls the procedure in the other user's schema.

4. EXECUTE privilege for the procedure in the other user's schema is revoked from the user, invalidating the user's own procedure.

5. EXECUTE privilege for the procedure in the other user's schema is granted to the user again. When the user executes their own procedure, it is implicitly recompiled and revalidated.

Administrative user:

Command> grant create any procedure to user1;

user1:

Command> create procedure user2.proc1 is
         begin
         dbms_output.put_line('user2.proc1 is called');
         end;
         /
 
Procedure created.
 
Command> create procedure user1.proc0 is
         begin
         dbms_output.put_line('user1.proc0 is called');
         user2.proc1;
         end;
         /
 
Procedure created.

Administrative user:

Command> grant execute on user2.proc1 to user1;

user1:

Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.

And to confirm user1 has no invalid objects:

Command> select * from user_objects where status='INVALID';
0 rows found.

Administrative user:

Now revoke the EXECUTE privilege from user1.

Command> revoke execute on user2.proc1 from user1;

user1:

Immediately, user1.proc0 becomes invalid because user1 no longer has privilege to execute user2.proc1.

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2021-06-04 14:51:34, 2021-06-04 14:58:23,
2021-06-04:14:58:23, INVALID, N, N, N, 1, <NULL> >
1 row found.

So user1 can no longer execute the procedure.

Command> begin
         user1.proc0;
         end;
         /
 8503: ORA-06550: line 2, column 7:
PLS-00905: object USER1.PROC0 is invalid
 8503: ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
The command failed.

Administrative user:

Again grant EXECUTE privilege on user2.proc1 to user1.

Command> grant execute on user2.proc1 to user1;

user1:

The procedure user1.proc0 is still invalid until it is either explicitly or implicitly recompiled. It is implicitly recompiled when it is executed, as shown here. Or ALTER PROCEDURE could be used to explicitly recompile it.

Command> select * from user_objects where status='INVALID';
< PROC0, <NULL>, 273, <NULL>, PROCEDURE, 2021-06-04 14:51:34, 2021-06-04 16:13:00,
2021-06-04:16:13:00, INVALID, N, N, N, 1, <NULL> >
1 row found.
Command> begin
         user1.proc0;
         end;
         /
user1.proc0 is called
user2.proc1 is called
 
PL/SQL procedure successfully completed.
 
Command> select * from user_objects where status='INVALID';
0 rows found.

Definer's Rights and Invoker's Rights (AUTHID clause)

When a PL/SQL procedure or function is defined, the optional AUTHID clause of the CREATE FUNCTION or CREATE PROCEDURE statement specifies whether the function or procedure executes with definer's rights (AUTHID DEFINER, the default) or invoker's rights (AUTHID CURRENT_USER).

The AUTHID setting affects the name resolution and privilege checking of SQL statements that a procedure or function issues at runtime. With definer's rights, SQL name resolution and privilege checking operate as though the owner of the procedure or function (the definer, in whose schema it resides) is running it. With invoker's rights, SQL name resolution and privilege checking simply operate as though the current user (the invoker) is running it.

For procedures or functions in a package, the AUTHID clause of the CREATE PACKAGE statement specifies whether each member function or procedure of the package executes with definer's rights or invoker's rights. The AUTHID clause is shown in the syntax documentation for these statements, under SQL Statements in Oracle TimesTen In-Memory Database SQL Reference.

Invoker's rights would be useful in a scenario where you might want to grant broad privileges for a body of code, but would want that code to affect only each user's own objects in the user's own schema.

Definer's rights would be useful in a situation where you want all users to have access to the same centralized tables or other SQL objects, but only for the specific and limited actions that are executed by the procedure. The users would not have access to the SQL objects otherwise.

Refer to Invoker's Rights and Definer's Rights (AUTHID Property) in Oracle Database PL/SQL Language Reference.

The following example runs a script twice in ttIsql with just one change, first defining a PL/SQL procedure with AUTHID CURRENT_USER for invoker's rights, then with AUTHID DEFINER for definer's rights.

Script for AUTHID examples:

The script assumes three users have been created: a tool vendor and two tool users (brandX and brandY). Each has been granted CREATE SESSION, CREATE PROCEDURE, and CREATE TABLE privileges as necessary. The following setup is also assumed, to allow "use username;" syntax to connect to the database as username.

connect adding "uid=toolVendor;pwd=pw" as toolVendor;
connect adding "uid=brandX;pwd=pw" as brandX;
connect adding "uid=brandY;pwd=pw" as brandY;

The script does the following:

• Creates the procedure, printInventoryStatistics, as the tool vendor.

• Creates a table with the same name, myInventory, in each of the three user schemas, populating it with unique data in each case.

• Runs the procedure as each of the tool users.

The different results between the two executions of the script show the difference between invoker's rights and definer's rights.

Following is the script for the invoker's rights execution.

use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);

create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
set serveroutput on
execute toolVendor.printInventoryStatistics;
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
set serveroutput on
execute toolVendor.printInventoryStatistics; 

The only difference for the definer's rights script is the change in the AUTHID clause for the procedure definition.

...
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
...

Using AUTHID CURRENT_USER:

This part shows the results when the procedure is defined with invoker's rights. Note that when the tool users brandX and brandY run the printInventoryStatistics procedure, each sees the data in the myInventory table as the invoker.

Command> run invoker.sql
 
use toolVendor;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid current_user is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/

Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
toothpaste 100
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
shampoo 10
 
PL/SQL procedure successfully completed.

Use the following to terminate all the connections:

Command> disconnect all;

Using AUTHID DEFINER:

This part shows the results when the procedure is defined with definer's rights. Note that when the tool users brandX and brandY run printInventoryStatistics, each sees the data in myInventory belonging to the tool vendor (the definer).

Command> run definer.sql
 
use toolVendor;

create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('butter', 1);
1 row inserted.
 
create or replace procedure printInventoryStatistics authid definer is
 inventoryCount pls_integer;
begin
 select count(*) into inventoryCount from myInventory;
 dbms_output.put_line('Total items in inventory: ' || inventoryCount);
 for currentItem in (select * from myInventory) loop
   dbms_output.put_line(currentItem.name || ' ' || currentItem.inventoryCount);
 end loop;
end;
/
 
Procedure created.
 
grant execute on printInventoryStatistics to brandX;
grant execute on printInventoryStatistics to brandY;
 
use brandX;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('toothpaste', 100);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.
 
use brandY;
create table myInventory (name varchar2(100), inventoryCount tt_integer);
insert into myInventory values('shampoo', 10);
1 row inserted.
set serveroutput on;
 
execute toolVendor.printInventoryStatistics;
Total items in inventory: 1
butter 1
 
PL/SQL procedure successfully completed.

In this case, it is also instructive to see that although brandX and brandY can each access the toolVendor.myInventory table through the procedure, they cannot access it directly. That is a key use of definer's rights, to enable specific and restricted access to a table or other SQL object through the actions of a procedure.

Command> use brandX;
brandx: Command> select * from toolVendor.myInventory;
15100: User BRANDX lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

brandx: Command> use brandY;
brandy: Command> select * from toolVendor.myInventory;
15100: User BRANDY lacks privilege SELECT on TOOLVENDOR.MYINVENTORY
The command failed.

When finished, terminate all the connections:

Command> disconnect all;