DBMS_RLS官方参考

原创 2008年10月02日 20:29:00
DBMS_RLS

The DBMS_RLS package contains the fine-grained access control administrative interface.



Dynamic Predicates


The functionality to support fine-grained access control is based on dynamic predicates, where security rules are not embedded in views, but are acquired at the statement parse time, when the base table or view is referenced in a DML statement.


A dynamic predicate for a table or view is generated by a PL/SQL function, which is associated with a security policy through a PL/SQL interface. For example:

DBMS_RLS.ADD_POLICY (

'scott', 'emp', 'emp_policy', 'secusr', 'emp_sec', 'select');


Whenever EMP table, under SCOTT schema, is referenced in a query or subquery (SELECT), the server calls the EMP_SEC function (under SECUSR schema). This returns a predicate specific to the current user for the EMP_POLICY policy. The policy function may generate the predicates based on whatever session environment variables are available during the function call. These variables usually appear in the form of application contexts.


The server then produces a transient view with the text:

SELECT * FROM scott.emp WHERE P1



Here, P1 (e.g., SAL > 10000, or even a subquery) is the predicate returned from the EMP_SEC function. The server treats the EMP table as a view and does the view expansion just like the ordinary view, except that the view text is taken from the transient view instead of the data dictionary.


If the predicate contains subqueries, then the owner (definer) of the policy function is used to resolve objects within the subqueries and checks security for those objects. In other words, users who have access privilege to the policy protected objects do not need to know anything about the policy. They do not need to be granted object privileges for any underlying security policy. Furthermore, the users also do not require EXECUTE privilege on the policy function, because the server makes the call with the function definer's right.



The DBMS_RLS package also provides the interface to drop and enable/disable security policies. For example, you can drop or disable the EMP_POLICY with the following PL/SQL statements:

DBMS_RLS.DROP_POLICY('scott', 'emp', 'emp_policy'); 

DBMS_RLS.ENABLE_POLICY('scott', 'emp', 'emp_policy', FALSE)

Security

A security check is performed when the transient view is created with subquery. 

The schema owning the policy function, which generates the dynamic predicate, is
the transient view's definer for the purpose of security check and object look-up.

Usage Notes


The DBMS_RLS procedures cause current DML transactions, if any, to commit before the operation. However, the procedures do not cause a commit first if they are inside a DDL event trigger. With DDL transactions, the DBMS_RLS procedures are part of the DDL transaction.


For example, you may create a trigger for CREATE TABLE. Inside the trigger, you may add a column through ALTER TABLE, and you can add a policy through DBMS_RLS. All these operations are in the same transaction as CREATE TABLE, even though each one is a DDL statement. The CREATE TABLE succeeds only if the trigger is completed successfully.


Summary of Subprograms


Table 41-1 DBMS_RLS Subprograms

Subprogram  Description 
ADD_POLICY procedure

 

Creates a fine-grained access control policy to a table or view.  

DROP_POLICY procedure

 

Drops a fine-grained access control policy from a table or view.  

REFRESH_POLICY procedure

 

Causes all the cached statements associated with the policy to be re-parsed.  

ENABLE_POLICY procedure

 

Enables or disables a fine-grained access control policy.  



ADD_POLICY procedure


This procedure creates a fine-grained access control policy to a table or view.


The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.



A commit is also performed at the end of the operation.


Syntax

DBMS_RLS.ADD_POLICY (

object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
function_schema IN VARCHAR2 := NULL,
policy_function IN VARCHAR2,
statement_types IN VARCHAR2 := NULL,
update_check IN BOOLEAN := FALSE,
enable IN BOOLEAN := TRUE);

Parameters


Table 41-2 ADD_POLICY Procedure Parameters

Parameter  Description 
object_schema

 

Schema containing the table or view (logon user, if NULL).  

object_name

 

Name of table or view to which the policy is added.  

policy_name

 

Name of policy to be added. It must be unique for the same table or view.  

function_schema

 

Schema of the policy function (logon user, if NULL).  

policy_function

 

Name of a function which generates a predicate for the policy. If the function is defined within a package, then the name of the package must be present.  

statement_types

 

Statement types that the policy will apply. It can be any combination of SELECT, INSERT, UPDATE, and DELETE. The default is to apply to all of these types.  

update_check

 

Optional argument for INSERT or UPDATE statement types. The default is FALSE. Setting update_check to TRUE causes the server to also check the policy against the value after insert or update.  

