AUTHID DEFINER\authid current_user與alter session set current_schema

denglt發表於2012-11-20
原文網址 : http://blog.itpub.net/195110/viewspace-749551/
Session
先看看Tom上的一個例子：
ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2     execute immediate 'alter session set current_schema=scott';
  3     for x in ( select sys_context('userenv','current_schema') x from dual )
  4     loop
  5          dbms_output.put_line( x.x );
  6     end loop;
  7 
  8 
  9     execute immediate 'alter session set current_schema=ops$tkyte';
10     for x in ( select sys_context('userenv','current_schema') x from dual )
11     loop
12          dbms_output.put_line( x.x );
13     end loop;
14  end;
15  /
SCOTT
OPS$TKYTE

PL/SQL procedure successfully completed.
Now, if your "block of code" is actually a stored procedure and it is further a DEFINERS RIGHTS procedure (the default kind) then expect this (logged in as ops$tkyte by the way):

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p
  2  as
  3  begin
  4     execute immediate 'alter session set current_schema=scott';
  5     for x in ( select sys_context('userenv','current_schema') x from dual )
  6     loop
  7          dbms_output.put_line( x.x );
  8     end loop;
  9 
10 
11     execute immediate 'alter session set current_schema=ops$tkyte';
12     for x in ( select sys_context('userenv','current_schema') x from dual )
13     loop
14          dbms_output.put_line( x.x );
15     end loop;
16  end;
17  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
OPS$TKYTE
OPS$TKYTE

PL/SQL procedure successfully completed.

you cannot change your identity in a definers rights procedure, it is fixed AT COMPILE time by definition (if you have my book "expert one on one", read the chapter on invoker vs definer rights). Now, try this:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p 2 AUTHID CURRENT_USER
  3  as
  4  begin
  5     execute immediate 'alter session set current_schema=scott';
  6     for x in ( select sys_context('userenv','current_schema') x from dual )
  7     loop
  8          dbms_output.put_line( x.x );
  9     end loop;
10 
11 
12     execute immediate 'alter session set current_schema=ops$tkyte';
13     for x in ( select sys_context('userenv','current_schema') x from dual )
14     loop
15          dbms_output.put_line( x.x );
16     end loop;
17  end;
18  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec p
SCOTT
OPS$TKYTE

PL/SQL procedure successfully completed.

可見對於DEFINERS RIGHTS procedure ，alter session set current_schema是無影響的；
對於authid current_user procedure將改變操作物件為current_schema指定的物件，預設current_schema為當前的呼叫者即current_user.
 
附：
CURRENT_SCHEMA 
Syntax: 

CURRENT_SCHEMA = schema 

The CURRENT_SCHEMA parameter changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another ALTER SESSION SET CURRENT_SCHEMA statement. 

This setting offers a convenient way to perform. operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session.
 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-749551/，如需轉載，請註明出處，否則將追究法律責任。

相關文章