Different DBA roles and the database password file

When you create an Oracle database, one of the most important security building blocks is the set of database administrator roles and privileges. The DBA role is powerful, but it is not the same as the special SYS*-level privileges (SYSDBA, SYSOPER, SYSBACKUP, and so on) that are tied to the database password file.

The DBA role is a role granted inside the database. It aggregates many system and object privileges and allows a user to:

Create and manage schemas and objects
Manage users and roles
Perform many online maintenance tasks while the database is open

However, granting DBA alone does not:

Create an entry for the user in the password file
Allow the user to connect AS SYSDBA or AS SYSOPER
Allow the user to start up or shut down the instance when the database is not yet open

The ability to connect with SYS*-level privileges (for example, CONNECT user AS SYSDBA) is controlled by the password file and the SYS* privileges (GRANT SYSDBA TO user, etc.), not by the DBA role alone.

Viewing DBA role grants with DBA_ROLE_PRIVS

The DBA_ROLE_PRIVS data dictionary view describes which roles have been granted to each user or role in the database. It is a useful way to audit who has DBA or other powerful roles.

Related view

USER_ROLE_PRIVS – shows only the roles granted to the current session’s user.

Column	Datatype	NULL	Description
GRANTEE	VARCHAR2(30)		Name of the user or role receiving the grant
GRANTED_ROLE	VARCHAR2(30)	NOT NULL	Name of the granted role (for example, DBA)
ADMIN_OPTION	VARCHAR2(3)		Indicates whether the role was granted WITH ADMIN OPTION (YES = can grant this role to others, NO = cannot)
DEFAULT_ROLE	VARCHAR2(3)		Indicates whether the role is enabled by default when the user logs in (YES or NO)

Remember that DBA_ROLE_PRIVS shows role grants (such as DBA, RESOURCE, custom roles), but it does not directly show SYS* privileges. SYS* privileges are surfaced through the password file and the V$PWFILE_USERS view, described below.

Why the database password file matters

A database password file allows named users to connect with special SYS*-level privileges over Oracle Net (for example, from a remote workstation). Historically, this has been handled by the orapwd utility and the REMOTE_LOGIN_PASSWORDFILE parameter; in newer releases and cloud deployments the file may reside in ASM or be managed by clusterware, but the underlying concepts remain the same.

Typical reasons to use a password file include:

Assigning SYS* privileges (SYSDBA, SYSOPER, SYSBACKUP, and others) to named, non-SYS users.
Allowing DBAs to connect remotely via Oracle Net with SYS* privileges.
Supporting Oracle features or utilities that require password file–based SYSDBA/SYSOPER access.

At a high level, implementing a password file involves:

Creating the password file with the orapwd utility.
Ensuring the initialization parameter REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE (recommended).
Granting SYS* privileges (for example, GRANT SYSDBA) to specific users, which adds them to the password file.

Creating a password file with orapwd

In a Linux or Unix environment, the password file is typically stored in the $ORACLE_HOME/dbs directory. A basic example:

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw<ORACLE_SID> password=<sys_password>

On Windows, the password file typically resides under %ORACLE_HOME%\database, with a slightly different filename convention:

c:\> cd %ORACLE_HOME%\database
c:\> orapwd file=PWD<ORACLE_SID>.ora password=<sys_password>

In both cases, replace <ORACLE_SID> with the instance SID and choose a strong password for the SYS account (or for the initial administrative identity in newer configurations).

Configuring REMOTE_LOGIN_PASSWORDFILE

To enable use of the password file, set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE (this is the recommended and default value in many environments). If needed, you can update the parameter in the server parameter file (SPFILE):

SQL> ALTER SYSTEM
  SET remote_login_passwordfile = 'EXCLUSIVE'
  SCOPE = SPFILE;

Because this is an instance-level parameter, you must stop and start the database instance for the new setting to take effect.

Adding users to the password file via SYS* privileges

You do not edit the password file by hand. Instead, Oracle automatically maintains it based on GRANT statements for SYS* privileges. When you run:

SQL> GRANT SYSDBA TO heera;
Grant succeeded.

Oracle adds an entry for user HEERA to the password file (assuming password file usage is enabled). You can grant other SYS* privileges such as SYSOPER, SYSBACKUP, and so on in a similar manner.

Once a user has SYSDBA in the password file, they can connect remotely with SYSDBA-level access:

$ sqlplus <username>/<password>@<database_connection_string> AS SYSDBA

This enables remote, named administration that would otherwise require logging in directly to the database server and using operating system authentication.

Auditing who has SYS* privileges: V$PWFILE_USERS

To see which users currently have SYS*-level privileges in the password file, query V$PWFILE_USERS:

SQL> SELECT username, sysdba, sysoper, sysbackup
  2  FROM   v$pwfile_users
  3  ORDER  BY username;

This view shows which accounts can connect with elevated privileges. From a security and compliance standpoint, this list should be:

Short – only a handful of trusted administrator accounts
Backed by strong authentication and, ideally, MFA or federated identity
Regularly reviewed and audited

In modern Oracle Database deployments (including Oracle Database 23ai in multicloud and hybrid environments), the exact storage and management of the password file may evolve, but the principles remain the same: use named accounts, grant SYS* privileges sparingly, and monitor who appears in V$PWFILE_USERS.