Virtual Private Databases

1. Virtual Private Databases Brandon Mason Database Administrator America First Credit Union

2. What is a Virtual Private Database ?

6. What is a VPD? Masks data in larger database Limits a user to only view/manipulate “private” data Contains both ROW and COLUMN level security

7. The Basics • Been around Since 1999 • Orale 8i • Also Known As: • Fine Grained Access Control (FGAC) (i.e. Policies can be applied per table) • Row-Level Security (RLS)

8. How Magic Happens . • Re-Writes Query • Appends Predicate to SQL Query Select * from table; Becomes Select * from table where salary < 50000; • Multiple Predicates are ‘ANDed’ • Allows Multiple Policies per Table

9. VPD Vs. Views • Difficult (sometimes impossible) to maintain large number of views • What if Security Policy Changes? • Views best suited for simple evaluations • Data must reside in the database (or be hard-coded in the view) • Users bypass security when accessing base tables

10. DataBase Security Traditional What Users Can See (Permission)

11. DataBase Security Traditional What Users Can See (Permission) VPD What Users Can’tSee (Prohibition)

12. Benefits • Scalable • 1 function can replace (n) number of views • Simple • Even I implemented this, and I have an HR degree • Attaching VPD to base tables affects all related views and applications • Security • Server-enforced • Can be granted to Security Admin User • Based on DB Objects (not applications)

13. Benefits, cont. • Granularity If you want to limit: All Records for Selects Insert and Update your own Department Delete only your own record VPD can handle that! • Certified for EBS, among others • Simplifies Admin • Transcends Apps • Databases Outlive Applications

15. When to use • HR Data • HIPAA requirements • Franchises • Gyms, Kiosks, etc. • America First Credit Union

16. Privs Needed • Create Procedure* • *Unless existing procs are sufficient already • Execute on DBMS_RLS Package • DBMS_RLS.ADD_POLICY • DBMS_RLS.DROP_POLICY • DBMS_RLS.ENABLE_POLICY • Does NOT require object privs on target object

17. Demonstration

18. Demonstration

19. Attaching a Policy DBMS_RLS.ADD_POLICY

20. Default Behavior

21. Removing a Policy DBMS_RLS.DROP_POLICY

22. WHO AM I? • Lewis Alcindor, Jr. • Kareem Abdul-Jabbar • Gordon Sumner • Sting • Gordon Schumway • A.LF.

23. Column Security

24. Column Security Select * from table gives the same results, However…

25. Column Security

26. ALL_ROWS

27. Column Masking

28. ALL_ROWS Features • Attach a policy only to security-relevant data • Default Behavior restricts entire row • MASKING behavior • Returns ALL rows, but returns NULL for secured values • Restrictions • Applies only to SELECT statements • Must be simple Boolean

30. STATEMENT_TYPES • Can restrict based on type of SQL statement • Select • Insert • Update • Delete • Index • If not specified, policy applies to all but INDEX

31. STATEMENT_TYPES Note the function is always FALSE.

32. STATEMENT_TYPES

33. STATEMENT_TYPES

34. STATEMENT_TYPES Re-try same query, but securing a single column

35. STATEMENT_TYPES

36. UPDATE_CHECK What if the VPD allows us to update data - However, performing the update would kick the record out of the security policy?

37. UPDATE_CHECK

38. STATEMENT_TYPES

39. Other Parameters • Enable • Toggles Policy on/off • Long_predicate • Increases length the of string returned by function • Static_policy / Policy_Type • Rarely used. Can improve speed with caching

40. Performance • Because VPD invokes a function each time a statement is issued, performance is a concern • 5 Options for controlling Caching • Dynamic (default) – no caching • Static – cached in SGA • Shared_Static - cached across multiple objects that use the same policy function • Context_Sensitive – Ideal for Connection Pooling • Shared_Context_Sensitive – Only executes function if it detects context changes

41. WHO AM I? • Carlos Irwin Estevez • Charlie Sheen • Cassius Clay • Muhammad Ali • William Bruce Rose • Axl Rose

42. Application Context . • Application Context securely caches user info • Global variable, holds info relevant to session • You can define, set, and access application attributes that you can use as a secure data cache • Increases performance, due to caching • Makes use of SYS_CONTEXT function

43. Application Context . • Preserves identity across multi-tier environments • Pre-defined app context “USERENV” • Describes the current session of the user • Computer ID • IP Address • OS Username *USERENV can only RETRIEVE session data, not set it • Chapter 6 of Oracle Database Security Guide for more information…

44. Application Context . Select syscontext(‘userenv’, ‘current_user’) from dual;

45. Application Context .

46. Application Context .

47. Application Context . • Set attribute value in an application context • DBMS_SESSION.SET_CONTEXT(‘namespace’, ‘attributename’, value); • Get attribute value from an application context, • SYS_CONTEXT(‘namespace’, ‘attributename’);

48. Policy Groups • Policy Group – set of security policies that belong to an application • Useful when multiple apps with complex share the same data • Example: a Data hosting company • DBMS_RLS.ADD_GROUPED_POLICY

49. Finding VPDs • ALL_SEC_RELEVANT_COLS; • select * from DBA_POLICIESwhere object_owner not in ('MDSYS', 'XDB');

50. Data Dict Views