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 07:13:11 -0700 (PDT)
Message-ID: <2d3fa539-1063-406e-b38b-493392e67dcc_at_t10g2000vbg.googlegroups.com>



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 Received on Wed Jun 24 2009 - 09:13:11 CDT

Original text of this message