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
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
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!
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...
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...
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...
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...
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?...
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...
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.
Ready to download the document? Go ahead and hit continue!