vpd sparks ver2.0
TRANSCRIPT
![Page 1: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/1.jpg)
![Page 2: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/2.jpg)
Welcome
• We will start momentarily
• Phones have been muted for recording purposes• Pressing #5 will un-mute (during Q&A) • 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 Q&A
• Please hold questions until the appropriate time• This presentation is being recorded
• Submit presentation topics by e-mail to [email protected]• Subject - Topic Submission: <Topic>
• Include 3-4 line abstract and desired presentation date
![Page 3: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/3.jpg)
<Insert Picture Here>
Overview of Virtual Private Database
Manish PattaniSystem Performance & Architecture
(System Performance & Architecture
Rapid Knowledge Sharing)
![Page 4: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/4.jpg)
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
![Page 5: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/5.jpg)
What is VPD?
![Page 6: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/6.jpg)
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
![Page 7: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/7.jpg)
History of VPD
![Page 8: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/8.jpg)
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
![Page 9: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/9.jpg)
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
![Page 10: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/10.jpg)
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
![Page 11: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/11.jpg)
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.
![Page 12: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/12.jpg)
History of VPD
• 11g provides integration for Enterprise manager for Row Level Security Policies.
![Page 13: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/13.jpg)
Why use VPD?
![Page 14: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/14.jpg)
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
![Page 15: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/15.jpg)
VPD Components
![Page 16: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/16.jpg)
VPD Components
• Application Context
• PL/SQL Function
• Security Policies
![Page 17: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/17.jpg)
Application Context
• Holds environmental variables
- Application name
- Username
• Gathers information using Dbms_session.set_context
![Page 18: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/18.jpg)
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
![Page 19: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/19.jpg)
Security Policies
• Static
• Non-Static
• Dynamic (Default)
![Page 20: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/20.jpg)
Security Policies (Static)
STATIC
The policy function is executed once, and the resulting string (the predicate) is stored in the Shared Global Area (SGA).
![Page 21: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/21.jpg)
Security Policies (Non-Static)
• SHARED_STATIC
Allows the predicate to be cached across multiple objects that use the same policy function.
![Page 22: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/22.jpg)
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.
![Page 23: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/23.jpg)
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.
![Page 24: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/24.jpg)
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
![Page 25: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/25.jpg)
Enforcement and Exceptions
![Page 26: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/26.jpg)
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
![Page 27: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/27.jpg)
Examples of Dynamic and Static Policies
![Page 28: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/28.jpg)
Example of Dynamic Policy
SQL> create user vpd Create user2 identified by vpd3 default tablespace users4 temporary tablespace temp5 /User created.
SQL> create user pattani Create user2 identified by pattani3 default tablespace users4 temporary tablespace temp5 /User created.
![Page 29: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/29.jpg)
Example of Dynamic Policy
SQL> grant connect,resource to vpd,pattani;
Grant succeeded.
SQL> grant execute on dbms_rls to vpd;
Grant succeeded.
SQL> grant select on scott.emp to pattani,vpd;
Grant succeeded.
![Page 30: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/30.jpg)
Example of Dynamic Policy
SQL> connect vpd/vpdConnected.
SQL> create table vpd_ply as select ename,deptno from scott.emp;
Table created.
SQL> select * from vpd_ply;ENAME DEPTNO---------- ----------SMITH 20ALLEN 30.
![Page 31: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/31.jpg)
Example of Dynamic Policy
SQL> insert into vpd_ply values('PATTANI',30);1 row created.
SQL> commit;Commit complete.
SQL> create or replace function fun_vpd_emp 2 (3 p_schema in varchar2, 4 p_table in varchar2 5 )6 return varchar27 as8 l_retstr varchar2(2000);
![Page 32: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/32.jpg)
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;
![Page 33: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/33.jpg)
Example of Dynamic Policy
21 l_retstr := ltrim(l_retstr,',');22 if (l_retstr is null) then23 l_retstr := '0=1';24 else25 l_retstr := 'DEPTNO IN ('||l_retstr||')';26 end if;27 end if;28 return l_retstr;29 end;30 /Function created.SQL> grant execute on fun_vpd_emp to public;Grant succeeded.
![Page 34: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/34.jpg)
Example of Dynamic Policy
SQL> connect vpd/vpd
SQL> begin
2 dbms_rls.add_policy (
3 object_schema => 'SCOTT',
4 object_name => 'EMP',
5 policy_name => 'EMP_DEPTNO_PLY_1',
6 function_schema => 'VPD',
7 policy_function => 'FUN_VPD_EMP',
8 statement_types => 'SELECT'
9 );
10 end;
11/
![Page 35: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/35.jpg)
Example of Dynamic Policy
SQL> begin
2 dbms_rls.add_policy (
3 object_schema => 'SCOTT',
4 object_name => 'EMP',
5 policy_name => 'EMP_DEPTNO_PLY_2',
6 function_schema => 'VPD',
7 policy_function => 'FUN_VPD_EMP',
8 statement_types => 'INSERT, UPDATE, DELETE',
9 update_check => TRUE
10 );
11 end;
12 /
![Page 36: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/36.jpg)
Example of Dynamic Policy
Login as Scott userSQL> select count(*) from emp;COUNT(*)----------14Login as pattani userSQL> select count(*) from scott.emp;COUNT(*)----------6SQL> delete from scott.emp where deptno<>30;0 rows deleted.SQL> update scott.emp set sal=sal+100;6 rows updated.
![Page 37: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/37.jpg)
Example of Dynamic Policy
SQL> 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
![Page 38: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/38.jpg)
Example with Static Policy
As Scott user
CREATE OR REPLACE function pol_func (objowner in varchar2, objname in varchar2) returnvarchar2 as deptno number;beginreturn 'empno = 7934';end;/
CREATE OR REPLACE FUNCTION myUpper (var in VARCHAR2)RETURN VARCHAR2 DETERMINISTIC ASBEGINRETURN UPPER(var);END;/
![Page 39: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/39.jpg)
Example with Static Policy
• As System or sys user
exec dbms_rls.add_policy (object_schema => 'SCOTT',object_name => 'EMP',policy_name => 'pol1',function_schema => 'SCOTT',policy_function => 'pol_func',statement_types => 'select, index',update_check => true);
As scott user
when trying to create an index when index is also part of statement_type following error is raised.
SQL> 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 1:ORA-28133: full table access is restricted by fine-grained security
![Page 40: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/40.jpg)
Benefits of using VPD
![Page 41: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/41.jpg)
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
![Page 42: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/42.jpg)
Drawbacks of using VPD
![Page 43: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/43.jpg)
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
![Page 44: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/44.jpg)
VPD Summary
![Page 45: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/45.jpg)
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.
![Page 46: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/46.jpg)
Review
![Page 47: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/47.jpg)
Review
• VPD Evolution
• Various VPD Components
• Pros and Cons of VPD
![Page 48: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/48.jpg)
Acknowledgements
![Page 49: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/49.jpg)
Acknowledgements
• Avadhani Yanamandra
• Peter Shi
• Sameer Mehta
• Winston Shirley
![Page 50: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/50.jpg)
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)
![Page 51: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/51.jpg)
AQ&
![Page 52: VPD Sparks Ver2.0](https://reader036.vdocuments.us/reader036/viewer/2022081503/5571f92a49795991698ef4ee/html5/thumbnails/52.jpg)
• Visit us at http://my.oracle.com/SPARKS for additional details and archived presentations