Lesson 9	Connecting as SYSDBA
Objective	Connect to the `COIN` database as SYSDBA in Oracle 23ai.

Connecting to the COIN Database as SYSDBA

The previous lessons created the password file, configured REMOTE_LOGIN_PASSWORDFILE, granted administrative privileges, and explained how named DBA accounts can be represented in the password file. This lesson completes that workflow by showing how to connect to the COIN database with the AS SYSDBA clause.

A normal database connection and a privileged administrative connection are not the same thing. The command:

CONNECT admin_user@coin

creates a normal database session for admin_user. The command:

CONNECT admin_user@coin AS SYSDBA

creates a privileged administrative session, assuming that admin_user has been granted the SYSDBA administrative privilege and the password file configuration supports remote privileged authentication.

Older examples often used SYSTEM/manager@coin to demonstrate the syntax. In Oracle 23ai, the better teaching pattern is to use a named administrative account and allow SQL*Plus to prompt for the password. This avoids placing a privileged password directly on the command line.

The Modern SYSDBA Connection Pattern

The recommended SQL*Plus connection pattern for this lesson is:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.
SQL>

This command has four important parts:

CONNECT is the SQL*Plus command used to begin a database session.
admin_user is a named administrative account that has been granted SYSDBA.
@coin identifies the Oracle Net service name for the remote COIN database.
AS SYSDBA requests a privileged administrative connection.

The safest instructional pattern is to place the user name, service name, and administrative clause on the SQL*Plus command line, then allow SQL*Plus to prompt for the password. SQL*Plus does not echo the password characters to the screen.

Breaking Down the CONNECT Command

Modern Oracle 23ai diagram showing CONNECT admin_user@coin AS SYSDBA broken into CONNECT, admin_user, @coin, and AS SYSDBA callouts. — The command `CONNECT admin_user@coin AS SYSDBA` uses a named administrative account, the `coin` Oracle Net service name, and the `AS SYSDBA` clause to create a privileged administrative session. Replace the image file name if your generated image uses a different name.

The seven legacy slideshow images on the original page all explained parts of one command. The modern version can be taught more clearly with one diagram and a direct text explanation. The main point is that AS SYSDBA changes the connection type. It is not decoration at the end of the command.

Why AS SYSDBA Matters

A database user may have ordinary database privileges, system privileges, object privileges, roles, and administrative privileges. These categories are not identical. A user can connect normally and still not be connected as SYSDBA.

For example:

SQL> CONNECT admin_user@coin
Enter password:
Connected.
SQL>

This creates a normal database session. The user is connected as admin_user, but the session is not using SYSDBA authority. The user may have the DBA role or other system privileges, but the session is still not a SYSDBA administrative session.

By contrast:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.
SQL>

This requests administrative authority through the password file. If the account has been granted SYSDBA, Oracle authenticates the privileged connection and starts a session with SYSDBA authority.

Connecting as SYSOPER

SYSOPER is a separate administrative privilege. If a user has been granted SYSOPER, the user can connect with:

SQL> CONNECT admin_user@coin AS SYSOPER
Enter password:
Connected.
SQL>

This does not mean every SYSDBA user should also be granted SYSOPER, or that every operational DBA needs SYSDBA. The purpose of separate administrative privileges is separation of duties. Use SYSOPER for operational administration when full SYSDBA authority is not required.

The important distinction is:

CONNECT admin_user@coin creates a normal session.
CONNECT admin_user@coin AS SYSDBA creates a SYSDBA administrative session if SYSDBA has been granted.
CONNECT admin_user@coin AS SYSOPER creates a SYSOPER administrative session if SYSOPER has been granted.

Secure Password Handling

The legacy version of this page used this form:

CONNECT system/manager@coin AS SYSDBA

That form is useful for showing the old syntax pieces, but it is not the recommended instructional pattern for Oracle 23ai. It places the password in the command text. That password can appear in terminal history, screenshots, scripts, command logs, shared notes, or copied examples.

Prefer this:

CONNECT admin_user@coin AS SYSDBA

Then allow SQL*Plus to prompt:

Enter password:

This pattern still teaches the full connection structure while avoiding the security problem of inline passwords. It also reinforces the use of named administrative accounts instead of shared use of SYSTEM or SYS.

Oracle Net Service Name: @coin

The @coin portion of the command identifies the Oracle Net service name. It tells SQL*Plus which remote database service to contact. In older material, you may see this described as a Net8 service. In current Oracle terminology, describe it as an Oracle Net service name, Easy Connect target, or directory naming entry, depending on how the environment resolves database services.

In this lesson, coin represents the network service used to connect to the COIN database:

CONNECT admin_user@coin AS SYSDBA

If the service name is not configured correctly, the connection may fail before Oracle even evaluates the password or administrative privilege. A DBA should verify the service name through the local naming method, such as tnsnames.ora, Easy Connect Plus syntax, directory naming, or cloud connection configuration.

How to Grant System Privileges

As with object privileges, the GRANT statement is used to confer privileges on a user or role. However, system privileges and object privileges use different delegation clauses.

