使用DBMS_RLS包實現列級VPD控制

物理狂人發表於2011-12-22
虛擬專用資料庫(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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章