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 describes the roles granted to all users and roles in the database.
USER_ROLE_PRIVS describes the roles granted to the current user.
||Name of the user or role receiving the grant
||Granted role name
||Indicates whether the grant was with the ADMIN
OPTION (YES) or not (NO)
||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:
- You want to assign non-sys users sys* privileges (sysdba, sysoper, sysbackup, and so on).
- You want to connect remotely to your database via Oracle Net with sys* privileges.
- An Oracle feature or utility requires the use of a password file.
Perform the following steps to implement a password file:
- Create the password file with the orapwd utility.
- 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;
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>
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;