Virtual Private Database

1
Virtual Private Database
using Oracle 10g

• Jalal Raissi

2
Objectives

• What is virtual private database?
• Implement VPD using VIEW database object
• Implement VPD using application context
• Implement the VPD feature
• Use data dictionary to view VPD
• Implement row- and column-level security

3
What is VPD?

• VPD deals with multiple-access single-door data
  security
• VPD controls data access at the row or column
  level
• Oracle 10g
• Specific functions
• Row-level security (RLS), fine-grained access
  (FGA)

4
Overview of VPD (continued)

• Shared database schema
• Contains data that belongs to different users
• User views or updates only data he or she owns
• One database schema serves multiple unrelated
  groups or entities

5
Overview of VPD (continued)

• Single-point of access
• Security requirements necessitate data access be
  restricted at row or column level
• No security-bypass
• No security-bypass backdoor to data using ODBC,
  JDBC, OLEDB, etc.
• Direct call threats to I/O devices still exist

6
Implementing VPD using Views

• View object limits what users can see and do with
  existing data
• CREATE VIEW statement creates data views
• Implementation requires careful design and
  development
• Vulnerable to security-bypass backdoors

7
Implementing VPD using Views (continued)

• Example implementation steps
• connect / as sysdba
• _at_ view_vpd_run
• _at_ f\vpd\view_vpd_setup
• _at_ f\vpd\view_vpd_user1
• _at_ f\vpd\view_vpd_user2
• _at_ f\vpd\view_vpd_show1

8
Implementing VPD using Views (continued)

• view_vpd_setup
• Create vpd_admin, vpd_user1, vpd_user2, and
  vpd_hacker1 users
• With vpd_admin user, create tables, views,
  triggers
• Grant select, insert to vpd_admin, vpd_user1,
  vpd_user2
• Grant select to vpd_hacker1
• Insert rows into tables

9
Implementing VPD using Views (continued)

• view_vpd_user1
• Connect vpd_user1/ksu
• Insert rows into employee, project, and works_on
  views
• view_vpd_user2
• Connect vpd_user2/ksu
• Insert rows into employee, project, and works_on
  views

10
Implementing VPD using Views (continued)

• view_vpd_show1
• Connect vpd_admin/ksu
• Select using employee, project, works-on views
• Connect vpd_user1/ksu
• Select using employee, project, works-on views
• Connect vpd_user2/ksu
• Select using employee, project, works-on views
• Connect vpd_hacker1/ksu
• Select using employee, project, works-on views

11
Implementing VPD using Views (continued)
12
Views Security based on Current User
13
Views Security Bypass using Backdoor
14
Implementing VPD using Application Context

• Application context
• Database application variables can be retrieved
  by database sessions
• Variables can be used for security context-based
  or user-defined environmental attributes
• Application context function SYS_CONTEXT

15
Implementing VPD using Application Context
(continued)
16
Implementing VPD using Application Context
(continued)

• Example implementation steps
• connect / as sysdba
• _at_ ac_vpd_run
• _at_ f\vpd\ac_vpd_setup
• _at_ f\vpd\ac_vpd_user1
• _at_ f\vpd\ac_vpd_user2
• _at_ f\vpd\ac_vpd_show1

17
Implementing VPD using Application Context
(continued)

• ac_vpd_setup
• Create vpd_admin, vpd_user1, vpd_user2, and
  vpd_hacker1 users
• With vpd_admin user, create APP_CONTEXT_USERS
• Create tables, views, triggers
• Grant select, insert to vpd_admin, vpd_user1,
  vpd_user2
• Grant select to vpd_hacker1
• Insert rows into tables

18
Implementing VPD using Application Context
(continued)

• ac_vpd_user1
• Connect vpd_user1/ksu
• Insert rows using employee and dependent views
• ac_vpd_user2
• Connect vpd_user2/ksu
• Insert rows using employee, project, and works_on
  views

19
Implementing VPD using Application Context
(continued)

• ac_vpd_show1
• Connect vpd_admin/ksu
• Select using employee, project, works-on views
• Connect vpd_user1/ksu
• Select using employee, project, works-on views
• Connect vpd_user2/ksu
• Select using employee, project, works-on views
• Connect vpd_hacker1/ksu
• Select using employee, project, works-on views

20
Implementing VPD using Application Context
(continued)

• Use Oracle PL/SQL package DBMS_SESSION
  (SET_CONTEXT procedure)
• Example steps
• Using VPD_ADMIN with privileges to create tables
  and other objects
• Application context table APP_CONTEXT_USERS
• EMPLOYEE table

21
Implementing VPD using Application Context
(continued)

• As VPD_ADMIN insert rows into
• EMPLOYEE table
• APP_CONTEXT_USERS table
• As VPD_ADMIN create a VIEW object to display rows
  based on Application Context SECURITY_LEVEL
• As VPD_ADMIN create context for EMPLOYEE_APP

22
Implementing VPD using Application Context
(continued)

• Create a package can be owned by SYS, SYSTEM or
  VPD_ADMIN
• Grant the user CREATE ANY CONTEXT privilege and
  the execute privilege to VPD_ADMIN
• Create a logon database trigger for VPD_USER2
• Connect as VPD_USER2 and select from the view

23
Implementing VPD using Application Context
(continued)
24
Implementing VPD

• VPD in a row- and column-level solution that
  eliminates backdoors
