Solved

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

Posted on 1997-11-18
10
667 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
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.

 

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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle 12c database link between pdb not working 20 125
use lov values 2 64
Error in creating a view. 8 23
Migration from sql server to oracle 5 26
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

828 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