0124AUTHID CURRENT_USER和AUTHID DEFINER

最新推荐文章于 2024-03-10 13:22:45 发布

cuixun7780

最新推荐文章于 2024-03-10 13:22:45 发布

阅读量104

点赞数

[20170124]AUTHID CURRENT_USER和AUTHID DEFINER.txt

--前几天写一个package脚本脚本遇到的问题,里面要访问all_tab_col_statistics视图,遇到一些问题,才想起以前也犯过类似错误,链接
--http://blog.itpub.net/267265/viewspace-2081042/,重新看看官方文档,学习相关语法.

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.

--直接抄别人的例子:http://www.2cto.com/database/201312/261221.html

2.环境:
SCOTT@book> @ &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> show user
USER is "SCOTT"

SCOTT@book> 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
table_count NUMBER;
BEGIN
SELECT COUNT(*) INTO table_count FROM user_tables;
RETURN table_count;
END;
/

grant execute on get_count to system;
grant execute on get_count2 to system;

3.以system用户执行:
SYSTEM@book> show user
USER is "SYSTEM"

SYSTEM@book> SELECT COUNT(*) FROM user_tables;
COUNT(*)
----------
169

SYSTEM@book> SELECT scott.get_count FROM dual;

GET_COUNT
----------
8

SYSTEM@book> SELECT scott.get_count2 FROM dual;
GET_COUNT2
----------
169

--//说明:调用get_count函数使用AUTHID DEFINER,而函数定义者是scott,所以返回8条记录.
--// 调用get_count函数使用AUTHID CURRENT_USER,而当前用户是system,所以返回169条.

--//如果以scott用户登录,两个执行结果应该一样.

SCOTT@book> select get_count from dual ;
GET_COUNT
----------
8

SCOTT@book> select get_count2 from dual ;
GET_COUNT2
----------
8

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

转载于:http://blog.itpub.net/267265/viewspace-2132871/