Re: Why I cannot do select on other's tables even when I have DBA privileges
From: zigzagdna <zigzagdna_at_yahoo.com>
Date: Wed, 24 Jun 2009 10:54:20 -0700 (PDT)
Message-ID: <6b911e25-0318-4cc4-abd6-b4a9bf8ee2b1_at_n30g2000vba.googlegroups.com>
On Jun 24, 12:57 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 24 Jun 2009 10:54:20 -0700 (PDT)
Message-ID: <6b911e25-0318-4cc4-abd6-b4a9bf8ee2b1_at_n30g2000vba.googlegroups.com>
On Jun 24, 12:57 pm, ddf <orat..._at_msn.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Thanks. Sorry, I got mixed up, I have select_catalog_role (not DBA role) in a database, so I can look at various data dictionary tables/ views. But then when I started doing select * from user2.table1 etc, it won't let me access that table.
.With a DBA role, I can do select on all the tables.
Sorry for the confusion. Received on Wed Jun 24 2009 - 12:54:20 CDT