1

Is there a way to get a list of tables and views that a specific user can see, without logging in as that user? Initially, I tried something like

select distinct p.table_name as value
from dba_tab_privs p
   join dba_synonyms s on
      p.owner = s.table_owner
      and p.table_name = s.table_name
      and s.owner = 'PUBLIC'
   join all_objects o on
      p.owner = o.owner
      and p.table_name = o.object_name
      and o.object_type in ('TABLE', 'VIEW')
where
   p.privilege = 'SELECT'
   and p.grantee = :user
order by p.table_name

However, while this would show tables and views that a specific user had been explicitly granted select on, it didn't seem to accurately reflect the full range of visible tables and views a user could see upon connecting to the database. For example, one user I tested had a little over 2000 tables and views visible when selecting on all_tables and all_views, but only reflected 300 records using the above query.

  • Getting the list of tables that the user has direct access to is relatively easy. The complexity comes in dealing with privileges granted through roles. And that gets complicated because roles can be password protected (so a user may or may not have access to them), they can be enabled and disabled within a session, they can be default or non-default, etc. So figuring out which set of roles to consider, in general, is a hard problem (thus privileges granted via roles aren't available in definer's rights procedures). Do you want to consider all roles regardless of default, password, etc? – Justin Cave Jul 28 '15 at 21:54
  • I would start with Pete Finnigan's who_can_access.sql script petefinnigan.com/who_can_access.sql from his very useful script collection petefinnigan.com/tools.htm . That's going to handle the needs of the vast majority of use cases. – Justin Cave Jul 28 '15 at 22:31
1

Try this...

select owner,table_name,grantable from dba_tab_privs where grantee = 'SCOTT' and privilege = 'SELECT';

Change SCOTT to the user you are interested in.

1

I don't know a 'legal' way to find out the tables visible in the ALL_TABLES view of another user, so I do some reengineering of the SYS.ALL_TABLES dictionary view and found the following expression in the where clause:

  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
        (select oa.obj#
         from sys.objauth$ oa
         where grantee# in ( select kzsrorol
                 from x$kzsro
                   )
        )
       or /* user has system privileges */
     exists (select null from v$enabledprivs
         where priv_number in (-45 /* LOCK ANY TABLE */,
                       -47 /* SELECT ANY TABLE */,
                       -48 /* INSERT ANY TABLE */,
                       -49 /* UPDATE ANY TABLE */,
                       -50 /* DELETE ANY TABLE */)
         )
       or /* user is SYSBACKUP */
     SYS_CONTEXT('USERENV','CURRENT_USERID') = 2147483617
      )

When logged in as SYS the rows returned by

select kzsrorol 
from sys.x$kzsro;

are

0
1

0 is the USER# of SYS and 1 is the USER# of PUBLIC (check SYS.USER$).

So I think the meaning of the clause is the following:

A table from DBA_TABLES is visible int the ALL_TABLES view of the user if at least one of the following holds:

  • the owner of the table is the user
  • the user has granted a privilege on the table (not only a SELECT privilege but any privilege)
  • PUBLIC has granted a privilege on the table
  • one of the system privileges LOCK ANY TABLE, etc. is enabled
  • the user is the SYSBACKUP user

Note that tables that are visible only because of the roles granted to the user are not visible in ALL_TABLES (see the comments of @JustinCave to the OP)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.