Using code-based access control

In this recipe, you'll use code base access control with invoker's rights procedure.

Getting ready

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

How to do it...

1. Connect to the database as a user with a DBA role (for example, zoran), create proc_user, and grant him the create session privilege:

   SQL> create user proc_user identified by oracle1;
   SQL> grant create session to proc_user;
   

2. Create table tbl1 and insert test data:

   SQL> create table tbl1(a number, b varchar2(30));
   SQL> insert into tbl1 values (1, 'old_value');
   SQL> commit;
   

3. Create the invoker's rights procedure UpdateTbl1 and grant execute on that procedure to proc_user:

   CREATE OR REPLACE PROCEDURE UpdateTbl1 (x IN number,
          y IN varchar2)
     AUTHID CURRENT_USER
       AS
         BEGIN
          UPDATE ZORAN.TBL1
          SET b = y
           WHERE a = x;
       END;
       /
   SQL> grant execute on zoran.UpdateTbl1 to proc_user;
   

4. Create the role proc_role...