0124AUTHID CURRENT_USER和AUTHID DEFINER
AUTHID CURRENT_USER和AUTHID DEFINER.txt
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
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
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.
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 22.214.171.124.0 Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production
SCOTT@book> show user
USER is "SCOTT"
SCOTT@book> SELECT COUNT(*) FROM user_tables;
CREATE OR REPLACE FUNCTION get_count RETURN NUMBER AUTHID DEFINER IS
SELECT COUNT(*) INTO table_count FROM user_tables;
CREATE OR REPLACE FUNCTION get_count2 RETURN NUMBER AUTHID CURRENT_USER IS
SELECT COUNT(*) INTO table_count FROM user_tables;
grant execute on get_count to system;
grant execute on get_count2 to system;
SYSTEM@book> show user
USER is "SYSTEM"
SYSTEM@book> SELECT COUNT(*) FROM user_tables;
SYSTEM@book> SELECT scott.get_count FROM dual;
SYSTEM@book> SELECT scott.get_count2 FROM dual;
--// 调用get_count函数使用AUTHID CURRENT_USER,而当前用户是system,所以返回169条.
SCOTT@book> select get_count from dual ;
SCOTT@book> select get_count2 from dual ;