All the Perl that's Practical to Extract and Report

Oracle package ownership

jdavidb on 2007-04-11T17:14:14

I need to figure out how to determine, from within code in a PL/SQL package, the owner of the package. The Oracle USER function returns the userid of the user running the session. Since Oracle uses a definer-rights system, where running code executes as the code's owner instead of the code's executer (unless otherwise specified), I thought I could just include a call to USER and would receive the name of the owner. This does not appear to work, at least for a standalone function on the server (although I'll be trying within the package itself in a minute).

all_source

bart on 2007-04-11T19:38:32

All source code is stored, line by line, in a system table called all_source. Try

select distinct owner from all_source where type='PACKAGE BODY' and name='PACKAGE_NAME'
(where you replace PACKAGE_NAME by the real name of your package) and you'll probably be closer to home. If you're allowed to touch that source.

Well, you probably can work something out from there.

Re:all_source

jdavidb on 2007-04-12T02:06:49

But the problem I was trying to resolve involved an identical package in two different schemas, which in that solution would result in duplicate results from ALL_SOURCE, and leave me with the same conundrum.

FWIW, there's a record in ALL_SOURCE for every line of code. A quicker query to get the same result would be FROM all_objects WHERE object_type = 'PACKAGE'

Re:all_source

bart on 2007-04-14T09:28:32

I knew you'd find a solution once you were pointed in the right direction! :)

One uses what one has used before, and I had used ALL_SOURCE (and USER_SOURCE) but not the many other stuff in a similar vein. In the meantime, I've played a little with other options, and I found that

  • If you decide to use ALL_SOURCE, it's better to select on type='PACKAGE' instead of type='PACKAGE BODY', because the latter only shows items you have write access to (usually just your own packages), while the former shows you all items you can use. And, of course, the number of records/lines is much less in the PACKAGE and in the PACKAGE BODY.
  • ALL_OBJECTS is indeed a nice directory list of everything you have access to

Re: Oracle package ownership

Mr. Muskrat on 2007-04-11T20:10:05

I wasn't able to reply earlier so I created a journal entry of my own with the same title: Oracle Package Ownership.

Re: Oracle package ownership

jdavidb on 2007-04-12T02:09:16

Thank you!! I believe SYSCONTEXT is the solution I was looking for (although I was able to synthesize another workaround this afternoon, but it still didn't solve this problem in the general case).

Re: Oracle package ownership

Mr. Muskrat on 2007-04-12T03:07:00

You're welcome. :)

Using the SYS_CONTEXT function with the USERENV namespace has replaced the old USERENV function.