VPD学习笔记

VPD

最近一直做MC的项目，根据context不同，利用VPD对查询数据进行过滤。

问题：之前我们做权限控制常用的做法是建立视图，然后多在查询语句中加where语句来控制。不过这样当程序改变时，DB改动也比较大。另外，这个只是在应用程序层面上进行过滤。抛开不安全不说，让应用程序自己控制权限，总有点职责不明。

VPD 即虚拟专用数据库（Virtual Private Database），简单来说通过在数据库里进行配置，从而让不同的用户只能查看某 个表里的部分数据。这里说的查看不同的部分数据分两个级别： 行级别和列级别。我们知道Oracle 是关系数据库，一行一条数据。
行级别：在该级别下，可以控制某些用户只能查看到某些数据行。

列级别：在该级别下，可以控制某些用户不能检索某个表的某个列的值。如果没权限则显示null。

行级别VPD

当一个表被设置了行级别的VPD时，当SQL执行时，Oracle会自动改写SQL 语句，在后面添加where条件。
例如，对MY_ORDER 设置了行VPD，且限制 customer ='5678'时：

SELECT * FROM orders_tab;

执行时，Oracle会自动转换成以下：

SELECT * FROM orders_tab cust_no ='5678';

注意，因为Oracle是改写where子句，因此VPD 的影响范围只能作用于where，而不能作用select等子句。

列级别VPD

对于一些table 列来说，有可能涉及一些敏感信息。这是为这些列加上VPD 后，就可以屏幕这些信息，只有有权限的用户才能访问到这些列信息。当用户不具有权限时，Oracle在这列上用null替换。

Policy

Policy 即策略。Oracle提供Policy 来定义和管理上边说的这些权限控制。比如，上边提到的 customer ='KAKA'。这样对于Table和 VPD的管理就分开了。利用Policy 可以很容易的维护我们的权限管理体系。

定义一个Policy，首先要定义一个function。这个函数的目的就是根据不同条件返回一个字符串，最后这个字符串会被Oracle执行SQL时拼接到SQL的where 子句中。

这是一个创建 Policy 函数的例子，简单定义一个返回 cust_no = 5678' 的function。

CREATE OR REPLACE FUNCTION auth_orders( 
  schema_var IN VARCHAR2,
  table_var  IN VARCHAR2
 )
 RETURN VARCHAR2
 IS
  return_val VARCHAR2 (50);
 BEGIN
  return_val := 'cust_no = 5678';
  RETURN return_val;
 END auth_orders;
/

这里在编写function时一定要小心，我在用的时候经常报 "ORA-00936: missing expression" 错，坑了我好多次。

然后我们就可以创建我们需要的 Policy了。Oracle 提供了DBMS_RLS 内置函数包帮助我们对Policy 进行维护。这里我简单地找了一些：

ADD_POLICY   将访问控制策略添加到对象
DROP_POLICY 删除对象中的策略
REFRESH_POLICY    重新解析与策略关联的、缓存的所有语句
ENABLE_POLICY      启用或禁用策略
CREATE_POLICY_GROUP           创建策略组
ADD_GROUPED_POLICY          将策略添加到策略组
ADD_POLICY_CONTEXT  添加当前应用程序的上下文
DELETE_POLICY_GROUP         删除策略组
DROP_GROUPED_POLICY       从策略组中删除一个策略
DROP_POLICY_CONTEXT         删除活动应用程序的上下文
ENABLE_GROUPED_POLICY   启用或禁用组策略
DISABLE_GROUPED_POLICY  禁用组策略
REFRESH_GROUPED_POLICY 重新解析与策略组关联的、缓存的所有语句

这里参考官网给出的例子：

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema    => 'scott', --数据表(或视图)所在的Schema名称
  object_name      => 'orders_tab', --数据表(或视图)的名称
  policy_name      => 'orders_policy', --POLICY的名称，主要用于将来对Policy的管理
  function_schema  => 'sysadmin_vpd', --返回Where子句的函数所在Schema名称
  policy_function  => 'auth_orders', --返回Where子句的函数名称
  statement_types  => 'select', -- DML类型，如 'Select,Insert,Update,Delete'
  Enable =>True --是否启用，值为'True'或'False'
  ); 
END;
/

此时，我们再执行以下语句只能得到两条数据了。

SELECT t.* from orders_tab t;

注意，创建Policy时，要用sys用户将DBMS_RLS包的运行权限 grant 给使用VPD用户。

创建基于列的Policy 也很类似：

CREATE OR REPLACE FUNCTION hide_sal_comm (
 v_schema IN VARCHAR2, 
 v_objname IN VARCHAR2)

RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
 con := 'deptno=30';
 RETURN (con);
END hide_sal_comm;
/

同样创建Policy，不过要指定作用的列。

BEGIN
 DBMS_RLS.ADD_POLICY (
  object_schema     => 'scott', 
  object_name       => 'emp',
  policy_name       => 'hide_sal_policy', 
  policy_function   => 'hide_sal_comm',
  sec_relevant_cols => 'sal,comm'); --作用列
END;
/

此时，当user没权限时，sal,comm 两列显示null。

可以通过下面SQL查看所有的Policy：

SELECT * FROM all_policies ;

context

