Skip to Main Content
  • Questions

  • Hiding Package Body Procedures and Functions for another user

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Shailendra.

Asked: August 13, 2024 - 6:37 am UTC

Last updated: January 12, 2026 - 6:01 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Tom,

First of all, I want to thank you for all the things which you have done and doing for Oracle community. It inspire me to learn more and more.

Now my question is --

Is there any way by which we can hide the procedures and functions code so the user can only execute it but cannot see and modify the code inside the package body ?

Thanks you!

and Connor said...

A package body in schema "X" cannot be seen from anyone outside schema "X" unless they are a DBA even if they have execution rights.


SQL> create or replace
  2  package MY_PKG is
  3    procedure P;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace
  2  package body MY_PKG is
  3    procedure P is
  4      x varchar2(100);
  5    begin
  6      select 'mysecretcode' into x from dual;
  7    end;
  8  end;
  9  /

Package body created.

SQL>
SQL>
SQL> grant execute on my_pkg to scott;

Grant succeeded.

SQL>
SQL> conn scott/tiger@pdb21a
Connected.
SQL> select text
  2  from all_source
  3  where name = 'MY_PKG';

TEXT
--------------------------------------------------
package MY_PKG is
  procedure P;
end;


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

CREATE/DEBUG ANY PROCEDURE

Mikhail Velikikh, August 19, 2024 - 9:36 am UTC

> A package body in schema "X" cannot be seen from anyone outside schema "X" unless they are a DBA even if they have execution rights.

Anyone with CREATE ANY PROCEDURE or DEBUG ANY PROCEDURE will see package bodies as far as 19c is concerned. Don't need to be a DBA for that. DBA is just one of the roles that have those system privileges.
Connor McDonald
August 21, 2024 - 5:17 am UTC

Anyone with CREATE ANY PROCEDURE or DEBUG ANY PROCEDURE raises far more questions that being able to *see* code :-)

DBMS_DDL.WRAP

Jim Dickson, August 19, 2024 - 10:48 am UTC

Additionally could WRAP the code
https://docs.oracle.com/en/database/oracle/oracle-database/23/lnpls/plsql-source-text-wrapping.html#GUID-C37788E2-0F5D-430B-8D9C-6CD708223C93
https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/DBMS_DDL.html#GUID-0CF2398C-57A2-4A2E-829B-07EF4AACF5EE
I believe this is not foolproof; if someone has privilege to read the wrapped code, they could reverse-engineer to obtain source code.


Connor McDonald
August 21, 2024 - 5:24 am UTC

DDL wrap is useful but be aware there are plenty of unwrappers out there.

What about trigger

Harris, January 08, 2026 - 8:05 pm UTC

Hi My question is in the same line except for ttriggers?
Can you hide the body of a trigger so no one can see the code except for the owner or maybe users with admin privileges
Chris Saxon
January 12, 2026 - 6:01 pm UTC

To see the body of a trigger in another schema, you need the DEBUG privilege on the trigger's table. With that you can see whatever's there:

create table t ( c1 int );

create or replace trigger trig 
before update on t
for each row 
begin
  :new.c1 := 1;
end;
/

grant create session to demo identified by demo;
grant debug on t to demo;

conn demo/demo

select owner, name, type, line, text 
from   all_source
where  name = 'TRIG';

OWNER    NAME    TYPE       LINE    TEXT                  
CHRIS    TRIG    TRIGGER          1 trigger trig 
CHRIS    TRIG    TRIGGER          2 before update on t
CHRIS    TRIG    TRIGGER          3 for each row 
CHRIS    TRIG    TRIGGER          4 begin
CHRIS    TRIG    TRIGGER          5   :new.c1 := 1;
CHRIS    TRIG    TRIGGER          6 end;


You can call packaged procedures/functions in triggers. So use those if you want to keep trigger logic hidden from other users.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library