Re: create user with global rights ..?

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Wed, 7 Aug 2002 21:36:35 +1000
Message-ID: <air0m6$s60$1@lust.ihug.co.nz>

There was a thread in this newsgroup, entitled "connecting as SYSDBA/normal", dated 29th July 2002. You can search for it at google.com (a very useful resource). Search in Group search for the phrase 'o7_dictionary', author Howard J. Rogers, and this group name, and it's the first thread listed.

But, by way of retrospection, here it is:

---

In Oracle 7, when I gave you the (for example) 'select ANY table' privilege, it meant you could exercise that privilege in ANY schema -including SYS's. Which meant that you had full access to my data dictionary tables, even though all I wanted to do was quickly give you the ability to select from Scott's EMP, Brenda's SALES, Fred's CUSTOMERS and Friedrich Marien-Battenburg's CAKE_RECIPES. Letting you waltz all through my data dictionary was not what I wanted at all!

So in Oracle 8.0, they split things. A grant of an "ANY" privilege now meant 'rights in anybody's schema EXCEPT SYS's'. Problem solved. But the problem was that a rather large number of badly-written applications had been written in Version 7 days that *needed* access to the data dictionary to work properly... so leaving things like this would have broken them all. So, a fix was needed in the form of a new init.ora parameter called o7_dictionary_accessibility. That could be set to TRUE, meaning that thing worked just like Oracle 7: the grant of an ANY privilege implied access to the data dictionary. Set it to false, and the new 8.0 idea of 'any schema BUT SYS's' became true. The default value for this parameter was TRUE, so in this respect, by default, Oracle 8.0 behaved exactly as Oracle 7 had done.

And so things stayed in Oracle 8i.

But in 9i, given that many moons had passed, they decided to change the default, so the same parameter now defaults to FALSE.

And it's this that explains the behaviour you report. Trouble is, with the default of FALSE, only privileged users can view the data dictionary tables. Yet those tables are owned by SYS. Therefore, SYS *must* be a privileged user to look at his own tables. Therefore, the login process demands that SYS connect 'as sysdba'.

The behaviour is reversible. If you take the trouble to set o7_dictionary_accessibility back to a setting of TRUE, you'll find that 'connect sys/oracle' -without the 'as sysdba'- works as it always did.

However, there was a good reason for inventing the split between 'any' and 'sys' in the first place, and I wouldn't recommend non-privileged SYS connections.

---

As for changing the global name, you could try the same thing. Search for global_name, author Howard J. Rogers, group this one, and you'll get this as the first thread:

alter database rename global_name to NEW.NAME.AND.DOMAIN

(Actually, it was me asking the same question, and Thomas Kyte answering).

If you don't use google every day, you should. It's a mine of useful information.

Regards
HJR "Md Irfan" <irfan_pk_at_hotmail.com> wrote in message news:c42168e7.0208070320.2b0de8ba_at_posting.google.com... > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<aio1j5$q20$1_at_lust.ihug.co.nz>...
> >
> > If so, and as I explained earlier, if o7_dictionary_accessibility=false
(the
> > default in 9i) then the 'any' keyword doesn't apply to SYS-owned tables.

>
> Can you please let me know where you have explained this
> 'o7_dictionary_accessibility=false'.
> Also how can or the best way to change the global_name in global_name
> table.
> Thanks..

Received on Wed Aug 07 2002 - 06:36:35 CDT