[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 682
  • Last Modified:

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

Hello!

1. How can know a DBA (SYS) the tables, views and snapshots which are accesibles from a concrete USER?

2. How is the view ALL_OBJECTS defined in a concrete USER?

Thanks.
0
ppgg
Asked:
ppgg
  • 6
  • 4
1 Solution
 
rodoCommented:
You can use the following queries:
1) select *
   from dba_objects
   where user='MY_USER';

2) select object_name, object_type
   from dba_objects
   where user='MY_USER'
   order by object_type;

3) select *
   from dba_objects
   where user = 'MY_USER'
   and object_type in ('TABLE', 'VIEW', 'SNAPSHOT');

etc, etc.

Remember, the username  (MY_USER in the examples) and
object types must be typed in uppercase.

For a description of ALL_OBJECTS execute in sqlplus
   describe ALL_OBJECTS

You may also find interesting the views USER_OBJECTS, USER_TABLES, USER_VIEWS, TABS, IND, etc, etc.

Also try
   select *
   from dict
   where table_name like ('USER%');
,
   select *
   from dict
   where comments like ('%any comment you search%');

etc, etc.

0
 
rodoCommented:
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';
0
 
ppggAuthor Commented:
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 ?????;
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
ppggAuthor Commented:
Adjusted points to 120
0
 
rodoCommented:
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:


0
 
rodoCommented:
The view DBA_TAB_PRIVS is useful also.

And all the resulting views from the query:

SELECT * FROM DICT WHERE TABLE_NAME LIKE '%PRIVS%'
0
 
ppggAuthor Commented:
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!
0
 
rodoCommented:
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

0
 
ppggAuthor Commented:
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.
0
 
rodoCommented:
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;
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now