Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

grant execute hanging!?

432 views
Skip to first unread message

Greg G

unread,
Nov 3, 2003, 11:37:18 AM11/3/03
to

I'm trying to do a simple thing 'grant execute on sys.dbms_pipe to auser;'

For some reason I cannot ascertain, this is hanging. I can ctrl-c it,
but I'm not sure why it's not completing. It doesn't get any better if
I try to grant to public. There don't appear to be any locks or
anything out of the ordinary happening.

Also, is there any way to find out what privs have been granted on this
package already?

Thanks.

-Greg G

Chris Leonard

unread,
Nov 3, 2003, 12:10:28 PM11/3/03
to
Greg,

I can't help you with why the grant is hanging without more information, but
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'

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.

--
Cheers,
Chris

___________________________________

Chris Leonard, The Database Guy
http://www.databaseguy.com

Brainbench MVP for Oracle Admin
http://www.brainbench.com

MCSE, MCDBA, OCP, CIW
___________________________________

"Greg G" <ggers...@CAKEctc.net> wrote in message
news:Zq6dnStAj5P...@ctc.net...

Greg G

unread,
Nov 3, 2003, 1:32:36 PM11/3/03
to

Chris Leonard wrote:
> Greg,
>
> 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.

> but
> 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.

> 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.

Where in the OEM can I find that? I didn't see anything in the
pre-defined charts for that.

Thanks.

-Greg G

Brian Peasland

unread,
Nov 3, 2003, 7:03:06 PM11/3/03
to
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.


> > but
>> 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?

>> 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.
>
> Where in the OEM can I find that? I didn't see anything in the
> pre-defined charts for that.

Look under Security.

HTH,
Brian

Greg G

unread,
Nov 4, 2003, 4:16:44 PM11/4/03
to

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

0 new messages