CODE : View My Source Code -- a Function

If you need to view the source code of a stored program you need to either :
a.  Be the owner of the program
b.  Have EXECUTE privilege on the program
c.  Have EXECUTE ANY ... privilege or the DBA role

If you are not the owner o the program, the owner can grant you access to view but not modify the program.

Here's code for a PL/SQL Function that allows this.  (I wrote this as a Function --- and not as a PL/SQL Procedure -- to be similar to the DBMS_METADATA.GET_DDL Function).

Imagine  that HR is the Application Schema and the owner of Tables and Programs.  Imagine that HEMANT has not been granted the DBA role or an EXECUTE privilege but needs access to view the source code.

Here's a quick method.  ("Quick" meaning that this code can be improved further -- e.g. by adding an Audit Trail and by better Error Handling).

SQL> connect hr/oracle
Connected.
SQL>
SQL> drop table authorized_view_source purge;

Table dropped.

SQL>
SQL> create table authorized_view_source
  2     (username  varchar2(30),
  3      object_type varchar2(23),
  4      object_name varchar2(30))
  5  /

Table created.

SQL>
SQL> create or replace function view_my_source(object_type_in in varchar2, object_name_in in varchar2)
  2  return clob
  3  as
  4  return_clob clob;
  5  line_out varchar2(4000);
  6  line_count pls_integer;
  7  line_no pls_integer;
  8  verify_count pls_integer;
  9  return_source  clob;
 10
 11  begin
 12   select count(*) into verify_count from authorized_view_source
 13  --  check if any of these three predicates fail
 14   where username = user
 15   and object_type = object_type_in
 16   and object_name = object_name_in;
 17
 18   if verify_count = 0 then
 19  -- don't tell if the object exists or not
 20   raise_application_error(-20001,'You are not authorized to view the source code of this object');
 21   return('FAILURE');
 22
 23   else
 24
 25   select count(*) into line_count from user_source
 26   where 1=1
 27   and type = object_type_in
 28   and name = object_name_in;
 29
 30   return_clob := ' ';
 31
 32   for line_no in 1..line_count
 33   loop
 34   return_clob := return_clob || line_out;
 35   select text into line_out from user_source
 36   where 1=1
 37   and type = object_type_in
 38   and name = object_name_in
 39   and line = line_no;
 40   end loop;
 41   return_clob := return_clob || line_out;
 42
 43   return return_clob;
 44   end if;
 45
 46  end view_my_source;
 47  /

Function created.
                                                                                                    
SQL>
SQL> show errors
No errors.
SQL> grant execute on view_my_source to hemant;

Grant succeeded.

SQL>
SQL> -- list all code objects
SQL> col object_name format a30
SQL> select object_type, object_name
  2  from user_objects
  3  where object_type not in ('TABLE','INDEX','VIEW')
  4  order by object_type, object_name
  5  /

OBJECT_TYPE             OBJECT_NAME
----------------------- ------------------------------
FUNCTION                VIEW_MY_SOURCE
PACKAGE                 ANOTHER_PKG
PACKAGE                 DEFINE_MY_VARIABLES
PACKAGE                 DUMMY_PKG
PACKAGE BODY            ANOTHER_PKG
PACKAGE BODY            DUMMY_PKG
PROCEDURE               ADD_JOB_HISTORY
PROCEDURE               SECURE_DML
SEQUENCE                DEPARTMENTS_SEQ
SEQUENCE                EMPLOYEES_SEQ
SEQUENCE                LOCATIONS_SEQ
TRIGGER                 SECURE_EMPLOYEES
TRIGGER                 UPDATE_JOB_HISTORY

13 rows selected.

SQL>
SQL> -- store list of authorzed access
SQL> -- e.g. HEMANT can't view the source for
SQL> --    "ANOTHER_PKG" and "VIEW_MY_SOURCE"
SQL> insert into authorized_view_source
  2  select 'HEMANT', object_type, object_name
  3  from user_objects
  4  where object_type not in ('TABLE','INDEX','VIEW')
  5  and object_name not in ('ANOTHER_PKG','VIEW_MY_SOURCE')
  6  /

10 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select count(*) from authorized_view_source
  2  where username = 'HEMANT'
  3  /

  COUNT(*)
----------
        10

SQL>
SQL>

So, HR has created a Table to list the Users that are authorized to view a certain list of stored programs.  This table, AUTHORIZED_VIEW_SOURCE excludes "ANOTHER_PKG" and "VIEW_MY_SOURCE" from the authorized list  for HEMANT.

Let's see what HEMANT can do :

SQL>
SQL> connect hemant/hemant
Connected.
SQL> -- the return type is a CLOB, so we SET LOMG
SQL> set long 1000000
SQL>
SQL> select hr.view_my_source('PACKAGE','DEFINE_MY_VARIABLES') from dual ;

HR.VIEW_MY_SOURCE('PACKAGE','DEFINE_MY_VARIABLES')
--------------------------------------------------------------------------------
 package
define_my_variables
authid definer
is
  my_application varchar2(25) := 'Human Resources';                             
  my_base_schema varchar2(25) := 'HR';                                          
end;                                                                            


SQL>
SQL> select hr.view_my_source('PACKAGE BODY','DUMMY_PKG') from dual ;

HR.VIEW_MY_SOURCE('PACKAGEBODY','DUMMY_PKG')
--------------------------------------------------------------------------------
 package body dummy_pkg as
procedure dummy_proc is
 begin
  raise_application_error (-20001,'Dummy Procedure');                           
  null;                                                                         
 end;                                                                           
end;                                                                            


SQL>
SQL> select hr.view_my_source('TRIGGER','SECURE_EMPLOYEES') from dual;

HR.VIEW_MY_SOURCE('TRIGGER','SECURE_EMPLOYEES')
--------------------------------------------------------------------------------
 TRIGGER secure_employees
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;                                                                   
END secure_employees;                                                           


SQL>
SQL> -- these two should raise an error
SQL> select hr.view_my_source('PACKAGE','ANOTHER_PKG') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL> select hr.view_my_source('FUNCTION','VIEW_MY_SOURCE') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL>
SQL> select hr.view_my_source('PACKAGE','NONEXISTENT') from dual;
ERROR:
ORA-20001: You are not authorized to view the source code of this object
ORA-06512: at "HR.VIEW_MY_SOURCE", line 20



no rows selected

SQL> 

This shows that HEMANT can view the source code of programs listed in the AUTHORIZED_VIEW_SOURCE table and not any others.   The way I have defined the "Your are not authroized ..." message, it doesn't even show whether the requested program exists or not.
.
.
.