Re: Why I cannot do select on other's tables even when I have DBA privileges
Date: Wed, 24 Jun 2009 10:32:39 -0700 (PDT)
Message-ID: <9e4a44d5-202b-4c6b-b5c3-e63b24edb681_at_c19g2000prh.googlegroups.com>
On Jun 24, 7: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. Some account (god)
> should have privilege to do select on all tables in database. As we
> see DBA can do all kinds of things yet cannot do simple things like
> select on other's tables.
Even though you have been using Oracle for many years, you seem to have missed a lot of concepts and how to apply them. As David pointed out, select any table is such a privilege. David was perhaps being too nice by not pointing out that it is a bad practice to have a god user (or at least, an unaudited god user). Oracle lets you do the good practice of being able to not see certain data, should the users have such a requirement. Some people argue that you have to trust your DBA's (which I generally agree with), but there are so many examples of trust being violated and plain incompetence that I'm starting to be convinced the default should be no trust. But it isn't the default, you appear to be incompetent. Or maybe someone set things up correctly because they are trying to protect things from you? Maybe you just need to log in as the user and grant access to the user who needs to look at it. But if that user is sys, you are doing it wrong.
And if you think doing everything as root is a good idea, you probably need some education there too.
jg
-- _at_home.com is bogus. http://www3.signonsandiego.com/stories/2009/jun/22/1m22assess235141-assessor-taxed-appeals-backlog/?uniontribReceived on Wed Jun 24 2009 - 12:32:39 CDT