By Mouhamadou Diaw
Oracle Virtual Private Database (VPD) enables you to dynamically add a WHERE clause in any SQL statement that a user executes. The WHERE clause filters the data the user is allowed to access, based on the identity of a user.
This feature restricts row and column level data access by creating a policy that enforces a WHERE clause for all SQL statements that query the database. The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect. You create and manage the VPD policy at the database table or view level, which means that you do not modify the applications that access the database.
In a multitenant environment, each Virtual Private Database policy applies only to the current pluggable database (PDB).
In this blog we are going to use this functionality in a pluggable database 12.1
We are going to setup a policy for the table emp of scott so that each user should be able to see only his own data. We present below contents of table emp of scott
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17- DEC -80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03- DEC -81 950 30 7902 FORD ANALYST 7566 03- DEC -81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. |
The first step is to create two users jones and miller for example and let’s give them the required privileges
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
SQL> create user jones identified by root default tablespace users ; User created. SQL> grant select on scott.emp to jones; Grant succeeded. SQL> SQL> create user miller identified by root default tablespace users ; User created. SQL> grant select on scott.emp to miller; Grant succeeded. SQL> grant create session to jones; Grant succeeded. SQL> grant create session to miller; Grant succeeded. SQL> |
Once users created, we have to create the function we will use to create the VPD policy
1
2
3
4
5
6
7
8
9
|
CREATE OR REPLACE FUNCTION emp_policy (user_name IN VARCHAR2,tab_name in varchar2) RETURN VARCHAR2 IS BEGIN RETURN 'upper(ename) = SYS_CONTEXT(' 'USERENV' ', ' 'CURRENT_USER' ')' ; END ; / Function created. |
With this function Oracle will add for each query on the table emp the following clause
1
|
Where upper (ename) = SYS_CONTEXT(’USERENV’, ‘ CURRENT_USER ’) |
when the VPD policy is set
Now let’s create the VPD policy
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> show user USER is "SYSTEM" SQL> BEGIN SYS.DBMS_RLS.ADD_POLICY( object_schema => 'scott' , object_name => 'emp' , policy_name => 'emp_vpd_policy' , function_schema => 'system' , policy_function => 'emp_policy' , statement_types => 'select' ); END ; 11 / PL/SQL procedure successfully completed. |
And then we can test that each user can see only his own data
With user miller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
[oracle@em13c wallet]$ rlwrap sqlplus miller/root@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:18 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time : Tue Jun 26 2018 17:34:57 +02:00 Connected to : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select * from scott.emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7934 MILLER CLERK 7782 23-JAN-82 1300 10 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options |
With user jones
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[oracle@em13c wallet]$ rlwrap sqlplus jones/root@pdb1 SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 26 17:39:25 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Last Successful login time : Tue Jun 26 2018 17:34:33 +02:00 Connected to : Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select * from scott.emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 SQL> |
But there is a problem now, because VPD is enforced by default for all users except those connected with SYSDBA privilege. This mean that if we connect for example with SYSTEM, we will not see any data
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL> show user USER is "SYSTEM" SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> select * from scott.emp; no rows selected SQL> |
To exempt SYSTEM to VPD we have to grant him the EXEMPT ACCESS POLICY
1
2
3
4
5
6
7
8
9
10
|
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> grant EXEMPT ACCESS POLICY to system; Grant succeeded. SQL> |
And then login again whith SYSTEM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
|
SQL> show user USER is "SYSTEM" SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7369 SMITH CLERK 7902 17- DEC -80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03- DEC -81 950 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ---------- DEPTNO ---------- 7902 FORD ANALYST 7566 03- DEC -81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SQL> |