oracle Policy的应用--DBMS_RLS.ADD_POLICY
来源:互联网 发布:数组是线性表吗 编辑:程序博客网 时间:2024/06/06 06:45
本文通过以下两篇文章整理所得:
http://www.knowsky.com/386463.html
http://www.itpub.net/thread-1004775-1-1.html
1 前言
数据访问权限控制,是一个古老而又实际的问题。
在大部份系统中,权限控制主要定义为模块进入权限的控制和数据列访问权限的控制(如:某某人可以进入某个控制,仓库不充许查看有关金额的字段等等)。
但在某些系统中,权限控制又必须定义到数据行访问权限的控制,此需求一般出现在同一系统,不同的相对独立机构使用的情况。(如:集团下属多个子公司,所有子公司使用同一套数据表,但不同子公司的数据相对隔离)
当然,绝大多数人会选择在View加上Where子句来进行数据隔离。此方法编码工作量大、系统适应用户治理体系的弹性空间较小,一旦权限逻辑发生变动,就可能需要修改权限体系,导致所有的View都必须修改。
Policy基本参数/创建语法
begin -- Call the procedure sys.dbms_rls.add_policy(object_schema => :数据表(或视图)所在的Schema名称/用户, object_name => :数据表(或视图)的名称, policy_name => :policy的名称 function_schema => :返回Where子句的函数所在Schema名称/用户, policy_function => :返回Where子句的函数名称, statement_types => :要使用该Policy的DML类型,如'Select,Insert,Update,Delete', update_check => 仅适用于Statement_Type为'Insert,Update',值为'True'或'False', enable => 是否启用,值为'True'或'False', static_policy => 默认值为FALSE。如果它被设置为TRUE则所有用户启用该策略,sys或特权用户例外。 policy_type => :默认值是null,意味着static_policy的值决定,在这里指定任何策略将覆盖static_policy的值。 long_predicate => long_predicate, sec_relevant_cols => :敏感的字段名称, sec_relevant_cols_opt => :设置为dbms_rls.ALL_ROWS来显示所有的行,敏感的列的值为null);end;
参考文档:
object_schema
Schema containing the table, view, or synonym. If no object_schema is specified,
the current log-on user schema is assumed.
object_name
Name of table, view, or synonym to which the policy is added.
policy_nameName of policy to be added. It must be unique for the same table or view.
function_schemaSchema of the policy function (current default schema, 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_typesStatement types to which the policy applies. It can be any combination of INDEX,
SELECT
, INSERT
, UPDATE
, or DELETE
. The default is to apply to all of these types except INDEX
.
Optional argument for INSERT
or UPDATE
statement types. The default is FALSE
. Settingupdate_check
toTRUE
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.
The default is FALSE
. If it is set to TRUE
, the server assumes that the policy function for the static policy produces the same predicate string for anyone accessing the object, except forSYS
or the privilege user who has theEXEMPT
ACCESS
POLICY
privilege.
Default is NULL
, which means policy_type
is decided by the value of static_policy
. The available policy types are listed inTable 89-4. Specifying any of these policy types overrides the value of static_policy
.
long_predicate
Default is FALSE
, which means the policy function can return a predicate with a length of up to 4000 bytes.TRUE
means the predicate text string length can be up to 32K bytes.Policies existing prior to the availability of this parameter retain a 32K limit.
sec_relevant_cols
Enables column-level Virtual Private Database (VPD), which enforces security policies when a column containing sensitive information is referenced in a query. Applies to tables and views, but not to synonyms. Specify a list of comma- or space-separated valid column names of the policy-protected object. The policy is enforced only if a specified column is referenced (or, for an abstract datatype column, its attributes are referenced) in the user SQL statement or its underlying view definition. Default is all the user-defined columns for the object.
sec_relevant_cols_opt
Use withsec_relevant_cols
to display all rows for column-level VPD filtered queries (SELECT
only), but where sensitive columns appear asNULL
. Default is set toNULL
, which allows the filtering defined withsec_relevant_cols
to take effect. Set todbms_rls.ALL_ROWS
to display all rows, but with sensitive column values, which are filtered bysec_relevant_cols
, displayed asNULL
. See"Usage Notes" for restrictions and additional information about this option. 删除Policy
begin -- Call the procedure sys.dbms_rls.drop_policy(object_schema => :要删除的Policy所在的Schema, object_name => :要删除Policy的数据表(或视图)名称, policy_name => :要删除的Policy名称);end;
例子1:
在一家公司,员工只可以看到本部门员工的记录:
创建表
create table employee(select 'qi' name,1 deptno,2000 salary from dual union all select 'guang',2,1600 from dual union all select 'wang',1,1800 from dual union all select 'li',1,2200 from dual union all select 'zhang',2,3000 from dual);
创建函数
create or replace function FN(p_owner in varchar2,p_object in varchar2) return varchar2 isbegin return 'deptno=1'; end FN;
创建策略
begindbms_rls.add_policy(object_schema => 'dbown',object_name => 'employee',policy_name => 'salary',function_schema => 'dbown',policy_function => 'FN',sec_relevant_cols => 'salary');end;
登录dbown用户查看employeeb表
select * from employee;name deptno salaryqi 1 2000wang 1 1800li 1 2200只可以看到部门1的相关记录。(其他用户可以看到所有信息,因为只对dbown用户做了策略。)
例子2:
在一家公司,员工可以看到本部门的所有信息,也可以看到其他部门的有关记录,但对于工资这个敏感数据则不能看到:
根据例子1,只要修改策略即可(表和函数都是岩用例子1的)
begindbms_rls.add_policy(object_schema => 'dbown',object_name => 'employee',policy_name => 'salary',function_schema => 'dbown',policy_function => 'FN',sec_relevant_cols => 'salary',sec_relevant_cols_opt => dbms_rls.ALL_ROWS);end;
登录dbown用户查看employeeb表
select * from employee; name deptno salary qi 1 2000guang 2 wang 1 1800li 1 2200zhang 2 则所有记录都有显示,只是非部门1的记录不能看到salary.
- oracle Policy的应用--DBMS_RLS.ADD_POLICY
- Oracle策略的使用(dbms_rls.add_policy)
- 042 - dbms_rls.add_policy
- dbms_rls
- dbms_rls
- dbms_rls
- Oracle Policy
- EnterpriseDB’s Virtual Private Database vs oracle’s DBMS_RLS
- Oracle Policy For NBU
- oracle 策略POLICY学习
- 实现数据行的权限控制(Policy的应用)
- 实现数据行的权限控制(Policy的应用)
- JAVAWEB应用的policy安全配置值得深思
- DBMS_RLS.SHARED_STATIC以及DBMS_RLS.SHARED_CONTEXT_SENSTIVE
- Oracle 10g backup policy
- route-policy与filter-policy的区别
- .JAVA.POLICY的设置
- JMeter的Cookie Policy
- ABAP开发 in BW
- 转 Linux下的 .o、.a、.so文件
- Max Points on a Line
- Android开发规范
- .NET数据连接和用户验证函数
- oracle Policy的应用--DBMS_RLS.ADD_POLICY
- 解决ScrollView嵌套ListView只显示一行的问题
- 学习css去w3school.cc
- 14年春面试前学习计划(二)
- Hbase 介绍
- 数据库优化的性能问题
- 网络编程中阻塞与非阻塞,同步与异步、I/O模型的理解
- 用OpenSceneGraph实现的NeHe OpenGL教程 - 第十三课
- 【KpmCup#0 省选模拟赛】题解