Oracle Database 12c Security Cookbook

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'); ...