Virtual Private Database

1
(No Transcript)
2
Welcome

• We will start momentarily
• Phones have been muted for recording purposes
• Pressing 5 will un-mute (during QA)
• Pressing 5 again will place line back on mute
• Avoid chat sessions during the presentation
• Chats may be leveraged at the end of the
  presentation for QA
• Please hold questions until the appropriate time
• This presentation is being recorded
• Submit presentation topics by e-mail to
  sparks_us_at_oracle.com
• Subject - Topic Submission ltTopicgt
• Include 3-4 line abstract and desired
  presentation date

3
Overview of Virtual Private Database
(System Performance Architecture Rapid
Knowledge Sharing)

• Manish PattaniSystem Performance Architecture

4
Agenda

• What is VPD?
• History of VPD
• Why use VPD?
• VPD components
• Enforcement and Exceptions
• Examples with Dynamic and Static Policies
• Benefits of using VPD
• Drawbacks of using VPD
• Acknowledgements
• Review

5
What is VPD?
6
What is VPD?

• Acronym for Virtual Private Database
• VPD enables you to enforce security, directly on
  tables, views or synonyms
• Sometimes referred to as Oracle Row-Level
  Security (RLS) or Fine Grained Access Control
  (FGAC)
• Allows to define which rows users may have access
  to

7
History of VPD
8
History of VPD

• Oracle VPD was introduced in Oracle 8i Version
  8.1.5 as a new solution to enforce granular
  access control of data at server level
• Dynamically returns a predicate against a target
  table
• This activity is transparent to the user
  executing the SQL

9
History of VPD

• In Oracle8i, the VPD provided the following key
  features
• - Fine grained Access Control
• - Application Context
• - Row Level Security
• - VPD support for table and view

10
History of VPD

• Oracle9i expanded the Virtual Private Database
  features as follows
• - Oracle Policy Manager
• - Partitioned fine-grained access control
• - Global application context
• - VPD support for synonyms

11
History of VPD

• Oracle 10g makes the following three major
  enhancements in Virtual Private Database
• Column-Level Privacy - It increases performance
  by limiting the number of queries that the
  database rewrites. Rewrites only occur when the
  statement references relevant columns. This
  feature also leads to more privacy.
• Customization - With the introduction of four new
  types of policies, you can customize VPD to
  always enforce the same predicate with a static
  policy or you can have VPD predicates that change
  dynamically with a non-static policy.
• Shared Policies - You can apply a single VPD
  policy to multiple objects, and therefore reduce
  administration costs.

12
History of VPD

• 11g provides integration for Enterprise manager
  for Row Level Security Policies.

13
Why use VPD?
14
Why use VPD?

• Protect confidential and secret information
• Regulations such as HIPAA and SOX
• You can have one database and control the
  delivery of the data to the right people
• VPD inclusive with Enterprise Edition no fees

15
VPD Components
16
VPD Components

• Application Context
• PL/SQL Function
• Security Policies

17
Application Context

• Holds environmental variables
• - Application name
• - Username
• Gathers information using Dbms_session.set_context
  

18
PL/SQL Function

• Functions are used to construct and return the
  Predicates that enforce the row-level security
• The function must be called in the correct
  standard, to ensure that the policy can call the
  function correctly
• Function returns a value

19
Security Policies

• Static
• Non-Static
• Dynamic (Default)
•       

20
Security Policies (Static)

•                               
• STATIC 
• The policy function is executed once, and the
  resulting string (the predicate) is stored in the
  Shared Global Area (SGA).

21
Security Policies (Non-Static)

•                               
• SHARED_STATIC
• Allows the predicate to be cached across
  multiple objects that use the same policy
  function.

22
Security Policies (Non-Static)

• CONTEXT_SENSITIVE 
• The server always executes the policy
  function on statement parsing. The server will
  only execute the policy function on statement
  execution if it detects context changes. This
  makes it ideal for connection pooling solutions
  that share a database schema and use application
  contexts to actually perform the user identity
  switching.

