In this recipe, you'll learn to create and use definer's rights procedures.
Connect to the database as a user with the DBA role (for example,
zoran
)SQL> connect zoran
Create two users (
procowner
andprocuser
) 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;
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;
Connect as a user,
procowner
, create a procedure to update tablezoran.tbl
, and grantexecute
on this procedure...