Virtual Private Databases - Amazon S3

Virtual Private Databases - Amazon S3

Virtual Private Databases Brandon Mason Database Administrator America First Credit Union What is a Virtual Private Database ?

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 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) 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 VPD Vs. Views Difficult (sometimes impossible) to maintain large number of views

Views best suited for simple evaluations

What if Security Policy Changes? Data must reside in the database (or be hardcoded in the view) Users bypass security when accessing base tables DataBase Security Traditional What Users Can See

(Permission) DataBase Security Traditional VPD What Users Can See (Permission) What Users Cant

See (Prohibition) 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) 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

When to use HR Data HIPAA requirements Franchises

Gyms, Kiosks, etc. America First Credit Union Privs Needed Create Procedure*

Execute on DBMS_RLS Package *Unless existing procs are sufficient already

DBMS_RLS.ADD_POLICY DBMS_RLS.DROP_POLICY DBMS_RLS.ENABLE_POLICY Does NOT require object privs on target object

Demonstration Demonstration Attaching a Policy DBMS_RLS.ADD_POLICY Default Behavior Removing a Policy

DBMS_RLS.DROP_POLICY WHO AM I? Lewis Alcindor, Jr. Kareem Abdul-Jabbar

Gordon Sumner Sting Gordon Schumway

A.LF. Column Security Column Security Select * from table gives the same results, However Column Security

ALL_ROWS Column Masking 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 STATEMENT_TYPES

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

INDEX STATEMENT_TYPES Note the function is always FALSE. STATEMENT_TYPES STATEMENT_TYPES STATEMENT_TYPES

Re-try same query, but securing a single column STATEMENT_TYPES UPDATE_CHECK What if the VPD allows us to update data However, performing the update would kick the record out of the security policy? UPDATE_CHECK STATEMENT_TYPES

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 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 WHO AM I? Carlos Irwin Estevez

Charlie Sheen Cassius Clay

Muhammad Ali William Bruce Rose Axl Rose 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 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 Application Context Select syscontext(userenv,

current_user) from dual; . Application Context . Application Context .

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); 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 Finding VPDs

ALL_SEC_RELEVANT_COLS; select * from DBA_POLICIES where object_owner not in ('MDSYS', 'XDB'); Data Dict Views View ALL_POLICIES Description

Describes all Oracle Virtual Private Database security policies for objects accessible to the current user. ALL_POLICY_CONTEXTS Describes the driving contexts defined for the synonyms, tables, and views accessible to the current user. A driving context is an application context used in an Oracle Virtual Private Database policy. ALL_POLICY_GROUPS

Describes the Oracle Virtual Private Database policy groups defined for the synonyms, tables, and views accessible to the current user ALL_SEC_RELEVANT_COLS Describes the security relevant columns of the security policies for the tables and views accessible to the current user

DBA_POLICIES Describes all Oracle Virtual Private Database security policies in the database. DBA_POLICY_GROUPS Describes all policy groups in the database. Data Dict Views DBA_POLICY_CONTEXTS

DBA_SEC_RELEVANT_COLS USER_POLICIES USER_POLICY_CONTEXTS USER_SEC_RELEVANT_COLS USER_POLICY_GROUPS V$VPD_POLICY

Describes all driving contexts in the database. Its columns are the same as those in ALL_POLICY_CONTEXTS. Describes the security relevant columns of all security policies in the database Describes all Oracle Virtual Private Database security policies associated with objects owned by the current user. This view does not display the OBJECT_OWNER column. Describes the driving contexts defined for the synonyms, tables, and views

owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_POLICY_CONTEXTS. Describes the security relevant columns of the security policies for the tables and views owned by the current user. Its columns (except for OBJECT_OWNER) are the same as those in ALL_SEC_RELEVANT_COLS. Describes the policy groups defined for the synonyms, tables, and views owned

by the current user. This view does not display the OBJECT_OWNER column. Displays all the fine-grained security policies and predicates associated with the cursors currently in the library cache. This view is useful for finding the policies that were applied to a SQL statement. Pitfalls

Difficult to Determine TRUE Permissions Performance EXEMPT_ACCESS_POLICY

Exempts you from ANY VPD Policy SYS and SYSDBA inherently have this role Export / Import Materialized View

Only on the PRODUCT of a view, not the base table Pitfalls, cont. Recursion Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.

(Oracle Database Security Guide, pg. 7-4) Beware that the function you write doesnt try to access the very table you are securing

Also, beware of having 2 tables with VPDs reference each other recursively Struggling? Create and review a trace file Questions? Thanks!

Recently Viewed Presentations

  • Breeding For Organic Production Systems (BOPS) Chris Reberg ...

    Breeding For Organic Production Systems (BOPS) Chris Reberg ...

    Breeding For Organic Production Systems ... as an isolating mechanism to alleviate the threat of GMO pollen in organic production fields. 3) Use dominant isolating mechanisms from Mexican ... Chris Reberg-Horton Tommy Carter Major Goodman Tom Isleib Paul Murphy George...
  • JRC Enlargement Action - Guvernul Romaniei

    JRC Enlargement Action - Guvernul Romaniei

    Some examples are provided in the following slides This is a standard presentation which has been prepared by the JRC Enlargement Unit of the JRC for dissemination (a input to the JRC National Contact Points of Acceding and Enlargement Countries...
  • Perception and Negotiation Chapter 5

    Perception and Negotiation Chapter 5

    For example Smiling Frowning Projection Occurs when people ascribe to others the characteristics or feelings that they possess themselves. For example Frustration Delays Questions Perception and Negotiation Chapter 5 By Ciandra Ross The Role of Perception Negotiators approach each negotiation...
  • Subroutines reasons for subroutines  repeat same code, or

    Subroutines reasons for subroutines repeat same code, or

    Register Usage: Preserving Registers. Functions that modify registers r4 - r11 are required to preserve and restore their original content. The best way to preserve and restore the registers is to . push. the registers onto the stack immediately upon...
  • Theory of Knowledge

    Theory of Knowledge

    The eye witness offers a primary source account of a historical event progression Implications for the area of knowledge Theory of Knowledge: the history essay History + Knowledge What is the function of history? The history model What is history?...
  • The Plurals in English - The Fascinating World of English

    The Plurals in English - The Fascinating World of English

    Special cases Als het gaat om meer dan 1 persoon of ding wordt in het Engels altijd meervoud gebruikt. Examples: It happened ten years ago. - His two sons are farmers. 10 Let op! In het Nederlands gebruik je dan...
  • Présentation PowerPoint

    Présentation PowerPoint

    Sur le chemin pour descendre a la plage, se dresse: La Chapelle l'Anse du Verger diaporamas carminé La chapelle l'Anse du verger est bien connue des Cancalais et des Malouins
  • Nerve activates contraction

    Nerve activates contraction

    Cells can contain different amounts of the different organelles. Different cell types carry out many different functions. Different types of cells make different types of proteins. All cells have at least a few microvilli, cilia, and flagella.