RE: dbms_pipe

Hi David,
How did you upgrade?
Did you use an export by chance (which would exclude sys grants)?
Privileges granted through a role (eg DBA execute any proc) don't work =
in stored code but do from SQL / anonymous PL SQL
so, that (I believe) is why you can execute from anonymous code but not =
from a stored procedure.

In your test system, try logging on as sysdba and granting execute on =
sys.dbms_pipe to account_owner.

IF that works, keep a separate script(s) of all the sys grants - these =
would need to be rerun if rebuilding from an export

Do you still have a version of your 8174 database available?
You could see if this had been done by using
 select * from dba_tab_privs where grantee=3D'account_owner'
OR
select * from dba_tab_privs where grantor =3D 'SYS'
and grantee IN ( select username from dba_users)

HTH,
Bruce Reardon

-----Original Message-----
From: David Boyd
Sent: Wednesday, 15 September 2004 6:53 AM

We upgraded a database from 8.1.7.4 to 9.2.0.5 recently.  The database =
is=20
for one application only.  So the account for the application was =
granted=20
all the system privileges directly in 8i.  A procedure in a package =
owned by=20
the account calls DBMS_PIPE.  In 8i the account was not granted =
'execution=20
on dbms_pipe' explicitly.  After the upgrade, the package body became=20
invalid.  I got "PLS-00201: identifier 'DBMS_PIPE' must be declared" =
when I=20
compiled the package.  Then I tried to grant the DBA role to the =
account. =20
An anonymous block that calls dbms_pipe ran successfully.  But I still =
could=20
not compile the package until I granted the 'execution on dbms_pipe' to =
the=20
account explicitly.  Does any one know if the privilege has been changed =
in=20
9i?  I cannot find any thing in the document.  Our DBA role is a default =

role from Oracle + 'select any dictionary'.

__________________________________________________________________

NOTICE
=20
This e-mail and any attachments are private and confidential and=20
may contain privileged information.
=20
If you are not an authorised recipient, the copying or distribution=20
of this e-mail and any attachments is prohibited and you must not=20
read, print or act in reliance on this e-mail or attachments.
=20
This notice should not be removed.
__________________________________________________________________
--
http://www.freelists.org/webpage/oracle-l

Other related posts: