虚拟专用数据库(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博客

使用DBMS_RLS包实现列级VPD控制

最新文章