Connecting as SYSDBA with Password File Authentication

This lesson modernizes an older Oracle administration topic that was historically described as connecting as INTERNAL with a password. In Oracle 23ai, the correct way to teach this topic is to explain how a database administrator connects to the COIN database as SYSDBA using password file authentication.

The older INTERNAL connection model belongs to legacy Oracle administration. Modern Oracle Database uses administrative privileges such as SYSDBA and SYSOPER. These privileges allow a DBA to perform startup, shutdown, recovery, mounting, opening, and other privileged operations. Therefore, the modern replacement for connecting as INTERNAL is connecting as a user with an administrative privilege, normally with the clause AS SYSDBA.

The workflow from the previous lessons is important. First, the password file was created with orapwd. Second, the database was configured with REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE. Now the DBA can connect to the COIN database through Oracle Net using password file authentication.

From INTERNAL to SYSDBA

Older Oracle course material often used the word INTERNAL to describe a privileged administrative connection. In that older model, connecting as INTERNAL meant that the DBA was connecting with special authority to start, stop, mount, open, or recover the database.

Oracle 23ai does not use INTERNAL as the current instructional model. Instead, administrative authority is expressed through privileges such as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, and SYSKM. The most familiar administrative connection is:

sqlplus / as sysdba

That command uses local operating system authentication. It is appropriate when the DBA is logged on to the database server with an operating system account that belongs to the correct Oracle administrative group. Remote administration is different. A remote connection normally uses Oracle Net and a password file.

Requirements Before Connecting

Before a DBA connects remotely to the COIN database as SYSDBA, several requirements should already be satisfied.

1. A password file must exist for the database.
2. The password file should be created with orapwd.
3. The database should be configured with REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE.
4. The Oracle Net service name, such as coin, must resolve to the correct database service.
5. The connecting user must have the administrative privilege needed for the connection, such as SYSDBA.

For a modern Oracle 23ai database, a password file creation command should avoid placing the password directly on the command line. A secure example is:

orapwd FILE=$ORACLE_BASE/dbs/orapwCOIN ENTRIES=10 FORMAT=12.2

With this pattern, Oracle prompts for the password instead of exposing it in terminal history or process listings. If your installation still uses the older operating system location, you may see examples that use $ORACLE_HOME/dbs. The important point is that the password file must be in the location expected by the Oracle instance.

The database must also be configured to use the password file:

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

SHUTDOWN IMMEDIATE;
STARTUP;

After the restart, Oracle rereads the server parameter file and applies the password file configuration.

Secure Local Administrative Connection

A local administrative connection is made from the database server itself. The common pattern is:

sqlplus / as sysdba

This form does not provide a database password. It depends on operating system authentication. If the operating system user belongs to the appropriate Oracle administrative group, Oracle allows the user to connect with SYSDBA authority.

Local operating system authentication is useful for server-side administration, but it does not explain the purpose of the password file. The password file becomes especially important when a DBA connects remotely.

Secure Remote Administrative Connection to COIN

To connect remotely to the COIN database as SYSDBA, use the Oracle Net service name and allow SQL*Plus to prompt for the password:

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

In this example, sys is the administrative user, coin is the Oracle Net service name, and AS SYSDBA tells Oracle that the connection is a privileged administrative connection. SQL*Plus prompts for the password and does not echo the typed characters to the screen.

This is the safest instructional pattern because the password is not displayed in the command, not saved in shell history, and not exposed in a visible script. The DBA types the password only when SQL*Plus prompts for it.

Four Connection Patterns

The legacy page used four INTERNAL examples. The modern equivalents use SYSDBA. The first two patterns are preferred because they allow SQL*Plus to prompt for the password. The second two patterns are shown only to explain why inline passwords should be avoided.

Local Connection with a Password Prompt

SQL> CONNECT sys AS SYSDBA
Enter password:
Connected.
SQL>

This pattern is useful when the DBA wants to authenticate as SYS with a password rather than relying on operating system authentication. SQL*Plus does not echo the password to the screen.

Remote Connection with a Password Prompt

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

This is the best pattern for the lesson objective. It connects to the COIN database remotely through Oracle Net and uses password file authentication for the privileged administrative connection.

Local Connection with an Inline Password - Not Recommended

SQL> CONNECT sys/password AS SYSDBA
Connected.
SQL>

Inline passwords can be exposed in screen recordings, terminal scrollback, copied command history, scripts, or shared documentation. A training page should show this form only to explain why it is less secure than the prompted method.

Remote Connection with an Inline Password - Not Recommended

SQL> CONNECT sys/password@coin AS SYSDBA
Connected.
SQL>

The more secure version is:

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

SQL*Plus Syntax Notes

The connection command contains several parts:

