数据加密

liuxbgiant

这个问题曾弄过，如今记不清了，想列出请各位帮忙解决一下：
如：员工的工资表，对于salary列 ,普通用户只能查看到自己的一行，而对于特殊用户，可见所有的行。注：好像用到系统的包过程，无wallet介入。

[ 本帖最后由 liuxbgiant 于 2012-4-20 13:39 编辑 ]

Maclean Liu(刘相兵

Oracle Virtual Private Database

Oracle introduced the VPD feature as early as Oracle 8i.  VPD is an encompassing term for fine grain access control (FGAC), row revel security (RLS and DBMS_RLS packages), application context and global application context.   VPD when initially released provided a way to dynamically change the dataset return based on a set of policies and business rules.  As of Oracle 10GR2 the functionality has been enhanced to include column security.  
Oracle Virtual Private Database provides a mechanism to programmatically define polices and rules for access to information.  By embedding these policies and rules within the database, these are dynamic and transparent to the user / application.  
VPD can secure data at the row and column level.  The packages and functions used for this are feature rich and this white paper will only cover some of the basic concepts to provide a working prototype.  




Virtual Private Database

Maclean Liu(刘相兵

FOR EXAMPLE:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options







SQL> conn system/oracle
Connected.


SQL> CREATE USER vpd IDENTIFIED BY oracle
DEFAULT TABLESPACE users
  2    3  TEMPORARY TABLESPACE temp;

GRANT CONNECT, RESOURCE TO vpd;

-- Included to enable the use of autotrace
GRANT select_catalog_role TO vpd;

User created.

SQL>


SQL>
Grant succeeded.

SQL> SQL> SQL> -- Included to enable the use of autotrace

Grant succeeded.

SQL> SQL> SQL> GRANT SELECT ANY dictionary TO vpd;

Grant succeeded.

SQL>
SQL> -- Included to enable the creation of policies
SQL> GRANT execute on DBMS_RLS to vpd;


Grant succeeded.

SQL> SQL> -- Included to enable the creation of application contexts
SQL> GRANT CREATE ANY CONTEXT to vpd;

Grant succeeded.



SQL> CREATE TABLE "VPD"."CUSTOMER"
  2    (
  3      "CUST_NUM"       VARCHAR2(50 BYTE) NOT NULL ENABLE,
  4      "CUST_NAME"      VARCHAR2(50 BYTE),
  5      "CUST_ADDRESS1"  VARCHAR2(50 BYTE),
  6      "CUST_ADDRESS2"  VARCHAR2(50 BYTE),
  7      "CUST_CITY"      VARCHAR2(50 BYTE),
  8      "CUST_PCODE_ZIP" VARCHAR2(20 BYTE),
  9      "CUST_STATE"     VARCHAR2(50 BYTE),
10      "CUST_COUNTRY"   VARCHAR2(50 BYTE),
11      "REGION"         VARCHAR2(50 BYTE),
12  CONSTRAINT "TABLE1_PK"  PRIMARY KEY ("CUST_NUM") USING INDEX)  
13  PCTFREE 10 INITRANS 2 MAXTRANS 255
14    TABLESPACE "USERS" ;


Table created.

SQL> SQL>   CREATE TABLE "VPD"."SALESPERSON"
  2    (
  3      "SALESPERSON_NUM"  VARCHAR2(20 BYTE) NOT NULL ENABLE,
  4      "FNAME"            VARCHAR2(50 BYTE),
  5      "LNAME"            VARCHAR2(50 BYTE),
  6      "USERNAME"         VARCHAR2(20 BYTE),
  7      "EMAIL"            VARCHAR2(50 BYTE),
  8      "MANAGER_USERNAME" VARCHAR2(20 BYTE),
  9      "HIRE_DATE" DATE,
10      "REGION" VARCHAR2(50 BYTE),
11      CONSTRAINT "SALESPERSON_PK" PRIMARY KEY ("SALESPERSON_NUM") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  TABLESPACE "USERS" ENABLE
12    )
13    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
14    TABLESPACE "USERS" ;
  

Table created.

SQL> SQL>   
SQL>
SQL> CREATE TABLE "VPD"."SALES"
  2    (
  3      "CUST_NUM"        VARCHAR2(20 BYTE) NOT NULL ENABLE,
  4      "SALESPERSON_NUM" VARCHAR2(20 BYTE) NOT NULL ENABLE,
  5      "SALES_NUM"       NUMBER NOT NULL ENABLE,
  6      "LINE_NUM"        NUMBER NOT NULL ENABLE,
  7      "QUANTITY"        NUMBER,
  8      "COST"            NUMBER,
  9      "TOTAL"           NUMBER,
10      "PRODUCT"         VARCHAR2(50 BYTE),
11      "SALES_DATE" DATE,
    CONSTRAINT "SALES_PK" PRIMARY KEY ("SALES_NUM", "LINE_NUM") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  TABLESPACE "USERS" ENABLE,
12   13      CONSTRAINT "CUST_SALES_FK" FOREIGN KEY ("CUST_NUM") REFERENCES "VPD"."CUSTOMER" ("CUST_NUM") ENABLE,
    CONSTRAINT "SALESPERSON_SALES_FK" FOREIGN KEY ("SALESPERSON_NUM") REFERENCES "VPD"."SALESPERSON" ("SALESPERSON_NUM") ENABLE
  )
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  TABLESPACE "USERS" ;

14   15   16  

Table created.






conn vpd/vpd

SQL> CREATE OR REPLACE
  2  FUNCTION CUST_REGION_SEC
  3  ( p_schema IN VARCHAR2 DEFAULT NULL, P_object IN VARCHAR2 DEFAULT NULL)
  4  RETURN VARCHAR2
  5  IS
  6  BEGIN
  7            
  8        RETURN ' REGION = (SELECT region FROM salesperson WHERE username = user)';
  9   
10    EXCEPTION
11        WHEN OTHERS
12              THEN RETURN ('1=2');
13  END;
14  /

Function created.


conn system/oracle

SQL> BEGIN
  2     DBMS_RLS.ADD_POLICY (object_schema => 'VPD', object_name =>
  3  'CUSTOMER', policy_name => 'CUST_REGION_POL', function_schema
  4  => 'VPD', policy_function => 'CUST_REGION_SEC');
  5  END;
  6  /

PL/SQL procedure successfully completed.






SQL> conn vpd/vpd
Connected.
SQL> set autotrace on;
SQL> set linesize 200 pagesize 1400


SQL> select * from customer;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 242466158

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |   228 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL | CUSTOMER    |     1 |   228 |     2   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| SALESPERSON |     1 |    39 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("REGION"= (SELECT /*+ */ "REGION" FROM VPD."SALESPERSON"
              "SALESPERSON" WHERE "USERNAME"=USER@!))
   2 - filter("USERNAME"=USER@!)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        321  recursive calls
          0  db block gets
         78  consistent gets
          0  physical reads
          0  redo size
        875  bytes sent via SQL*Net to client
        458  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
          0  rows processed

gdpr-dba

其实没必要这么复杂，应用代码加上where语句，后面条件是“员工Id=xxx”就可以了。