• User functions
• VPD_ADMIN user application schema owner
• EMPLOYEE used to demonstrate VPDs
• VPD_USER1, VPD_USER2, and VPD_HACKER1 users
  database users that are used to test VPDs

25
Implementing VPD

• Example implementation steps
• connect / as sysdba
• _at_ rls_vpd_run
• _at_ f\vpd\rls_vpd_setup
• _at_ f\vpd\rls_vpd_user1
• _at_ f\vpd\rls_vpd_user2
• _at_ f\vpd\rls_vpd_show1

26
Implementing VPD (continued)

• Create table for EMPLOYEE users
• Create the EMPLOYEE table
• Insert rows into the EMPLOYEE table
• Create three users for testing, VPD_USER1,
  VPD_USER2, and VPD_HACKER1
• Grant the necessary privileges on the EMPLOYEE
  table to use each test

27
Implementing VPD (continued)

• ROW_OWNER security row-level security based on
  user that owns row
• Steps
• Create a policy function to add a predicate to
  the WHERE clause
• Using DBMS_RLS add the VPD policy
  Oracle-supplied package
• Log in as VPD_USER1 display number of records
  that this user can see
• Disable this policy

28
Implementing VPD (continued)

• DBMS_RLS
• PROCEDURE ADD_POLICY
• Adds a policy for the table
• PROCEDURE ADD_POLICY_CONTEXT
• Adds an application context to a policy
• PROCEDURE DROP_POLICY
• Removes a VPD policy from the table
• PROCEDURE ENABLE_POLICY
• Enables or disables a policy

29
Implementing VPD (continued)

• APPLICATION-CONTEXT security allows specific
  users to see only rows for a specific sales
  representative
• Steps
• Create the VPD_EMPLOYEE_APP_CONTEXT table
• Insert rows
• Create a trusted package that allows VPD_ADMIN to
  execute DBMS_SESSION

30
Implementing VPD (continued)

• Steps (continued)
• Create an application context for this policy
• Create a new VPD function policy to add a WHERE
  clause predicate
• Add a VPD policy for the EMPLOYEE table
• Create an after-logon trigger
• Now log on as VPD_USER2

31
Implementing VPD (continued)

• ROLE SECURITY LEVEL
• Detects the role of the user
• A predicate is used to filter the rows that can
  be seen by each user
• Steps
• Disable any policies on the EMPLOYEE table
• Disable the AFTER LOGON database trigger

32
Implementing VPD (continued)
USER ROLES
33
Implementing VPD (continued)

• Steps (continued)
• Create three new roles
• Create application context for the security level
• Create application context package to set the
  application context
• Create a policy function to implement row-level
  security
• Create a policy to enforce a WHERE clause
  predicate

34
Implementing VPD (continued)

• Steps (continued)
• Application logs on as VPD_USER1 run
  PKG_VPD_ADMIN_ROLE_SECURITY_LEVEL package
• Repeat last step with VPD_USER2
• VPD policies can be grouped for organizational
  purposes
• Oracle enforces row-level security using all the
  DML statements

35
Viewing VPD Policies and Applications Context
using the Data Dictionary
36
Row-based Security Using Access Levels

• Variation of both
• Application table-based security model
• Application function-based security model
• Access levels
• 0 No access
• 1 select
• 2 select, insert
• 3 select, insert, update

37
Row-based Security Using Access Levels (continued)

• Access levels (continued)
• 4 select, insert, update, delete
• 5 administrator access
• Steps
• Create the APPLICATION USERS table
• Alter the EMPLOYEE table to include the ACCESS
  CONTROL column
• With the security structure in place use a view
  to retrieve data

38
Row-based Security Using Application Functions

• Steps (continued)
• Apply privileges
• Drawbacks it allows insertion, update, and
  deletion of records
• Alternatives
• Use stored procedures
• Use application functions access table list a
  function instead of a level

39
Column-based Security

• VPD and Column Access steps
• Log in as VPD_USER1 and view rows and columns in
  the EMPLOYEE table
• Log in as the VPD_ADMIN user and recreate the
  policy on EMPLOYEE
• Log in as VPD_USER1 and query the EMPLOYEE table

40
Column-based Security

• Column privileges steps
• Log in as VPD_ADMIN
• Grant SELECT on the EMPLOYEE table to VPD_USER2
• Grant UPDATE only on the column BDATE in the
  EMPLOYEE table to VPD_USER2
• Insert a row into the EMPLOYEE table and save it

41
Column-based Security (continued)

• Column privileges in Oracle steps (continued)
• Log in as VPD_USER2 and query the EMPLOYEE table
  owned by VPD_ADMIN
• Update the BDATE column in the EMPLOYEE table
• Try to update the SALARY column in the EMPLOYEE
  table

42
Summary

• A VPD allows or prevents data access at the row
  or column level
• Oracle refers to VPD as row-level security (RLS)
  or fine-grained access (FGA)
• VPD can be implemented using View object.
  However, this method does not prevent
  security-bypass backdoors

43
Summary (continued)

• Oracle Application context
• Allows setting of database application be
  retrieved by database sessions
• SYS_CONTEXT function
• PL/SQL package DBMS_SESSION
• SET_CONTEXT procedure
• This method also does not prevent security-bypass
  backdoors

44
Summary (continued)

• Use Oracle-supplied package DBMS_RLS to add the
  VPD policy
• Oracle data dictionary views
• Oracle can restrict updates or inserts on
  columns, using GRANT UPDATE(column) and
  INSERT(column)

45
References