Re: view problems with imbedded procedure.....

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 22 Apr 2005 06:38:38 -0700
Message-ID: <1114177118.438698.130180@f14g2000cwb.googlegroups.com>

steven.fox_at_dfas.mil wrote:
> I had a blond moment here.... the procedure is a package....
> one more time....
> Sorry
> Lets clearify.... the view is in one schema while the package it
> references is in another. the view schema has execute on the
> package, but when we try to grant select to the schema which owns the
> package we get an error that the package needs a execute granted
> with grant option which is frond on here...so the question is does
the
> rights to execute a package follow when it is called by a view???

If by "procedure" you mean "function", and by "an error..." you mean "ORA-01720: grant option does not exist for '[function_owner.function_name]'", we can reproduce the problem:

As user WILLIAM:

SQL> create function f return varchar2 as begin return 'OK'; end;   2 /

Function created.

SQL> grant execute on f to donald;

Grant succeeded.

As user DONALD:

SQL> create view v as select william.f as testcol from dual;

View created.

SQL> select * from v;

TESTCOL

---

OK

1 row selected.

SQL> grant select on v to william;
grant select on v to william

                *

ERROR at line 1:
ORA-01720: grant option does not exist for 'WILLIAM.F'

Cause: A grant was being performed on a view and the grant option was not present for an underlying object.

Action: Obtain the grant option on all underlying objects of the view.

You get this error whatever schema you attempt to grant to. In fact, seeing this error when attempting to grant SELECT on the view back to the owner of the function (as well as to other users who can already execute the function) shows that Oracle does not care what other privileges the grantee has, so there is not likely to be a workaround.

According to the documentation:
http://download-uk.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg03sch.htm#863 "If the view owner intends to grant access to the view to other users, then the owner must receive the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION; if not, then the view owner has insufficient privileges to grant access to the view to other users."

If you do get the full grant (i.e. "with grant option"), note that granting SELECT on the view does not pass on any general permission to execute the function to the recipient. It does mean though that now user DONALD can grant execute on WILLIAM.F to whomever he likes, which perhaps explains the frowning.

The only solution that comes to mind, other than granting sufficient privileges on the package and dealing with the frowning, is to create a second function or package that simply calls the original one, and making the grants on that. Received on Fri Apr 22 2005 - 08:38:38 CDT