Re: help for unknown oracle database

From: Jason Baugher <jason_at_baugher.pike.il.us>
Date: Wed, 10 Jul 2002 03:48:44 GMT
Message-ID: <Xns9246E80D0ABADjasonbaugherpikeilus@209.242.76.10>

ob112_at_web.de (Oliver Brausch) wrote in
news:cd11d95c.0207080100.73dcc6de_at_posting.google.com:

> Hi, first thank you very much for your help.
> I tried the selects you wrote:
>

>> check your user privs.  Apparently the user you are currently
>> connected as has privs that allow them to query dba_tables, but not
>> the dbfa.external_id_info table.  As the user that cannot query the
>> table, do this:
>> 
>> SELECT * from user_sys_privs;

>
> no rows selected
>

>> SELECT * from all_tab_privs_recd;


> 

> GRANTEE                        OWNER

> ------------------------------ ------------------------------

> TABLE_NAME                     GRANTOR

> ------------------------------ ------------------------------

> PRIVILEGE                                GRA

> ---------------------------------------- ---

> PUBLIC                         SYS

> DUAL                           SYS

> SELECT                                   YES

> 

> PUBLIC                         SYS

> SYSTEM_PRIVILEGE_MAP           SYS

> SELECT                                   YES

>
> and this appeared 1404 times(!!!!)
> seems that there is nothing granted for nobody.
> my username is "ppadm" and I think I should have admin rights.
> If not I cannot see the dba_tables!?
> but then how can I grant access to the tables to myself?
>
> thx again, Oliver
>

You're not looking at this from an Oracle perspective. There isn't any such thing as "admin rights". You need to learn how Oracle's priveleges work.

PUBLIC means everyone. That's why ppadm can see dba_tables.

What you have here are 1404 SPECIFIC grants that allow anyone (PUBLIC) to do a SELECT against the specific tables listed in each grant.

What you DON'T have are any grants BY the dbfa user to either PUBLIC or specifically to the ppadm user, to perform a SELECT on the dbfa.external_id_info table.

So, since you have SELECT on dba_tables and a lot of the other SYS tables, you can do a "describe dbfa.external_id_info", but since you DON'T have SELECT on the actual dbfa.external_id_info table, you can't query the data stored there.

Try this:

connect dbfa/password # insert the real password here, obviously grant select on external_id_info to ppadm; connect ppadm/password
describe dbfa.external_id_info
select * from dbfa.external_id_info;

In summary, when you create a schema/user, they do not have any privileges until you grant them. When you create objects as a user, they cannot be accessed by other schema/users unless privs are granted, either at the object level or at the system level.

-- 
Jason Baugher 
Virtual Adept Professional Consulting Services
1406 Adams St.
Quincy, IL 62301
(217) 221-5406
http://baugher.pike.il.us/virtualadept
jason_at_baugher.pike.il.us

Received on Tue Jul 09 2002 - 22:48:44 CDT