Privileges for PL/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
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. |
|
Or:
|
Alter a procedure, function, or package. |
|
Ownership of the procedure, function, or package Or:
|
Drop a procedure, function, package definition, or package body. |
|
Ownership of the procedure, function, or package Or:
|
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:
Or:
|
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 procedureuser2.proc2
. Ifuser1
is granted privilege to executeproc1
but is not granted privilege to executeproc2
, the user could not runproc2
directly but could still runproc1
. -
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 thePUBLIC
role.
The following example shows a series of attempted operations by a user, user1
, as follows:
-
The user attempts each operation before having the necessary privilege. The resulting error is shown.
-
The instance administrator grants the necessary privilege.
-
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
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:
-
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. -
The user is granted
EXECUTE
privilege to execute the procedure in the other user's schema. -
The user executes the procedure in their schema that calls the procedure in the other user's schema.
-
EXECUTE
privilege for the procedure in the other user's schema is revoked from the user, invalidating the user's own procedure. -
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;