Virtual Private Databases

1. Virtual Private Databases

2. Objectives • Define the term “virtual private database” and explain its importance • Implement a virtual private database by using the VIEW database object • Introduce the Oracle virtual private database feature

3. Overview of Virtual Private Databases • A VPD deals with data access • VPD controls data access at the row or column level • Oracle10g: • Specific function • Two other names: Row-level security (RLS), fine-grained access (FGA)

4. Overview of Virtual Private Databases (continued) A shared database schema containing data that belongs to many different users, and each user can view or update only the data he or she owns.

5. Overview of Virtual Private Databases (continued) • Shared database schema: • Containing data that belongs to different users • User view or update only data he or she owns • Purposes/benefits: • Security requirements necessitate data access be restricted at row or column level (FGA) • One database schema serves multiple unrelated groups or entities

6. Implementing a VPD Using Views • View object limits what users can see and do with existing data: hides columns or rows from users • CREATE VIEW statement: creates data views • Views can become hard to administer • business rules require that each department can see only its own employees • need to create a view for each department • Solution is VPD

7. CREATE VIEW EMP_FOR_DEP_20 AS • SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, JOB_ID • FROM EMPLOYEES • WHERE DEPARTMENT_ID = 20

8. Implementing a VPD Using Views (continued) • Example implementation steps: (in class code) • Logon as user1 • Create the table “shared” • Create a VIEW object “shared_view” to display rows that belong only to the logged on user • Grant SELECT and INSERT on this view to another user user2 • Insert a row using “shared_view”

9. Implementing a VPD Using Views (continued) • Example implementation steps (continued) • Logon as the other user user2 • Select the “shared_view” VIEW object; you see only rows that belongs to the other user user2

10. Hiding Rows Based on the Current User • System function USER: • Returns database user • Used to implement row-based security • Implementing row-based security with views: • Need a column in your tables for the row’s owner • Use a trigger to make sure the row’s owner is inserted every time a new row is inserted into “shared”

11. Implementing a VPD Using Application Context in Oracle • Triggers • a stored PL/SQL procedure that fires (is called) automatically when a specific event occurs, such as the BEFORE INSERT event • Application context: • Functionality specific to Oracle • Allows to set database application variables that can be retrieved by database sessions • Variables can be used for security context-based or user-defined environmental attributes • Dynamic performance view V$SESSION • Application context function SYS_CONTEXT • USERENV: predefined user-environment attributes

12. Implementing a VPD Using Application Context in Oracle (continued)

13. Implementing a VPD Using Application Context in Oracle (continued) • Set your own application context: use Oracle PL/SQL package DBMS_SESSION • DBMS_SESSION contains several functions and procedures, for example: SET_CONTEXT

14. Implementing Oracle Virtual Private Databases • VPDs are a more direct solution • User functions: • DBSEC users: application schema owner • CUSTOMERS: used to demonstrate VPDs • VPD_CLERK1, VPD_CLERK2, and VPD_CLERK3 users: database users that are used to test VPDs

15. Implementing Oracle Virtual Private Databases (continued)

16. Implementing Oracle Virtual Private Databases (continued) • Create table for customer users: • Create the CUSTOMERS table • Insert rows into the CUSTOMERS table • Create three users for testing, VPD_CLERK1, VPD_CLERK2, and VPD_CLERK3 • Grant the necessary privileges on the CUSTOMERS table to use each test • ROW_OWNER security: row-level security based on user that owns row

17. Implementing Oracle Virtual Private Databases (continued) • 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_CLERK1; display number of records that this user can see • Disable this policy

18. Implementing Oracle Virtual Private Databases (continued)

19. create or replace function • dbsec_row_owner_where (p_schema_name in varchar2, • p_object_name in varchar2) return varchar2 is • v_where varchar2(4000); • begin • v_where := 'CTL_UPD_USER = ' || user ; • return v_where; • end; • /

20. EXEC DBMS_RLS.ADD_POLICY(OBJECT_SCHEMA=>'DBSEC',- • OBJECT_NAME=>'CUSTOMERS',- • POLICY_NAME=>'DBSEC_ROW_OWNER_POLICY',- • FUNCTION_SCHEMA=>'DBSEC',- • POLICY_FUNCTION=>'DBSEC_ROW_OWNER_WHERE',- • STATEMENT_TYPES=>'SELECT,UPDATE,INSERT,DELETE',- • ENABLE=>TRUE) • /