Oracle SQL Developer privileges for Other Users procedures and package bodies

To view code for procedures and package bodies in SQL Developer that are owned by other users, you need the following privilege:

grant select_catalog_role to <user> ;

This privilege does not give access to the$ view, so should be safe to grant out.

You also need to use version 3 or above of Oracle SQL Developer, because of bug 9530717 in version 2 which caused package body to not display for users who did not have elevated security privileges.  Note that Oracle Support Note 1226324.1 still states that SQL Developer v3 is not released yet Рactually it was released March 2011.

Granting ‘select any dictionary’ would also be sufficient for SQL Developer users to see other users procedures and package bodies – but that is more powerful than select_catalog_role especially in 11g and above with this security issue.

Mike Smithers has a related posting on this SQL Developer issue – although his workaround is no longer required with SQL Developer v3.

If moving from an older to a newer version of SQL Developer, you can export your saved connections first – including stored passwords – and then import them into the new version. To do this, right click on ‘Connections’ and choose ‘Export Connections’ or ‘Import Connections’. The export writes the information to an xml file which you can delete afterwards, although the passwords inside it are encrypted anyway.

August 11, 2011

Leave a Reply

Your email address will not be published. Required fields are marked *