Toad World® Forums

Problem with viewing Package Body


#1

Hi everybody,

i am trying to solve a problem i am facing with the possibility of viewing a package body.
I have a user A which has a package and a user B who wants to view the package body of the package owned by user A.

I have executed the
Grant execute on A.PACKAGE_NAME to B
Grant debug on A.PACKAGE_NAME to B.

User B has no other privilege in the system except for the CONNECT privilige.

If i log into the database as user B with Toad for Oracle i can view the Package Body with no problem, with just these two privileges granted.

If i log into the DB with Sql Navigator 5.5 i can view the Package Body in the DB Navigator.I just installed the Version 6.2 and the problem persists.
I have tried checking the Enable DBA views on the session , but it still doesn’t work.

Please help me.
How can i work this out using SQL Navigator because developers are like using SQL Navigator.

Thanks


#2

Hi

I presume you wanted to say

“If i log into the DB with Sql Navigator 5.5 i CAN’T view the Package Bodyin the DB Navigator.I just installed the Version 6.2 and the problempersists.”

Am I right?

Regards


#3

I have a similar request to Triggers. If you click the button (See Attached, sorry for the crudeness), this brings up the old navigator and it should work in there.
dbnavigator.bmp


#4

Hi,

I tried using SQLNav(both 5.5 and 6.2) and Toad with your steps. But I found SQLNav and Toad behaved the same:
9i or 10g - both SQLNav and Toad cannot see package body;
11g - both SQLNav and Toad can see package body on the tree and can open it;

May I ask which version of database you were using? Did you use the same one when trying with SQLNav and Toad?

Thanks,
Vincent


#5

The Database is Oracle 10.2.0.1.0.

I am using Sql Navigator 5.5. and Oracle Client 10.2 to connect to the DB, and i can not view the package body.

In another computer i am using Toad for Oracle 9.7.2 with Oracle client 8i to connect to the same DB and the package body is showing perfectly.

Why can’t the same thing happen with SQL Navigator.The developers are used to it.

Thanks.


#6

Hi,

Would you mind provide us with the extract DDL of the user please? Please make sure the preference to ‘Include Object grants’ is ON. I would also recommend you to contact our support chanel if this issue is critical to your team. We can arrange web-ex session to investigate this problem further.

Thanks,
Gwen


#7

You can find attache the DDL of the user.

– Start of DDL Script for User USERB
– Generated 16-Jun-2009 9:25:43 from SYS@test

– Drop the old instance of USERB
DROP USER USERB
CASCADE
/

CREATE USER USERB
IDENTIFIED BY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP1
PROFILE USER_PROFILE
QUOTA UNLIMITED ON USERS
/
GRANT CREATE PROCEDURE TO USERB
/
GRANT CREATE SESSION TO USERB
/
GRANT DEBUG ON ed TO USERB
/
GRANT EXECUTE ON ed TO USERB
/
GRANT DEBUG ON pkg_test TO USERB
/
GRANT DEBUG ON pkg_test TO USERB
/
GRANT EXECUTE ON pkg_test TO USERB
/
GRANT EXECUTE ON pkg_test TO USERB
/

– End of DDL Script for User USERB


#8

Hi,

I can reproduce the issue now. We will have it fixed soon. Thanks for your feedbacks.

Gwen


#9

Hi,

The dev team has reviewed this request and identified it as a high-risk change. Because we’re coming close to the end of our release, we decided not to include it in version 6.2. It will be addressed in version 6.3.

Gwen


#10

Just piling on (sorry) as another user with a similar if not the same issue. I wanted to give more info for when you work on the problem in version 6.3 hopefully.

Like others I can see my own package body code, but cannot see it in the tree or code editor if I am not the owner.

Let me try to answer some troubleshooting questions in advance:

  • Using SqlNav 5.5 with same login and DB, I can see others’ pkg body code just fine
  • Same behavior with Enable DBA Dictionary views checked or unchecked
  • I do have execute privs on the packages in question; I can see Pkg Spec fine
  • I do not have DBA privs - no chance for getting them either :frowning:
  • I do have Oracle SELECT ANY DICTIONARY session priv
  • I can see pkg body source fine with a SQL command Select * from dba_source …

Using your nifty SQL Monitor, the code being executed when I try to view a package body in the tool in 6.2 (1478) reveals:
select object_id,last_ddl_time,status from sys.all_objects
where owner=:owner and object_type=:type and object_name=:name
owner = ‘xxx’ ; TYPE = ‘PACKAGE BODY’ ; name = ‘xxxxxxxxxxx’

This is where I get hung up. With Oracle I’m not sure how you can see others’ package body rows in all_objects unless you are a dba, the owner, or you have Create Any Procedure (0 for 3 in my case). In SqlNav 5.5, the same type of operation seems to query sys.dba_objects for me which is probably why its working there.

Hope this helps.


#11

Hi Hunter,

Thanks for the feedbacks. We will definitely look into it for 6.3.

Gwen


#12

Hi Hunter.

Can i ask you if you can see the package body of another schema using SQL Navigator 5.5 object navigator ?

I did everything you listed and i still can not see the package body in the object navigator of the other schema.

I have execute , debug privilege on the package.
I have enable DBA VIEWS turned on.
I have the Select any dictionary privilege.

I still can not see the package body in version 5.5.0.713


#13

Hi Gwen,

Try this:
As SYSDBA: GRANT CREATE ANY PACKAGE TO
I’m not sure but it’s very likely that this will give you what you are looking for.

Regards
Andre


#14

Would this give the user the possibility to Create a package in SYS or SYSTEM schema ?


#15

Hi Scorpio,

Yes, I can see other users’ package body in ver 5.5.4.847

Here’s what the tool seems to be doing when I try to view the source. With the account you’ve set up, you may want to verify oracle permissions if the user can see the package body and source with queries like this in sqlplus:

– To see the object
select owner,a.object_name,a.object_id,a.created,a.last_ddl_time,decode(a.status,‘VALID’,0,‘INVALID’,1,2) status
from sys.dba_objects a
where a.owner like :schema
and object_name like :object_name
and object_type=‘PACKAGE BODY’

– To see the source
select text,line from sys.dba_source
where owner=:owner
and name=:object_name
and type=‘PACKAGE BODY’
order by line

I don’t have the CREATE ANY PROCEDURE privilege.

Hope that helps.


#16

I granted all you suggested , without the Create any package.
Both your queries return results.
I can see the source of the package using the query you supplied.
Still i can not see the source from the object navigator.

Instead if i revoke all privileges and give only CONNECT and CREATE ANY PACKAGE sys priviliges, it works.I can see the package body. I would’nt opt for this because i don’t want to allow users to modify SYS and SYSTEM schemas, creating new packages there.

Maybe Select any dictionary works with your version of SQL Navigator.