right-click Describe option not in list

[HSchuerger]

We are on Oracle 11gR2.
We have version 10.0.5.1710 installed.
We have SELECT on DBA views granted via a role.
We have Tools, Pereferences, Oracle, Options, "Use DBA Views if available" selected.

Right-click on a table-name does not include Describe as an option.

I had this security configured and tested it and it seemed to work. Our developers complained that it was not working for them. I observed their actions, and it indeed was not working for them. I had them try the test-account that I was using and it did not work for them. And now it does not work when I use the test-account.

The security does work in TOAD.
The goal is for developers to be able to describe objects without having SELECT on objects until they have created a CRUD analysis. They are able to navigate in the Objects list and view table structure but not package logic.

I did run with DebugSQL, so I am aware that the package logic is not available because it uses ALL_PROBE_OBJECTS, which is based on ALL_OBJECTS, so developers would only see logic for which they have been granted EXECUTE permission. I have yet to determine why the right-click Describe option is not available for tables/views in the SQL Window. I will post a comment on this thread with the DebugSQL that ran for the attempted right-click.

The following security allows us to achieve our goal in TOAD:
GRANT SELECT ANY DICTIONARY TO usr_view_metadata;
GRANT select_catalog_role TO usr_view_metadata;
GRANT SELECT ON budba.schema_info TO usr_view_metadata;

We also include the following to allow developers to search the metadata directly:
GRANT SELECT ON dba_dependencies TO usr_view_metadata;
GRANT SELECT ON dba_tab_columns TO usr_view_metadata;
GRANT SELECT ON dba_tab_comments TO usr_view_metadata;
GRANT SELECT ON dba_col_comments TO usr_view_metadata;
GRANT SELECT ON dba_objects TO usr_view_metadata;
GRANT SELECT ON dba_source TO usr_view_metadata;

 

[HSchuerger]

declare
c integer := 0;
p1 varchar2(500);
p2 varchar2(500);
expr varchar2(500);
dblink varchar2(500);
part1_type integer;
object_number integer;
dp integer;
guard_error exception;
pragma exception_init(guard_error, -16224);
begin
bject_type := null;
bject_owner := null;
bject_name := null;
:sub_object := null;
expr := art1;
if art2 is not null then expr := expr || '.' || art2; end if;
if art3 is not null then expr := expr || '.' || art3; end if;
loop
begin
sys.dbms_utility.name_resolve(name => expr,
context => c,
schema => bject_owner,
part1 => p1,
part2 => p2,
dblink => dblink,
part1_type => part1_type,
object_number => object_number);
if part1_type = 1 then bject_type := 'INDEX'; end if;
if part1_type = 2 then bject_type := 'TABLE'; end if;
if part1_type = 4 then bject_type := 'VIEW'; end if;
if part1_type = 5 then bject_type := 'SYNONYM'; end if;
if part1_type = 6 then bject_type := 'SEQUENCE'; end if;
if part1_type = 7 then bject_type := 'PROCEDURE'; end if;
if part1_type = 8 then bject_type := 'FUNCTION'; end if;
if part1_type = 9 then bject_type := 'PACKAGE'; end if;
if part1_type = 12 then bject_type := 'TRIGGER'; end if;
if part1_type = 13 then bject_type := 'TYPE'; end if;
if part1_type = 28 then bject_type := 'JAVA SOURCE'; end if;
if part1_type = 29 then bject_type := 'JAVA CLASS'; end if;
if bject_type is null then
select object_type into bject_type
from sys.dba_objects
where object_id = object_number;
end if;
exception
when guard_error then
raise;
when others then
null;
end;
c := c + 1;
if c > 9 then
dp := instr(expr, '.', -1);
if dp > 0 then
if :sub_object is not null then
:sub_object := '.' || :sub_object;
end if;
:sub_object := upper(substr(expr, dp + 1)) || :sub_object;
expr := substr(expr, 1, dp - 1);
c := 0;
end if;
end if;
exit when object_type is not null) or (c > 9);
end loop;
if bject_type is not null then
if p1 is null then
bject_name := p2;
elsif p2 is null then
bject_name := p1;
if bject_name = art1 and art2 is not null then
:sub_object := art2;
end if;
if bject_name = art2 and art3 is not null then
:sub_object := art3;
end if;
else
bject_name := p1;
:sub_object := p2;
end if;
return;
end if;
begin
if art2 is null and art3 is null then
select 'USER', null, art1
into bject_type, bject_owner, bject_name
from sys.dba_users u
where u.username = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
begin
if art2 is null and art3 is null and :deep != 0 then
select 'ROLE', null, art1
into bject_type, bject_owner, bject_name
from sys.session_roles r
where r.role = art1
and rownum = 1;
return;
end if;
exception
when no_data_found then
null;
end;
if :deep != 0 then
begin
if art2 is null then
select constraint_type, owner, constraint_name
into bject_type, bject_owner, bject_name
from sys.dba_constraints c
where c.constraint_name = art1
and c.owner = :cur_schema
and rownum = 1;
else
select constraint_type, owner, constraint_name, art3
into bject_type, bject_owner, bject_name, :sub_object
from sys.dba_constraints c
where c.constraint_name = art2
and c.owner = art1
and rownum = 1;
end if;
if bject_type = 'P' then
bject_type := 'PRIMARY KEY';
end if;
if bject_type = 'U' then
bject_type := 'UNIQUE KEY';
end if;
if bject_type = 'R' then
bject_type := 'FOREIGN KEY';
end if;
if bject_type = 'C' then
bject_type := 'CHECK CONSTRAINT';
end if;
return;
exception
when no_data_found then
null;
end;
end if;
end;

