Search This Blog

Saturday, August 25, 2007

Password File Authentication

Preparing to Use Password File Authentication

To enable authentication of an administrative user using password file authentication you must do the following:

1. If not already created, create the password file using the ORAPWD utility:

ORAPWD FILE=filename PASSWORD=password ENTRIES=max_users

C:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1

C:\>ORAPWD file=%ORACLE_HOME%\database\pwdorcl.ora password=oracle entries=5

2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE. (This is the default).


REMOTE_LOGIN_PASSWORDFILE is a static initialization parameter and therefore cannot be changed without restarting the database.

3. Grant the SYSDBA or SYSOPER system privilege to the user:

SQL>grant SYSDBA,SYSOPER to scott;

Grant succeeded.

This statement adds the user to the password file, thereby enabling connection AS SYSDBA.

You can check in v$pwfile_users views for which user having SYSDBA,SYSOPER privs.

SQL> select * from v$pwfile_users;

------------------------------ ----- -----

Operating system authentication takes precedence over password file authentication. Specifically, if you are a member of the OSDBA or OSOPER group for the operating system,
and you connect as SYSDBA or SYSOPER, you will be connected with associated administrative privileges regardless of the username/password that you specify.
If you are not in the OSDBA or OSOPER groups, and you are not in the password file, then attempting to connect as SYSDBA or as SYSOPER fails.

No comments: