Re: Why I cannot do select on other's tables even when I have DBA privileges

From: ddf <oratune_at_msn.com>
Date: Wed, 24 Jun 2009 09:57:45 -0700 (PDT)
Message-ID: <09452114-dd3c-482f-8842-36bf7cf3dd1a_at_n4g2000vba.googlegroups.com>

On Jun 24, 9:50 am, zigzagdna <zigzag..._at_yahoo.com> wrote:
> On Jun 24, 10:13 am, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
> > Comments embedded.
>
> > On Jun 23, 9:12 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
>
> > > I have dba privileges;
>
> > I'm both impressed and appalled that you possess such power and can't
> > understand how to use it.
>
> > > My database has users user1, user2…
>
> > How ... creative ...
>
> > > When I enter
> > > Select * from user1.table1
> > > I get an error table or view does not exit,  
>
> > Most tables don't exit, so this is not surprising.  I presume you mean
> > 'table or view does not exist'.
>
> > > I am pretty sure table1
> > > is in user1 schema.
>
> > If you have DBA privileges why have you not used common sense and
> > queried DBA_TABLES for the owner?   It's a fairly simple query to
> > write:
>
> > select owner
> > from dba_tables
> > where table_name = 'TABLE1';
>
> > You would then KNOW who owns TABLE1.  Apparently it's NOT user1.
>
> > > Is only way to see contents of table1 is to logon as user1, or user1
> > > has to explicitly grant select to other accounts including sys,
>
> > No, and you've posted here enough to know how to read a manual.  The
> > DBA role has, among others, the SELECT ANY TABLE privilege, which
> > allows anyone granted that role to, gee, select from ANY table.
>
> > > Looks like DBA privilege is not similar to UNIX root.
>
> > Looks like you need to read the manual.  It also appears you need a
> > clue, since you obviously haven't the faintest idea of how to
> > determine who owns which objects in a database.
>
> > David Fitzjarrell
>
> Even  though I have been using Oracle for many years, this is another
> fndamental feature which is missing from Oracle.

Sorry to disappoint you but it isn't missing.

> Some account (god)
> should have privilege to do select on all tables in database.

Any DBA-privileged account does, provided the table in question exists in the given schema.

> As we
> see DBA can do all kinds of things yet cannot do simple things like
> select on other's tables

Prove that, because I can prove otherwise:

SQL> --
SQL> -- Show current user privileges
SQL> --
SQL> select *

  2 from user_role_privs;

USERNAME                       GRANTED_ROLE                   ADM DEF

OS_

------------------------------ ------------------------------ --- ---
---
BING                           CONNECT                        NO  YES
NO
BING                           DBA                            NO  YES
NO
BING                           EXECUTE_CATALOG_ROLE           NO  YES
NO
BING                           PLUSTRACE                      YES YES
NO
BING                           SELECT_CATALOG_ROLE            NO  YES

NO

SQL>
SQL> --
SQL> --  Create a new user
SQL> --
SQL> create user bong identified by yangzee

  2 quota unlimited on users;

User created.

SQL>
SQL> grant create session, create table to bong;

Grant succeeded.

SQL>
SQL> --
SQL> -- Connect as new user, create a table
SQL> --
SQL> connect bong/yangzee

Connected.
SQL>
SQL> create table table1(

  2  	     mykey   number primary key,
  3  	     mydata  varchar2(60)

  4 );

Table created.

SQL>
SQL> --
SQL> -- Populate table
SQL> --
SQL> begin
  2  	     for i in 1..1000 loop
  3  		     insert into table1
  4  		     values(i,'Data for key '||i);
  5  	     end loop;
  6
  7  	     commit;

  8 end;
  9 /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Connect as DBA account
SQL> --
SQL> connect bing/#%#%#%#%#

Connected.

SQL>
SQL> --
SQL> -- Select from existing table
SQL> --
SQL> select * from bong.table1

  2 where rownum <40;

     MYKEY
MYDATA

---

---

         1 Data for key
1

         2 Data for key
2

         3 Data for key
3

         4 Data for key
4

         5 Data for key
5

         6 Data for key
6

         7 Data for key
7

         8 Data for key
8

         9 Data for key
9

        10 Data for key
10

        11 Data for key
11

     MYKEY
MYDATA

---

---

        12 Data for key
12

        13 Data for key
13

        14 Data for key
14

        15 Data for key
15

        16 Data for key
16

        17 Data for key
17

        18 Data for key
18

        19 Data for key
19

        20 Data for key
20

        21 Data for key
21

        22 Data for key
22

     MYKEY
MYDATA

---

---

        23 Data for key
23

        24 Data for key
24

        25 Data for key
25

        26 Data for key
26

        27 Data for key
27

        28 Data for key
28

        29 Data for key
29

        30 Data for key
30

        31 Data for key
31

        32 Data for key
32

        33 Data for key
33

     MYKEY
MYDATA

---

---

        34 Data for key
34

        35 Data for key
35

        36 Data for key
36

        37 Data for key
37

        38 Data for key
38

        39 Data for key
39

39 rows selected.

SQL>
SQL> --
SQL> -- Try to select from non-existing table
SQL> --
SQL> select * from bong.table2

  2 where rownum <40;
select * from bong.table2

                   *

ERROR at line 1:
ORA-00942: table or view does not exist

SQL> If you truly have been a DBA for years why did you not know this?

David Fitzjarrell Received on Wed Jun 24 2009 - 11:57:45 CDT