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

Actually, I think DBMS_LOB is run under with the privilege of the current user, not SYS. You can not update the data if you do not have the "update" privilege.
So this problem appears to be limited to FGAC only.

SQL> create table tyu (id number(1), clob_col clob);

Table created.

SQL> insert into tyu values(1,'abc');

1 row created.

SQL> grant select on tyu to scott;

Grant succeeded.

SQL> conn scott/tiger_at_athena
Connected.

• Normal update failed as expected SQL> update jyang.tyu set clob_col='test'; update jyang.tyu set clob_col='test' * ERROR at line 1: ORA-01031: insufficient privileges
  • You can lock the record, no problem SQL> declare 2 x clob; 3 begin 4 select clob_col into x from jyang.tyu for update; 5 end; 6 /

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

• But if you try to update the clob with DBMS_LOB, error is raized. SQL> declare 2 x clob; 3 begin 4 select clob_col into x from jyang.tyu for update; 5 dbms_lob.writeappend(x,6,'wwwwww'); 6 end; 7 / declare * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_LOB", line 789 ORA-06512: at line 5

SQL> "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.
Received on Thu Sep 26 2002 - 19:20:27 CDT