Determine The Owner Of A Package

Sometimes it is useful to know the owner of a PL/SQL Package at runtime.

Hm, why would we not know which owner a package belongs to? There are multiple reasons. A utility package might be installed in several schemata, or in development, every developer might have his own copy of the application schemata.

One way to find the owner, is by inspecting the PL/SQL call stack. The latest entry on the stack references the current PL/SQL object and its owner.

Example of a call stack:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
3e218c390        13  package body PKG_OWNER.PKG_DEFINER_RIGHTS
3e5e67860         6  package body PKG_OWNER2.PKG_REAL

Why don't we just use sys_context('userenv', 'current_schema')? The current schema is not always the package owner. If you are using a PL/SQL package with invoker rights (authid current_user), it shows the caller.

OK, let's start with the preparation for a simple example. We'll create two database users first, pkg_owner (which will own the PL/SQL packages) and pkg_caller (which will invoke them).

create user pkg_owner identified by pkg_owner;
grant connect, create procedure to pkg_owner;

create user pkg_caller identified by pkg_caller;
grant connect to pkg_caller;

Next, we create to packages, pkg_definer_rights with definer rights and pkg_invoker_rights with invoker rights in the pkg_owner schema. Both have a function to retrieve the current schema and another function to get the owner from the callstack.

-- create the test packages in the pkg_owner schema
connect pkg_owner/pkg_owner;
--/
create or replace package pkg_definer_rights
is
  -- get current schema always returns the owner for AUTHID DEFINER packages
  function get_current_schema return varchar2;
  -- the call stack always shows the correct owner, no matter what AUTHID is set to
  function get_owner_by_callstack return varchar2;

end;
/
--/
create or replace package body pkg_definer_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,1,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

--/
create or replace package pkg_invoker_rights
authid current_user
is

  -- get current schema returns the current schema, not the package owner for 
  --   AUTHID CURRENT_USER packages
  function get_current_schema return varchar2;
  -- the call stack always shows the correct owner, no matter what AUTHID is set to
  function get_owner_by_callstack return varchar2;

end;
/
--/
create or replace package body pkg_invoker_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,1,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

grant execute on pkg_definer_rights to pkg_caller;
grant execute on pkg_invoker_rights to pkg_caller;

OK, we're prepared. So let's test them:

-- connect as caller to invoke the test packages
connect pkg_caller/pkg_caller;
--
select pkg_owner.pkg_definer_rights.get_current_schema definer_current_schema
,      pkg_owner.pkg_invoker_rights.get_current_schema invoker_current_schema
,      pkg_owner.pkg_definer_rights.get_owner_by_callstack definer_owner_by_callstack
,      pkg_owner.pkg_invoker_rights.get_owner_by_callstack invoker_owner_by_callstack
from   dual
;

DEFINER_CURRENT_SCHEMA:        PKG_OWNER
INVOKER_CURRENT_SCHEMA:        PKG_CALLER
DEFINER_OWNER_BY_CALLSTACK:    PKG_OWNER
INVOKER_OWNER_BY_CALLSTACK:    PKG_OWNER

As expected, the function pkg_invoker_rights.get_current_schema does not return the package owner, but the current schema. It can be changed using alter session set current_schema=schema.

alter session set current_schema = pkg_owner;
select pkg_owner.pkg_definer_rights.get_current_schema definer_current_schema
,      pkg_owner.pkg_invoker_rights.get_current_schema invoker_current_schema
,      pkg_owner.pkg_definer_rights.get_owner_by_callstack owner_by_callstack
,      pkg_owner.pkg_invoker_rights.get_owner_by_callstack invoker_owner_by_callstack
from   dual
;

DEFINER_CURRENT_SCHEMA:        PKG_OWNER
INVOKER_CURRENT_SCHEMA:        PKG_OWNER
DEFINER_OWNER_BY_CALLSTACK:    PKG_OWNER
INVOKER_OWNER_BY_CALLSTACK:    PKG_OWNER

Yes, the invoker current schema now reflects our alter session command.

If we'd like to include the code of the function get_owner_by_callstack in a common utility package and not include it in each and every PL/SQL Unit we are developing, does our example still work?

To find out, we create a package pkg_real in a new schema, pkg_owner2. This package is a mock up for one of our actual application package that want to make use of our utility to show the package owner:

create user pkg_owner2 identified by pkg_owner2;
grant connect, create procedure to pkg_owner2;

-- grant permissions to pkg_owner2
connect pkg_owner/pkg_owner;
grant execute on pkg_definer_rights to pkg_owner2;

-- create an invoking package
connect pkg_owner2/pkg_owner2;

create or replace package pkg_real
is
  function real_function return varchar2;
end;
/
create or replace package body pkg_real
is
  function real_function return varchar2
  is
  begin
    return pkg_owner.pkg_definer_rights.get_owner_by_callstack;
  end;
end;
/

grant execute on pkg_real to pkg_caller;

OK, now we test our package:

-- call the real one
connect pkg_caller/pkg_caller
--
select pkg_owner2.pkg_real.real_function
from   dual
;

PKG_REAL_OWNER
--------------
PKG_OWNER

Is this correct? No! The owner is pkg_owner2, not pkg_owner... What went wrong? Let's look at the call stack:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number
name
3e218c390        13  package body PKG_OWNER.PKG_DEFINER_RIGHTS
3e5e67860         6  package body PKG_OWNER2.PKG_REAL

Of course! The top most entry in the call stack is now the utility function, not the real application package that we're interested in. So we change the method to get the second line in the call stack which will be the actual package that uses the utility function:

connect pkg_owner/pkg_owner
--
create or replace package body pkg_definer_rights
is

  function get_current_schema return varchar2 
  is
  begin
    return sys_context('userenv', 'current_schema');
  end;

  function get_owner_by_callstack return varchar2
  is
  begin
    -- the first occurrence of the pattern . in the call stack
    --   is . of the least recently called package (i.e. this one)
    -- therefore take the . string out of the call stack and
    --   then take just the first part before the dot as the owner
    return regexp_replace(regexp_substr(dbms_utility.format_call_stack
                                       ,'[A-Z][A-Z0-9_$#]*\.[A-Z][A-Z0-9_$#]*',1,2,'i'
                                       )
                         ,'([A-Z][A-Z0-9_$#]*)\.([A-Z][A-Z0-9_$#]*)','\1',1,1,'i'
                         );
  end;

end;
/

And we try again:

-- try again
connect pkg_caller/pkg_caller
--
select pkg_owner2.pkg_real.real_function
from   dual
;

REAL_FUNCTION
-------------
PKG_OWNER2

OK, that looks better :-)