Password Files   «Prev 

Different DBA roles

One of the default roles that you get when you create an Oracle database is the DBA role. The DBA role encompasses all possible system privileges and is consequently very powerful. However, the DBA role does not generate an entry in the database password file. Without a password file entry, you cannot perform any task on a database unless it is fully open. The DBA role does not, for example, allow you to start and stop the database. The DBA role does give you the ability to perform all the many maintenance tasks that can be done while the database is online and open.

DBA_ROLE_PRIVS

DBA_ROLE_PRIVS describes the roles granted to all users and roles in the database.
Related View
USER_ROLE_PRIVS describes the roles granted to the current user.
Column Datatype NULL Description
GRANTEE VARCHAR2(30) Name of the user or role receiving the grant
GRANTED_ROLE VARCHAR2(30) NOT NULL Granted role name
ADMIN_OPTION VARCHAR2(3) Indicates whether the grant was with the ADMIN
OPTION (YES) or not (NO)
DEFAULT_ROLE VARCHAR2(3) Indicates whether the role is designated as a DEFAULT
ROLE for the user (YES) or not (NO)

Creating a Password File

Creating a password file is optional. There are some good reasons for requiring a password file:
  1. You want to assign non-sys users sys* privileges (sysdba, sysoper, sysbackup, and so on).
  2. You want to connect remotely to your database via Oracle Net with sys* privileges.
  3. An Oracle feature or utility requires the use of a password file.

Perform the following steps to implement a password file:
  1. Create the password file with the orapwd utility.
  2. Set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.

In a Linux/Unix environment, use the orapwd utility to create a password file, as follows: 

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

In a Linux/Unix environment the password file is usually stored in ORACLE_HOME/dbs; in Windows it’s typically placed in the ORACLE_HOME\database directory. The format of the filename that you specify in the previous command may vary by OS. For instance, in Windows the format is PWD<ORACLE_SID>.ora. The following example shows the syntax in a Windows environment: 
c:\> cd %ORACLE_HOME%\database
c:\> orapwd file=PWD<ORACLE_SID>.ora password=<sys password>

To enable the use of the password file, set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE (this is the default value). If the parameter is not set to EXCLUSIVE, then you’ll have to modify your parameter file: 
SQL> alter system 
set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

You need to stop and start the instance to instantiate the prior setting. You can add users to the password file via the GRANT
<any SYS privilege> statement. The following example grants SYSDBA privileges to the heera user (and thus adds heera to the password file): 
VSQL> grant sysdba to heera;
Grant succeeded.

Enabling a password file also allows you to connect to your database remotely with SYS*-level privileges via an Oracle Net connection. This example shows the syntax for a remote connection with SYSDBA-level privileges: 
$ sqlplus <username>/<password>@<database connection string> 
as sysdba

This allows you to do remote maintenance with sys* privileges (sysdba, sysoper, sysbackup, and so on) that would otherwise require your logging in to the database server physically. You can verify which users have sys* privileges by querying the V$PWFILE_USERS view: 
SQL> select * from v$pwfile_users;