Creating and using definer's rights procedures

In this recipe, you'll learn to create and use definer's rights procedures.

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 the DBA role (for example, zoran)

   SQL> connect zoran
   

2. Create two users (procowner and procuser) and grant them appropriate privileges:

   SQL> create user procowner identified by oracle1;
   SQL> create user procuser identified by oracle2;
   SQL> grant create session, create procedure to procowner;
   SQL> grant create session to procuser;
   

3. Create a table called zoran.tbl and grant users privileges on this table:

   SQL> create table zoran.tbl(a number, b varchar2(40));
   SQL> insert into zoran.tbl values(1, 'old_value');
   SQL> commit;
   SQL> grant select on zoran.tbl to procuser;
   SQL> grant update on zoran.tbl to procowner;
   

4. Connect as a user, procowner, create a procedure to update table zoran...