Home » Other » General » how to know whether the library or program is INVOKER or DEFINER In Schema (10.2.0.4)
how to know whether the library or program is INVOKER or DEFINER In Schema [message #466454] Mon, 19 July 2010 14:27 Go to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe because SYS.PROCEDURE$ only contains PL/SQL procedure.

Regards
Michel
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 Go to previous messageGo to next message
ramoradba
Messages: 2456
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
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 Go to previous messageGo to next message
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
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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:
  


Current Time: Thu Mar 28 12:37:28 CDT 2024