How can I allow users to view other users procedure & functions code?

Toad for Oracle
1

I don’t want to give out the SELECT_CATALOG_ROLE to our developers, but they want to be able to see other users code in Toad. I have already created a view into dba_source, limiting them access to certain schemas, but they would prefer to click (in Toad) on another schemas’ proc or function, and open up the code , which is in a more viewable format. Is there any easy way to do this?

Thx,

Peter

2

Hi Peter,

Did you ever get an answer to this question or find a solution as I’m facing the same problem as well?

Many Thanks

Sean

3

If you grant EXECUTE on the procedures/functions in question, they will be able to see the source for these objects in ALL_SOURCE.

4

Hi John

Thank you for reply. We have application DBA’s who are not allowed privileges like SELECT_CATALOG_ROLE but want to use the full functionality of toad for investigating problems and performance issues across many schemas. We have tried creating wrapper packages for all the sensitive views so we can filter out the sensitive fields but Toad does this check

select sum(decode(table_name, ‘DBA_SYNONYMS’, 1, 0)) dba_syns,

sum(decode(table_name, ‘DBA_OBJECTS’, 1, 0)) dba_objs

from table_privileges

where owner = ‘SYS’

and table_name in (‘DBA_SYNONYMS’, ‘DBA_OBJECTS’)

and select_priv in (‘Y’, ‘G’);

After it checks to see if the user has SELECT_CATALOG_ROLE.

So it appears there is no way of getting around granting the SELECT_CATALOG_ROLE.

We have 3000 databases and lots of potential toad users who would like this functionality but until we find a solution we cannot roll this properly out.

Any thoughts or help would be greatly appreciated.

Many Thanks

Sean

5

The reason we check for SELECT_CATALOG_ROLE is that if you do have it, Toad will know that it can use the DBA_ views (like DBA_SOURCE, DBA_OBJECTS, etc).

If you don’t have SELECT_CATALOG_ROLE and if DBA_SOURCE, DBA_OBJECTS, etc, are not granted to the user by some other method, then Toad uses ALL_SOURCE, ALL_OBJECTS, etc. These views are limited in that the user can see only the objects that have been granted.

So, if you want your users to be able to see the source of a particular package without granting SELECT_CATALOG_ROLE or DBA_SOURCE, then do this:

GRANT EXECUTE on <package, procedure, or function> to .

Once that is done, then will be able to see the source for <package, procedure, or function> in ALL_SOURCE.

6

Hi John,

This would require a huge amount of objects grants to the required users which isn’t really what we want as it wouldbe a lot of effort and maintenance. Is this the only solution to our pronlem?

Many Thanks

Sean

7

Well, you have to grant something to these users if you want them to be able to see these objects. Toad isn’t going to get you around Oracle’s security measures.

Any of these would work:

  1. make the grants to the users.

  2. make the grants to a role, and then grant the role to the users.

  3. grant SELECT on DBA_SOURCE, DBA_PROCEDURES, DBA_OBJECTS, and maybe a few other DBA_ views, depending on what you want the users to be able to see. You could make these grants directly, or through a role. I could help you come up with a list of views that need to be granted if you want to go this way.

  4. let the users log is as the user that owns the objects that they need to work on.

8

Reply #5 here community.oracle.com/…/2386990 is one way to accomplish the #4 above, without giving away the schema’s password.

Reply #6 on the same thread shows a relatively simple way to manage all those grants.