Re: grant execute hanging!?

From: Greg G <ggershSNACK_at_CAKEctc.net>
Date: Tue, 04 Nov 2003 16:16:44 -0500
Message-ID: <s_CdnUpvm_HJijWiRVn-iw@ctc.net>

Brian Peasland wrote:

> On Monday 03 November 2003 12:32, Greg G thoughtfully contributed:
> 
>

>>>I can't help you with why the grant is hanging without more information,
>>
>> What information can I give you to help diagnose this? I'm working
>>with Oracle 8.1.7.0.0 (yeah, I know, I know) if that helps any.

> 
> 
> Start the GRANT command in one SQL*Plus window. In another, query V$SESSION
> to get the SID for the session executing the GRANT command. Then query
> V$SESSION_WAIT (multiple times) and query V$SESSION_EVENT to see what this
> session is waiting on to complete. 

v$session wait tells me:
	28	  256 library cache pin
handle address		2269941396 874C8E94
pin address		 2285347372 8837A22C
10*mode+namespace	31 0000001F	      0
	    136 WAITING

Of course, I have no idea what this means.

>>>one way to see what users have already been granted the execute privilege
>>>on DBMS_PIPE is to run this query:
>>>
>>>select * from dba_tab_privs
>>>where owner = 'SYS' and table_name = 'DBMS_PIPE'
>>
>> That returns me 6 reasonable-looking rows.
> But did it return the row for the user you are trying to grant to?

No. The grant has timed out every time I've tried it.

>>>Since DBMS_PIPE is a package, only the EXECUTE privilege applies to it,
>>>and
>>>these are all listed in DBMS_TAB_PRIVS. Of course, several GUI tools,
>>>such as the Oracle Enterprise Manager, will run the query for you and
>>>show you these permissions as well.

Hmmm. That's really strange. According to the OEM's security view, I already have execute privs on that package. Now I'm really confused. The function I'm trying to compile complains that sys.dbms_pipe has to be declared, and everything I've seen indicated that this is a permission problem.

-Greg G Received on Tue Nov 04 2003 - 15:16:44 CST