Difference between connecting to database as normal and sysdba/sysoper

As it is discussed in Database Administrator Authentication, it is said whenever you connect to database as sysdba privilege, you are connecting to SYS default schema. And whenever you are connecting to database as sysoper privilege, you are connecting to PUBLIC default schema.

Note that, sysdba and sysoper are special privilege and therefore certain types of operations can be performed whenever you assign sysdba/sysoper privilege to a user. A complete lists of operations that can be performed by the user who has sysdba/sysoper privilege are listed in the post SYSDBA and SYSOPER authorized operations. A very important thing to remember that, whenever you only assign these two privileges to a user and you don't assign any more privilege then user will not be able to do any schema/table level modification unless you specifically login as sysdba privilege.

With examples I will try to make you more clear between the differences.

sysdba privilege is not enough for a user to login to database unless he login as sysdba privilege
1. Login as sysdba.
E:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Jul 17 15:12:05 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2. Create a user named test_sysdba with password test_sysdba.
SQL> create user test_sysdba identified by test_sysdba;

User created.

3. Change default tablespace to users of test_sysdba user.
SQL> alter user test_sysdba default tablespace users;

User altered.

4. Grant sysdba privilege to user test_sysdba.
SQL> grant sysdba to test_sysdba;

Grant succeeded.

As soon as we assign sysbda privilege under password file there will be an entry. By querying v$pwfile_users view we can see an entry.

SQL> select * from v$pwfile_users ;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
TEST_SYSDBA                    TRUE  FALSE FALSE

5. Try to connect to database as test_sysdba
SQL> conn test_sysdba/test_sysdba
ERROR:
ORA-01045: user TEST_SYSDBA lacks CREATE SESSION privilege; logon denied


Warning: You are no longer connected to ORACLE.

As we have not specify "as sysdba" while login so it does not permit test_sysdba to login to database even he has sysdba privilege.

If you don't login as SYSDBA privilege it will act as a normal user
1. Log in as sysdba
SQL> conn / as sysdba
Connected.

2. Grant create session privilege to test_sysdba.
SQL> grant create session to test_sysdba;

Grant succeeded.

3. Now try to login as test_sysdba privilege and try to create table.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test(col1 number);
create table test(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

The "create table" statement fails as while login we did not specify "sysdba" privilege, and so user has connected to database as normal user.

4. Now connect as sysdba privilege and grant dba to test_sysdba user.
SQL> conn / as sysdba
Connected.

SQL> grant dba to test_sysdba;

Grant succeeded.

5. Connection will be successful as it has dba role but still it is normal test_sysdba user.
SQL> conn test_sysdba/test_sysdba
Connected.

SQL> create table test_sysdba_table1(col1 number);

Table created.

SQL> show user
USER is "TEST_SYSDBA"

Note that the user is TEST_SYSDBA.

Whenever we specify "sysdba privilege" while connecting the schema became SYS
1. Connect to database with test_sysdba user and using sysdba privilege.
SQL> conn test_sysdba/test_sysdba as sysdba
Connected.

SQL> show user
USER is "SYS"

Note that, now user became SYS as we specified "as sysdba" while login.

2. As it is SYS user and any table we create will go under SYS default "SYSTEM" tablespace whereas whenever we connect as normal test_sysdba user it would go under that user default schema.

SQL> create table test_sysdba_table2(col1 number);

Table created.

SQL> set lines 200
SQL> select owner, table_name, tablespace_name from dba_tables where table_name like 'TEST_SYSDBA_TABLE%';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
TEST_SYSDBA                    TEST_SYSDBA_TABLE1             USERS
SYS                            TEST_SYSDBA_TABLE2             SYSTEM

Whenever we connect through sysoper privilege the schema is PUBLIC
SQL> grant sysoper to test_sysdba;

Grant succeeded.

SQL> conn test_sysdba/test_sysdba as sysoper;
Connected.

SQL> show user
USER is "PUBLIC"

SQL> create table test_sysdba_table3(col1 number);
create table test_sysdba_table3(col1 number)
*
ERROR at line 1:
ORA-01031: insufficient privileges

So user connecting as sysoper privilege will not be able to create table as PUBLIC user is not permitted so.