Definer and Invoker Rights

To enable code to run with Invoker rights, an AUTHID clause needs to be used before the

IS or AS keyword in the routine header. The AUTHID clause tells Oracle whether the

routine is to be run with the invoker rights (CURRENT_USER), or with the Owner rights

(DEFINER). If you do not specify this clause, Oracle by default assumes it to be AUTHID DEFINER.

测试一：
conn a/a

CREATE TABLE APPPARMST
(PARCOD VARCHAR2(20) NOT NULL,
PARVAL VARCHAR2(200));

SQL> insert into appparmst values('updated by', null);

已创建 1 行。

SQL> commit;

提交完成。

create or replace procedure update_par(pi_parcod in varchar2,
pi_val in varchar2,
pio_status in out varchar2)
authid current_user
is
begin
pio_status := 'OK';
update appparmst
set parval = pi_val
where parcod = pi_parcod
and rownum = 1;
if sql%notfound then
pio_status := 'Error in resetting the parameter';
end if;
end;

SQL> grant execute on update_par to B;

Grant succeeded.

SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' || user, l_status);
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from a.appparmst;

PARCOD PARVAL
-------------------- ----------------------------------------------------
updated by User A

SQL> conn b/b
Connected.
SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' || user, l_status);
5 commit;
6 end;
7 /
declare
*
ERROR at line 1
ORA-00942 table or view does not exist
ORA-06512 at "A.UPDATE_PAR", line 6
ORA-06512 at line 4

SQL> DOC the error occurred because table APPPARMST does not exist for user B.
DOC> I create it for user B and then call update_par again

SQL> CREATE TABLE APPPARMST
2 (PARCOD VARCHAR2(20) NOT NULL,
3 PARVAL VARCHAR2(200));

Table created.

SQL> insert into appparmst values('updated by', null);

1 row created.

SQL> commit;

Commit complete.

SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' || user, l_status);
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select * from b.appparmst;

PARCOD PARVAL
-------------------- ---------------------------------------
updated by User B

测试二：
conn a/a
create or replace procedure update_par(pi_parcod in varchar2,
pi_val in varchar2,
pio_status in out varchar2)
is
begin
pio_status := 'OK';
update appparmst
set parval = pi_val
where parcod = pi_parcod
and rownum = 1;
if sql%notfound then
pio_status := 'Error in resetting the parameter';
end if;
end;

conn b/b
SQL> drop table appparmst;

表已删除。

SQL> declare
2 l_status varchar2(200);
3 begin
4 a.update_par('updated by', 'User ' || user, l_status);
5 commit;
6 end;
7 /

PL/SQL 过程已成功完成。
To enable code to run with Invoker rights, an AUTHID clause needs to be used before the

IS or AS keyword in the routine header. The AUTHID clause tells Oracle whether the

routine is to be run with the invoker rights (CURRENT_USER), or with the Owner rights

(DEFINER). If you do not specify this clause, Oracle by default assumes it to be AUTHID

DEFINER.

关于两者的定义：

（Definer rights
A routine stored in the database by default, is executed with the definer rights (owner

of the routine), depending on the user who calls it. This is a good way of having the

required code perform process logic in one place. It gives better control, preventing

direct access to objects that belong to another user, which might result in security

issues.

For example, table APPPARMST belongs to schema A. User A creates a procedure UPDATE_PAR

allowing for updates of a table. User B is granted execute privileges on the procedure.

Now user B cannot access the table as no privileges have been granted, but can call the

procedure to do the required process logic for updating the table.

Invoker Rights
Invoker rights is a new model for resolving references to database elements in a PL/SQL

program unit. From Oracle 8i onwards, we can decide if a program unit should run with

the authority of the definer or of the invoker. This means that multiple schemas,

accessing only those elements belonging to the invoker, can share the same piece of

code.

For example, let's take the above case. The table, APPPARMST, is created in schema B

also. Each of the schema will now own the same set of objects but different data, as

they are being used for different purposes. Since the called procedure, UPDATE_PAR, is

owned by User A, the ideal solution in Oracle 8 and earlier releases, was to compile it

in schema B also, so that it will use the objects thereof.

With Oracle 8i, there is no need for this duplication of code. A single compiled

program unit can be made to use schema A's objects when invoked by User A and schema

B's objects when invoked by User B. This way, we have the option of creating a code

repository in one place and sharing it with various production users. The owner of the

routine must grant EXECUTE privilege to other users. ）

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472991/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472991/