Password file is only used to authenticate for SYSDBA,SYSOPER and SYSASM. Not for the normal user.
password file location: $ORACLE_HOME/dbs/orapw$ORACLE_SID
Example creating a passwordfile:
orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=orcl entries=5
If the password file already exists:
orapwd file=$ORACLE_HOME/dbs/orapwMYDB password=orcl entries=5 FORCE=Y
* The FORCE parameter is available starting from Oracle 10g.
Example: The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.
orapwd FILE=orapworcl ENTRIES=30
ENTRIES
This argument specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER.
Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are:
•NONE: Setting this parameter to NONE causes Oracle Database to behave as if the password file does not exist. That is, no privileged connections are allowed over nonsecure connections.
•EXCLUSIVE: (The default) An EXCLUSIVE password file can be used with only one instance of one database. Only an EXCLUSIVE file can be modified. Using an EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
•SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.
This option is useful if you are administering multiple databases or a RAC database.
If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.
Note:
You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.
Creating a Password File and Adding New Users to It
Use the following procedure to create a password and add new users to it:
1.Follow the instructions for creating a password file as explained in "Using ORAPWD".
2.Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default.)
Note: REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.
3.Connect with SYSDBA privileges as shown in the following example, and enter the SYS password when prompted:
CONNECT SYS AS SYSDBA
4.Start up the instance and create the database if necessary, or mount and open an existing database.
5.Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges", later in this section.
Viewing Password File Members
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA or SYSOPER system privileges for a database.
The columns displayed by this view are as follows:
Column Description
USERNAME This column contains the name of the user that is recognized by the password file.
SYSDBA If the value of this column is TRUE, then the user can log on with SYSDBA system privileges.
SYSOPER If the value of this column is TRUE, then the user can log on with SYSOPER system privileges.
[oracle@ORASERVER ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle force=y
[oracle@ORASERVER ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Nov 26 11:25:35 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
USER is "SYS"
SQL> CREATE USER NEWUSER IDENTIFIED BY NEWPASSW;
User created.
SQL> grant sysdba to NEWUSER;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
NEWUSER TRUE FALSE FALSE
2 rows selected.
revoke sysdba rights from newuser
SQL> revoke sysdba from NEWUSER;
No Comments