Oracle – for when it was like that when you got there

Main menu

Post navigation

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 !) :

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 :

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 :

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.

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.

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).

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 !

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 😉