SYS owns the data dictionary tables; SYSTEM does not 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 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 Alth 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, The He Really Is System No better, no worse, except for the business of ooning the data dictionary.
So How do you ask (Again, in 8i) for sys to log on as order, but powerful, sys? You just say 'connect sys / password'. Just as you would type 'connecture 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 have: IT GRANTS you ' 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 do not 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 indictive you're trying to get oracle to check its oow 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 the password file, and the the the 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 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 the as same as before: seeing the as sysdba word, oracle checks the o / s. * it's * o / s ... IE, UNIX. is it likely to find you 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 company 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 does not matter:?. O / S authentication IS at work, and so you can type anything you like in, and still get connect.
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 area other isssues 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 Undering What is Going ON).
