Minimum privileges required for "Compare User Objects" as non-object-owner?

[Bernhard S]

What minimum privileges are required to "Compare User Objects", if you are not the object owner?
I found out, that to be able to compare a package of another user you need to be granted select on sys.dba_source, sys.dba_procedures and sys.dba_objects.
Of course the preference "Use DBA Views if available" needs to be selected then.
Are there other alternatives allowing compares as non-object-owner, which require less privileges? What minimum privileges do you need to compare other object types then?

 

[Marco Kalter]

For PL/SQL Objects you only need select privileges on dba_source and dba_objects. For other object types you need select privileges on the dba views that hold their definition in the dictionary.

 

[Bernhard S]

Our DBAs don't want a developer to see the real "SYS.DBA_SOURCE" and "SYS.DBA_OBJECTS". So we defined synonyms named "DBA_SOURCE" and "DBA_OBJECTS" in the access user schema pointing to a reduced view of the real "SYS.DBA_SOURCE" and "SYS.DBA_OBJECTS", revealing only code that the access user is supposed to see. This approach doesn't seem to work though. I guess "PL/SQL Developer" tries to use schema-qualified (="SYS.") access to "DBA_%"-Views. Is there any way to achieve the following at the same time using "PL/SQL Developer":
- Letting DBAs define, what source code an access user is supposed to see, i.e. only allowing application user source code visibility
- Being able to compare this source code as this access user with source code in a development environment
- Avoiding to grant execute privileges on those source objects to this access user, just so this code will be visible for the access user in the ALL_SOURCE and ALL_OBJECTS data dictionary views

So how could this be done then?

 

[Marco Kalter]

PL/SQL Developer does indeed explicitly use the SYS.DBA_xxxx views, so I don't immediately see a solution.

 

[T-Gergely]

What about redefining sys.all_source?

 

[Bernhard S]

Hi T-Gergely, we thought about that, but architects and DBAs wouldn't approve that. You'd have to think about that each time you deploy a new system then as well, which is easily forgotten.

The best way seems to be to use "grant debug any procedure" or "grant debug" for all sources you need to have access to and you want to be able to compare.