Re: Grant select any tables to user

Peter <peter_at_nomorenewsspammin.ca> wrote in message news:<5e55dvs2f0fhg0pkmto6rvulsjvdss0pl1_at_4ax.com>...
> Will the statement
> grant select any tables to Peter;
>
> allow Peter to run a select statement on a table created by SYS?
>

Er, it depends. It depends primarily on your version, and your init.ora. In the bad old days of Oracle 7, then yes... an "ANY' privilege (be it select any table, delete any table etc etc ) meant you could exercise that privilege in anybody's schema, including SYS's.

(Your specific error is probably because you forgot to prepend the schema owner in front of the object name. Try a select * from sys.uet$ ...ie, specify that the table belongs to SYS).

However, that behaviour is quite clearly a disaster waiting to happen, and so quite rightly Oracle changed things in Oracle 8.0. The grant of an ANY privilege in that or any higher version means you can exercise that privilege in anybody's schema *except* for SYS's.

However, a rather large number of important (but badly written) apps. would have instantly broken if that had been made official default practice from the word go (mentioning no names, but Peoplesoft, SAP and Siebel spring to mind as likely culprits). Those apps were written assuming they had majestic and imperial access to the SYS schema objects, and would have gone the way of all flesh if subject to the new restrictions. So Oracle gave them a get-out: they invented an init.ora parameter called O7-DICTIONARY_ACCESSIBILITY (that's an "oh" at the front, not a zero). If set to true, then 8.0 and above behave exactly like Oracle 7 did: access to the dictionary tables is assured if you possess an 'ANY' privilege.

The default was 'TRUE'.

And thus things stayed... until 9i, when Oracle (rather quietly, in my opinion) changed the default to FALSE. Which means that in 9i, with the default, the answer to your question is 'no'... the 'ANY' privilege gives you no inherent access to the SYS tables.

You can always change it back to FALSE, of course. But the advice is strongly not to. There was a good reason for Oracle doing the thing in the first place.

Regards
HJR
>
> I did just that but I got the following error
>
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
> Did I miss some privileges that I should have granted to Peter?
> Thanks
Received on Mon May 26 2003 - 23:23:44 CDT