Grant SYSDBA Privileges to a Database Administrator

After creating the password file, enabling REMOTE_LOGIN_PASSWORDFILE, and connecting as SYSDBA, the next administrative task is granting administrative privileges to named DBA accounts. This lesson completes the password-file workflow by showing how to grant SYSDBA and how to verify that the grant appears in the password file privilege registry.

The SYSDBA privilege is the most powerful administrative privilege in Oracle Database. A user connected AS SYSDBA can start up and shut down the database, perform complete recovery, create or drop databases, and bypass normal object-level security checks.

In Oracle Database 23ai, the mechanics of granting SYSDBA remain familiar, but modern administration places much more emphasis on named accounts, least privilege, secrets management, and auditing. A DBA should not treat SYSDBA as an ordinary role. It is a special administrative privilege that gives the user database-level authority.

• Use named administrator accounts instead of shared logins.
• Use centralized identity, password, or secrets management where appropriate.
• Apply least privilege and separation of duties.
• Audit SYSDBA logins and privileged actions.

This lesson walks through the steps to grant SYSDBA to a database administrator, explains how SYSDBA differs from SYSOPER and the DBA role, and shows how to verify the grant in an Oracle 23ai password-file environment.

SYSDBA is a special system privilege, not just another role. A user connected as SYSDBA can:

• Start up and shut down the database instance.
• Mount, open, and close databases.
• Perform complete recovery, including time-based recovery.
• Create and drop databases.
• Bypass normal privileges when accessing data dictionary objects.
• Perform other critical maintenance operations.

Because SYSDBA effectively gives the user full database administrative authority, it must be granted only to a small number of trusted DBAs and tightly audited.

To grant SYSDBA to someone else, you must already be connected with SYSDBA privilege. In practice, that means one of the following:

• You connect as an OS-authenticated administrator on the database server:

  sqlplus / AS SYSDBA

• You connect remotely as SYS or another user that already has SYSDBA:

  sqlplus sys@coin AS SYSDBA

For password-based SYSDBA authentication, the instance must be configured with a password file created with orapwd and controlled by the REMOTE_LOGIN_PASSWORDFILE parameter. In a standard Oracle 23ai training database, the expected setting is usually:

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

In cloud or managed environments, password-file behavior may be integrated with service-level controls, wallets, IAM, or secrets management. The grant syntax remains recognizable, but the operational controls may be stronger.

1. Connect as an existing SYSDBA user

   From the database server, or through appropriate remote privileged authentication, connect as an existing SYSDBA user:

   sqlplus / AS SYSDBA
   -- or
   sqlplus sys@coin AS SYSDBA

2. Create or identify the DBA account

   Use a named account for each administrator. If the account does not yet exist, create it and grant only the privileges it needs:

   CREATE USER admin_user IDENTIFIED BY "Use_A_Strong_Secret_Here";
   
   GRANT CREATE SESSION TO admin_user;

   If the user also needs broad database administration privileges, you may grant the DBA role:

   GRANT DBA TO admin_user;

   Granting the DBA role gives broad object and system privileges, but it does not automatically allow the user to connect AS SYSDBA.

3. Grant the SYSDBA privilege

   From your SYSDBA session, run:

   GRANT SYSDBA TO admin_user;

   This marks admin_user as a SYSDBA-capable account. In a password-file environment, the user is represented in the password file privilege registry while the grant remains in effect.

4. Verify the SYSDBA grant

   To confirm that the user now has SYSDBA, query V$PWFILE_USERS:

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

   You should see SYSDBA = TRUE for the account.

5. Check password file information

   For continuity with the password-file lessons, you can also inspect password file metadata:

   SELECT FILE_NAME, FORMAT
   FROM V$PASSWORDFILE_INFO;

   This helps verify the password file location and format. For a new Oracle 23ai environment, a modern password file format such as 12.2 is preferred when supported by the environment.

6. Connect as the new SYSDBA user

   The DBA can now connect using a prompted password:

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

   This is better than placing the password directly in the command line. Prompted password entry avoids exposing the administrative password in terminal history, scripts, screenshots, or shared documentation.

Always ensure that SYSDBA accounts are:

