| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Fine Grain Security and select... for update
I have already opened a Tar and gave a similar test case to Oracle. This is
what they have to say.....
I hope I am not violating any copyright law here....
Oracle's Comment
"I agree with you that it overides the policy that is set using fine grained
access control
policy, but if we analyze how it works, it just appends to the where clause
the predicate that is
set depending on any criteria that is set by the user. As select is not
having any restriction in the
ploicy which has been set, it selects the LOB locator. Only LOB column can
be updated in this
way using dbms_lob.write package. So this behaviour can make FCAG a
limitation on LOB columns.
But Yes, I'll create a Note in Metalink and will mail you the URL. As far as
workaround is concerned
I can think of only one way, along with LOB column select one more column;
for eg in the example you
have posted change the query as
declare
mvclob clob;
mvar varchar2(100) := 'This is updated by ' || user || ' 2';
begin
select ind_col, clob_col into mvclob from lobtab where ind_col = 2 for
update;
update lobtab set ind_col = ind_col where rownum<=1;
/* This will just try to update one row to the same value This acts as a
check for policies that has been set*/
if sql%rowcount>0 then
dbms_lob.write(mvclob, length(mvar), 1, mvar);
end if
end;
/
Please try the above workaround. I've not tested this. But I'm sure this must work."
Thanks
Ronnie Yours
"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
>
> --------------------------------------------------------------------------
 |
 |