SYS vs SYSTEM and SYSDBA vs SYSOPER
You Asked
Hi I am very new to oracle.
I have installed Oracle 10g compatible with windows vista.
I am confused with what are exactly, SYS, SYSDBA, SYSOPER and SYSTEM?
How they differ and what is the specific purpose of these automatically created accounts when a new database installation is done?
Please provide me detailed information or please provide me some useful links to read about it in detail.
Thanks
Animesh
I have installed Oracle 10g compatible with windows vista.
I am confused with what are exactly, SYS, SYSDBA, SYSOPER and SYSTEM?
How they differ and what is the specific purpose of these automatically created accounts when a new database installation is done?
Please provide me detailed information or please provide me some useful links to read about it in detail.
Thanks
Animesh
and we said...
sys and system are "real schemas", there is a user SYS and a user SYSTEM.
In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist.
sysdba and sysoper are ROLES - they are not users, not schemas. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
In real life, you hardly EVER need sysdba - typically only during an upgrade or patch.
sysoper is another role, if you connect as sysoper, you'll be in a schema "public" and will only be able to do things granted to public AND start/stop the database. sysoper is something you should use to startup and shutdown. You'll use sysoper much more often than sysdba.
do not grant sysdba to anyone unless and until you have absolutely verified they have the NEED for sysdba - the same with sysoper.
====================
Addenda: Nov 2017
Thanks for some good additional information from Paul Alsemgeest from Netherlands to bring this content up to date:
In the versions from 12c onwards, you will also find roles SYSDG, SYSBACKUP and SYSKM.
These are somewhat less powerfull than sysdba, and meant for special user actions.
SYSDG for using Data Guard, SYSBACKUP for ... yes backup actions with RMAN and such, and SYSKM for security handling with TDE (Transparant Data Encryption). If you are very strict, you can use them all. Maybe this is usefull if you have multiple DBA teams with seperate responsibilities. I have not seen it yet. If you are alone on a small environment, SYSDBA will work where DBA is not enough.
In general, unless the documentation tells you, you will NEVER LOG IN as sys or system, they are our internal data dictionary accounts and not for your use. You will be best served by forgetting they exist.
sysdba and sysoper are ROLES - they are not users, not schemas. The SYSDBA role is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
In real life, you hardly EVER need sysdba - typically only during an upgrade or patch.
sysoper is another role, if you connect as sysoper, you'll be in a schema "public" and will only be able to do things granted to public AND start/stop the database. sysoper is something you should use to startup and shutdown. You'll use sysoper much more often than sysdba.
do not grant sysdba to anyone unless and until you have absolutely verified they have the NEED for sysdba - the same with sysoper.
====================
Addenda: Nov 2017
Thanks for some good additional information from Paul Alsemgeest from Netherlands to bring this content up to date:
In the versions from 12c onwards, you will also find roles SYSDG, SYSBACKUP and SYSKM.
These are somewhat less powerfull than sysdba, and meant for special user actions.
SYSDG for using Data Guard, SYSBACKUP for ... yes backup actions with RMAN and such, and SYSKM for security handling with TDE (Transparant Data Encryption). If you are very strict, you can use them all. Maybe this is usefull if you have multiple DBA teams with seperate responsibilities. I have not seen it yet. If you are alone on a small environment, SYSDBA will work where DBA is not enough.
以上内容摘录自：https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2659418700346202574
