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 Virtual Private Database

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) Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

7. Implementing VPD using Views (continued) • Example implementation steps: • connect / as sysdba • @ view_vpd_run • @ f:\vpd\view_vpd_setup • @ f:\vpd\view_vpd_user1 • @ f:\vpd\view_vpd_user2 • @ f:\vpd\view_vpd_show1 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

11. Implementing VPD using Views (continued) Virtual Private Database

12. Views Security based on Current User Virtual Private Database

13. Views Security Bypass using Backdoor Virtual Private Database

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 Virtual Private Database

15. Implementing VPD using Application Context (continued) Virtual Private Database

16. Implementing VPD using Application Context (continued) • Example implementation steps: • connect / as sysdba • @ ac_vpd_run • @ f:\vpd\ac_vpd_setup • @ f:\vpd\ac_vpd_user1 • @ f:\vpd\ac_vpd_user2 • @ f:\vpd\ac_vpd_show1 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

23. Implementing VPD using Application Context (continued) Virtual Private Database

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_HACKER1users: database users that are used to test VPDs Virtual Private Database

25. Implementing VPD • Example implementation steps: • connect / as sysdba • @ rls_vpd_run • @ f:\vpd\rls_vpd_setup • @ f:\vpd\rls_vpd_user1 • @ f:\vpd\rls_vpd_user2 • @ f:\vpd\rls_vpd_show1 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

32. Implementing VPD (continued) USER ROLES: Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

35. Viewing VPD Policies and Applications Context using the Data Dictionary Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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 Virtual Private Database

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) Virtual Private Database

45. References Virtual Private Database