Oracle Internals from Basic to Advanced....: ORACLE User Accounts and Authentication…

Two administrative user accounts are automatically created when Oracle Database is installed:

SYS (default password: CHANGE_ON_INSTALL)
SYSTEM (default password: MANAGER)

SYS

When you create an Oracle database, the user SYS is automatically created and granted the DBA role.

SYSTEM

When you create an Oracle Database, the user SYSTEM is also automatically created and granted the DBA role.

Note - A predefined DBA role is automatically created with every Oracle Database installation. This role contains most database system privileges. Therefore, the DBA role should be granted only to actual database administrators.

SYSDBA and SYSOPER – are two Administrative privileges that are required for an administrator to perform basic database operations.

Important note :- The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is totally outside of the database itself.

With reference above note, I would like to refer to one of the important links of ”Tanel Poder”, very he demonstrated how to logon to DB in case of crash. It’s very interesting and innovative

Request to go through the link and find the things how actually ORACLE login process works

http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so

Now, let us see what are things SYSDBA and SYSOPER can perform or difference between the both of them

SYSDBA and SYSOPER

The following operations are authorized by the SYSDBA and SYSOPER system privileges:

SYSDBA

Perform STARTUP and SHUTDOWN operations
ALTER DATABASE: open, mount, back up, or change character set
CREATE DATABASE
DROP DATABASE
CREATE SPFILE
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER
Includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.

SYSOPER

Perform STARTUP and SHUTDOWN operations
CREATE SPFILE
ALTER DATABASE OPEN/MOUNT/BACKUP
ALTER DATABASE ARCHIVELOG
ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
Includes the RESTRICTED SESSION privilege

This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.

When we connect to user as SYSDBA – then default schema which we are connecting to DB is SYS Schema, similarly when we connect as SYSOPER – then default schema is PUBLIC schema.

Let’s see an example for the above statements.

   1: SQL> connect / as sysdba   2: Connected.   3: SQL> create user test2 identified by test2;   4:     5: User created.   6:     7: SQL> grant connect,resource to test2;   8:     9: Grant succeeded.  10:    11: SQL> disconnect  12: Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  13: With the Partitioning, OLAP, Data Mining and Real Application Testing options  14: SQL> conn test2/test2  15: Connected.  16: SQL> create table table1(c1 number);  17:    18: Table created.  19:    20: SQL> disconnect  21: Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production  22: With the Partitioning, OLAP, Data Mining and Real Application Testing options  23: SQL> conn test2 as sysdba  24: Enter password:  25: Connected.  26: SQL> show user  27: USER is "SYS"  28: SQL> select * from table1;  29: select * from table1  30:               *  31: ERROR at line 1:  32: ORA-00942: table or view does not exist

Authentication Method :-

Database authentication takes place through Data dictionary. I will talk about the Oracle 11g Release versions. Oracle Database 11g Release 1, database passwords are case-sensitive. From Oracle Database 11g Release 2, SEC_CASE_SENSITIVE_LOGON is TRUE.

   1: SQL> select * from v$version;   2:     3: BANNER   4: --------------------------------------------------------------------------------   5: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production   6: PL/SQL Release 11.2.0.1.0 - Production   7: CORE    11.2.0.1.0      Production   8: TNS for 32-bit Windows: Version 11.2.0.1.0 - Production   9: NLSRTL Version 11.2.0.1.0 - Production  10:    11: SQL> show parameter SEC_CASE_SENSITIVE_LOGON  12:    13: NAME                                 TYPE        VALUE  14: ------------------------------------ ----------- ------------------------------  15: sec_case_sensitive_logon             boolean     TRUE