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.

About these ads

11 thoughts on “Unable to See Package Bodies in SQLDeveloper 2.1.1

  1. In testing, if the user has CREATE PROCEDURE privilege, the cursor will return rows and allow the user to utilize their ANY privilege.

    • Richard,

      I was assuming that, as this was a controlled environment :

      1) The supporter would not have any write privs ( as in the post)
      2) SQLDeveloper would not be used as an end-user tool at-all.

      A wise man once said “Assumption is the mother of all cock-ups”. Well, maybe he didn’t, but he jolly well should have.

      Anyhow, changing the where clause in the c_has_priv trigger as follows might help :

      WHERE privilege IN
      CASE ora_dict_obj_owner 
           WHEN USER THEN 'CREATE PROCEDURE'
            ELSE 'CREATE ANY PROCEDURE'
      END, 'CREATE ANY PROCEDURE'
      

      So, when a user is trying to create an object in their own schema the IN list should evaluate to ‘CREATE PROCEDURE’, ‘CREATE ANY PROCEDURE’.
      When the object is being created in another schema the IN list should evaluate to ‘CREATE ANY PROCEDURE’, ‘CREATE ANY PROCEDURE’.

      Not the most elegant solution, I grant you, but it does seem to be quite effective.

  2. I was considering a solution that would evaluate the schema where the object was being created but couldn’t come up with “ora_dict_obj_owner”. This worked. Thank you!

  3. I was experiencing a performance problem with the cursor. Outside of the trigger, the cursor worked perfectly but, inside the trigger, the cursor took nearly a minute to complete. I’ve rewritten the trigger as follows:

    create or replace
    TRIGGER CHK_CREATE_PRIVS_TRG
    BEFORE CREATE ON DATABASE
    DECLARE
    — CREATE ROLE viewsource;
    — GRANT CREATE ANY PROCEDURE TO viewsource;
    — GRANT viewsource TO :user;
    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
    — This query needs to check where the relevant privilege
    — is inherited from ( or whether it’s granted directly)

    FOR r IN (
    SELECT 1
    FROM SYS.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 SYS.DBA_ROLE_PRIVS
    WHERE GRANTED_ROLE != ‘VIEWSOURCE’
    START WITH GRANTEE = USER
    CONNECT BY PRIOR GRANTED_ROLE = GRANTEE
    )
    )
    AND rownum = 1
    )
    LOOP
    RETURN ;
    END LOOP;
    END IF ;
    RAISE e_no_priv;

    EXCEPTION
    WHEN e_no_priv THEN
    RAISE_APPLICATION_ERROR( -20001, ‘You must have create privileges other than through VIEWSOURCE to do this.’);
    END;
    /

    • I’ve not been able to replicate this, though obviously I’m working on a much smaller system at home – less users, less grants to wade through etc.
      Thanks for posting your updated code. First signs are that this bug is fixed in SQLDeveloper 3.0, so hopefully you won’t have the same problem as and when you upgrade.

  4. I realize I’m quite late to this party, but I did find that granting debug any procedure, connect debug session will allow a user to view package body code without granting create any procedure (provided they’re debugging at the time).

    I hope this helps!
    -T. J.

    • T.J.
      Thanks for the tip.
      It’s another option for people to consider.
      This is all still pretty relevant as Oracle haven’t got around to a production release of SQLDeveloper 3 yet. Just hope they sort out the DBMS_OUTPUT glitches in the EA releases before they do !

      Mike

  5. Pingback: Oracle SQL Developer privileges for Other Users procedures and package bodies | Andrew Fraser DBA

    • At the time I wrote this post, SQLDeveloper 3.0 was still in beta and 2.1.1 was the latest production release. You are correct, this problem has indeed been resolved in version 3.0. Still prefer SQL*Plus though ;-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s