Re: Why I cannot do select on other's tables even when I have DBA privileges
Date: Wed, 24 Jun 2009 09:57:45 -0700 (PDT)
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 DEFOS_
------------------------------ ------------------------------ --- --- --- 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 YESNO
SQL> SQL> -- SQL> -- Create a new user SQL> -- SQL> create user bong identified by yangzee2 quota unlimited on users;
SQL> grant create session, create table to bong;
SQL> SQL> -- SQL> -- Connect as new user, create a table SQL> -- SQL> connect bong/yangzee
SQL> create table table1(
2 mykey number primary key, 3 mydata varchar2(60)
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;
PL/SQL procedure successfully completed.
SQL> SQL> -- SQL> -- Connect as DBA account SQL> -- SQL> connect bing/#%#%#%#%#
SQL> SQL> -- SQL> -- Select from existing table SQL> -- SQL> select * from bong.table1
2 where rownum <40;
1 Data for key
2 Data for key
3 Data for key
4 Data for key
5 Data for key
6 Data for key
7 Data for key
8 Data for key
9 Data for key
10 Data for key
11 Data for key
12 Data for key
13 Data for key
14 Data for key
15 Data for key
16 Data for key
17 Data for key
18 Data for key
19 Data for key
20 Data for key
21 Data for key
22 Data for key
23 Data for key
24 Data for key
25 Data for key
26 Data for key
27 Data for key
28 Data for key
29 Data for key
30 Data for key
31 Data for key
32 Data for key
33 Data for key
34 Data for key
35 Data for key
36 Data for key
37 Data for key
38 Data for key
39 Data for key
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