Re: grant on view with blob

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 Mon Mar 27 2000 - 00:00:00 CST