Creating and using invoker's right procedures

In this recipe, you'll learn to create and use invoker's rights procedures. They can be useful when creating PL/SQL procedures in a highly privileged schema (because in this case, it is more secure to grant specific privileges to the invoker). Also, when there is no SQL code in the PL/SQL procedure and the procedure is available to other users, invoker's rights procedure will be executed more efficiently. There are no changes in the values of current schema and currently enabled roles during the execution (these changes are not necessary because without SQL in PL/SQL code, privilege checking is not performed).

Getting ready

To complete this recipe, you'll use a user who has the DBA role.

How to do it...

1. Connect to the database as a user with the DBA role (for example, zoran):

   SQL> connect zoran
   

2. Create two users (procuser1, procuser2) and grant them privileges:

   SQL> create user procuser1 identified by oracle1;
   SQL> create user procuser2 identified...