0124AUTHID CURRENT_USER和AUTHID DEFINER

[20170124]AUTHID CURRENT_USER和AUTHID DEFINER.txt

--前幾天寫一個package腳本腳本遇到的問題,裡面要通路all_tab_col_statistics視圖,遇到一些問題,才想起以前也犯過類似錯誤,連結

1.相關連結:

<a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00809">http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/subprograms.htm#LNPLS00809</a>

Invoker's Rights and Definer's Rights (AUTHID Property)

The AUTHID property of a stored PL/SQL unit affects the name resolution and privilege checking of SQL statements that

the unit issues at run time. The AUTHID property does not affect compilation, and has no meaning for units that have no

code, such as collection types.

AUTHID property values are exposed in the static data dictionary view *_PROCEDURES. For units for which AUTHID has

meaning, the view shows the value CURRENT_USER or DEFINER; for other units, the view shows NULL.

For stored PL/SQL units that you create or alter with the following statements, you can use the optional AUTHID clause

to specify either DEFINER (the default) or CURRENT_USER:

"CREATE FUNCTION Statement"

"CREATE PACKAGE Statement"

"CREATE PROCEDURE Statement"

"CREATE TYPE Statement"

"ALTER TYPE Statement"

A unit whose AUTHID value is CURRENT_USER is called an invoker's rights unit, or IR unit. A unit whose AUTHID value is

DEFINER is called a definer's rights unit, or DR unit. An anonymous block always behaves like an IR unit. A trigger or

view always behaves like a DR unit.

The AUTHID property of a unit determines whether the unit is IR or DR, and it affects both name resolution and privilege

checking at run time:

    The context for name resolution is CURRENT_SCHEMA.

    The privileges checked are those of the CURRENT_USER and the enabled roles.

When a session starts, CURRENT_SCHEMA has the value of the schema owned by SESSION_USER, and CURRENT_USER has the same

value as SESSION_USER. (To get the current value of CURRENT_SCHEMA, CURRENT_USER, or SESSION_USER, use the SYS_CONTEXT

function, documented in Oracle Database SQL Language Reference.)

CURRENT_SCHEMA can be changed during the session with the SQL statement ALTER SESSION SET CURRENT_SCHEMA. CURRENT_USER

cannot be changed programmatically, but it might change when a PL/SQL unit or a view is pushed onto, or popped from, the

call stack.

Note:

Oracle recommends against issuing ALTER SESSION SET CURRENT_SCHEMA from in a stored PL/SQL unit.

During a server call, when a DR unit is pushed onto the call stack, the database stores the currently enabled roles and

the current values of CURRENT_USER and CURRENT_SCHEMA. It then changes both CURRENT_USER and CURRENT_SCHEMA to the owner

of the DR unit, and enables only the role PUBLIC. (The stored and new roles and values are not necessarily different.)

When the DR unit is popped from the call stack, the database restores the stored roles and values. In contrast, when an

IR unit is pushed onto, or popped from, the call stack, the values of CURRENT_USER and CURRENT_SCHEMA, and the currently

enabled roles do not change.

For dynamic SQL statements issued by a PL/SQL unit, name resolution and privilege checking are done once, at run time.

For static SQL statements, name resolution and privilege checking are done twice: first, when the PL/SQL unit is

compiled, and then again at run time. At compilation time, the AUTHID property has no effect—both DR and IR units are

treated like DR units. At run time, however, the AUTHID property determines whether a unit is IR or DR, and the unit is

treated accordingly.

--我的了解:

AUTHID CURRENT_USER 表示以目前使用者來調用視圖.

AUTHID DEFINER      表示以定義者來調用視圖.

By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user.

Suchdefiner's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the

same schema without qualifying their names. For example, if schemas HR and OEboth have a table called departments, a

procedure owned by HR can refer to departments rather than HR.departments. If user OE calls HR's procedure, the

procedure still accesses the departments table owned by HR.

A more maintainable way is to use the AUTHID clause, which makes stored procedures and SQL methods execute with the

privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call

it to access their own data.

2.環境:

SCOTT@book&gt; @ &amp;r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book&gt; show user

USER is "SCOTT"

SCOTT@book&gt; SELECT COUNT(*)  FROM user_tables;

  COUNT(*)

----------

         8

--//8條記錄.

CREATE OR REPLACE FUNCTION get_count RETURN NUMBER AUTHID DEFINER IS

  table_count NUMBER;

BEGIN

  SELECT COUNT(*) INTO table_count FROM user_tables;

  RETURN table_count;

END;

/

CREATE OR REPLACE FUNCTION get_count2 RETURN NUMBER AUTHID CURRENT_USER IS

grant execute on get_count to system;

grant execute on get_count2 to system;

3.以system使用者執行:

SYSTEM@book&gt; show user

USER is "SYSTEM"

SYSTEM@book&gt; SELECT COUNT(*)  FROM user_tables;

       169

SYSTEM@book&gt; SELECT scott.get_count FROM dual;

GET_COUNT

SYSTEM@book&gt; SELECT scott.get_count2 FROM dual;

GET_COUNT2

--//說明:調用get_count函數使用AUTHID DEFINER,而函數定義者是scott,是以傳回8條記錄.

--//     調用get_count函數使用AUTHID CURRENT_USER,而目前使用者是system,是以傳回169條.

--//如果以scott使用者登入,兩個執行結果應該一樣.

SCOTT@book&gt; select get_count from dual ;

SCOTT@book&gt; select get_count2 from dual ;