Re: Fine Grain Security and select... for update

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 26 Sep 2002 23:30:33 +1000
Message-ID: <ePDk9.40498$g9.116691@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 - 08:30:33 CDT