23
Security Policies (Non-Static)

•                               
• SHARED_CONTEXT_SENSITIVE 
• The same as CONTEXT_SENSITIVE except that the
  policy can be shared across multiple objects that
  use the same policy function.

24
Security Policies (Dynamic)

•                               
• DYNAMIC
• The default, which makes no assumptions about
  caching. This policy will be invoked every time
  the SQL statement is parsed or executed

25
Enforcement and Exceptions
26
Enforcement and Exceptions

• VPD is not enforced during DIRECT path export
• VPD policies cannot be applied to objects in the
  SYS schema
• Any users with EXEMPT ACCESS POLICY directly or
  indirectly thru a role, are exempt from VPD
  enforcements
• Administrators can enforce VPD policies on index
  maintenance operations by specifying INDEX with
  the statement_types parameter

27
Examples of Dynamic and Static Policies
28
Example of Dynamic Policy

• SQLgt create user vpd ?
  Create user
• 2 identified by vpd
• 3 default tablespace users
• 4 temporary tablespace temp
• 5 /
• User created.
• SQLgt create user pattani ?
  Create user
• 2 identified by pattani
• 3 default tablespace users
• 4 temporary tablespace temp
• 5 /
• User created.

29
Example of Dynamic Policy

• SQLgt grant connect,resource to vpd,pattani
• Grant succeeded.
• SQLgt grant execute on dbms_rls to vpd
• Grant succeeded.
• SQLgt grant select on scott.emp to pattani,vpd
• Grant succeeded.

30
Example of Dynamic Policy

• SQLgt connect vpd/vpd
• Connected.
• SQLgt create table vpd_ply as select ename,deptno
  from scott.emp
• Table created.
• SQLgt select from vpd_ply
• ENAME DEPTNO
• ---------- ----------
• SMITH 20
• ALLEN 30.

31
Example of Dynamic Policy

• SQLgt insert into vpd_ply values('PATTANI',30)
• 1 row created.
• SQLgt commit
• Commit complete.
• SQLgt create or replace function fun_vpd_emp
  
• 2 (
• 3 p_schema in varchar2,
• 4 p_table in varchar2
• 5 )
• 6 return varchar2
• 7 as
• 8 l_retstr varchar2(2000)

32
Example of Dynamic Policy

• 9 begin
• 10 if (p_schema user) then
• 11 l_retstr null
• 12 else
• 13 for user_rec in
• 14 (
• 15 select deptno
• 16 from vpd_ply
• 17 where ename user
• 18 ) loop
• 19 l_retstr l_retstr','user_rec.deptno
• 20 end loop

33
Example of Dynamic Policy

• 21 l_retstr ltrim(l_retstr,',')
• 22 if (l_retstr is null) then
• 23 l_retstr '01'
• 24 else
• 25 l_retstr 'DEPTNO IN ('l_retstr')'
• 26 end if
• 27 end if
• 28 return l_retstr
• 29 end
• 30 /
• Function created.
• SQLgt grant execute on fun_vpd_emp to public
• Grant succeeded.

34
Example of Dynamic Policy

• SQLgt connect vpd/vpd
• SQLgt begin
• 2 dbms_rls.add_policy (
• 3 object_schema gt 'SCOTT',
• 4 object_name gt 'EMP',
• 5 policy_name gt 'EMP_DEPTNO_PLY_1',
• 6 function_schema gt 'VPD',
• 7 policy_function gt 'FUN_VPD_EMP',
• 8 statement_types gt 'SELECT'
• 9 )
• 10 end
• 11/

35
Example of Dynamic Policy

• SQLgt begin
• 2 dbms_rls.add_policy (
• 3 object_schema gt 'SCOTT',
• 4 object_name gt 'EMP',
• 5 policy_name gt 'EMP_DEPTNO_PLY_2',
• 6 function_schema gt 'VPD',
• 7 policy_function gt 'FUN_VPD_EMP',
• 8 statement_types gt 'INSERT, UPDATE, DELETE',
• 9 update_check gt TRUE
• 10 )
• 11 end
• 12 /