• Protected by strong authentication, such as strong passwords, MFA, federated identity, or managed secrets where available.
• Individually assigned to named administrators.
• Audited, with alerts on unexpected SYSDBA logins.
• Reviewed periodically so unnecessary administrative grants can be revoked.

Oracle provides several ways to perform administrative tasks. For a deeper dive, see the companion page Different DBA Roles. At a high level:

1. SYSOPER: A special administrative privilege that allows the holder to perform routine instance operations, including:
   1. Start up the database instance.
   2. Shut down the database.
   3. Open and close the database.
   4. Perform certain types of recovery, but not full time-based recovery.
   5. Connect when the database is in restricted session mode.
   6. Stop and start archiving.
   7. Manually archive log files.
   8. View archive log status.

   SYSOPER is useful for operations staff who manage availability but do not need full database authority.

2. SYSDBA: A broader administrative privilege that includes SYSOPER-level authority and also allows the holder to:
   1. Perform time-based and full recovery.
   2. Create and drop databases.
   3. Bypass normal privileges when accessing database objects.

   In practice, SYSDBA gives full database administrative authority.

3. DBA role: A role that aggregates many system and object privileges, such as creating users and managing objects, but does not automatically include SYSDBA or SYSOPER. A user with the DBA role still cannot connect AS SYSDBA unless SYSDBA has been explicitly granted.

Scenario: A user receives the DBA role. The user can perform many database administration tasks, but cannot connect AS SYSDBA or AS SYSOPER until those administrative privileges are granted explicitly.

SQL> CONNECT pubs/pubs
Connected.

SQL> DROP USER tom;
User dropped.

SQL> CREATE USER tom IDENTIFIED BY "tom1234";
User created.

SQL> GRANT DBA TO tom;
Grant succeeded.

SQL> CONNECT tom/tom1234 AS SYSDBA;
ERROR:
ORA-01031: insufficient privileges

SQL> CONNECT sys AS SYSDBA
Connected.

SQL> GRANT SYSDBA TO tom;
Grant succeeded.

SQL> CONNECT tom/tom1234 AS SYSDBA;
Connected.

SQL> CONNECT tom/tom1234 AS SYSOPER;
ERROR:
ORA-01031: insufficient privileges

SQL> CONNECT sys AS SYSDBA
Connected.

SQL> GRANT SYSOPER TO tom;
Grant succeeded.

SQL> CONNECT tom/tom1234 AS SYSOPER;
Connected.

This example shows the separation clearly. The DBA role does not include SYSDBA. The SYSDBA grant does not automatically include SYSOPER as a separate password-file grant. Each administrative privilege must be granted intentionally.

The key point is that SYSDBA and SYSOPER are not implied by the DBA role. They are elevated, instance-level administrative privileges that belong to the password-file authentication model.

To grant SYSOPER or SYSDBA to a user, you must connect with SYSDBA yourself. Older Oracle documentation sometimes used an INTERNAL user in examples. That mechanism is historical. In Oracle 23ai, use modern administrative privilege syntax:

SQL> CONNECT sys AS SYSDBA
Connected.

SQL> GRANT SYSDBA TO system;
Grant succeeded.

SQL> GRANT SYSOPER TO system;
Grant succeeded.

When you grant SYSDBA or SYSOPER to a user in an environment that uses a password file, that user receives an entry in the password file privilege registry as long as the privilege remains granted.

Not every administrative task requires SYSDBA. For example, to create and manage Resource Manager plans and consumer groups, a user only needs the ADMINISTER_RESOURCE_MANAGER privilege, which can be granted using the DBMS_RESOURCE_MANAGER_PRIVS package:

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
  grantee_name  => 'GGOULD',
  privilege_name => 'ADMINISTER_RESOURCE_MANAGER',
  admin_option   => TRUE
);

You can later revoke this privilege through the REVOKE_SYSTEM_PRIVILEGE procedure in the same package. This pattern demonstrates least privilege: grant only what is needed for a task instead of defaulting to SYSDBA.

Before moving on to the next lesson, click the Exercise link below to practice granting the SYSDBA privilege to the SYSTEM user in your COIN database or a similar training environment.

Granting Privileges - Exercise