ࡱ> Y[X#` 0=bjbj\.\. R>D>D5\ \ \ \ 4 !< x6|H!H!H!H!H!$$$5555555$7h\:6 %$$%%6\ \ H!H!26 , , ,%\ RH! H!5 ,%5 , ,Nx3 " 5H! 'test_user', object_name => 'stock_level', function_schema => 'test_user', policy_function => 'stock_predicate', statement_types => 'select, insert, update, delete', update_check => TRUE, policy_name => 'stock_restrict', enable => TRUE, static_policy => FALSE -- v9 feature only ); end; . / In this call to the add_policy procedure, we identify our table and the function we have written. We also note that we want the predicate to be created on selects, inserts, updates, and deletes. The update_check is a bit like the with check option for views; it ensures that we are not allowed to insert or update a row in so that it fails the test in the generated predicate and becomes invisible. Finally, we give the policy (the combination of object, function and actions) a name, and enable it. The last parameter in the procedure, static_policy, is a very important one that I will discuss in a moment. If we now insert some data, we can see the effects of the policy. We start by connecting as the table owner (test_user) to load the data. insert into stock_level values(sysdate,1,100,'Confection','Hershey'); insert into stock_level values(sysdate,2,60,'Deli','Hershey'); insert into stock_level values(sysdate,3,60,'Confection','Cadbury'); insert into stock_level values(sysdate,4,60,'Deli','Cadbury'); commit; When we connect query the data as this user, we will see all four rows. However, if we connect as user u1 and select * from test_user.stock_level, we will see: STOCK_DAT PRODUCT_ID QTY DEPT_ID SUPPLIER_CODE --------- ---------- ---------- -------------------- ------------- 19-OCT-03 1 100 Confection Hershey 19-OCT-03 2 60 Deli Hershey And if we connect as user u2 and run exactly the same query, we will see: STOCK_DAT PRODUCT_ID QTY DEPT_ID SUPPLIER_CODE --------- ---------- ---------- -------------------- ------------- 19-OCT-03 1 100 Confection Hershey 19-OCT-03 3 60 Confection Cadbury As you can see, each user sees a different set of data. The original query has been modified on the fly, the reference to the table stock_level has been replaced by a reference to an in-line view that contains our generated predicate (think what that might do to efficiency, especially in complex outer joins). For example the simple select executed by user u2 will have been changed into: Select * from ( select * from stock_level where dept_id = 'Confection' ) By the way, if you see Oracle error ORA-28113: policy predicate has error you have probably got a typing error copying out all the duplicated single quote marks in the policy function Oracle is telling you that the text it has generated does not fit as a legal where clause. Problems There are inevitably a few problems with this mechanism. To start with, under Oracle 8.1 you cannot see the generated predicate anywhere in the system as far as v$sql and trace files are concerned the modified SQL statement simply does not exist. You can work around this problem by setting sql_trace to true and then setting event 10730 in a session that is using RLS. If you do this then every hard parse of a statement will generate a section of trace file that looks something like the following: Logon user : U1 Table/View : TEST_USER.STOCK_LEVEL Policy name : STOCK_RESTRICT Policy function: TEST_USER.STOCK_PREDICATE RLS view : SELECT "STOCK_DATE","PRODUCT_ID","QTY","DEPT_ID","SUPPLIER_CODE" FROM "TEST_USER"."STOCK_LEVEL" "STOCK_LEVEL" WHERE (supplier_code = 'Hershey') This problem has been addressed in Oracle 9 (although the efficiency of the solution is a little questionable) by the introduction of the view v$vpd_policy. A simple query against this view can pick up the following information: ADDRESS : 6F5664F0 PARADDR : 6F5638AC SQL_HASH : 1816753535 CHILD_NUMBER : 0 OBJECT_OWNER : TEST_USER OBJECT_NAME : STOCK_LEVEL POLICY_GROUP : SYS_DEFAULT POLICY : STOCK_RESTRICT POLICY_FUNCTION_OWNER : TEST_USER PREDICATE : supplier_code = 'Hershey' Based on the paraddr, sql_hash, and child_number from this view, you can query the v$sql view to find the related SQL with a query like the following: Select sql_text from v$sql where address = '6F5638AC' and hash_value = 1816753535 and child_number= 0 The inefficiency of this is that the v$vpd_policy view includes the x$kglcursor object which is the object underlying v$sql anyway so you might as well define your own version of the v$vpd_policy view to avoid the pointless extra join. Even then there is no efficient path between the two x$ objects underpinning the view. But there are other issues I promised that I would mention the static_policy parameter of the add_policy procedure again. This is a boolean parameter introduced in Oracle 9 to give you a choice of two evils. If you set the parameter to true then the security predicate seems to be generated just once on the first hard parse which means that user u2 would end up running exactly the same query as user u1 if user u1 just happened to be first person to run the query. On the other hand, if the parameter is set to false, then the security function is executed (apparently twice) for every single execution (not just parse) of the query, and the function is executed through the following fairly hefty, anonymous, pl/sql block, which isnt going to do much for concurrency and scalability. begin p := STOCK_PREDICATE(:sn,:on); :v1 := substr(p,1,4000); :v2 := substr(p,4001,4000); :v3 := substr(p,8001,4000); :v4 := substr(p,12001,4000); :v5 := substr(p,16001,4000); :v6 := substr(p,20001,4000); :v7 := substr(p,24001,4000); :v8 := substr(p,28001,4000); :v9 := substr(p,32001,767); :v10 := substr(p, 4000, 1); :v11 := substr(p,8000,1); :v12 := substr(p, 12000, 1); :v13 := substr(p,16000,1); :v14 := substr(p, 20000, 1); :v15 := substr(p,24000,1); :v16 := substr(p, 28000, 1); :v17 := substr(p,32000,1); end; I suspect this change was an emergency fix in response to the observation that some time-variant security predicates were not being re-evaluated when required. The response, however, is a bit extreme. The issue has been addressed more subtly in version 10g of Oracle, where several levels of variability have been introduced for the policy type. A final thought there is a suggestion from Oracle that it is not a good idea to use joins to reference tables to impose security (think what all those in-line views could do) and that you should try to restrict your security predicates to simple usage of the sys_context() function. But look what the SQL Reference (version 9.2, page 6-154) says about sys_context(): Note: SYS_CONTEXT returns session attributes. Therefore, you cannot use it in parallel queries or in a Real Application Clusters environment. Conclusion Row-level security is quite easy to design and impose, but there are costs you need to be aware of. In particular, if you are using RLS on version 8, you might get a nasty surprise when you migrate to version 9. For relatively simple requirements Im not convinced that you really need to go any further than the deliberate creation of views described in the first article in this series. Jonathan Lewis is a freelance consultant with more than 18 years experience of Oracle. He specialises in physical database design and the strategic use of the Oracle database engine, is author of 'Practical Oracle 8I - Designing Efficient Databases' published by Addison-Wesley, and is one of the best-known speakers on the UK Oracle circuit. Further details of his published papers, presentations and seminars and tutorials can be found at http://www.jlcomp.demon.co.uk, which also hosts The Co-operative Oracle Users' FAQ for the Oracle-related Usenet newsgroups. .   (,8hut,6 Zd  c!p!"""####%%&&&&''4(Y()#)))))T*^*}****,,.../ //;/A/0000<0b0g00011t1111!2 h:U'>* h:U'56 h:U'6h:U'].  5 P c { xyUV^ o#==VPQ_23_vJhvw7Y 8<ACEF!!6"7"}""#@# o#@#H#I###-$p$$$$5%6%y%%%6&7&'''''((%)&)/)&+'+ o#'+;+b++++K,L,2-3-\-----(.T.....//////// o#/1121 3 3N4O4W4445=5z555 6D6}66677S9T9999t; & F^ o#!2%2222222;3@33333667788T9Z9f9s:v:t;u;====h:U'OJQJ h:U'5CJ h:U'6h:U' h:U'56t;u;======7 0P@PBP. A!"#8$8%88 7 0P@PBP. A!"#8$8%88 7 0P@PBP. A!"#8$8%88  P`P Normal'CJOJQJ^J_HaJhmH sH tH J`J Heading 1$./@&a$ 5>*\<`< Heading 2@& 5>*\8`8 Heading 3@&5\` Heading 47$&$d%d&d'd/+D@&5>*CJOJQJ\^JaJ~`~ Heading 57$&$d%d&d'd/+D@&5CJOJQJ\^JaJDA@D Default Paragraph FontVi@V  Table Normal :V 44 la (k@(No List F@F Normal Indent0^`0<+`<  Endnote TextCJaJ4@4 Header  o#D`"D  Footnote Text ^5\>O2> Double Indent ^2oB2 Action 5>*\8"`8 Caption xx5\>ob> programCJOJQJ^JaJp-`rp  Macro Text"  ` @ OJQJ^J_HhmH sH tH rB`r Body Text1&$d%d&d'd/+DCJOJQJ^JaJ0U@0 Hyperlink>*B*BC`B Body Text Indent6]O bullet pointx & F hh>T^h`@V@@ FollowedHyperlink>*B* <O< aw list body  & F&O& n  & FDY`D  Document Map-D  OJQJ^J5t35 R 9R rR.5555h0h0Xh0h0h0.5Pc{x y U V P Q _ 23_vJhvw7Y 8<ACEF67}@HI-p56y67  %!&!/!&#'#;#b####K$L$2%3%\%%%%%(&T&&&&&''''''''1)2) + +N,O,W,,,-=-z--- .D.}...//S1T1111t3u355h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0h0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000!2='V@#'+/t;= "#$%&(=!qr/Xr$9C_iyuUir$߀M'$,,Gi# L0e0e     A5% 8c8c     ?1 d0u0@Ty2 NP'p<'pA)BCD|E||@q 0(  B S  ?5;<];4454459*urn:schemas-microsoft-com:office:smarttagsplaceB*urn:schemas-microsoft-com:office:smarttagscountry-region "BMQ[dn*N]`hw07,5 )49HMV[jo~Zdcp"CN !!T"]"/$<$$$&&&&&' '';'@'''''''''((0(;(b(g(((t)))))),,n,t,,,,,,,,, --'---F-L-d-j-----------..0.6.M.S.j.p.00D1O1555;Q[dn|39`hwMQuy<?7=}@F##''''''''M)Q)))O,T,X,Y,{-~----- ..E.I.}..H1P155333333333333333333333333333333333333333333333355|df}@^N~RHHhMw8( %j^"~ |ޑ@ ^ )ކSN30GJcy>_~eCs) :?w0F-g} ^`.^`.^`.^`. ^`OJQJo( ^`OJQJo( ^`OJQJo( ^`OJQJo(hh^h`. hh^h`OJQJo(*hh^h`. hh^h`OJQJo(hh^h`. hh^h`OJQJo( hh^h`OJQJo(@hh^h`B*OJQJo(@hh^h`B*OJQJo( hh^h`OJQJo( hh^h`OJQJo(hh^h`.X~}|cy>_~eCsSN30@F-g}~:?w^ )|d@h h^h`OJQJo(:U'l?_55@H5@UnknownGz Times New Roman5Symbol3& z ArialO1 CourierCourier New?5 z Courier New5& zaTahoma"CPhx7ù&:r-a-a!P2d552PHP)?l?_2Row level security - part 2(Oracle RLS FGAC VPD security performanceJonathan LewisJonathan Lewis`                Oh+'0`t     Row level security - part 2Jonathan Lewis,Oracle RLS FGAC VPD security performance<Part 2 of an introduction to Oracle's Row Level Security Normal.dotJonathan Lewis406Microsoft Office Word@HR7@d@e@-՜.+,0L px   ,$Oracle design security performacneJL Computer Consultancya5 Row level security - part 2 Title  !"#$%&'()+,-./0123456789:;<=>?@ABCDEFGIJKLMNOQRSTUVWZRoot Entry F'ǡ\1Table*;WordDocumentRSummaryInformation(HDocumentSummaryInformation8PCompObjq  FMicrosoft Office Word Document MSWordDocWord.Document.89q