Password Files   «Prev  Next»

Lesson 9 Connecting as SYSDBA
Objective Connect to the COIN database as SYSDBA.

Oracle Connecting Database as SYSDBA

In order to connect to the COIN database as SYSTEM and make use of the SYSDBA role, you need to use a special form of the CONNECT command. The following SlideShow shows this and explains each part of the command.

  1. This CONNECT command is similar to what you have seen so far, the major addition is the SYSDBA clause at the end.
  2. CONNECT system/manager@coin AS SYSDBA
    The username is system 
  3. CONNECT system/manager@coin AS SYSDBA
    The password is manager
  4. CONNECT system/manager@coin AS SYSDBA
    The password is manager
  5. CONNECT system/manager@coin AS SYSDBA
    The AS SYSDBA Clause lets the system user connect in its role as a SYSDBA
  6. CONNECT system/manager@coin

    The system user could connect like this, but then would not have access to any privileges confered by the SYSDBA role. 
  7. CONNECT system/manager@coin AS SYSOPER
    To connect using the SYSOPER role, assuming that you have been granted that role, use AS SYSOPER instead of SYSDBA.

System DBA Clause

SYSDB versus SYSOPER Role

If you are a user who has been granted the SYSDBA role, or SYSOPER for that matter, your password will be stored in two different places. One copy will be in the database password file. Another copy will be stored within the database itself. When you connect using AS SYSDBA or AS SYSOPER, Oracle compares the password that you supply with the CONNECT command to that stored in the password file. Otherwise, Oracle compares the password that you supply with that stored in the database. Oracle is supposed to ensure that the password in the password file matches that stored in the database. On a very few occasions, I have seen things get out of sync. If that ever happens, one way to fix the problem is to recreate the password file.
Since you have already granted the SYSDBA role to yourself, the SYSTEM user, now you should try to log into the database as SYSDBA.

Administrative Privileges

Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, 1) SYSDBA and 2) SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
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. The SYSDBA and SYSOPER privileges can also be thought of as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other fashion. For example, you if you have the SYSDBA privilege, you can connect to the database by specifying CONNECT AS SYSDBA.

SYSDBA and SYSOPER

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

System Privilege and Operations Authorized

SYSDBA
  1. Perform STARTUP and SHUTDOWN operations
  2. ALTER DATABASE: open, mount, back up, or change character set
  3. CREATE DATABASE
  4. DROP DATABASE
  5. CREATE SPFILE
  6. ALTER DATABASE ARCHIVELOG
  7. ALTER DATABASE RECOVER
  8. includes the RESTRICTED SESSION privilege

Effectively, this system privilege allows a user to connect as user SYS.
SYSOPER
  1. Perform STARTUP and SHUTDOWN operations
  2. CREATE SPFILE
  3. ALTER DATABASE OPEN/MOUNT/BACKUP
  4. ALTER DATABASE ARCHIVELOG
  5. ALTER DATABASE RECOVER (Complete recovery only. Any form of incomplete recovery, such as UNTIL TIME|CHANGE|CANCEL|CONTROLFILE requires connecting as SYSDBA.)
  6. Includes the RESTRICTED SESSION privilege
This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The manner in which you are authorized to use these privileges depends upon the method of authentication that you use. When you connect with SYSDBA or SYSOPER privileges, you connect with a default schema, not with the schema that is generally associated with your username. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
Before Oracle 12c an Oracle Instance could only have one Database. With Oracle 12c, Oracle added the ability for a single Instance to manage multiple Databases.

SysDBA - Quiz

Click the Quiz link below to take a short quiz on Sysdba in Oracle.
SysDBA - Quiz