Re: SYS vs. SYSTEM

On Wed, 06 Aug 2003 15:36:40 GMT, Randy Nichols <randynichols_at_yahoo.com> wrote:

> 1. Can someone clarify the difference between logging in as SYS and
> SYSTEM
> via SQL*PLUS?
>
> 2. If in both cases you log in as SYSDBA (via SQL*PLUS), is there a
> difference?
>
> 3. What does it mean in each case if you **don't** specify "AS SYSDBA"
> when
> logging in via SQL*PLUS. Don't they both belong to SYSDBA role anyway?
>
> 4. Why does Enterprise Manager Console, in some of my installations
> (can't
> remember, perhaps 8i?), not let me specify "AS SYSDBA" when logging in as
> SYSTEM, but only recognizes "AS NORMAL"?
>
> 5. Why with Oracle 9.2.0.3 am I able to log in as
> "SYS/CHANGE_ON_INSTALL_at_mydatabase AS SYSDBA" when the database setup
> required me to change the password for SYS and SYSTEM? It also allows me
> to
> log in as SYSTEM/MANAGER as well, although I supplied a new password
> during
> database creation. These are fresh databases newly created under
> 9.2.0.3,
> not migrations.
>
> I welcome all thoughts and discussion on this.
>
> -Randy Nichols
>

SYS owns the data dictionary tables; SYSTEM doesn't. Both, however, are granted the DBA role, and therefore both are equally as powerful as each other: both can create tablespaces, create tables, change user passwords, add datafiles, perform complete exports, you name it.

However, the fact that SYS owns the data dictionary makes him a bit more special than SYSTEM, to the point where you would not really want to get into the habit of logging on as SYS in order to do 'routine' administration tasks such as those I've just listed. As a specific example, although SYS can perform a complete database export, his own objects will never be exported (for the perfectly sensible reason that his tables will be the data dictionary, and you'll presumably be importing your export into another database as some point... which has its own data dictionary.) Trouble is, if you routinely log in as SYS to create tables or indexes or grant permissions, then those ordinary tables, indexes and permissions can never be exported either. Hence SYS is special.

He's so special, in fact, that he is what is termed a 'privileged user'. A privileged user is one who can perform startups, shutdowns, backups, recoveries... and can create databases in the first place. SYSTEM is not a privileged user. And although SYS *can be* a privileged user, he doesn't have to be (in 8i, at least). If SYS logs in as a non-privileged user, then he really is just as equally powerful as SYSTEM. No better, no worse, except for the business of owning the data dictionary.

So how do you ask (again, in 8i) for SYS to log on as ordinary, but powerful, SYS? You just say 'connect sys/password'. Just as you would type 'connect system/password'.

But to connect SYS as a privileged user, you type 'connect sys/password AS SYSDBA'. The "as sysdba" phrase is a request to acquire the privileges associated with the single SYSDBA system privilege (namely, the ability to startup, shutdown, backup, recover and create). SYSDBA is not, therefore, a role, but a privilege. They're different things.

When Oracle sees the words "AS SYSDBA", it does two things. First, it runs off to the operating system to discover who you are. It then asks the operating system "is randy a member of the dba group (unix) or the ora_dba group (windows). If you are, it is happy: it grants you the SYSDBA privilege, and you are logged on as SYS. Second, if you're not in one of those two O/S groups, it consults its own password file (which is created at database creation time by the database configuration assistant, or by yourself using the orapwd utility if you're doing it all manually). If whoever you are logging on to Oracle as has an entry in the password file, you're logged on as SYS. These two methods of authorizing privileged user access to the database are known as 'operating system authentication' and 'passwordfile authentication'. They only work when considering whether to let a prospective privileged user onto the database.

What does the connection string look like in each case? For O/S authentication, you just type 'connect / as sysdba'. You don't need to tell Oracle who you are, or what your password is: you've already logged on successfully to the O/S, so Oracle asks the O/S who you are and what groups you're a member of.

For passwordfile authentication, the connection string looks like 'connect sys/password as sysdba'. SYS being an Oracle user name, not an O/S one, that's a good indication you're trying to get Oracle to check its own authentication mechanism, that is: the password file.

Now, assume you have password file authentication set up, and not O/S. What would typing 'connect system/manager as sysdba' mean? It means Oracle would check the password file to see whether SYSTEM is in there (which would be the case if you'd ever done a 'grant SYSDBA to system'). Since you should never grant that privilege to SYSTEM, SYSTEM won't be in the password file, and therefore authentication will fail.

So now assume you have O/S authentication set up. What would typing 'connect sdkjghgfjhgf/kjhjghdkfj as sysdba' do? Well, Oracle would see the two words 'as sysdba', and check the operating system. It would discover that the user is RANDY. It would discover that RANDY is a member of the dba group (or ORA_DBA on Windows), and .... it would let you connect. If you've proved who you are to the O/S, you can type complete gibberish to Oracle, and it will make no difference. Oracle's not checking its own users and passwords anyway.

Now throw in a client connection (which is what you're doing with Enterprise Manager). You have (let's say) a Windows PC connecting to a Unix server. You've logged into the Windows PC as user Randy. You attempt to do a 'connect system/manager as sysdba'. What happens then? Same as before: seeing the AS SYSDBA words, Oracle checks the O/S. *It's* O/S... ie, Unix. Is it likely to find your Windows account name in the Unix dba group? Nah. So it will refuse to let you connect as a privileged user.

Now connect directly to the server. Try connecting as 'system/manager as sysdba'. Will it let you on now? Yes. Because by being on the server itself, you've presumably logged into the unix box, and used an account which was previously made a member of the dba group, and hence you can be granted privileged user status. Which means you'll be connected... as SYS, not SYSTEM. Remember that with O/S authentication, you can type complete gibberish and still get connected. It matters not what you supply as the username: the key thing is, are you the Unix user a member of the right group? That's why your question 5 works, by the way. You may have changed the passwords, but it doesn't matter: O/S authentication is at work, and so you can type anything you like in, and still get connected.

And one final complication: in 8i, you could log in as 'normal' sys, or as privileged sys. ('Connect sys/password' versus 'connect sys/password as sysdba'). In 9i, you can't (by default). If you try 'connect sys/password' in 9i, you'll be told that connections as SYS must be as SYSDBA. That's a good thing (because SYS should only be used for privileged actions, as I mentioned, even in earlier versions. It's just now that distinction is enforced). The reasons for the change in behaviour is simply that a parameter in the init.ora (O7_DICTIONARY_ACCESSIBILITY) was TRUE by default in 8i, but is now FALSE by default. Setting it to FALSE means that only privileged users can access the data dictionary tables. Yet SYS owns them. Therefore, SYS must log on as a privileged user.

Simple, really!!

(There are other issues to consider here, too... so, it can get a big topic. But hopefully that explains what it going on in your various scenarios and will get you started understanding what is going on).

~QM Received on Wed Aug 06 2003 - 15:28:43 CDT