OraFAQ Forum: General » how to know whether the library or program is INVOKER or DEFINER In Schema

Home » Other » General » how to know whether the library or program is INVOKER or DEFINER In Schema (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to know whether the library or program is INVOKER or DEFINER In Schema [message #466454]

Mon, 19 July 2010 14:27

leahchow
Messages: 11
Registered: June 2005

Junior Member

Hello,

Is there any way user can find out whether the object (library or program)is invoker or definer?

the statement I used to check the package or procedure is something like this:

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_type = 'PACAKGE'
AND dbo.object_name = 'object_name'
AND dbo.owner = 'username';

this statement shows me whether it is invoker or definer.

But it doesn't show any information about if the object_type=program or libary.

Do you know how to get these information from oracle database?

thanks a bunch
Leah

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466456 is a reply to message #466454]

Mon, 19 July 2010 14:31

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A package is neither a libray nor a program, it is a package.
If you want a library or prgram data you have to select dbo.object_type = 'LIBRARY' or 'PROGRAM'.

Regards
Michel

[Updated on: Mon, 19 July 2010 14:33]

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466460 is a reply to message #466456]

Mon, 19 July 2010 15:05

leahchow
Messages: 11
Registered: June 2005

Junior Member

Yes,

that script just shows how to find the information i asked if the object_type=PACKAGE or FUNCTION. BUt when the object_type=LIBRARY or =PROGRAM, it returns nothing when i run the same script.

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466462 is a reply to message #466460]

Mon, 19 July 2010 15:09

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Maybe because SYS.PROCEDURE$ only contains PL/SQL procedure.

Regards
Michel

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466651 is a reply to message #466462]

Tue, 20 July 2010 07:42

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

Maybe because SYS.PROCEDURE$ only contains PL/SQL procedure

Not exactly

SQL>  select count(*) from sys.procedure$
  2   where obj# in (select object_id from dba_objects
  3   where object_type='FUNCTION');

  COUNT(*)
----------
       351

SQL>  select count(*) from sys.procedure$
  2   where obj# in (select object_id from dba_objects
  3   where object_type='PACKAGE');

  COUNT(*)
----------
       858

Where as it does n`t contain any info for 'LIBRARY' type.

SQL>  select count(*) from sys.procedure$
  2   where obj# in (select object_id from dba_objects
  3   where object_type='PROGRAM')
  4   /

  COUNT(*)
----------
         0

SQL>  select count(*) from sys.procedure$
  2   where obj# in (select object_id from dba_objects
  3   where object_type='LIBRARY');

  COUNT(*)
----------
         0

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466653 is a reply to message #466651]

Tue, 20 July 2010 07:46

leahchow
Messages: 11
Registered: June 2005

Junior Member

So it mean there have no other views or tables show the 'program' or 'library' information as sys.procedure$ table shows, right?

thanks for your help

Leah

Report message to a moderator

Re: how to know whether the library or program is INVOKER or DEFINER In Schema [message #466654 is a reply to message #466651]

Tue, 20 July 2010 07:47

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Not exactly

And then? FUNCTION is not a PL/SQL procedure? Procedure is there as a generic term, like CREATE PROCEDURE privilege it contains ALL PL/SQL procedural objects. This should be understandable when you read the previous messages where we talk about packages.

In addition, your queries do not prove anything as we don't know if you have any object of types PROGRAM or LIBRARY in your database.
It useless to post new queries to prove it, yes, you have, but your previous post did not prove anything. It was just to tell you to take care.

Regards
Michel

[Updated on: Tue, 20 July 2010 07:50]

Report message to a moderator

Previous Topic:	HOW TO READ STATSPACK & AWR REPORT
Next Topic:	ORA-00600: internal error code, arguments: [kokle_lob2lob13:input mismatch]

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 16:10:35 CST 2024