Re: grant on view with blob

Check if the grant is done via a role. In cases of views you have to grant the privileges directly to the creator
of the view and not via a role.

HtH
eugen

Mike Olivieri schrieb:

> Juhan Kundla wrote:
> >
> > Mike Olivieri wrote:
> > > I am having trouble granting permssions to another user for a view that
> > > uses dbms_lob.getLength(). The message I get is:
> > >
> > > SQL> GRANT SELECT ON MY_View TO User2;
> > > GRANT SELECT ON MY_View TO WBTBuilder
> > > *
> > > ERROR at line 1:
> > > ORA-01720: grant option does not exist for 'SYS.DBMS_LOB'
> > >
> > > MY_View:
> > > CREATE OR REPLACE VIEW MY_View AS
> > > SELECT ParentID, nvl(VSIZE(MyID),0) + nvl(VSIZE(ParentID),0) +
> > > nvl(DBMS_LOB.GETLENGTH(my_blob),0) AS Bytes
> > > FROM My_Table;
> > >
> > > I also tried granting SELECT on my_table before granting on the view;
> > > the table grant worked but the view grant resulted the same error
> > > message.
> >
> > Looks like one must have execute on sys.dbms_lob package privilege
> > granted, before (s)he can select data from this view. And you must have
> > execute on sys.dbms_lob with grant option to grant select from your_view
> > to other users.
> >
> > Juhan
>
> Both users are already able to execute functions from the sys.dbms_lob
> package. Both User1 and User2 have tables with BLOBs; both User1 and
> User2 can use the package outside of the view with no problems. For
> example, I connect as User1 or User2 and try:
>
> select dbms_lob.getLength(my_blob) from user2.My_Table;
>
> I get many rows of data back with no error message. User2 is the owner
> and I have granted ALL on My_Table to user1.
>
> The problem seems to arise only in the view. What are the restrictions
> about executing a function from a package within a view? Or is this a
> special circumstance because of the BLOB datatype?
>
> Thanks again,
> Mike
Received on Tue Mar 28 2000 - 00:00:00 CST