Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Animesh.

Asked: July 06, 2010 - 3:14 pm UTC

Last updated: January 10, 2018 - 12:50 am UTC

Version: 10.2.0

Viewed 100K+ times! This question is

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

and Tom 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.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks. I helped.

Animesh Gupta, July 12, 2010 - 6:54 am UTC

I got the answer I was looking for. I was very keen to have some good web link to read more about it. Thanks a lot anyways. I will keep coming back with more questions and confusions.

difference between SYS and SYSTEM?

Andrew Wolfe, July 12, 2010 - 8:35 am UTC

Thanks for the info. Why two schemas SYS and SYSTEM?
Tom Kyte
July 19, 2010 - 10:32 am UTC

SYS is like root for us. It holds the data dictionary, it is special (it physically works differently from other accounts - no flashback query for it, no read only transactions, no triggers, etc)

SYSTEM is our DBA account, it is just a normal user, subject to normal rules - but it is OUR account nonetheless. It holds non-mandatory things - extras if you will.

sysdba is not role by system privliages

amihay gonen, October 22, 2013 - 1:08 pm UTC

accordoing to http://docs.oracle.com/cd/B19306_01/server.102/b14231/dba.htm#sthref137

" two special system privileges, SYSDBA and SYSOPER"

goog knowledge

Gaurang Tyagi, August 25, 2014 - 11:29 am UTC


more roles in recent versions

Paul Alsemgeest, November 23, 2017 - 6:46 am UTC

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.
Connor McDonald
November 27, 2017 - 1:10 am UTC

Thanks - we've updated the question with the info

Useful, but dangerous

JCRowan, January 08, 2018 - 9:27 pm UTC

We are being forced to give up the sqlplus / as sysdba as that is a SOX violation in our Exadata world where we have multiple databases on the same server. If one is granted sysdba to a database user who is a DBA. When the account get locked they can still log in as sqlplus <user> as sydba, then enter the password to get in. It allows you in to shutdown or startup or whatever as sysdba, even if the <user> account has been locked. What is you suggestion so that sqlplus / as sysdba is disabled and individual DBA's id's can log in to startup or shutdown databases?
Connor McDonald
January 10, 2018 - 12:50 am UTC

This is more to do with *where* you are allowing DBA's access to. If you give someone local access to the server (in the dba group) then yes, you are basically giving them the keys (because you could do whatever they want at an OS level anyway).

If you protect that server access (as you should) then sysdba access becomes controlled in the normal manner. For example

--
-- on db server
--

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> create user the_dba identified by the_dba;

User created.

SQL> grant create session to the_dba;

Grant succeeded.

SQL> grant sysdba to the_dba;

Grant succeeded.

--
-- from remote machine
--
SQL> conn the_dba/the_dba@np122
Connected.

SQL> sho user
USER is "THE_DBA"
SQL> conn the_dba/the_dba@np122 as sysdba
Connected.

SQL> sho user
USER is "SYS"
SQL> disc
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


--
-- on db server
--
SQL> alter user the_dba account lock;

User altered.

SQL> conn the_dba/the_dba@np122 as sysdba
Connected.

--
-- from remote machine
--
SQL> conn the_dba/the_dba@np122 as sysdba
ERROR:
ORA-28000: the account is locked


More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database