我们通常叫context 为上下文。在很多地方都看到过。在Oracle中，用户登录后相当创建了一个session，这个session的一些信息就是context。默认情况下Oracle提供了一个context，即userenv(user environment)，有点类似 key-value的 map。
这个context是Oracle在用户登录时，数据库登录触发器执行，通过调用dbms_session.set_context为用户设置的应用程序上下文。
个人觉得context的最主要的作用是提供了对信息的一种管理上的方便。
例如：

SELECT SYS_CONTEXT('USERENV','LANGUAGE'), SYS_CONTEXT('USERENV','HOST') FROM dual ;

Oracle也允许我们自定义 context，语法如下：

DBMS_SESSION.SET_CONTEXT
 ( namespace VARCHAR2, attribute VARCHAR2, value VARCHAR2, username VARCHAR2, client_id VARCHAR2 );

这里，前三个参数是必须的，后两个非必须，如果不写的话就默认为null。后面两个参数是针对全局的context。

session

我们知道在很多地方，session 都是通信双方从开始通信到通信结束期间的一个上下文（context）。因此，就有种可能： 在通信时，客户端通过设置context 的信息值，然后Oracle在执行过程中使用该值。

这个时候相信你已经将前后联系起来了。现在改写我们的 Policy function，看官网的例子：

CREATE OR REPLACE FUNCTION get_user_orders(
  schema_p   IN VARCHAR2,
  table_p    IN VARCHAR2)
 RETURN VARCHAR2
 AS
  orders_pred VARCHAR2 (400);
 BEGIN
  orders_pred := 'cust_no = SYS_CONTEXT(''orders_ctx'', ''cust_no'')'; -- 使用 context 里的信息
 RETURN orders_pred;
END;

这时的查询就相当于：

SELECT * FROM scott.orders_tab where cust_no = SYS_CONTEXT('orders_ctx'', 'cust_no');

Package

除了context里面，我们也可以在创建 PACKAGE，然后在package 定义变量。每次查询前调用function或存储过程，更新变量的值。如果我们的Policy function 使用到这个变量，一样能达到效果。这里解释下包。

在一个系统中，可能有很多模块，而每个模块又有自己的存储过程、函数等。默认情况下，这些是放在一起的。利用面向对象设计的思路，对其进行分类就形成包。因此，可以将包看成是一组相关过程、函数、变量、常量、类型和游标等元素的组合。
包由两个分开的部分组成：包规范和包体。

包规范：是应用程序的接口，声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素，这些元素为包的公有元素。这点类似Java中的接口。

包主体：是包定义部分的具体实现，其中定义了包规范部分所声明的游标和子程序，另外在包主体中还可以声明包的私有元素。如果在包主体中的游标或子程序并没有在包头中定义，那么这个游标或子程序是私有的。

也就是说，包主体实现了包规范的接口，然后还添加了私有的元素。

Java应用

JDBC

如果程序中使用JDBC，可以利用JDBC的API帮我们更新context。

我们知道， JDBC在和Oracle交互的过程，主要经过以下几个步骤：

1. 获取连接 getConnection

2. 构造Statement对象

createStatement()   //最容用的方式，缺点容易被注入攻击  
prepareStatement(String sql) //防止SQL注入  
prepareCall(String sql)  //存储过程调用

3. 执行Execute
4. 获取ResultSet，提交事务（Commit）

因此，同样可以在查询前，调用存储过程，更新变量。

Spring

在Spring中，我们通常将dataSource 定义在配置里。比如：

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="oracle.jdbc.OracleDriver" />
    <property name="url" value="jdbc:oracle:thin:@xxx.xxx.com" />
    <property name="username" value="user***" />
    <property name="password" value="pass***" />
    <property name="connectionInitSqls">
        <util:list>
              <value>call PKG_RLS.SET_CONTEXT ('context', 'your_context', 'SET_VALUE')</value>
        </util:list>
    </property>
</bean>

这里利用了，connection的初始化sql的参数。只是这里有一点，这个属性是1.3 添加的，如果你的版本比这个低，需要升级。具体可以参考API。

OracleConnection

Oracle提供 VPD，也对OracleConnection提供了更改context的功能。这里简单给下例子：

    public ResultSet readAPJob() throws SQLException {
        Connection connection = getDBConnection();
        processContext(connection);
        PreparedStatement statement = connection.prepareStatement(readAPJobSql);
        return statement.executeQuery();
    }

    private Connection getDBConnection() throws SQLException {
        return DriverManager.getConnection(URL, "username", "password");
    }

    private void processContext(Connection connection) throws SQLException {
        OracleConnection oraCon = connection.unwrap(OracleConnection.class);
        oraCon.setApplicationContext("CLIENTCONTEXT", "ORACLE_USER_ID", "");
    }

这里主要利用了OracleConnection 的方法 setApplicationContext()。

eclipselink

如果你用的是eclipselink，通俗的做法利用SessionEventAdapter。把它跟数据库配置一样放在persistence.xml 中。

<property name="eclipselink.session-event-listener" value="YourVPDSessionEventAdapter" />

然后让 YourVPDSessionEventAdapter 继承 SessionEventAdapter ，然后我们就可以根据event 获取session，然后操作session即可。session event 还有其他功能，有兴趣的可以参考 文档。