Virtual Private Database Features in
Virtual Private Database Features in
Oracle 10g
Oracle 10g
.
.
SAGE Computing Services
SAGE Computing Services
Customised Oracle Training Workshops and Consulting.
Customised Oracle Training Workshops and Consulting.
www.sagecomputing.com.au
www.sagecomputing.com.au
Agenda
Agenda
¾ Modern security requirements ¾ Virtual Private Database (VPD)
¾ Row Level Security (RLS) and Policies ¾ Contexts and the after_logon_db trigger ¾ Column Level Security
¾ Column Level Masking
¾ Fine Grained Auditing (FGA) ¾ VPD lessons-learnt!
Traditional Oracle RDBMS Security
¾
One central schema user account
¾
Multiple user accounts
¾
DB roles granted privileges on schema
objects
¾
DB roles granted to user accounts
¾
DBA responsible for user account
Contemporary Security
Requirements
¾
No-longer just a technical issue
¾
It's not just about the password!
¾
Makes business sense
¾
Upcoming Australian legislation
¾
Security studies show greatest threat is
Contemporary Security – Web
Driven
¾
Scalable architectures: 1-1000s users
¾
Mid-tier persistence frameworks using
connection pooling
¾ 1 schema account & 1 Oracle user account
¾ Users and permissions handled by application,
not database
Potential Security Solutions
¾ Modify all queries:
¾ Where clauses use functions to determine if rows returned
¾ Columns use functions to determine if you can see column data
¾ An expensive exercise with a maintenance overhead
¾ Will future programmes remember to implement this?
¾ Oracle’s virtual private database (VPD) features provide a solution to all these issues
Row Level Security (RLS)
¾
Enforce an additional predicate (where
clause) against all queries on a table
¾
Use to limit access to rows
SELECT * FROM employees;
Becomes
Policies & dbms_rls
¾ Create a database “Policy” object
¾ Use dbms_rls PL/SQL package dbms_rls.add_policy( object_schema => 'HR' ,object_name => 'EMPLOYEES' ,policy_name => 'EMP_RLS_POLICY' ,function_schema => 'HR' ,policy_function => 'emp_mgmt_pk.f_emp_rls_policy' ,statement_types => 'SELECT');
Policy Functions
¾ Policies are based on PL/SQL functions
PACKAGE BODY emp_mgmt_pk AS
FUNCTION f_emp_rls_policy
(object_schema IN VARCHAR2,object_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'department_id = 80'; END;
Policy Types
¾ Specify via dbms_rls.add_policy call
¾ Dynamic
¾ Default
¾ Re-parsed and evaluated each time
¾ Static
¾ Parsed and evaluated once ¾ Cached in the SGA for speed
RLS Implementation
¾ Allowed on tables, views and synonyms
¾ Not limited to select, can be extended to all DML on an object
¾ Multiple policies on same object are allowed (ANDed)
¾ Applies to all user & users' DML on that object
RLS Examples
¾ Common Examples
¾ Bank Accounts
¾ Employees - Department Head ¾ Others?
RLS Limitations?
¾
Q: What’s the limitation with the RLS policy
we created?
¾
A: Hardcoded static predicate
“department_id = 80”
¾
Need the ability to create a dynamic
predicate, that is dependent on the user
who is currently logged in
Contexts
¾ A session attribute group called a “Namespace”
¾ Exist for the life of the session connection
¾ Applications can use this to store the “context” of the current user
Create Contexts
¾ A context is created with an associated trusted package
¾ Create as Sys
¾ The package is trusted to change the context attributes
Write to a Context
¾ Call dbms_session.set_context in your context package
PACKAGE BODY emp_mgmt_pk AS PROCEDURE p_set_context IS BEGIN dbms_session.set_context( namespace => 'HR_CONTEXT' ,attribute => 'USER_DEPT' ,value => '80'); END; END;
Reading Contexts
¾ Via a sys_context call
SELECT sys_context('HR_CONTEXT','USER_DEPT') FROM DUAL;
SYS_CONTEXT('HR_CONTEXT','USER_DEPT')
---80
Modified Policy
PACKAGE BODY emp_mgmt_pk AS
FUNCTION f_emp_rls_policy
(object_schema IN VARCHAR2,object_name IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN 'department_id = ' || 'sys_context(''HR_CONTEXT'',''USER_DEPT'')'; END; END;
USERENV Context
¾ A default context USERENV exists for all sessions
¾ Provides predefined attributes
¾ Access via:
USERENV Attributes
¾ Some interesting attributes:
¾ DB_NAME Database name
¾ SESSION_USER Authenticated database user name ¾ SESSIONID Session identifier
¾ LANGUAGE Session language and character set
¾ IP_ADDRESS Client IP address
¾ CLIENT_IDENTIFIER User defined client identifier for the session
¾ HOST Database host name
¾ OS_USER Operating system client username etc
¾ Many catches and anomalies in using these so be careful
¾ See Barry Johnson's presentation (July AUSOUG seminar):
Enforcing Context – after_logon_db
¾ Create a database after_logon_db trigger
¾ Fires on every database connection (except Sys)
¾ Enforces context set for all connections
CREATE OR REPLACE TRIGGER after_logon_db AFTER LOGON ON DATABASE
BEGIN
hr.emp_mgmt_pk.p_set_context; END;
RLS Errors
¾ As RLS policies are based on a literal string:
¾ Syntax errors can occur
¾ Syntax errors only detected at runtime
¾ Result in an ORA-28113 'policy predicate error'
¾ Reported in user trace files
¾ Presents a problem for developers as they’ll need access to these to debug
¾ DBAs will need to provide access on the network for quick debugging
after_logon_db Errors
¾ Any error in this trigger will cause all connections to the database to fail, Sys exempted but including OEM
¾ Be wary of basing trigger on PLSQL with dependencies
¾ Any changes to dependencies will invalidate PL/SQL then trigger
¾ I find DBAs get annoyed when you do this for the 10th time
¾ Suggested approach is developers have their own db
¾ Only move code to shared development database once testing complete
Policy Groups
¾ Provides easier maintenance of policies
dbms_rls.create_policy_group( object_schema => 'HR'
,object_name => 'EMPLOYEES' ,policy_group => 'HR_GRP');
¾ New policies can be created in the policy group
¾ Also drop_policy_group
RLS Alternative Uses
¾ Not just useful for security
¾ eg. Time governed queries:
¾ Stop developers from implementing where clause everywhere
¾ Instead use RLS so predicate added regardless
SELECT *
FROM land_assessments
WHERE effective_from_date <= :p_date AND (effective_to_date >= :p_date OR effective_to_date IS NULL);
Column Level VPD
¾ Enforcement when specified columns queried
¾ If columns aren't queried, policy isn't enforced
¾ Policy function similar to RLS policy function:
FUNCTION f_emp_cl_policy
(object_schema IN VARCHAR2, object_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN 'department_id = 90'; END;
Column Level Add Policy
¾ Policy includes specified columns
¾ Create on tables, views but not synonyms
¾ Early implementation of column masking
dbms_rls.add_policy( object_schema => 'HR' ,object_name => 'EMPLOYEES' ,policy_name => 'EMP_CL_POLICY' ,function_schema => 'HR' ,policy_function => 'emp_mgmt_pk.f_emp_cl_policy ' ,sec_relevant_cols => 'SALARY, COMMISION_PCT');
Column Masking VPD
¾ Implemented similar to column-level VPD
dbms_rls.add_policy( object_schema => 'HR' ,object_name => 'EMPLOYEES' ,policy_name => 'EMP_CL_POLICY' ,function_schema => 'HR' ,policy_function => 'emp_mgmt_pk.f_emp_cl_policy' ,sec_relevant_cols => 'SALARY, COMMISION_PCT'
Column Masking Predicates
¾ All rows returned regardless
¾ If predicate evaluates as:
¾ TRUE Column result returned
¾ FALSE Column returned as NULL
¾
If a NULL predicate is returned:
¾ NULL Column result returned
Column Masking Catches
¾
If masked columns are part of a predefined
fk used by queries,
¾ and VPD policy returns null,
¾ query join will not find any results
Masking Uses
¾
Common examples:
¾ Credit card numbers ¾ People's names
VPD Policy Exemptions
¾
Sys is always exempted from VPD policies
¾
Other database users may be exempt
through granting EXEMPT ACCESS
POLICY
VPD Data Dictionary
all_policies
user_policies
all_policy_groups
user_policy_groups
all_policy_contexts user_policy_contexts
v$vpd_policy
VPD Lessons Learnt
¾
Start security early to continuously test
VPD code
¾
Your first instinct in debugging with VPD is
often wrong
¾
Ensure mid-tier programmers are aware of
Performance Implications
¾
Explain plans do show
VPD predicates
¾
Be wary: Trace/TKProf
hides VPD predicates
¾
However indexes etc are
used
¾
Be wary of policy type
VPD Future (Predictions)
¾
Reduce technical limitations
¾
Improved performance analysis support
¾
10g Release 2:
¾ Transparent Data Encryption (TDE) ¾ (not VPD but interesting).
RIP? (VPD Predictions)
¾ Public synonym should become redundant (security hole anyhow)
¾ Replaced by private synonyms per connecting pool account and VPD
¾ Database user accounts will become limited
¾ Database roles will lose their convenience
¾ Free DBAs up for other tasks
¾ Developers will need to consider application security from mid-tier/client-tier
Database Auditing Mechanisms
¾
Database auditing mechanisms:
¾ Statement Auditing ¾ Privilege Auditing
¾ Schema Object Auditing ¾ Fine Grained Auditing
Fine Grained Auditing (FGA)
¾
Audit on rows and columns returned &
modified
¾
Focus on DML executed on sensitive data
¾ 9i Select only
¾ 9i database must be running with CBO ¾ 10g Select, Insert, Update & Delete
dbms_fga Policy
dbms_fga.add_policy( object_schema => 'HR' ,object_name => 'EMPLOYEES' ,policy_name => 'EMP_FGA_POLICY'); ...or.... ,audit_column => 'SALARY' ,audit_condition => 'SALARY >= 5000');FGA Data Dictionary
SELECT * FROM employees;
...results in...
SELECT ... FROM dba_fga_audit_trail;
...populated with...
TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT --- --- --- --- --- ---15-AUG-05 HR CHRIS HR EMPLOYEES (next line)
FGA Data Dictionary
all_audit_policies
user_audit_policies
Alternative FGA Uses
¾
Not just security auditing:
¾ Capture all SQL for index planning ¾ Capture bind variables for designing
histograms
¾ Via handle mechanism can fire PL/SQL to (as
an example) send emails
¾ Or use as a trigger to do other work on Select
Consider VPD/FGA....
¾ Plan for RLS/column masking implementation
¾ Australian Legislation is changing
¾ Required by govt and free-enterprise (liability!)
¾ VPD can be applied retrospectively
¾ However don't forget regression-testing!
¾ Immediately consider FGA to audit unsecure data usage
¾ Put auditing processes in place (don't just collect the data, report on it)
VPD & FGA Availability
¾
Only available in Oracle 10g Enterprise
Resources
¾
Oracle Manuals:
¾ Security Guide
¾ Security Overview
¾ PL/SQL Packages and Types References
¾
Online:
¾ OTN Security
Question & Answers!
[email protected]SAGE Computing Services
SAGE Computing Services
www.sagecomputing.com.au
www.sagecomputing.com.au
Customised Oracle Training Workshops and Consulting
