The Oracle (tm) Users' Co-Operative FAQ

Can I see all the tables that are in the database if I have the DBA privilege ?


Author's name: Mark D Powell

Author's Email: Mark dot Powell @ eds dot com

Date written: 08 September 2003

Oracle version(s): 9.2

Can I see all the tables that are in the database if I have the DBA privilege ?

Back to index of questions


The answer hinges on the word ALL.  Using version 9.2 as a reference a DBA privileged user has been granted around 87 system related any type privileges such as select any table.  The net effect is that a DBA privileged user has select access to any and all selectable user created objects defined to the database.  The key word in the preceding sentence is user created.  Excluded from these privileges are access rights to the SYS owned X$ views that the V$ views and that some RDBMS dictionary views are built on.  Select privilege on the RDBMS base tables such as obj$ which house the RDBMS dictionary information are however included in the default DBA privileges. 

        select count(*)
        from   dba_sys_privs
        where  grantee = 'DBA'
        and    privilege like '%ANY%';
        
          COUNT(*)
        ----------
                87
 

The RDBMS dictionary views user_ and all_ continue to work as normal for a DBA privileged user; however since a DBA has access to all user objects the all_ views should return the same counts as the dba_ views. 


Further reading:

How do I find out which users have the rights, or privileges, to access a given object ?


Back to top

Back to index of questions