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.

AS SYSDBA Clause

1)This CONNECT command is similar to what you have seen so far, the major addition is the SYSDBA clause at the end.
1) This CONNECT command is similar to what you have seen so far, the major addition is the SYSDBA clause at the end.

2) The username is system
2) The username is system 
CONNECT system/manager@coin AS SYSDBA

3) The password is manager
3) The password is manager 
CONNECT system/manager@coin AS SYSDBA

4) Coin is the name of a Net8 Service. The @coin syntax is being used to connect to a remote database over a network
4) Coin is the name of a Network Service. The @coin syntax is being used to connect to a remote database over a network. 
CONNECT system/manager@coin AS SYSDBA

5) AS SYSDBA Clause lets the system user connect in its role as a SYSDBA
5) The AS SYSDBA Clause lets the system user connect in its role as a SYSDBA 
CONNECT system/manager@coin AS SYSDBA

6) The system user could connect like this, but then would not have access to any privileges confered by the SYSDBA role
6) The system user could connect like this, but then would not have access to any privileges confered by the SYSDBA role. 
CONNECT system/manager@coin


7) To connect using the SYSOPER role, assuming that you have been granted that role, use AS SYSOPER instead of SYSDBA.
7) To connect using the SYSOPER role, assuming that you have been granted that role, use AS SYSOPER instead of SYSDBA. 
CONNECT system/manager@coin AS SYSOPER

  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.

How to Grant System Privileges

As with object privileges, you use the GRANT statement to confer system privileges on either a user or a role. Unlike object privileges, the optional keywords WITH ADMIN OPTION are required to additionally allow the grantee to confer these privileges on other users and roles. For example, to give the CREATE USER, ALTER USER, and DROP USER privileges to the role APPL_DBA, you execute the following statement: 
GRANT create user, alter user, 
drop user TO appl_dba;
  1. System and role privileges require the wording WITH ADMIN OPTION;
  2. object privileges require the wording WITH GRANT OPTION.
Because the function is so similar but the syntax is different, be sure you know when to use ADMIN and when to use GRANT, a question involving this subtle difference may appear on the exam.

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