那款捕鱼爆率高

書名： Oracle Database 12c Security Cookbook
作者名： Zoran Pavlovi? Maja Veselica
本章字數： 441字
更新時間： 2021-07-02 16:43:17

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...

Connect to the database as a user with the DBA role (for example, zoran)
```
SQL> connect zoran
```

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;

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 table zoran.tbl, and grant execute on this procedure to user procuser:

SQL> connect procowner/oracle1
CREATE OR REPLACE PROCEDURE UpdateTbl (x IN number, y IN varchar2)
 AUTHID DEFINER
 AS
 BEGIN
 UPDATE ZORAN.TBL
 SET b = y
 WHERE a = x;
 END;
 /
SQL> grant execute on UpdateTbl to procuser;

Connect as user procuser and try to directly update table zoran.tbl:

SQL> connect procuser/oracle2
SQL> UPDATE ZORAN.TBL SET B = 'value1' WHERE A = 1;
UPDATE ZORAN.TBL SET B = 'value1' WHERE A = 1
 *
ERROR at line 1:
ORA-01031: insufficient privileges

When the previous step fails, update table by using the UpdateTbl procedure:

SQL> EXEC procowner.UpdateTbl(1, 'new_value');
PL/SQL procedure successfully completed.

Check whether the table is updated:

SQL> select * from zoran.tbl;
 A B
---------- ----------------------------------------
 1 new_value

How it works...

Definer's rights procedures are executed by using privileges that are granted to the owner of the procedure. In our example, we have two users: procowner - a user who is the owner of the procedure and has privilege to update table zoran.tbl and procuser - a user who just executes the procedure. In step 4, procuser creates procedure by using the AUTHID DEFINER clause, which means that this procedure will be definer's rights procedure. This is a default behavior (we can omit the AUTHID DEFINER clause). In step 5, procuser tries to update table zoran.tbl directly, but it gets an error:

SQL> UPDATE ZORAN.TBL SET B = 'value1' WHERE A = 1;
UPDATE ZORAN.TBL SET B = 'value1' WHERE A = 1
 *
ERROR at line 1:
ORA-01031: insufficient privileges

This is the expected behavior, considering that procuser doesn't have an update privilege on zoran.tbl. When procuser executes the procedure in step 6, the table is updated because the privilege of the definer is applied.

官术网_书友最值得收藏!

Oracle Database 12c Security Cookbook

Creating and using definer's rights procedures

Getting ready

How to do it...

How it works...