[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4313
  • Last Modified:

Oracle8i - view oracle package source code from read only user

I have a read-only login to production and I want to select and see some of the oracle packages that are stored.. I could not retrieve anything if I use select * from user_source or all_source where type = 'PACKAGE'. How do I see this, I have TOAD but that still does not show anything in schema browser under procs...
0
mahjag
Asked:
mahjag
  • 7
  • 7
  • 4
  • +1
1 Solution
 
MikeOM_DBACommented:

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.
0
 
mahjagAuthor Commented:
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?
0
 
Mark GeerlingsDatabase AdministratorCommented:
"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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
mahjagAuthor Commented:
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..
0
 
vishal68Commented:
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
0
 
mahjagAuthor Commented:
is all_source same as dba_source, can I ask select grant on all_source instead?
0
 
vishal68Commented:
no all_source is not same as dba_source. You will need select grant on dba_source.

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
mahjagAuthor Commented:
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'
0
 
Mark GeerlingsDatabase AdministratorCommented:
"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.
0
 
vishal68Commented:
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
0
 
mahjagAuthor Commented:
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
0
 
vishal68Commented:
did not saw Markgeer comment

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

HTH
Vishal
0
 
mahjagAuthor Commented:
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
0
 
vishal68Commented:
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
0
 
mahjagAuthor Commented:
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..
0
 
vishal68Commented:
I can not think of any other solution. Maybe some one else can help

HTH
Vishal
0
 
Mark GeerlingsDatabase AdministratorCommented:
We do not get to change the way Oracle works, even if we want to.  Dba_source is *THE ONLY* place in Oracle that all PL\SQL source code in the database can be viewed.  By default, this is only available to accounts that have DBA privilege or the "select_catalog_role".  Only a DBA can grant either of these.

If you want a non-DBA account to be able to view all of the PL\SQL source code, you will need to log in once as a DBA, and either: "grant select on dba_source to [user_name];" or "grant select_catalog_role to [username];".
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now