ART1 = POSNCTL
:OBJECT_TYPE = Null
:OBJECT_OWNER = Null
:OBJECT_NAME = Null
ART2 = NORCMNT
:SUB_OBJECT = Null
ART3 =
EEP = 1
:CUR_SCHEMA = BUI_DEVELOPER_ACCOUNT

 

[HSchuerger]

Is there a definitive answer to this security question?

 

[Marco Kalter]

It seems we overlooked your topic.

Perhaps you need to enable the "Use DBA views if available" option (Tools > Preferences > Oracle / Options)?

 

[HSchuerger]

We do have that option enabled.

Package logic is not available because it uses ALL_PROBE_OBJECTS as the basis for making it available. It seems there should be an alternative for that.

I have yet to determine why the right-click Describe option is not available for tables/views in the SQL Window. I posted in the results of debug.

I added some dbms_output and ran the SQL in TOAD, getting the values before and after dbms_utility.name_resolve.

Starting...
BEFORE
name=POSNCTL.NORCMNT
context=0
scgena=
part1=
part2=
dblink=
part1_type=
object_number=
AFTER
name=POSNCTL.NORCMNT
context=0
scgena=POSNCTL
part1=NORCMNT
part2=
dblink=
part1_type=2
object_number=1636969
object_type=TABLE
object_name=NORCMNT
sub_object=
p1.p2=NORCMNT.

 

[Marco Kalter]

The ALL_PROBE_OBJECTS view is only used to determine whether the object is compiled with debug information. It does not affect the "Describe" option.

To obtain some more diagnostic information, can you modify the shortcut and add the DebugSQL parameter? For example:

"C:\Program Files\PLSQL Developer\plsqldev.exe" DebugSQL

Reproduce the problem and send me the debug.txt file that is generated in the PL/SQL Developer directory or in the %APPDATA%\PLSQL Developer directory (e.g. C:\Users\\AppData\Roaming\PLSQL Developer).

 

[HSchuerger]

I will create a debug trace. Meanwhile, is there a limitation on F2 and F4 functionality when object privilege is via a role?

 

[HSchuerger]

The following files represent two testing sessions. The difference between the two is modest. Both sessions have access to DBA views. The second session enabled a password-protected role that also has SELECT on tables and EXECUTE on packages involved in a development project.

Links to the debug information:http://www.facstaff.bucknell.edu/hs029/STUFF/Debug-with-default-roles.txthttp://www.facstaff.bucknell.edu/hs029/STUFF/Debug-with-pwp-role-enabled.txt

EXECUTE on budba.schema_management is granted directly to the account in both cases.

Where privilege is granted via a role, the F2 & F4 options do not work nor is DESCRIBE in the RIGHT-CLICK pick-list. We may navigate to them and describe them, we may also search for them and then user F2 & F4.

EXECUTE on edwstg.edw_student_extr has not been granted in either case. The code should be available via the DBA views. When looking at the package body, we cannot navigate to the procedures and functions the same way as when EXECUTE has been granted. So, it would seem that the view is assembled differently.

 

[HSchuerger]

An account with the DBA role cannot make effective use of right-click-describe as Describe is not an option. F4 also does not work. Must search or otherwise navigate to the object first.
http://www.facstaff.bucknell.edu/hs029/STUFF/Debug-set-role-DBA.txt

 

[HSchuerger]

Greetings. I was wondering if there were any observations to report based on the debug files provided.

 

[Marco Kalter]

Can you try the following? Edit the Params.ini file in the PL/SQL Developer installation directory and add the following line:

OldNameResolve=1

Let me know if this affects the issue.

 

[HSchuerger]

Please let us know if there is anything we can do (configuration-wise) to allow right-click describe with the security we have in place. If not, please explain what is required. I think direct-grants are a bad idea. Role-based grants should suffice.