My thoughts on cause of the issue were a bit different (not sure I
am right however):
DBMS_LOB works with LOB locators, and these do not have direct
links to the table they came from - they just point to LOB segment
and corresponding index (I may be wrong here). As soon as you
get the locator, DBMS_LOB doesn't care about the table it came from
and whatever policies we may have for it. Otherwise it would have to
track back to the table this LOB locator came from and check policies
on every write request (a lot of extra work to do, especially if we don't
have any policies). Another problem here is what it should do if it
detects that write is in violation of the update policy? Normally, policy
just excludes certain set of rows from DML and they are never touched.
But with DBMS_LOB this is not the case - we already got the locator,
so what should we do? Ignore all writes that violate the policy (what a
waste of resources...)? Or raise an exception? But policies do not raise
exceptions unless update_check is true and is violated, that is, row will
not be visible after update while it was before. With DBMS_LOB this is
not the case - since we could get the locator, changing data it points to
will not change its visibility...
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:ePDk9.40498$g9.116691_at_newsfeeds.bigpond.com...
> Hi Thomas, Vladimir and all,
>
> Interesting !!
>
> I suspect the reasoning for this is that you are updating the LOB via a
> procedure. The procedure is run under the privileges of the owner (in this
> case SYS) and SYS is not affected by FGC. Hence you can update anything you
> can select.
>
> So it does kinda make sense but I do agree it's a hole. The only way I can
> see it being plugged is, hummmm, tricky, not sure !! Maybe having the policy
> set for select as well although this may not always be appropriate. Having
> the dbms.lob procedures with current_user authid might make sense but then
> that opens up other issues.
>
> However, having an implicit update capability just because you can perform a
> select sounds decidedly suz.
>
> Let's keep it really quiet ...
>
> Richard
> "Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message
> news:3D91DE5C.D898E380_at_noaa.gov...
> > Vlad -
> >
> > I'm very impressed with your fine test case. From here, it looks as
> > though you've uncovered a potentially serious security hole that Oracle
> > and all Oracle DBAs should be made aware. I highly
> > recommend that the originator of
> > this thread (Ronnie Yours) open a TAR with Oracle and pursue this issue.
> > A quick search through Metalink doesn't reveal any existing, published
> > bugs or notes on this issue.
> >
> > If Ronnie isn't willing to open a TAR, then I will. Oracle needs to know
> about
> > this if it doesn't know already.
> >
> > Thanks,
> > TG
> >
> > "Vladimir M. Zakharychev" wrote:
> >
> > > Well, for me this is issue with FGAC where LOB update through DBMS_LOB
> > > bypasses FGAC update policy. For example, if you are attempting to
> modify
> > > a LOB via DBMS_LOB in a table for which you don't have UPDATE privilege,
> > > ORA-1031 will be raised, and this is expected (though one can SELECT FOR
> > > UPDATE from such table, as you noted). But if you do the same on a table
> > > where FGAC controls access, update policy is simply not enforced, that
> is
> > > DBMS_LOB does not verify if LOB write is in violation of the policy.
> This is
> > > not good, to say the least, as this allows for covert alteration of data
> in a
> > > policy-protected table and essentially defeats the purpose of FGAC, at
> least
> > > for LOBs... I verified that this issue exists on 8.1.7.4 and 9.2.0.1 -
> positive
> > > on both:
> > >
> > > SQL> select banner from v$version;
> > >
> > > BANNER
> > > ----------------------------------------------------------------
> > > Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> > > PL/SQL Release 9.2.0.1.0 - Production
> > > CORE 9.2.0.1.0 Production
> > > TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
> > > NLSRTL Version 9.2.0.1.0 - Production
> > >
> > > SQL> create table lob_fgac_test ( id number(10,0) primary key,
> > > 2 lob CLOB default empty_clob()
> > > 3 );
> > >
> > > Table created.
> > >
> > > SQL> create table lob_updates_log( tstamp date default sysdate,
> > > 2 usr varchar2(32) default USER,
> > > 3 logstr varchar2(200)
> > > 4 );
> > >
> > > Table created.
> > >
> > > SQL> create or replace trigger trg$r_bu$log_fgac_test
> > > 2 before update on lob_fgac_test
> > > 3 for each row
> > > 4 declare
> > > 5 pragma autonomous_transaction;
> > > 6 begin
> > > 7 insert into lob_updates_log (logstr)
> > > 8 values ('Attempt to update LOB #'||to_char(:old.id));
> > > 9 commit;
> > > 10 end;
> > > 11 /
> > >
> > > Trigger created.
> > >
> > > SQL> insert into lob_fgac_test (id) values (1);
> > >
> > > 1 row created.
> > >
> > > SQL> update lob_fgac_test set lob = 'Original LOB content.' where id =
> 1;
> > >
> > > 1 row updated.
> > >
> > > SQL> commit;
> > >
> > > Commit complete.
> > >
> > > SQL> create or replace function lob_fgac_policy(p1 varchar2, p2
> varchar2)
> > > 2 return varchar2
> > > 3 as
> > > 4 begin
> > > 5 return '1=0';
> > > 6 end;
> > > 7 /
> > >
> > > Function created.
> > >
> > > SQL> begin
> > > 2 dbms_rls.add_policy( object_name => 'LOB_FGAC_TEST',
> > > 3 policy_name => 'LOB_FGAC_TEST_DENY_UPDATES',
> > > 4 policy_function => 'LOB_FGAC_POLICY',
> > > 5 statement_types => 'update',
> > > 6 update_check => TRUE);
> > > 7 end;
> > > 8 /
> > >
> > > PL/SQL procedure successfully completed.
> > >
> > > SQL> select lob from lob_fgac_test where id = 1;
> > >
> > > LOB
> >
> > --------------------------------------------------------------------------
> ------
> > > Original LOB content.
> > >
> > > SQL> update lob_fgac_test set lob = empty_clob() where id = 1;
> > >
> > > 0 rows updated. <--- !!! that's just fine, our policy prevented rogue
> update.
> > >
> > > SQL> select lob from lob_fgac_test where id = 1;
> > >
> > > LOB
> >
> > --------------------------------------------------------------------------
> ------
> > > Original LOB content.
> > >
> > > Cool, content not changed - policy did work as expected.
> > >
> > > SQL> declare
> > > 2 l CLOB;
> > > 3 begin
> > > 4 select lob into l from lob_fgac_test for update;
> > > 5 dbms_lob.writeAppend(l, 6, '+trash');
> > > 6 commit;
> > > 7 end;
> > > 8 /
> > >
> > > PL/SQL procedure successfully completed.
> > >
> > > SQL> select lob from lob_fgac_test where id = 1;
> > >
> > > LOB
> >
> > --------------------------------------------------------------------------
> ------
> > > Original LOB content.+trash
> > >
> > > Hey, what's that?
> > >
> > > SQL> select to_char(tstamp, 'HH24:MI:SS') tstamp,
> > > 2 substr(usr,1,10) usr, substr(logstr, 1, 60) logged_action
> > > 3 from lob_updates_log;
> > >
> > > TSTAMP USR LOGGED_ACTION
> >
> > -------- ---------- ------------------------------------------------------
> ------
> > > 19:42:58 SCOTT Attempt to update LOB #1
> > >
> > > oops... LOB is updated despite the policy we have for the table... More
> than
> > > that, there is no way to catch and log this update since DBMS_LOB writes
> do
> > > not cause update triggers to be fired... Umm... Not good at all...
> > > Not sure if this all is documented (I am sure I've seen the trigger not
> firing
> > > issue documented, but not the fact that DBMS_LOB also bypasses FGAC
> > > policies.) Another broken feature in Unbreakable ("can't break in")
> database...
> > >
> > > --
> > > Vladimir Zakharychev (bob_at_dpsp-yes.com)
> http://www.dpsp-yes.com
> > > Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
> applications.
> > > All opinions are mine and do not necessarily go in line with those of my
> employer.
> > >
> > > "Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
> > > news:130ba93a.0209201242.298b31bf_at_posting.google.com...
> > > > I am curious if you have resolved this issue. I wonder if this is at
> > > > all related to the Fine Grained Security. In general, when you grant
> > > > "select" privilege to a user, you also gives him the right to lock the
> > > > rows in the table. So the grantee can issue "select ... for update.."
> > > > on the table. This SQL just identifies the rows that are needed and
> > > > locks them so nobody else can modify them. However, if the grantee
> > > > subsequently try to actually "update" the rows, errors will be raised,
> > > > as the grantee does not have "update" privilege. So, the point is, you
> > > > can issue a "select .. for update" without actually carrying out the
> > > > "update".
> > > >
> > > >
> > > >
> > > > "Ronnie Yours" <ronnie_yours_at_yahoo.com> wrote in message
> > > news:<am7fka$47k$1_at_nntp-m01.news.aol.com>...
> > > > > Hi,
> > > > >
> > > > > I am in the process of implementing fine grain security on our
> databases and
> > > > > it works great except for the fact that when it comes to Lobs it
> doesnt seem
> > > > > to work, especially in case of updates.
> > > > >
> > > > > Is it a known issue or am I doing something wrong.
> > > > >
> > > > > In my database certain users can see a record but cannot update it.
> > > > >
> > > > > The problem is when trying to update a clob field I need to do a
> select
> > > > > for... update and this selects the row for update irrespective of
> the
> > > > > security on it. This is because the user has select rights on the
> row . But
> > > > > the user should not be able to issue a select ... for update because
> he/she
> > > > > does not have update rights on the row.
> > > > >
> > > > > Now using dbms_lobs.write the user can write into a lob field even
> if he
> > > > > does not have priveleges to do it.
> > > > > Please suggest.
> > > > >
> > > > > Thanks
> > > > > Ronnie
> >
> >
> >
>
>
Received on Thu Sep 26 2002 - 12:01:17 CDT
