To connect to Oracle Database as a privileged user over a nonsecure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database user names that have been granted the SYSDBA or SYSOPER system privilege
The syntax of the ORAPWD command is as follows:
ORAPWD FILE=abc.txt ENTRIES=50 FORCE=y IGNORECASE=y password=pass
Command arguments are summarized in the following table
Name to assign to the password file. You must supply a complete path. If you supply only a file name, the file is written to the current directory.
(Optional) Maximum number of entries (user accounts) to permit in the file.
(Optional) If y, permits overwriting an existing password file.
(Optional) If y, passwords are treated as case-insensitive.(11g)
Sharing and Disabling the Password File
You use the initialization parameter REMOTE_LOGIN_PASSWORDFILE to control whether a password file is shared among multiple Oracle Database instances. You can also use this parameter to disable password file authentication. The values recognized for REMOTE_LOGIN_PASSWORDFILE 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 USERcommand.
ï 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 (Oracle RAC) database. A SHARED password file cannot be modified. Therefore, 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 orSYSOPER 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 an Oracle RAC database.
Granting and Revoking SYSDBA and SYSOPER Privileges
If your server is using an EXCLUSIVE password file, use the GRANT statement to grant the SYSDBA or SYSOPER system privilege to a user, as shown in the following example:
GRANT SYSDBA TO scott;
Find all users who have been granted the SYSDBA privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != ‘SYS’ AND SYSDBA=’TRUE’;
Use the REVOKE statement to revoke the SYSDBA or SYSOPER system privilege from a user, as shown in the following example:
REVOKE SYSDBA FROM scott;
Because SYSDBA and SYSOPER are the most powerful database privileges, the WITH ADMIN OPTION is not used in the GRANT statement. That is, the grantee cannot in turn grant the SYSDBA or SYSOPER privilege to another user. Only a user currently connected as SYSDBA can grant or revoke another user’s SYSDBA orSYSOPER system privileges. These privileges cannot be granted to roles, because roles are available only after database startup. Do not confuse the SYSDBAand SYSOPER database privileges with operating system roles
Keeping Administrator Passwords Synchronized with the Data Dictionary
If you change the REMOTE_LOGIN_PASSWORDFILE initialization parameter from NONE to EXCLUSIVE or SHARED, or if you re-create the password file with a different SYS password, then you must ensure that the passwords in the data dictionary and password file for the SYS user are the same.
To synchronize the SYS passwords, use the ALTER USER statement to change the SYS password. The ALTER USER statement updates and synchronizes both the dictionary and password file passwords.
To synchronize the passwords for non-SYS users who log in using the SYSDBA or SYSOPER privilege, you must revoke and then regrant the privilege to the user, as follows:
— Find all users who have been granted the SYSDBA privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSDBA='TRUE';
— Revoke and then re-grant the SYSDBA privilege to these users.
REVOKE SYSDBA FROM non-SYS-user; GRANT SYSDBA TO non-SYS-user;
— Find all users who have been granted the SYSOPER privilege.
SELECT USERNAME FROM V$PWFILE_USERS WHERE USERNAME != 'SYS' AND SYSOPER='TRUE';
— Revoke and regrant the SYSOPER privilege to these users.
REVOKE SYSOPER FROM non-SYS-user;