Browser filters incompletely

[bcoulam]

Hello fellow PSD users and Marco.

PL/SQL Developer was just rolled out to lots of developers at my current employer to get them off TOAD and save us a boatload of cash. Both tools seem to have an issue showing the objects that belong to other accounts.

Being the Development DBA and designer/tuner for the last 10 years at various shops, I'm used to having direct access to the account that owns all the application's objects. But all these developers are used to having access to personal accounts, or "gateway" accounts that don't own anything, but instead have roles and privileges granted to see and use the objects in the application owner account.

When they log into PSD, the default filter gives them empty nodes in the browser tree, because -- of course -- they don't own anything. So they have to expand the Users node and expand the object-owner account. When they do so, PSD only show tables, views, sequences and pl/sql packages, procedures and functions. It does not show object-owner's triggers, materialized views, synonyms or types to which they've been granted access.

So one thing I tried was to change the "My Objects" filter to set owner = object-owning-account instead of owner = user. This helps. Logged in as one of these object-less developers, I can now see the other account's objects under my main browser tree nodes as if they were mine. But still, the nodes for triggers, views, materialized views, synonyms and types are all empty. Why?

Is this a bug in PSD's use of all_objects and other data dictionary views, or am I just slow and not using the tool right?

 

[Marco Kalter]

To view objects for which you cannot grant privileges, like tiggers, packages bodies and type bodies, you need select privileges the sys.dba_objects and sys.dba_source views. The "Use DBA views if available" preference (Tools > Preferences > Oracle / Options tab page) needs to be enabled as well.

 

[bcoulam]

Thanks Marco,

I'll see if I can get management approval for those two views, or SELECT ANY DICTIONARY to be granted to the object-less "gateway" accounts.

On a related note, I'm trying to create a new folder in the Browser so that I can see application contexts.

It's a bit tricky in that any contexts actually get owned by SYS. The only way I know of to see these contexts is by access to DBA_CONTEXT or all_objects where object_type = 'CONTEXT'. Since the browser uses all_objects, I tried this in the folder definition:

WHERE object_type = 'CONTEXT' AND owner = 'SYS';

But it doesn't work. Any ideas?

 

[bcoulam]

Alright, I was able to get approval to test this out. I granted the empty "gateway" account additional rights than usual, and modified the "My Objects" filter to be "owner = 'ACCOUNT'". Per your recommendation, I granted select on dba_objects and dba_source. On my first test to see if it made a difference, I tried to expand the Triggers node in the tree. It gave me a little dialog "Properties of ACCOUNT.TRIGGER not available" once for each trigger in the object-owning schema. Unfortunately, the object-owning ACCOUNT schema (not its real name of course) has 295 triggers, so it took me a while to get through all those error dialogs. When I'd clicked through all of them, finally control returned to the Browser, and all of ACCOUNT's triggers showed under Triggers, but the References node doesn't work, and they can't reverse-engineer into an editor pane. I tried granting SELECT privs on dba_triggers, but that didn't make a difference.

But the Materialized Views node works beautifully now. So too do the others.

Jobs, DB Links and Directories still don't show anything either.

Is there another grant missing to get triggers, jobs, links and directories working?

 

[Marco Kalter]

WHERE object_type = 'CONTEXT' AND owner = 'SYS';

But it doesn't work. Any ideas?

Only object types that are explicitly supported by PL/SQL Developer can be included. Context objects do not yet fall into this category.

Is there another grant missing to get triggers, jobs, links and directories working?

Probably. I will check it out.