ࡱ> n^ [)isXwRPNG  IHDRFxxtkPLTE R"# !~>?T9@Ά䕚{~fMOKQW`6 #kr͖ש๿X?DJ38#[mtJ]uydqƴ-$,MCL  WJY`RcgZpxkLD\΍UTl ,,D$$-DDTLL\TTd\\m44<\\dLLR+,5$w{8T,4FLYwm\jդܵĽTdZdx෸IThCL\l}S]ljuz튖NT\ڜ{{~3+ iFe7 /fFf|!0u6Eדu9QOe֓Ks;Oؚ{?iðnuoqu3Zo*>S[[L}=^NN46fOϚ{0۱zjl/< |y|a_lc"N5B?%}Ľ]kw ?I~C&֓[46uD#ZxnQ쿗Tc' mF(Vqt _X,4Gb&=!ǚ~K;Wiz$r s-G/xJ3w{Z<>ha^xһ 5`9|$<7__~jRS@YN M廸! #³%]7RmzB)tD; )F{TBR!Md]5V>2wԊoXR%ħpih+O fWse>.]Qě+;]aS`Ňg 2 qTs|+)r~Gom\A1SSi8~b4#: 1H_M:|Ƙr sb4(E,WewV4e;ىqϿX{/Te6d_fZ[=3{;::3[xk'cbf|̱fl)$9۶WkVh!328IʇeMw-KHuߪʥL,=˰.TbriY&OIcvK3yN_rۂ#mBӎKs G2j'[͡raySNәkqжÄS(reqtҁan LȮ%s@ ipKNv{ ;E8?8D" ff!&x]P)ҸnR)4Y.;eL:șf`~sO1YCvE1ϔiV(KXy_A2ѐ1Cɹk̡0`{H"gR^,kY6,5!9bC& k`z{08,crVJ9sopcyFofpLM"'>55š^`RL BhVLl40Scppi=eBY(T-k4%9/X)E4R 0#IGʋ"uTAʺɧ.*"' ţHbr@( H^2ӜI<HkqI93aU(Z!&+!yŕwsVk^(ȅ,0ݤxؓNqF24w*\1 Cb`ނ&07 O T0Rհ)*L4", (DK&r?5ab$JL˺} ;)F4}CDJ!9Ћt oȳ8C-$XH1sS]EN2 ) >rMY!~$]HЗ٬H?]%/{U} ے,z#8/ 4Vl.DՖAͪP8&5$xm1bt|-,2'I.b DL, ]el:Ap~KY7_}jdlƢĢ5}Yu4 j ]$>bȹ(dšiEzKkj. \;&^e,N{@*V9o26ڸB\Ϊّ=[ X YvUյH,IͨH*7;Joꎏ d;_\{81]mtGqv .o#;ّx20-0w|"')gY0vi\#0!0q݇W{O>y7w,1~4:HsܜH(D$M"{jҮ{s@r 0%m#C̘<;,WB/NoLL,rd$#U;+8l[VDt13]}k\m$Cת=91s#$0T1 m1%2YqRnݺ=vg۩ ܕ+ d Α82Q,Y emboՕK:@6#bvt)5q-'_nϷ>BʕiEEz(1آFc%wߍ M&?1=v#]$;[DtA|GE0~711!*?)K98='2W$6Π41OL\oJQ8K$#DWŒݍrD%w"W3ￏ%qHS ;9_T;G;lH60}.GG>լjGɕԻh2EBOv3[m5ΑM|oAȑd;yz%xICmB}LkGZFtl&iY5O)f34[;cB1|أu+0P xO>f;)hn4Z/ ?@B@I8Fێ27IceIENDB`Q(    p/ 0|DTimes New RomanUbbv 0b( 0gDArialNew RomanUbbv 0b( 0g" DComic Sans MSnUbbv 0b( 0gB0DCourier NewSnUbbv 0b( 0g1@DCourierNewSnUbbv 0b( 0c .@  @@``  @n?" dd@  @@``   lr!   $*     !/X$yb$33| Lڭ$fy T=?  ~ `bt&4nC@yg4<d<dv 0bPppp@  <4!d!d4b 0bbuʚ;2Nʚ;<4dddd4b{ 0 bb:2___PPT9/ 0? %O =,qUtah Oracle Users Group0 3Evergreen Database Technologies, Inc. www.EvDBT.Com*4&  *AgendaBrief discussion of the levels of security in the Oracle RDBMS The need for fine-grain or row-level security Oracle8i Virtual Private Database (VPD) feature how it is implemented where it is might be usefulx2L  2&Levels of security in the Oracle RDBMS8Accounts password protection authentication by Oracle server or by client analogous to databases in other DBMS s like Sybase or MySQL Tables, views, and procedures GRANT and REVOKE static permissions roles session-based permission groupings can be passworded Rows ummmm& well& hmmm& gee&  ZZ-Z<ZZZZZ5ZZZ -  &    5Pjr Row-level security???Prior to Oracle8i, row-level security is not something that was provided as part of the database engine the application is made responsible if need be, you could use database views in a manner similar to the data dictionary views USER_xxx, ALL_xxx, DBA_xxx views but in complex applications, the introduction of database views might severely complicate performance tuning until Oracle8.0 and the advent of INSTEAD OF triggers, DML could not be performed on complex views The need for row-level security was not clearly defined...h$Z!;W$D!  ;>The need for row-level securitySatisfying the auditors Generally, SQL statements and stored procedures provide row-level security within the application, and the client-side interface uses these to enforce application security application modules thus act as a proxy, allowing access according to the rules of the application but ad-hoc tools (i.e. SQL*Plus, Discoverer) and low-level application programming interfaces or APIs (i.e. PRO*C, OCI, JDBC, SQLJ) bypass all of this as a result, from an auditor s point of view, an application is not secure if a method of data access exists which does not enforce application security in short, it can be hacked!c8b"< *#& @    V  The need for row-level securityQSatisfying nervous customers and end-users Your application might serve customers who are mortal enemies of one another if they become aware of the fact that they are co-habitating the same database, how can you satisfy them that their data will never, EVER be viewed (or modified) by the wrong party? Often, logical explanations about application security do not provide the necessary reassurance tales about hackers and disgruntled employees are very powerful for political reasons, it might become necessary to create separate databases on separate servers! Talk about a draconian solution!+M`@c!+/`  ?    c  The need for row-level securityAvoiding the amoeba or hydra-headed application Case study: Oracle Applications and multi-org functionality full suite of Oracle Applications (Financials, Manufacturing/ERP, etc) totals to dozens of accounts/schemas, thousands of tables and indexes, and might be integrated with 3rd-party packages (i.e. tax, catalogs) and customizations getting it all installed, configured, integrated, and customized in the first place and then maintaining it over time can be extremely complex imagine doing it 15-20 times, once for each subsidiary or department in your company After all, each subsidiary or department uses the same accounting structures, but they do not want (or are not allowed) to see each other s data0=    ]               9  The need for row-level securityfIn summary, row-level (fine-grain) security might be needed for the sake of: security manageability all of the above others??? The feature of virtual private databases (VPDs) has been introduced with Oracle8i to supplement application security logic built into application code it will also form the core of Oracle s Secure Server 2.0 product line, which is the (long-delayed) successor to the Oracle7-based Trusted Oracle product lineM2M1'F'IVirtual Private DatabasesHow are they implemented? a brief illustration using the history of the multi-org functionality in Oracle Apps as a case study description of the mechanisms simple code examples some ideas for other uses of VPDs:. Oracle Apps Multi-Org$ A full implementation of the Oracle Apps for a single organization within a company is very complex What about an implementation which includes multiple independent organizations (i.e. divisions, subsidiaries, etc) within the same company? Requirements: Each organization is only allowed to access (and modify) it s own data Corporate-level users want to view all data globallyNd|d|  <Oracle Apps Multi-Org (cont d)$  Solutions? Separate installation for each organization? satisfies the  separation requirement fails to satisfy the  global view requirement One big installation for all organizations, and each is on the  honor system to only access/modify their own information? Satisfies the  global view requirement (all too well!) Fails to satisfy the  separation requirement One big installation for all organizations, and some form of row-level security is utilized -V{f\ -V{) 0= <Oracle Apps Multi-Org (cont d).  Starting in the R10.6 timeframe, Oracle implemented multi-org functionality using: database views for each table to be shared amongst multiple organizations each table was renamed  <table-name>_ALL each view named after the original table each user logged on a  OE_<org-name> instead of just  OE each view included a big DECODE statement which selected ORG_ID based on the current session username some implementers embedded PL/SQL functions to replace the big, hardcoded DECODE turned out to be a big mistakeSJQ4 J J~Q     &<Oracle Apps Multi-Org (cont d). >In R10.7 (and R11.0?), things got better and simpler: views and tables named  _ALL still remain but the big DECODE is removed instead, a stored procedure is executed when a user logs on through an Oracle Apps module and the ORG_ID is stored in the Oracle SGA using the supplied packaged procedure SET_CLIENT_INFO in the package DBMS_APPLICATION_INFO ORG_ID value is stored in V$SESSION.CLIENT_INFO the WHERE clause of all multi-org views now contained the simple predicate: AND ORG_ID = USERENV( CLIENT_INFO ) problem is, this can be hacked!6I0L% 6I0L%   VPDsFor VPDs, the idea of storing a security context labels in the SGA continues from R10.7& context is just a label or string stored in the SGA, associated with the session can be retrieved any time during the session But, in order to meet even moderate security schemes, the simplistic scheme of storing/retrieving just data values is inadequate many common security schemes might utilize advanced programming techniques and complex logic might need to interface with operating-system securityrY~ (vv VPDsfor example, in addition to just ORG_ID value, an application s security scheme might have additional considerations such as: time of day or day of week physical location of end-user s connection data values in other tables in the database or conditions out in the operating-system an access control list, which includes inclusion and exclusion logic, possibly in a tree-structured or recursive n-dimensional format revalidation might be necessary after a period of time or number of operations (i.e. security token expiration) You probably can t do this with a stored string saved in the SGA :-)~E!V   8VPD mechanismsThe Optimizer: determines if a security policy is in effect for one (or more) table(s) being accessed by the SQL statement being parsed if so, a user-defined PL/SQL packaged function is executed function contains code which implements the user-defined row-level security policy returns a string which is appended to the WHERE-clause of the SQL statement being parsed the optimizer edits the SQL statement! then, the Optimizer finishes parsing the finished SQL statement after it has been edited, and then the statement is executedSY'}SY  }VPDs: simple case studyRoadmap to setting up a virtual private database 1. Determine what your security scheme will be 2. Create a PL/SQL package to implement it package contains at least one procedure for setting security context (as defined in your scheme) package contains at least one function which takes two input strings (table OWNER and NAME) and returns a VARCHAR2 string 3. Use the new CREATE CONTEXT command to designate the PL/SQL package as the trusted enforcer of the scheme 4. Use the new DBMS_RLS.ADD_POLICY procedure to link a specific PL/SQL function in the trusted package to be executed upon a specific action on a specific table function returns string which becomes a WHERE-clause predicate in the original SQL statement1Z ]1Z,Mf']  VPDs: creating a PL/SQL package *create or replace package vpdtest as procedure setlabel; function getlabel(owner in varchar2, name in varchar2) returns varchar2; end vpdtest; / create or replace package body vpdtest as procedure setlabel is begin if to_number(to_char(SYSDATE, HH24 )) between 9 and 17 then dbms_session.set_context( MYTEST , LABEL , X ); else dbms_session.set_context( MYTEST , LABEL ,  ); end setlabel; A" J30VPDs: creating a PL/SQL package  function getlabel(owner in varchar2, name in varchar2) returns varchar2 is begin if sys_context( MYTEST , LABEL ) <>  X then return  1 = 2 ; else return   ; end if; end getlabel; end vpdtest; /> VPDs: CREATE CONTEXT Once the package is successfully defined, then associate it with a context to make it the privileged enforcement package: CREATE CONTEXT MYTEST USING VPDTEST; the procedure DBMS_SESSION.SET_CONTEXT will fail unless it is executed inside the trusted PL/SQL package protects against hackersz%iC/1VPDs: DBMS_RLS.ADD_POLICYADD_POLICY procedure from the supplied package DBMS_RLS is used to associate actions against tables with specific PL/SQL functions inside the privileged package for a context DBMS_RLS.ADD_POLICY ( object_schema IN VARCHAR2 := NULL, object_name IN VARCHAR2, policy_name IN VARCHAR2, function_schema IN VARCHAR2 := NULL, policy_function IN VARCHAR2, statement_types IN VARCHAR2 := NULL, update_check IN BOOLEAN := FALSE, enable IN BOOLEAN := TRUE);j M % !VPDs: simple case study in action"!SQL> connect scott/tiger SQL> select count(*) from emp; COUNT(*) -------- 0 SQL> exec vpdtest.setlabel SQL> select count(*) from emp; COUNT(*) -------- 14b !'&Some ideas for ways to set the context''Use an AFTER LOGON trigger to fire as someone connects to the database: CREATE OR REPLACE TRIGGER SCOTT.VPDLOGON AFTER LOGON CALL SCOTT.VPDTEST.SETLABEL; Authentication certificate if the SETLABEL procedure is not called, then no access is allowed (as in the example) forces users to call SETLABEL SETLABEL can authenticate the user using rules stored in the database More& ???xHTWd HTWd  VPDs: Performance issuesPrevious row-level security schemes involving database views could complicate SQL tuning issues VPDs, allowing complex logic yet simpler predicates, may avoid such complications Executing some user-defined code during the PARSE phase of SQL execution allows powerful functionality to execute only once, instead of each time a row is retrieved editing in  ORG_ID IN (value-list) can be much faster than adding  ORG_ID IN (sub-query) or  ORG_ID = join-column `Ru`Rr   .  `h*VPDs: the fine print& <Predicates can t be longer than 2000 characters I haven t tested this to see if it is true& :-) Use the table-name in the returned predicate to clarify ambiguities, in the event that a SQL statement hits more than one table with a policy The Optimizer will resolve the table name to the appropriate table-alias before appending the predicate to the SQL statement there is no determinate order for applying the policies The trusted PL/SQL package cannot reference a table which it is protecting results in indefinite locks on library cache pin00K100 |4       A sneaky hack?The trusted PL/SQL package must not change database state using DML (i.e. INSERT, UPDATE, DELETE statements) documentation says that using PRAGMA RESTRICT_REFERENCES(func-name, WNDS) is required this isn t true (so far!) What does this imply? the mechanism of VPDs is almost like a database trigger firing, isn t it? & imagine triggers on SELECT statements... ...the mind reels...mVJ*49   J),YtData WarehousingCSecurity in the decision-support environment is not often discussed How can you control data access in an environment where ease-of-use, accessibility, and even ad-hoc access from a variety of off-the-shelf utilities part of the design? VPDs provide an effective way to control and audit access in numerous creative ways. DD8  RSummaryEven without such hacking (which might be closed off in future versions anyway), the mechanism of VPDs promises some very interesting and creative solutions to problems in security and application management Q&A?$ Dbp Documentation$Oracle8i (8.1.6) Application Developer s Guide - Fundamentals chapter 11 on  Establishing Security Policies section on  Fine-grained Access Control downloadable and viewable at http://technet.oracle.com Oracle Magazine article by Mary Ann Davidson at http://www.oracle.com/oramag/oracle/99-Jul/49sec.html Online example of  Virtual Private Database on my web-site at http://www.evdbt.com/library.htm>/)7f``=/)0  5    ?      `RJ !023/2Pb  ` ̙33` 3` 3333f` 999MMM` f` f3` 3>?" dU@$`b|?" xd @  dd`8 n?" dZ(@   @@``PR    @ ` ` p>> R(     `Igֳgֳ ?``  >*   `Hgֳgֳ ?`   <    `pgֳgֳ ?`   @*pB  Hp?x   Z(gֳgֳ ?HH|  ? Slide Title  #  T8gֳgֳ ?  ;Body Text Second Level Third Level Fourth Level Fifth Level     <  T gֳgֳ?Q @*H  0޽h? ? ( PowerPointu0 5-@( (   TPjJjJ ?G    R*    T|jJjJ ?    T*    Z`jJjJ ?`G   R*    ZxjJjJ ?`   T*    TUU?oyW  ~ Page *Z  ###55FFp  01 ?  #  Tgֳgֳ ? 6  ;Body Text Second Level Third Level Fourth Level Fifth Level     <H  0sTh? ? a(p @8` (     T8xjJjJ ?G    B*   T 4xjJjJ ?   x D*   Z*xjJjJ ?`G  x B*   Z"xjJjJ ?`  x D*   Tp#xUU?oyW  l Page *Z ###55FFH  0sTh? ? a( % -% (     fgֳgֳ ? 0  =  Zgֳgֳ ?X  AOracle8i Virtual Private Databases Tim Gorman 14 February, 2001VB( ( ( (     s gֳgֳ ?` @  " d`8  C ZABC:\Tim\Work\Website\8i_small.gif H H  0޽h ? (  P( x l  C x HH|  x l  C Dx00 x H  0޽h ? (  p( $@ l  C P H0|   l  C LQ` `  H  0޽h ? (  ( x l  C  HH|   l  C ̛`  H  0޽h ? (  ( x l  C $ HH|   l  C ` `  H  0޽h ? (   `( x l  C Virtual Private Databases%  &.  .-2 <Virtual Private Databases%  &.@Times New Roman-  .2 Vv Tim Gorman  .  .!2 G14 February, 2001  .--y`--  @BComic Sans MS-  .?2 .%Evergreen Database Technologies, Inc.          .@BComic Sans MS-  . 2 Twww. .  .2 TEvDBT .  . 2 T.Com  .&C xF(Fx!   sR? &    #/A~T$# 6! #"&$$$$-$$,$-$$&%M.+~$,$,,$5,+,,,D,,/.Y1.0183J,4,64,F4,444<44<94<4<<4L<8D<:<<<T>4@>c?>D?XD@@ALCMMD<\D<DD@TDD\DLLFDNIYJW]JtQKfLB\LCTLDRLL\LLOMfPMPQcR`TTD\TDhTITTL\TNTTTdTTlTU`WYWwYLpZgm[\\L\\Td\Tx\T\\\d\\m\\l]S\]b^ccrxdLdTxdZid\qdygdjhfhhj\tjfkxrkzl\l]lf|lgnmrnqqteztkltttujuzxv}y{v{w~{|l|||}lm{tz{zÔ˜եūά̭ծ鰘ƴʹĴܵջļмüͼɼ辪ôĬĴļĽĿǵ̴̼̾ͳмԼ$$  $$WAᥥBpp1AᥥR0WAspW~ȑAAWAᑑsʑ0AAᑑ.p WAA0s,pAA0ApAssp3A0 p W00ʴBBϴ000p0ʴAʴдѽϜлߴѽʻМᴽʜssss~~~~~ʴ޴ѴsXpXmXppsspppmmmmp~дϽоЦѦёWWTWXWRX`XmXmXpXmQmXm~ϾѽϴϽསsFRBWAWBXRW@WXWTWXRXWmX~ѽѾߖpABABRARWBRRRBRCRBRBRXXsѽіpRBA8?BRABBBBRBRBRBXRXϦ~Х~XABARBA8BBBRBBBBRXXѽѽϖB8ABABABBB8RRBRBXѽѽཅ޼ZE*##;BRTmϾ߽нѦʾӲ <Ͻ߽ϴ˒ӣziFNCNHh PϽߦӣ޶X@WQ*:Ͻ޽Ӳ޽ѦXss~ЦӵнмϵӲϧѽм̵˽ѽz=PPqPϼѵ䵼іpR~[qPP+hsώн䲣~RBXϵ|ϽϽМӲӣABsвq[=+ϽX~q !н~~~ӲӽϽQXP Рн~llssжϾR+ :Бѽ|оϽннӲ!2нϦПӲʻѽѾϾж=2~ʾѻϽмнѾӲѼѾʾϽϽ=EϽϽѽѽПӲжЦϽѽϽqϽϽѽнϠ~ϴϽXlsmӲӲ|Цʽʽ϶ϦϽϴBXmXӲϧϽϽϵϼϽ϶϶BmmWӲӲϾϽЦϽϽМϽϽRXm~ȲдʾнѽнѽϦPqPPP:~~~ϽϴXmв|uϽϽжнϢoqP=q=OsssϽϦXOϼϽϽ|qPPPq=Zsss~ϦTXm~=qPPP=P+t~~sm~pʼϴTm\llwqPPP=PqE~s~smmm~ѴѽTTmTlYl|qPPPqot~s~sss~Ƚ߽llm\m\llvqqʴ߽ʴXlXjYlllvsqqq~ssll\lYkllvxsqqPqqqq|~smmmpRp~llYlllllvxqqPPPqqPPPo~sssmXmsXXp~llllllsxlxPqPPPqP=+ssmm~sXXXsWR~llvsxqqqqqPPPPqPhsmmm~mWTmpTWϴsllllv}PqqqPPPPPPmmXmpRW~pRʜllllvsqqPPPP~~~lmmpXmmʻsllllvv~|qqqPqPqqϼʴʥvllllvx|qqqPPqqqPPq||ϼʥp~lllllwoPPP=PqqqPqP|oP=:Z~pmXmpRW~mXvlllvli:+=++=qPqPo[==!smXWmpRWWWss~sx2!!+++=PPqPP=+q[GTRXsXFAppX~~~~~~vlvx+=+++=PPPoh=+P+ NWWWRRpWW~~~~~~~~~~swvvxx[!+++=q:=P! RXmBWm~pʴ~~~~~~~~~svvvlxxxn+!+++=PoqqPZʜ~~~~~~sssssslvvvxxZ+!++++=Ͻ䣣ʖp~ϴ~~ssssssssssssvllxxkh+!++++=Ϡ+=W`mXϜ~sssssssssssppxx:++++++O}}s~E  P2BFXpW~sssssssppppmmmw:++++++zss9+FR~R~sssppppmmmmmpmmxlvx[[PPPP=}}}}~ss9 P,XWύsppmmmmmmmmmmmXpxvlxx:+==++=~ssN +q^ϽsmmmmmmmmsWpmQWmXvvxh+++++On:PqP:=ϴpmmmmpXpWpWmXXmmXmvlxxz+++++[~~s~ssМsG =P!pϻpXpXpWmWmXmmXmm`WmWxxt+++++PsspsmW9+qqiϽmXpWmXmXjW`````RXTWTO==++P}~~ss~R<|:OsmmXmmXXmWmWmXTWWTWWRWslvv}qoP=Pz}~o+PEsXmWmRWmRXWTWWRWTRWWRWRl~wvvx[=++=[qqpmRmWXWTWRWTWRWRWR;RBBWBlslllxn+!+=+Z}μsmRXWWRXRXWWRWWBWBBFBFWWBWsxsxx^+P=+:qPqsWWTWRRWRWRRBWABWBWWWWRAABAmlvvP=+++}}}}[Pq^WRXRWWWFRFAWFWBWWWABABAABBABXvllls~:==PP:^WRWR;RBBRBWWWAARBABABBABBAAAAllllll~xlwn2=PPoP9RWBFBWFWWWBABABABBABBAAAAAAAAA~lxslsu[=++Pzt[qqq!#BWBWWWARAABABABABBAAAAAAAAAAA1A1~~~u:PqP++o[::PqP==9;WWWBABABABBABAAAAAAAAAAAA1A1A1A1?mss~hhoo[[o[O2?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuwxyz{|}~Root Entry@dO)PicturesrHfCurrent User@FOrSummaryInformation for DBAs(v3PowerPoint Document( DocumentSummaryInformation@.8@.5t@@:i+00#C:\@dw_ch13_err_files@NLLLLM @X@@:i+00#C:\1@N