How can know a DBA the accesibles tables from a user?

Sorry. There is a slight mistake in my examples.
Please replace "user" by "owner" in the querys from
DBA_OBJECTS, as follow:

   select *
   from dba_objects
   where owner='MY_USER';

Thank you for your answerd, but:

1. The DBA want to know the objects
which are "accesibles" from a concrete USER and not only the
object with a concrete USER as OWNER.

There are tables (e.g. DUAL) accesibles for user SCOTT and SCOTT is not its owner.

2. I want to know the sentence with which the view ALL_OBJECTS is defined. E.g.:

CREATE VIEW ALL_OBJECTS as SELECT ????? FROM ????? WHERE ?????;

Adjusted points to 120

1) If the object is not owned by the user, then there must be
a synonym pointing to the object, and the user must have been
GRANTed privileges to access the object, at least SELECT.
The synonym can be public or owner by the user.

You can query about synonyms with the view DBA_SYNONYMS,
and about what grants are grants made, with the view DBA_TAB_GRANTS, as in:

SELECT * FROM DBA_TAB_GRANTS WHERE GRANTEE='MY_USER';

Also, check the views resulting from the query:

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%GRANT%'

For example, DUAL is a table owner by SYS, there is a
public synonym pointing to it and the SELECT privilege
has been granted to the role "PUBLIC" (i.e. to all users) when
the data dictionary were created.
The same to ALL_OBJECTS and the rest of the dictionary.


2) The sentences to define ALL_OBJECTS is the following
(extracted form the script $ORACLE_HOME/rdbms73/admin/catalog.sql):

create or replace view ALL_OBJECTS
    (OWNER, OBJECT_NAME, OBJECT_ID, OBJECT_TYPE,
     CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS)
as
select u.name, o.name, o.obj#,
       decode(o.type, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                      4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                      7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                      11, 'PACKAGE BODY', 12, 'TRIGGER', 'UNDEFINED'),
       o.ctime, o.mtime,
       to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
       decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID')
from sys.obj$ o, sys.user$ u
where o.owner# = u.user#
  and o.linkname is null
  and o.type != 10
  and
  (
    o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
    or
    (
      o.type != 11 /* EXECUTE priv does not let user see pkg body */
      and
      o.obj# in (select obj# from sys.objauth$
                 where grantee# in (select kzsrorol from x$kzsro)
                   and privilege# in (3 /* DELETE */,   6 /* INSERT */,
                                      7 /* LOCK */,     9 /* SELECT */,
                                      10 /* UPDATE */, 12 /* EXECUTE */))
    )
    or
    (
       o.type in (7, 8, 9) /* prc, fcn, pkg */
       and
       exists (select null from v$enabledprivs
             where priv_number in (
                                    -144 /* EXECUTE ANY PROCEDURE */,
                                    -141 /* CREATE ANY PROCEDURE */
                            )
              )
    )
    or
    (
       o.type in (12) /* trigger */
       and
       exists (select null from v$enabledprivs
             where priv_number in (
                                    -152 /* CREATE ANY TRIGGER */
                            )
              )
    )
    or
    (
       o.type = 11 /* pkg body */
       and
       exists (select null from v$enabledprivs
             where priv_number =   -141 /* CREATE ANY PROCEDURE */
              )
    )
    or
    (
       o.type in (1, 2, 4, 5) /* index, table, view, synonym */
       and
       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
    ( o.type = 6 /* sequence */
      and
      exists (select null from v$enabledprivs
              where priv_number = -109 /* SELECT ANY SEQUENCE */)
    )
  );

create public synonym ALL_OBJECTS for ALL_OBJECTS;

grant select on ALL_OBJECTS to PUBLIC with grant option:

The view DBA_TAB_PRIVS is useful also.

And all the resulting views from the query:

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%PRIVS%'

So, all tables accesibles by a user are (or do not?):

SELECT OBJECT_NAME FROM DBA_OBJECTS DO
WHERE OBJECT_TYPE IN ('TABLE') AND
      (DO.OWNER='USER1' OR
       1=(SELECT COUNT(*) FROM DBA_TAB_PRIVS
          WHERE TABLE_NAME=OBJECT_NAME AND GRANTEE   IN ('USER1','PUBLIC') AND
                GRANTOR   =DO.OWNER    AND PRIVILEGE IN ('ALL','SELECT')) );

i.e. A table is accesible to USER1 if it belong to DBA_OBJECTS and it's a table and:
   a) Or USER1 is it OWNER
   b) Or this tables was granted (to USER1 or to PUBLIC)

But if the user has the DBA privileges the previus SELECT is not equal to:

select TABLE_NAME from all_tables;

Why? (I don't understand it)

Thanks!

The role DBA has the system prigivlege "SELECT ANY TABLE" granted so a DBA user doesn't need explicit grants to access a
particular table.

That's why the result of ALL_TABLES is greater for a user that
has the DBA role. And that is not wrong.

About your query, it's a bit "esoteric", isn't it?  :)

1)I'd replace OBJECT_TYPE IN ('TABLE')  by  OBJECT_TYPE='TABLE' in this case.

2)I'd also replace 1=(SELECT COUNT(*)  by  1 <= (SELECT COUNT(*)

3)Remember that the grantor doesn't have to be necessarily the owner, so remove  GRANTOR=DO.OWNER

You say "remove GRANTOR=DO.OWNER", but then it may be fuzzy,
because it's possible to exists some tables with the same name
and different owners.

That's right. A user indeed can have access to different
tables with the same name, each one owned by different owners.
Such tables can be referred with different synonyms, or by
specifying the schema name (i.e. owner name) this way:
     select columns from user.table;