This content has been marked as final. Show 3 replies
One way is, you can grant the debug to the user for that object, but this is only per object basis, not one for everything.
grant debug on object_name to user
The assertion that SQL Dev uses DBMS_METADATA for everything is wrong. DBMS_METADATA is used to show stuff shown on SQL tab e.g. for views. For PL/SQL objects code is extracted from dba_ all_ source. More specific info is need (test case) to investigate why it failed.
No doubt SQL Developer wants to be competitive with Toad but, in terms of raising red flags over inconsistencies in basic functionality, any difference versus SQL*Plus would be more surprising than versus Toad.
DBA_SOURCE and DBA_OBJECTS are public synonyms available in any standard Oracle installation, but you also need the SELECT ANY DICTIONARY privilege to get maximum benefit from them. That lets you view code in another user's schema without having an explicit privilege (like EXECUTE) on a package/procedure/function.
If you already do have such privileges (either SELECT ANY DICTIONARY, or EXECUTE or DEBUG on specific executable objects), and the Code tab is empty, then you will need to provide a test case, as Vadim indicates.
In terms of any case where SQL Developer utilizes DBMS_METADATA, my understanding is the API relies on the SELECT_CATALOG_ROLE privilege if a user does not own or otherwise have an explicitly granted privilege on an object. Developers like to use this API wherever possible in place of writing a script that might break or need maintenance to deal with future versions of the Oracle database.
I have no idea how Toad works, and I probably shouldn't speculate, but here is one scenario that could explain the case you mention without the connection user having any of the privileges noted above. Let's say the Toad client software calls a PL/SQL package that selects from DBA_SOURCE and is installed in a schema that has the SELECT ANY DICTIONARY privilege. All Toad users are granted EXECUTE on this package and therefore inherit SELECT ANY DICTIONARY in the context of running the package. This will work, but at the price of complicating installation of Toad. Also, it doesn't afford an administrator fine-grained control over metadata security.
SQL Developer Team