ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用DBMS_RLS包实现列级VPD控制

使用DBMS_RLS包实现列级VPD控制

原创 Linux操作系统 作者:物理狂人 时间:2011-12-22 15:02:29 0 删除 编辑
虚拟专用数据库(VPD) 包括:
– 详细访问控制(FGAC)
– 安全应用程序上下文

1)创建temp_user用户,并授予相应权限
SQL> grant dba to temp_user identified by "123";

授权成功。

2)授予scott用户操作dbms_rls包的权限
SQL> grant execute on dbms_rls to scott;

授权成功。

3)scott用户创建scott_emp安全策略函数
SQL> conn scott/tiger
已连接。
SQL> create or replace function scott_emp
  2  (p_owner in varchar2,p_obj in varchar2)
  3  return varchar2
  4  is
  5  l_ret varchar2(2000);
  6  begin
  7  if(p_owner=USER) then
  8  l_ret:=NULL;
  9  else
 10  l_ret:='1=2';
 11  end if;
 12  return l_ret;
 13  end;
 14  /
注:USER表示当前用户,p_owner,p_obj为策略对象的拥有者scott和对象emp表,在服务器调用时会传递给策略函数。返回NULL,表示没有限制。


4)利用dbms_rls.add_policy创建安全策略
SQL> begin
  2  dbms_rls.add_policy(
  3  object_schema=>'scott',
  4  object_name=>'emp',
  5  policy_name=>'scott_emp1',
  6  function_schema=>'scott',
  7  policy_function=>'scott_emp',
  8  statement_types=>'select',
  9  sec_relevant_cols=>'sal',
 10  sec_relevant_cols_opt=>dbms_rls.all_rows);
 11  end;
 12  /

PL/SQL 过程已成功完成。
注:sec_relevant_cols_opt=dbms_rls.all_rows表示在策略函数返回逻辑值时,将sec_relevant_cols指定的列值显示为NULL



5)scott用户访问sal列时,显示sal数据
SQL> select ename,sal from emp where rownum<5;

ENAME                       SAL
-------------------- ----------
SMITH                       800
ALLEN                      1600
WARD                       1250
JONES                      2975

6)temp_user用户访问sal列时,不显示数据
SQL> conn temp_user/123
已连接。
SQL> select ename,sal from scott.emp where rownum<5;

ENAME                       SAL
-------------------- ----------
SMITH
ALLEN
WARD
JONES

参考资料:
  • SYS is free of any security policy.

  • If no object_schema is specified, the current log-on user schema is assumed.

  • 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, view, or synonym to which the policy applies. 
  • If the function returns a zero length predicate, then it is interpreted as no restriction being applied to the current user for the policy.

Column-level VPD column masking behavior. (specified with sec_relevant_cols_opt => dbms_rls.ALL_ROWS) is fundamentally different from all other VPD policies, which return only a subset of rows. Instead the column masking behavior. returns all rows specified by the user's query, but the sensitive column values display as NULL. The restrictions for this option are as follows:

  • Only applies to SELECT statements

  • Unlike regular VPD predicates, the masking condition that is generated by the policy function must be a simple boolean expression.

  • If your application performs calculations, or does not expect NULL values, then you should use the default behavior. of column-level VPD, which is specified with the sec_relevant_cols parameter.

  • If you use UPDATE AS SELECT with this option, then only the values in the columns you are allowed to see will be updated.

  • This option may prevent some rows from displaying. For example:

    select * from employees
    where salary = 10
    
    

    This query may not return rows if the salary column returns a NULL value because the column masking option has been set.

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

请登录后发表评论 登录
全部评论

注册时间:2010-06-16

  • 博文量
    80
  • 访问量
    140527