Oracle: How to get list of objects accessible to the user

oracle-11g-r2system-tables

I want to get list of all objects which is accessible to the login user. The list should include Table, Sequence, Function, Procedure, View, Sequence, Index, Trigger etc.

I am using Oracle 11g and Oracle 12c. My requirement is that the owner of the db objects grant privileges to other user, so that user can access those objects by running select or other query. I did try earlier dba_objects but DBA does not allow to grant permission for dba_objects to the user. So I am looking for different solution.

I did try all_objects that list of all objects accessible to the user, but It lists the Table, View, Index and Sequence only.

Either I am missing something or its returns it only.

Can anybody suggest me correct way to get all accessible objects to the user?

Best Answer

_{Community wiki answer:}

ALL_OBJECTS lists all the object types, at least those which you need, except triggers, for which you can query ALL_TRIGGERS.

Related Solutions

Security for Application Developers doing PL/SQL work in Oracle

Back in the days when I worked in an Oracle shop, we had a specific 'dev' (development) server, which had different security restrictions than the 'prod' (production) server. Developers could do whatever they needed, and then we'd hand off the necessary scripts to the DBA to apply to the production server.

In the case of our critical systems (SCT Banner, for tracking classes & students, and Oracle Financials), there were also 'test' and 'seed' servers. Test was for user acceptance testing before stuff migrated from dev to prod; 'seed' was stock install of the software, so should we find a bug, we could verify if it was something we had introduced or came from SCT or Oracle's software.

Objects in v$bh but not dba_objects

The majority of the unmatched objects will be Undo.

You can join v$bh with DBA_ROLLBACK_SEGS to identify the Undo segments involved.

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;

Best Answer

Related Solutions

Security for Application Developers doing PL/SQL work in Oracle

Objects in v$bh but not dba_objects

Related Question