Using a password file

Using a password file

Created by Vikash Singh

Description : It’s an alternative to connect to the remote database with sysdba/sysoper privileges as a non-sys user.

Location of password file : $ORACLE_HOME/dbs

Data Dictionary ( to know the user to whom sysdba or sysoper privileges is granted ) :
v$pwfile_users

How to create a password file 

Step 1 : Create the password file :

i. Go to the password binary location :

$ su – oracle1
$ cd $ORACLE_HOME/dbs

ii. Create the password file :

$ orapwd file=<any_name>_<ORACLE_SID>.ora password=<sys password> entries= <n>
where, file : user defined name
password : sys password

enteries : n is the maximum number of users you want to remotely access the database.

ex : $ orapwd file=vks_orcl.ora password=manager entries=3;

Step 2 : Edit the parameter file (assumed database is using the spfile )

sql> alter system set remote_login_passwordfile=exclusive scope=spfile;

Step 3 : Bounce the database :

SQL> startup force ;

Step 4 : Check if the password file is connected with the data dictionary or not:

i. connect to the database

$ sqlplus /nolog

SQL> conn /as sysdba

ii. Check the password file users name :

SQL > select username from v$pwfile_users;

USERNAME
------------------------------
SYS

- if this dictionary has sys a user it’s mean you have successfully create the password file which is connect to
the dictionary “v$pwfile_users” .

iii. create a user :

SQL> create user vikash identified by kumar;

iv. Grant sysdba to a newly created user :

SQL> grant sysdba to vikash ;
SQL > select username from v$pwfile_users;

USERNAME
------------------------------
SYS
VIKASH

- now user “vikash“ can log into the database remotely .
Rights assigned by oracle to the system users :
system privilege Authorized Operations
sysdba starts up and shut down database, alter database,create and drop database, toggle archivelog mode, recover
database.
sysoper starts up and shut down database, alter database, toggle archivelog mode, recover database.