Privileges to see database code in Schema Browser

My analysis shows that you need at the minumum

SELECT from dba_source and execute any procedure privileges in order to view a PACKAGE BODY in schema browser in TOAD.

I am curious, why the second privilege is needed?

I assume that you are talking about a package body in another schema.

Do you see the package body source in the ALL_SOURCE view?

From: gmeltser@aigfpc.com [mailto:bounce-gmeltseraigfpccom@toadworld.com]

Sent: Friday, September 06, 2013 1:00 PM

To: toadoracle@toadworld.com

Subject: [Toad for Oracle - Discussion Forum] Privileges to see database code in Schema Browser

Privileges to see database code in Schema Browser

Thread created by
gmeltser@aigfpc.com

My analysis shows that you need at the minumum

SELECT from dba_source and execute any procedure privileges in order to view a PACKAGE BODY in schema browser in TOAD.

I am curious, why the second privilege is needed?

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

No, i see nothing in the ALL_SOURCE view, but i see both spec and body in the DBA_SOURCE view.

Right…dba_source in this case is Oracle’s requirement, not Toad’s.

I didn’t double check just now, but I don’t think you need execute_any_procedure just to see it in the SB though…you’d only need that (or a direct grant) to
execute it.

From: gmeltser@aigfpc.com [mailto:bounce-gmeltseraigfpccom@toadworld.com]

Sent: Friday, September 06, 2013 1:52 PM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Privileges to see database code in Schema Browser

RE: Privileges to see database code in Schema
Browser

Reply by
gmeltser@aigfpc.com

No, i see nothing in the ALL_SOURCE view, but i see both spec and body in the DBA_SOURCE view.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

Morning All,
Assume you have two users, A and B. A has created a package and wishes to allow B to view the source for BOTH the specification and the body.
Solution 1.
DBA grants SELECT ANY CATALOG to user B and B can now view the code in DBA_SOURCE.
Mildly insecure as B can also see the source code for everything in the database. Possible problem. B cannot execute the package.
Solution 2.
A grants B EXECUTE access on the package. The source code is now visible in B’s own ALL_SOURCE view, but only for the spec, not the body. B can also DESC A.PACKAGE_NAME as well.
ALL_OBJECTS will reveal that only the PACKAGE is visible to B. The BODY is not listed.
Mildly insecure as A might not want B to be able to execute the package!
Solution 3.
A grants B DEBUG privilege to the package.
B cannot execute it, but should be able to view the source in ALL_SOURCE. B can also DESC A.PACKAGE_NAME as well.
ALL_OBJECTS will reveal that both the PACKAGE and PACKAGE BODY are visible to B.
The source code for both the spec and the body can be read from ALL_SOURCE as required.
So solution 3 is the best, B can see the code in both parts of the package owned by A, but B cannot execute it. Toad should therefore be able to view the source code from ALL_SOURCE if access to DBA_SOURCE isn’t available.
HTH
– Cheers,
Norm. [TeamT]