• sys is the administrative database user.
• coin is the Oracle Net service name for the COIN database.
• AS SYSDBA requests a privileged administrative connection.
• The password prompt authenticates the administrative user through the password file.

The service name coin may be resolved through a tnsnames.ora entry, Easy Connect syntax, a directory naming service, or another configured Oracle Net naming method. In modern Oracle administration, service names are preferred over raw SID-based connection thinking because services map better to high availability, multitenant architecture, and cloud deployment patterns.

REMOTE_LOGIN_PASSWORDFILE

Verifying the Configuration

Before troubleshooting a failed remote SYSDBA connection, check the parameter value:

SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE

Then verify the administrative users represented in the password file:

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
FROM V$PWFILE_USERS
ORDER BY USERNAME;

If the database supports it, inspect the password file name and format:

SELECT FILE_NAME, FORMAT
FROM V$PASSWORDFILE_INFO;

These checks help answer three practical questions:

1. Is Oracle configured to use password file authentication?
2. Is the administrative user listed in the password file?
3. Is the password file using a modern format such as 12.2?

Why Prompted Password Entry Is Safer

When a password is entered in response to a SQL*Plus prompt, Oracle does not echo the characters to the screen. This protects the password from casual observation and avoids leaving the password inside the command text.

The older examples used a password such as herman directly in commands. That style is useful for explaining syntax, but it should not be used as the recommended practice in a modern Oracle 23ai lesson. Administrative passwords should not be typed into commands that may be saved, copied, logged, or exposed.

Prefer this:

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

Avoid this as a normal working habit:

SQL> CONNECT sys/password@coin AS SYSDBA
Connected.
SQL>

Why Password Files Support Named DBA Accounts

The real utility of password files is not merely that they let a DBA connect remotely. Their larger value is that they allow administrative privileges to be granted to individual named users. This avoids forcing every administrator to share the SYS account.

For example, a database could have one user for backup administration and another user for Data Guard administration. Each account can be granted only the administrative privilege it needs:

GRANT SYSBACKUP TO backup_admin;
GRANT SYSDG TO dataguard_admin;

After those grants, the users can be checked with:

SELECT USERNAME, SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM
FROM V$PWFILE_USERS
ORDER BY USERNAME;

This is more secure and more auditable than using one shared administrative account for all DBA activity. In the next lessons, this course can build on this idea by explaining how administrative privileges are granted to other database administrators.

Security Considerations

Password file authentication is powerful because it enables remote administrative access. That also makes it sensitive. A DBA should apply the following practices:

1. Do not place administrative passwords directly on the SQL*Plus command line.
2. Do not hard-code privileged passwords in scripts.
3. Use named DBA accounts where possible instead of sharing the SYS account.
4. Use strong passwords that are not dictionary words.
5. Protect the password file at the operating system level.
6. Keep REMOTE_LOGIN_PASSWORDFILE set to EXCLUSIVE when named administrative user management is required.
7. Use Oracle Unified Auditing where appropriate to review privileged administrative activity.

The safest instructional pattern is to enter the user name and service name on the SQL*Plus command line, then allow SQL*Plus to prompt for the password without echoing it to the screen.

Common Connection Errors

Missing AS SYSDBA Clause

A privileged administrative connection must include the administrative clause:

CONNECT sys@coin AS SYSDBA

Without AS SYSDBA, Oracle treats the connection differently and the login may fail or lack the required administrative authority.

Password File Not Found

If the password file is missing or located in the wrong directory, remote privileged authentication can fail. Confirm the password file location and verify password file metadata with V$PASSWORDFILE_INFO when available.

REMOTE_LOGIN_PASSWORDFILE Set to NONE

If REMOTE_LOGIN_PASSWORDFILE is set to NONE, Oracle does not use the password file for remote privileged authentication.

Service Name Does Not Resolve

If coin does not resolve to the correct database service, the connection may fail before authentication is even attempted. Check tnsnames.ora, Easy Connect syntax, or the naming method used in the environment.

User Not Granted Administrative Privilege

A named user cannot connect AS SYSDBA unless that user has been granted SYSDBA. Use V$PWFILE_USERS to verify which users have password file administrative privileges.

Summary

The legacy phrase "connecting as INTERNAL with a password" should be understood as a historical version of modern password file authentication. In Oracle 23ai, the correct instructional model is to connect as SYSDBA or another administrative privilege through the password file.

The recommended remote connection pattern for the COIN database is:

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

This form uses the Oracle Net service name coin, requests SYSDBA authority, and allows SQL*Plus to prompt for the password securely. It replaces the old connect internal examples with the correct modern Oracle administration model.

In the next lessons, the password file becomes even more useful because it allows administrative privileges to be granted to individual DBA accounts instead of requiring every DBA to share the SYS account.