To give the CREATE USER, ALTER USER, and DROP USER privileges to the role APPL_DBA, use:

GRANT CREATE USER, ALTER USER, DROP USER
TO appl_dba;

If the grantee should also be able to grant those system privileges to other users or roles, use WITH ADMIN OPTION:

GRANT CREATE USER, ALTER USER, DROP USER
TO appl_dba
WITH ADMIN OPTION;

Object privileges use a different clause. For example:

GRANT SELECT ON employees
TO report_user
WITH GRANT OPTION;

System privileges and role grants use WITH ADMIN OPTION.
Object privileges use WITH GRANT OPTION.

The function is similar because both clauses allow a grantee to pass something onward, but the syntax is different. Use ADMIN for system privileges and role grants. Use GRANT for object privileges.

SYSDBA versus SYSOPER

SYSDBA and SYSOPER are special administrative privileges, not ordinary application roles. When a user connects AS SYSDBA or AS SYSOPER, Oracle authenticates the administrative connection through the password file when password-file authentication is being used.

A normal login and a privileged login are authenticated in different administrative contexts. A normal login checks the user's ordinary database authentication path. A privileged login using AS SYSDBA or AS SYSOPER depends on the password file configuration and the administrative privilege granted to the user.

If privileged authentication behaves unexpectedly, do not casually recreate the password file as a first step. First verify the configuration:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
FROM V$PWFILE_USERS
WHERE USERNAME = 'ADMIN_USER';

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

Recreating the password file may be a controlled repair step in some cases, but it should be planned carefully because remote administrative access, backup operations, Data Guard administration, and automation may depend on the password file.

Administrative Privileges

Administrative privileges required for basic database operations are granted through special privileges such as SYSDBA and SYSOPER. A user must have the appropriate administrative privilege depending on the level of authority required.

The SYSDBA and SYSOPER privileges allow access to a database instance even when the database is not open. This is why they are treated differently from ordinary database roles. They are connection privileges that allow certain operations to occur before normal database access is available.

For example, if a user has the SYSDBA privilege, that user can connect with:

CONNECT admin_user@coin AS SYSDBA

SYSDBA and SYSOPER Operations

The following operations are authorized by the SYSDBA and SYSOPER administrative privileges.

SYSDBA

Perform STARTUP and SHUTDOWN operations.
Use ALTER DATABASE to open, mount, back up, or change database state.
Create a database.
Drop a database.
Create an SPFILE.
Use ALTER DATABASE ARCHIVELOG.
Use ALTER DATABASE RECOVER, including incomplete recovery when required.
Use the RESTRICTED SESSION privilege.

Effectively, SYSDBA allows the user to connect with the authority of SYS. It should be granted only to trusted administrators who require full database instance authority.

SYSOPER

Perform STARTUP and SHUTDOWN operations.
Create an SPFILE.
Use ALTER DATABASE OPEN, ALTER DATABASE MOUNT, and ALTER DATABASE BACKUP.
Use ALTER DATABASE ARCHIVELOG.
Use ALTER DATABASE RECOVER for complete recovery.
Use the RESTRICTED SESSION privilege.

SYSOPER allows a user to perform basic operational tasks without granting full SYSDBA authority. It is useful for administrators who need to manage availability but do not need unrestricted database control.

When a user connects with SYSDBA or SYSOPER, the user connects with a default administrative schema rather than the ordinary schema associated with the user name. For SYSDBA, the default schema is SYS. For SYSOPER, the default schema is PUBLIC.

In modern Oracle multitenant architecture, the database environment may contain a container database and multiple pluggable databases. Privileged authentication must be understood at the administrative level of the database architecture rather than as a simple ordinary user login.

Common Connection Mistakes

Connecting Without AS SYSDBA

This command creates a normal database session:

CONNECT admin_user@coin

It does not activate SYSDBA authority.

Using an Inline Password

This form is discouraged:

CONNECT admin_user/password@coin AS SYSDBA

Use a prompted password instead:

CONNECT admin_user@coin AS SYSDBA

Using SYSOPER Without the SYSOPER Grant

A user cannot connect AS SYSOPER unless SYSOPER has been granted explicitly:

CONNECT admin_user@coin AS SYSOPER

Using Old Net8 Terminology

Older Oracle lessons may describe @coin as a Net8 service. Modern Oracle lessons should describe it as an Oracle Net service name or a service resolved through Easy Connect Plus, local naming, directory naming, or cloud connection configuration.

Summary

To connect to the COIN database as SYSDBA, use a named administrative account, the Oracle Net service name, and the AS SYSDBA clause:

SQL> CONNECT admin_user@coin AS SYSDBA
Enter password:
Connected.
SQL>

The AS SYSDBA clause changes the session from a normal database login into a privileged administrative connection. Without that clause, the user gets an ordinary database session. With AS SYSOPER, the user gets a SYSOPER session only if that administrative privilege has been granted.

The modern Oracle 23ai pattern is to use named administrative accounts, avoid inline passwords, verify password-file users, and audit privileged connections.

SysDBA - Quiz

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