Oracle’s Password File: remote_login_passwordfile

May 22, 2012 Leave a comment

Oracle’s password file helps you to authenticate the database until the instance has been started. As you can guess, the password cannot be saved in DB , because DBAs will not be able to access database until instance is started.

There are two methods to authentication the DBA:

1. OS authentication.

2. Password file authentication.

The initialization parameter remote_login_passwordfile indicates which method should be used.

Available Values:

1. NONE : No password file is used.  Authentication happens via OS.

2. EXCLUSIVE: This is a default value. Password file can only be used by one database. It enables you to grant SYSDBA or SYSOPER privileges to the users other than SYS. You are also able to change their passwords, which can’t be done in SHARED mode.

Note: Entries in password file is created automatically when you grant SYSDBA or SYSOPER privileges to the user. If user has any of these privileges, entry exists in password file. If you revoke  both of them entry will be deleted automatically.

To check what user entries are located in passwordfile:

select * from v$pwfile_users;

3. SHARED: Means password file is shared and one or more database can use it. But this also means that ONLY SYS user can be added to the password file. And you are not able to change its password.  

Note: Password file can contain non-SYS users, only if they were added before the value of this parameter has been changed to SHARED.

Note:  In Real Application Clusters each instance must have the same value.

Password file is created with the orapwd.

Advertisement

Filed under DB Tagged with , , , , ,

Oracle’s Password File: remote_login_passwordfile

May 22, 2012 Leave a comment

Oracle’s password file helps you to authenticate the database until the instance has been started. As you can guess, the password cannot be saved in DB , because DBAs will not be able to access database until instance is started.

There are two methods to authentication the DBA:

1. OS authentication.

2. Password file authentication.

The initialization parameter remote_login_passwordfile indicates which method should be used.

Available Values:

1. NONE : No password file is used.  Authentication happens via OS.

2. EXCLUSIVE: This is a default value. Password file can only be used by one database. It enables you to grant SYSDBA or SYSOPER privileges to the users other than SYS. You are also able to change their passwords, which can’t be done in SHARED mode.

Note: Entries in password file is created automatically when you grant SYSDBA or SYSOPER privileges to the user. If user has any of these privileges, entry exists in password file. If you revoke  both of them entry will be deleted automatically.

To check what user entries are located in passwordfile:

select * from v$pwfile_users;

3. SHARED: Means password file is shared and one or more database can use it. But this also means that ONLY SYS user can be added to the password file. And you are not able to change its password.  

Note: Password file can contain non-SYS users, only if they were added before the value of this parameter has been changed to SHARED.

Note:  In Real Application Clusters each instance must have the same value.

Password file is created with the orapwd.

Filed under DB Tagged with , , , , ,

What is orapwd?

January 23, 2011 Leave a comment

Orapwd is the Oracle utility to create password file.  The syntax is the following:

orapwd file=file_name
password=password for SYS
[entries=number_of_users]
[force=Y/N]
[ignorecase=Y/N]
[nosysdba=Y/N]

Where,
file– is the password file name. If you do not indicate the full path, then file will be created in the current directory.
password-is the password for sys user.
entries– is the maximum number of users that can be granted sysdba or sysoper privileges.
force-if the value of this parameter is Y then the existing password file will be overwritten.
ignorecase– password will be case insensitive.

Note: parameters that are enclosed by ‘[‘ and ‘]’ are optional.

For example:

 orapwd file= pwdorcl password=sys entries=20

Explanation:

Password file, called pwdorcl.ora, will be created in the current directory. The password for sys user will be sys and maximum 20 users can be granted sysdba or sysoper privileges.

Note: Default location of the password file on Windows is %ORACLE_HOME%\database\ and name is pwd%ORACLE_SID%.ora ….. On Linux $ORACLE_HOME\dbs and name orapw$ORACLE_SID. If you do not consider this you will get error:  ORA-01017

In addition to password file creation:

The initialization parameter remote_login_passwordfile must be set to the appropriate value:

  • None: means that Oracle will behave like that the password file doesn’t exist. Which will cause that no privileged connections will be allowed over nonsecure connections.
  • Exclusive: means that the password file will be used with the only one database instance. Setting this value gives the ability to grant/revoke sysdba or sysoper privileges to/from users(Note that granting or revoking privs. causes the password file modifications). It also enables you to change password of SYS user with ALTER USER command. It is the default value.
  • Shared: It is used by multiple DBs, which are running on the same server, or with RAC. Setting this value prohibits you from changing the password file. If you try to change the password file generates the error. To make available to modify this file, first change this parameter to exclusive, modify file and change it back to the share value.

To see how many users are added to the password file run the following command:

select *
from v$pwfile_users

–My output is the following

USERNAME |SYSDBA |SYSOPER
SYS      |TRUE   |TRUE

Deleting password file

To remove the password file, first delete it and then set the initialization parameter remote_login_passwordfile to none. After that, the users that can authenticate by the operating system will be able to connect  to the database as sysdba/sysoper.

Filed under DB Tagged with , , ,