Unable to See Package Bodies in SQLDeveloper 2.1.1

After many happy months spent sauntering contentedly through the database, I recently came across a curious little bug in SQLDeveloper 1.5.4 where the Triggers on a View are not displayed in the appropriate Tab.
Not to worry, it’s about time I upgraded to 2.1.1 anyway. Or so I thought. I should have known – it’s the summer and bugs are everywhere.
Incidentally, if you need a workaround for the Views issue ( which seems to afflict all version up to 2.1.x, then a workaround is available here.

Fast forward then and I’m now sitting here front of SQLDeveloper 2.1.1.64.45 on Windows Vista…and wondering what exactly it’s done to all of those package bodies that were there a moment ago.
What follows is a summary of my attempts to find out just what is going on and how to get around it.

Our starting point is that we have a user who, in SQLDeveloper 1.5.4, can see a package body in another user’s schema. SQLDeveloper 2.1.1 however, is refusing to display it in the object navigator tree.
After some experimentation, I’ve managed to create a user with minimal appropriate privileges to replicate this

GRANT connect, select any dictionary TO mike_support IDENTIFIED BY pwd;

Whilst I’m here, I’d better set up a test user with a package body we can look at ( or not, as the case may be !) :

GRANT connect, resource TO mike_user IDENTIFIED BY pwd;

Now, as mike_user :

CREATE OR REPLACE PACKAGE my_secret IS
	PROCEDURE shhh;
END my_secret;
/
CREATE OR REPLACE PACKAGE BODY my_secret IS
PROCEDURE shhh IS
BEGIN
	DBMS_OUTPUT.PUT_LINE('Not telling');
END shhh;
END my_secret;
/

At this stage, we can connect as mike_support in SQLDeveloper 1.5.4 and make a few observations.

We’ve not granted any object privileges on the package, yet we can still see it in the object browser tree.
Although mike_support cannot see the source in ALL_VIEWS, it can in DBA_VIEWS :

SELECT text
FROM all_source
WHERE owner = 'MIKE_USER'
AND type = 'PACKAGE BODY'
AND name = 'MY_SECRET';
-- 
-- Does not return any rows
--
SELECT text
FROM dba_source
WHERE owner = 'MIKE_USER'
AND type = 'PACKAGE BODY'
AND name = 'MY_SECRET';
--
-- returns data
--

Now, let’s move on to SQLDeveloper 2.1.1.

As you’d expect, the ALL_SOURCE and DBA_SOURCE queries return exactly the same results. The package body however, is nowhere to be found in the object browser.

Granting SELECT_CATALOG_ROLE to mike_support does not cause it to re-appear. Neither does granting DEBUG ANY PROCEDURE, which has the effect of making the ALL_SOURCE query return rows.

The lack of efficacy of the SELECT_CATALOG_ROLE means that we can rule out this problem being related to SQLDeveloper using DBMS_METADATA to retrieve the source code.
The fact that we can now see the package body in ALL_SOURCE means that we can also discount permissions on this view being a factor in the marked absence of package bodies.

Ultimately, the only way to get SQLDeveloper to tell us where the bodies are buried is :

GRANT create any procedure to mike_support;

At this point, I’ve revoked both the SELECT_CATALOG_ROLE and DEBUG ANY PROCEDURE.
All mike_support has now is CREATE SESSION ( via the CONNECT role), SELECT ANY DICTIONARY, and CREATE ANY PROCEDURE.
Refresh the package in the object viewer tree and the Package Body node miraculously appears.
Problem solved ! Well, not quite.
We’ve had to grant an extremely powerful privilege to get this to work. Whilst this may be just about OK on a development environment, your DBA is likely to be a little bit squeamish about bestowing this privilege in Live.

Let’s have a quick look at exactly what you can do with this privilege.
First off, mike_support can now create Functions, Procedures, Packages and Package Bodies – not just in it’s own schema, but in ANY schema.

There are a few oddities – CREATE OR REPLACE of existing objects always fail with insufficient privileges but those in the users own schema can be dropped.
To demonstrate, connect as mike_support :

CREATE OR REPLACE FUNCTION hello
	RETURN VARCHAR2 AS
BEGIN
	RETURN 'Loco pero no peligrosso';
END;
/

Function created

CREATE OR REPLACE FUNCTION hello
	RETURN VARCHAR2 AS
BEGIN
	RETURN 'Loco Y peligrosso';
END;
/

CREATE OR REPLACE FUNCTION hello
*
ERROR at line 1:
ORA-01031: insufficient privileges

Now, creating objects in another schema :

CREATE OR REPLACE PROCEDURE mike_user.invasion AS
BEGIN
	DBMS_OUTPUT.PUT_LINE('Ahhh the power !');
END;
/

Procedure created

CREATE OR REPLACE PROCEDURE mike_user.invasion AS
BEGIN
	DBMS_OUTPUT.PUT_LINE('Ahhh the power ! POWER!!!!!');
END;
/

CREATE OR REPLACE FUNCTION hello
*
ERROR at line 1:
ORA-01031: insufficient privileges

DROP PROCEDURE mike_user.invasion
/
DROP PROCEDURE mike_user.invasion
*
ERROR at line 1:
ORA-01031: insufficient privileges

To summarise then, a user with this privilege can :

• create functions, packages and procedures in their own schema
• drop these objects in their own schema
• create these objects in other schemas

We want to ensure that this privilege is being used solely for the purpose for which it was granted.
This can be achieved by means of a Role and a Database Trigger.

CREATE ROLE sqldev211
/
GRANT create any procedure TO sqldev211
/
REVOKE create any procedure FROM mike_support
/
GRANT sqldev211 TO mike_support
/

The user mike_support now has CREATE ANY PROCEDURE only via the sqldev211 role. The package bodies are still visible in SQLDeveloper.

Now for the trigger. We don’t need to worry about the ability to drop objects in the user’s own schema as the trigger will prevent their creation in the first place. So, we’re only concerned with the creation of the relevant object types – FUNCTION, PROCEDURE, PACKAGE and PACKAGE BODY. So, as a user with DBA privileges :

CREATE OR REPLACE TRIGGER chk_create_privs_trg 
    BEFORE CREATE ON DATABASE 
DECLARE    
    
    l_dummy PLS_INTEGER;
    
    --
    -- This query needs to check where the relevant privilege
    -- is inherited from ( or whether it's granted directly)
    --
    CURSOR c_has_priv IS
        SELECT 1
        FROM dba_sys_privs
        WHERE privilege IN (
           CASE ora_dict_obj_owner 
              WHEN USER THEN 'CREATE PROCEDURE'
              ELSE 'CREATE ANY PROCEDURE'
           END, 'CREATE ANY PROCEDURE')
        AND ( grantee = USER
        OR grantee IN (
            SELECT granted_role
            FROM dba_role_privs
            WHERE granted_role != 'SQLDEV211'
            START WITH grantee = USER
            CONNECT BY PRIOR granted_role = grantee)
        );
        
    e_no_priv EXCEPTION;
    
BEGIN
    --
    -- Note - bitter experience teaches us to always leave a back-door
    -- for SYS and SYSTEM in a trigger such as this - just in case
    -- it doesn't quite work as expected first time !
    --
    IF USER NOT IN ('SYS', 'SYSTEM')
        AND ora_dict_obj_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE',
            'PACKAGE BODY')
    THEN
        OPEN c_has_priv;
        FETCH c_has_priv INTO l_dummy;
        IF c_has_priv%NOTFOUND THEN
            CLOSE c_has_priv;
            RAISE e_no_priv;
        END IF;
        CLOSE c_has_priv;
    END IF;
EXCEPTION
    WHEN e_no_priv THEN
        RAISE_APPLICATION_ERROR( -20001,
            'You must have create privileges other than through SQLDEV211 to do this.');
END;
/

At this point, I should say a big thanks to Richard ( see comments below) for pointing out a “slight problem” with the original version of this trigger.
Now, let’s try creating objects as mike_support :

CREATE OR REPLACE FUNCTION impunity
  RETURN VARCHAR2
BEGIN
  RETURN 'Privileges - Pah!';
END;
/

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You must have create privileges other than through SQLDEV211 to do this.

CREATE OR REPLACE FUNCTION mike_user.impunity
  RETURN VARCHAR2
BEGIN
  RETURN 'Privileges - Pah!';
END;
/

ORA-00604: error occurred at recursive SQL level 1
ORA-20001: You must have create privileges other than through SQLDEV211 to do this.

Just as a double-check, now try to create the function in another schema as a user who does have CREATE ANY PROCEDURE from another role or granted directly.

So, there you have it. It’s not the most elegant workaround but it will do the job.