Oracle8i - view oracle package source code from read only user

Unfortunately, to be able to view other schema's (user) packages you need to be granted 'execute any procedure' privilege.

Now, oracle supplied packages belonging to SYS are 'wrapped' and you cannot see the source code.

I know the owner of the oracle packages and I do not want to look at 'SYS' owned packages anyway, but using all_source I could not view the packages, is there any grant to just view the packages and not execute them?

"Is there any grant to just view the packages and not execute them?"
No.

If you are not the owner of the packages, you cannot view the source code for the packages unless one of these is true:
1. you are logged in as a DBA (which includes the "execute any procedure" privilege)
2. you have been granted "execute" on each package by the package owner
3. you have been granted the "execute any procedure" system privilege by a DBA

Oracle does not have a "view any procedure" system privilege, or a "grant view on [procedure]" grant.

can the execute grant do more than select, like I should not be able to edit or compile the package using this grant, I can then ask dba to assign me the grant..

HI
 You do not need execute permission to view the source code. You need select on dba_source for the same. see the following example

SQL> create user u1 identified by u1;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> grant select on dba_source to u1;

Grant succeeded.

SQL> connect u1/u1@orcl
Connected.
SQL> select text from dba_source where name='P' and owner='SCOTT';

TEXT
--------------------------------------------------------------------------------
PACKAGE p IS

FUNCTION foo (arg sub_t) RETURN NUMBER;
END;
PACKAGE BODY p IS

procedure sp_getData1
 as
   v_query long :='select substr(IERP_SHIP_TO,1,8)ASC_CODE,';
   v_recordCount number :=0;
 begin

TEXT
--------------------------------------------------------------------------------
       select count(*) into v_recordcount from emp;





  end; --end of sp_getData

FUNCTION foo (arg sub_t) RETURN NUMBER IS BEGIN RETURN 2; END;
end ;

21 rows selected.

SQL>

HTH
Vishal

is all_source same as dba_source, can I ask select grant on all_source instead?

no all_source is not same as dba_source. You will need select grant on dba_source.

HTH
Vishal

Yes, Vishal is correct, if you "grant select on dba_source to [your user]" you will be able to read all of the source code that is in the database, regardless of which schema owns the objects.

The "all_source" view is limited to those PL\SQL objects that you own, or have been granted execute rights on, so it is definitely not the same as dba_source.

You will need DBA privilege to grant select on dba_source.

reason why I am inclining towards all_source is release engineer here is not a dba, he or she will not have dba privilege to grant select on dba_source, rather she will have grant select on all_source, not sure how I can retrieve source code for the objects that I own, is there a way to grant select on all_source and then say owner = 'Myowner'

"how I can retrieve source code for the objects that I own?"  That is very easy, just select from "user_source".

You will also need DBA privilege to grant select on all_source.  Yes, it is possible to restrict the records selected from all_source to those owned by a particular user, but remember that the "all_source" view is *NOT* all of the PL\SQL source in the database!  It is just the objects in user_source plus those objects that have been granted explicitly to the user who is logged in.

as long as you are looking for code that you own, you do not need any special privileges. All you need is to select user_source

HTH
Vishal

I tried user_source from my user id login and I got no rows, the owner of the package is not the same as the user id that I login to and that was the reason why I need to specify owner name

did not saw Markgeer comment

In that case either the owner of that package needs to grant you execute privilege or you need select on dba_source

HTH
Vishal

I just got reply from release engineer that she cannot grant execute privelege as this might be violation of SOX rules that developers dont get execute grant, I am not sure that why we do not have select privelege on a package, is execute same as select grant for oracle package.. I am confused here so is my release engineer who thinks that I can execute the package after the grant. I only need select access, I dont think she has dba privelege to grant select on dba_source

She is right, execute privilege will mean that you can execute the package. As I can see there are only two ways for you.

1. Select on DBA_SOURCE as defined in my post above. She will need to login as SYS to grant this privilege
2. If it is one time job, you can create a script and ask her to run it and give you the output.

HTH
Vishal

option -1 will not work as she does not have dba privelege..

it is not one-time job so I need a solution for this..

I can not think of any other solution. Maybe some one else can help

HTH
Vishal