Solved

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

Posted on 1997-11-18
10
659 Views
Last Modified: 2008-03-10
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
Comment
Question by:ppgg
  • 6
  • 4
10 Comments
 
LVL 1

Accepted Solution

by:
rodo earned 120 total points
ID: 1080842
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
 
LVL 1

Expert Comment

by:rodo
ID: 1080843
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
 

Author Comment

by:ppgg
ID: 1080844
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
 

Author Comment

by:ppgg
ID: 1080845
Adjusted points to 120
0
 
LVL 1

Expert Comment

by:rodo
ID: 1080846
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:rodo
ID: 1080847
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
 

Author Comment

by:ppgg
ID: 1080848
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
 
LVL 1

Expert Comment

by:rodo
ID: 1080849
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
 

Author Comment

by:ppgg
ID: 1080850
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
 
LVL 1

Expert Comment

by:rodo
ID: 1080851
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now