7 Access Control for PL/SQL Programs

Example 7-1 Granting of required privileges

This 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 one'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 his own schema, he can execute it because he owns 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.

Instance administrator:

Command> grant create any procedure to user1;

USER1:

Now user1 can create a procedure in another schema, but he cannot execute it yet because he does not own it or have privilege.

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.

Example 7-2 Invalidated object

This example shows a sequence that results in an invalidated object, in this case a PL/SQL procedure, as follows:

1. A user is granted CREATE ANY PROCEDURE privilege, creates a procedure in another user's schema, then creates a procedure in his 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 his 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 he executes his own procedure, it is implicitly recompiled and revalidated.

Instance administrator:

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.
 

Instance administrator:

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.

Instance administrator:

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, 2009-06-04 14:51:34, 2009-06-04 14:58:23,
2009-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.

Instance administrator:

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, 2009-06-04 14:51:34, 2009-06-04 16:13:00,
2009-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.