enable

 

Indicates if the policy is enabled when it is added. The default is TRUE  



Usage Notes



  • SYS is free of any security policy.


  • The policy functions which generate dynamic predicates are called by the server. Following is the interface for the function:

        FUNCTION policy_function (object_schema IN VARCHAR2, object_name VARCHAR2) 
    
    RETURN VARCHAR2
    --- object_schema is the schema owning the table of view.
    --- object_name is the name of table of view that the policy will apply.


    The maximum length of the predicate that the policy function can return is 2,000 bytes.


  • The policy functions must have the purity level of WNDS (write no database state).



  • Dynamic predicates generated out of different policies for the same object have the combined effect of a conjunction (ANDed) of all the predicates.


  • The security check and object lookup are performed against the owner of the policy function for objects in the subqueries of the dynamic predicates.


  • If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the policy.


  • When table alias is required (e.g., parent object is a type table) in the predicate, the name of the table or view itself must be used as the name of the alias. The server constructs the transient view as something like "select c1, c2, ... from tab where ".


  • The checking of the validity of the function is done at runtime for ease of installation and other dependency issues during import/export.


DROP_POLICY procedure


This procedure drops a fine-grained access control policy from a table or view.


The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.



A commit is also performed at the end of the operation.


Syntax

DBMS_RLS.DROP_POLICY (

object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2);

Parameters


Table 41-3 DROP_POLICY Procedure Parameters

Parameter  Description 
object_schema

 

Schema containing the table or view (logon user if NULL).  

object_name

 

Name of table or view.  

policy_name

 

Name of policy to be dropped from the table or view.  



REFRESH_POLICY procedure


This procedure causes all the cached statements associated with the policy to be re-parsed. This guarantees that the latest change to this policy will have immediate effect after the procedure is executed.


The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.



A commit is also performed at the end of the operation.


Syntax

DBMS_RLS.REFRESH_POLICY (

object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2 := NULL,
policy_name IN VARCHAR2 := NULL);

Parameters


Table 41-4 REFRESH_POLICY Procedure Parameters

Parameter  Description 
object_schema

 

Schema containing the table or view.  

object_name

 

Name of table or view that the policy is associated with.  

policy_name

 

Name of policy to be refreshed.  



Errors


The procedure returns an error if it tries to refresh a disabled policy.


ENABLE_POLICY procedure


This procedure enables or disables a fine-grained access control policy. A policy is enabled when it is created.


The procedure causes the current transaction, if any, to commit before the operation is carried out. However, this does not cause a commit first if it is inside a DDL event trigger.



A commit is also performed at the end of the operation.


Syntax

DBMS_RLS.ENABLE_POLICY (

object_schema IN VARCHAR2 := NULL,
object_name IN VARCHAR2,
policy_name IN VARCHAR2,
enable IN BOOLEAN);

Parameters


Table 41-5 ENABLE_POLICY Procedure Parameters

Parameter  Description 
object_schema

 

Schema containing the table or view (logon user if NULL).  

object_name

 

Name of table or view that the policy is associated with.  

policy_name

 

Name of policy to be enabled or disabled.  

enable

 

TRUE to enable the policy, FALSE to disable the policy.  



Example


This example illustrates the necessary steps to enforce a fine-grained access control policy.


In an Oracle HR application, PER_PEOPLE is a view for the PER_ALL_PEOPLE table, and both objects are under APPS schema.

CREATE TABLE per_all_people 

(person_id NUMBER(15),
last_name VARCHAR2(30),
emp_no VARCHAR2(15), ...);
CREATE VIEW per_people AS
SELECT * FROM per_all_people;


There should be a security policy that limits access to the PER_PEOPLE view based on the user's role in the company. The predicates for the policy can be generated by the SECURE_PERSON function in the HR_SECURITY package. The package is under schema APPS and contains functions to support all security policies related to the HR application. Also, all the application contexts are under the APPS_SEC namespace.

CREATE PACKAGE BODY hr_security IS

