敗家貓

小記一下這些身份的差別

SYS

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle; they should never be modified by any user or database administrator, and no one should create any tables in the schema of the user SYS. The DBA should change the password for SYS immediately after database creation!!!

SYSTEM

The SYSTEM username creates additional tables and views that display administrative information, and internal tables and views used by Oracle tools. Never create in the SYSTEM schema tables of interest to individual users.
SYSTEM is a little bit "weaker" user than SYS, for example, it has no access to so called X$ tables (the very internal structure tables of Oracle).

Although in real life you may be in a situation when some product or whatever you want to create objects in above mentioned user's schemas. Be flexible, don't sacriface a product only because it will create some objects in SYS or SYSTEM schema

The DBA should change the password for SYSTEM immediately after database creation!!!



This is a very good topic, since it is sometimes confusing. I read through all the responses to this topic and would like to summarize it, please correct it as you like:

1. sys and system are database user, very privileged users.
2. sysdba, sysoper are two privileges for database administration (startup, shutdown, backup etc.) usage.
3. sys by default has sysdba privilege, but if you log in sys as regular user, you don't automatically has sysdba privilege, you must log in sys as sysdba.
4. internal also has sysdba privilege, and always has it. So you don't need to log in internal as sysdba, just connect internal.
5. only users who log in as sysdba or sysoper can startup and shutdown database. Even sys cannot shut down the database without log in as sysdba! So, you do need sysdba privilege. Otherwise, no one can startup the database.
6. the difference between sys, system and sysdba, sysoper is that the formers are users and the later privileges. Every one knows what the difference between user and privilege, does it?

7. why do we need sysdba and sysoper?
a. We don't want everyone to be DBA. We can give sysdba to someone temporarily, while you are on vacation, who will do DBA's work for you.
b. If you have 5 DBAs, every one logs in will be the user sys. Then something happened, whom to blame to? If a user with sysdba logs in, his user name is his regular user name, if he makes mistake, it is easy to find.
c. Last, sysdba can remotely log in to oracle to maintain the database. Think about this, you have a database in Beijin, 500km away, the database is closed. How can it be startup if you are in Shenyang? You need sysdba's privilege to log in remotely and startup the database.

8. for remote log in as sysdba, oracle password file is used.
9. when you create oracle password file, you define password only for sys and internal, both the same. When you, the user with sysdba privilege, grant sysdba to other users, their name/password will be recorded into the password file.
10. you need to set up 「remote_login_passwordfile=exclusive」 in init.ora file in order for other user to log in as sysdba remotely.
11. only user log in as sysdba can grant sysdba to others.
12. if you don't change password for user 「sys」, he may have two passwords if you also create a password file, one for log in as regular user, "change_on_install" by default, and the other, defined in the password file, for log in as sysdba. However, as soon as you change the password for sys, the password will be unified for both, regular user and sysdba for sys.
13. Remember, you must log in as sysdba to startup or shutdown db. The only exception is user INTERNAL, internal is the sysdba at all time.

I hope this will make it easier to understand about these stupid concepts.