36
Example of Dynamic Policy

• Login as Scott user
• SQLgt select count() from emp
• COUNT()
• ----------
• 14
• Login as pattani user
• SQLgt select count() from scott.emp
• COUNT()
• ----------
• 6
• SQLgt delete from scott.emp where deptnoltgt30
• 0 rows deleted.
• SQLgt update scott.emp set salsal100
• 6 rows updated.

37
Example of Dynamic Policy

• SQLgt insert into scott.emp(empno,ename,deptno)
  values
• (9999,'VPD',10)
• insert into scott.emp(empno,ename,deptno)
  values(9999,'VPD',10)
• ERROR at line 1
• ORA-28115 policy with check option violation

38
Example with Static Policy

• As Scott userCREATE OR REPLACE function
  pol_func (objowner in varchar2, objname in
  varchar2) returnvarchar2 as deptno
  numberbeginreturn 'empno 7934'end/ CREA
  TE OR REPLACE FUNCTION myUpper (var in
  VARCHAR2)RETURN VARCHAR2 DETERMINISTIC
  ASBEGINRETURN UPPER(var)END/

39
Example with Static Policy

• As System or sys user
• exec dbms_rls.add_policy (object_schema gt
  'SCOTT',object_name gt 'EMP',policy_name gt
  'pol1',function_schema gt 'SCOTT',policy_function
  gt 'pol_func',statement_types gt 'select,
  index',update_check gt true)As scott
  userwhen trying to create an index when index
  is also part of statement_type following error is
  raised.SQLgt CREATE INDEX emp_i ON scott.emp
  (SUBSTR(myupper(ename),1,20)) CREATE INDEX
  emp_i ON scott.emp (SUBSTR(myupper(ename),1,20)) 
                             ERROR at line
  1ORA-28133 full table access is restricted by
  fine-grained security

40
Benefits of using VPD
41
Benefits of using VPD

• Dynamic Security
• No need to maintain complex roles and grants
• Multiple Security
• You can place more than one policy on each
  object, as well as stack them on other base
  polices.
• No backdoors
• Users can no longer bypass security polices
  embedded in applications, as they are attached to
  the data

42
Drawbacks of using VPD
43
Drawbacks of using VPD

• Requires Oracle User ID
• VPD requires that an Oracle user Id be defined
  to every person who connects to the database.
  This adds maintenance and overhead
• Hard to audit
• It is hard to write an audit script that
  defines the exact access for each user

44
VPD Summary
45
VPD Summary

• Security
• By attaching security policies to tables,
  views, or synonyms, fine-grained access control
  ensures that the same security is in force, no
  matter how a user accesses the data.
• Simplicity
• Adding the security policy to the table, view,
  or synonym means that you make the addition only
  once, rather than repeatedly adding it to each of
  your table-, view-, or synonym-based
  applications.
• Flexibility
• You can have one security policy for SELECT
  statements, another for INSERT statements, and
  still others for UPDATE and DELETE statements.

46
Review
47
Review

• VPD Evolution
• Various VPD Components
• Pros and Cons of VPD

48
Acknowledgements
49
Acknowledgements

• Avadhani Yanamandra
• Peter Shi
• Sameer Mehta
• Winston Shirley

50
References

• Oracle Database Security Guide Documentation
• VPD White Papers
• Metalink article 250795.1 - 10G Policy Enforced
  Only When the Relevant Column is Queried in Any
  Way
• Metalink article 281829.1 - Evolution of Fine
  Grain Access Control FGAC Feature From 8i to 10g
  and it has lots of links to FGA
• http//asktom.oracle.com
• http//www.petefinnigan.com/orasec.htm (Oracle
  security papers)

51
Q

A
52

• Visit us at http//my.oracle.com/SPARKS for
  additional details and archived presentations