FUNCTION secure_person(obj_schema VARCHAR2, obj_name VARCHAR2)
RETURN VARCHAR2 IS
d_predicate VARCHAR2(2000);
BEGIN
-- for users with HR_ROLE set to EMP, map logon user name
-- to employee id. FND_USER table stores relationship
-- among database users, application users,
-- and people held in the HR person table.
IF SYS_CONTEXT('apps_sec', 'hr_role') = 'EMP' THEN
d_predicate = 'person_id IN
(SELECT employee_id FROM apps.fnd_user
WHERE user_name = SYS_CONTEXT('userenv', 'session_
user'))';
-- for users with HR_ROLE set to MGR (manager), map
-- security profile id to a list of employee id that
-- the user can access
ELSE IF SYS_CONTEXT('apps_sec', 'hr_role') = 'MGR' THEN
d_predicate = 'person_id IN
(SELECT ppl.employee_id FROM per_person_list ppl WHERE
ppl.security_profile_id = SYS_CONTEXT('apps_sec',
'security_profile_id'))
OR EXISTS (SELECT NULL FROM apps.per security_profiles psp
WHERE
SYS_CONTEXT('apps_sec', 'security_profile_id') =
psp.security_profile_id AND psp.view_all_flag = 'Y'))';
ELSE
d_predicate = '1=2'; -- deny access to other users, may use
something like 'keycol=null'
END IF;
RETURN d_predicate;
END secure_person;
END hr_security;


The next step is to associate a policy (here we call it PER_PEOPLE_SEC) for the PER_PEOPLE view to the HR_SECURITY.SECURE_PERSON function that generates the dynamic predicates:

DBMS_RLS.ADD_POLICY('apps', 'per_people', 'per_people_sec', 'apps'    

'hr_security.secure_person', 'select, update, delete');


Now, any SELECT, UPDATE, and DELETE statement with the PER_PEOPLE view involved will pick up one of the three predicates based on the value of the application context HR_ROLE.


DBMS_RLS包实现数据库表行级安全控制

DBMS_RLS 是实现数据库表行级别安全控制的,这个包包含精细访问控制管理接口,这个接口是用来实现VPD(Virtual Private Database),虚拟私有数据库。DBMS_RLS只能在O...
  • waterxcfg304
  • waterxcfg304
  • 2014年06月09日 11:30
  • 2567

利用DBMS_RLS实现VPD 实例

VPD(virtual Private database):虚拟私有数据库,从名字上可能不太好理解。形象地举个例子:比如我们钢铁行业的数据库,既提供给宝钢使用,又提供给鞍钢使用(这个在实际中是不可能的...
  • strikers1982
  • strikers1982
  • 2010年04月05日 18:50
  • 2359

Altera和Xilinx的参考设计资源

Altera的设计范例:http://www.altera.com.cn/support/examples/exm-index.html Altera的参考设计:http://www.alt...
  • simpldz
  • simpldz
  • 2016年04月06日 20:18
  • 728

dbms_rls

dbms_rlsdbms_rls is used to administer virtual private databases (also called: fine grained access c...
  • firefoxboy
  • firefoxboy
  • 2008年10月02日 19:34
  • 288

新编Windows API参考大全

书名:新编Windows API参考大全    作者:本书编写组    页数:981页    开数:16开    字数:2392千字    出版日期:2000年4月第二次印刷    出版社:电子工业出...
  • carl2380
  • carl2380
  • 2009年06月01日 12:12
  • 8286

Android编码规范风格指导(翻译)

原文地址:http://source.android.com/source/code-style.html 有很多为Android开源社区贡献代码的程序员,每个人都有不同的代码风格,为此googl...
  • leirenbaobao
  • leirenbaobao
  • 2015年04月28日 21:00
  • 772

VBA的Word参考教程

下面是VBA的Word参考教程的链接: http://www.feiesoft.com/vba/word/ ※:内容很全面非常,讲解非常详细...
  • a601861
  • a601861
  • 2015年05月21日 09:32
  • 263

WordPress官方函数参考

http://codex.wordpress.org/zh-cn:函数参考
  • ljguo212
  • ljguo212
  • 2012年03月05日 00:44
  • 152

Mongodb官方参考

http://docs.mongodb.org/manual/reference/
  • shellching
  • shellching
  • 2013年01月22日 15:06
  • 470

四. 常见H.264视频编解码器(X264和JM)及参考软件JM的下载与编解码

常见H.264视频编解码器(X264和JM)及参考软件JM的下载与编解码 我们已经知道,H.264是一种视频压缩标准,其只规定了符合标准的码流的格式,以及码流中各个语法元素的解析方法。H.264标准...
  • liu0808
  • liu0808
  • 2017年05月17日 22:13
  • 616
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:DBMS_RLS官方参考
举报原因:
原因补充:

(最多只允许输入30个字)