How do I…Use the password file to authenticate database administrators?
Problem
I created a database using the instructions in How-To 1.1. I want some other users to use Server Manager or Enterprise Manager with database administrator privileges. How do I create and maintain a password file?
Technique
Examine the value of initialization parameter REMOTE_LOGIN_ PASSWORDFILE and modify it, if necessary, so that it has the value SHARED. Query the V$PWFILE_USERS dynamic performance view to ascertain the current configuration of the password file. Grant the SYSOPER and SYSDBA privileges to the appropriate users and connect to the database using the AS SYSOPER or AS SYSDBA clause of the connect command.
Steps
1. Use the Server Manager show parameter command to determine the value of the initialization parameter REMOTE_LOGIN_PASSWORDFILE. In this case, the value is SHARED. The possible values and their meanings follow. The results of the show parameter command appear in Figure 1.5.
NONE: The Oracle8 server ignores the password file; the operating system is responsible for database administrator authentication. SHARED: The Oracle8 server uses the password file but only the users SYS and INTERNAL can appear within the password file as users with administration privileges. More than one database can use this password file.
EXCLUSIVE: The Oracle8 server uses the password file, and you can add users other than SYS and INTERNAL to the file. These users can connect as themselves to Server Manager and Enterprise Manager and still have administrator privileges. Only one database can use this password file.
2. Use any editor to change the value of REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE.
3. Shut down the database and restart it. Repeat Step 1 to verify that the value of REMOTE_LOGIN_PASSWORDFILE is now EXCLUSIVE.
4. Query the view V$PWFILE_USERS to determine which users currently appear in the password file. In Figure 1.6, this view indicates that only SYS and INTERNAL can currently connect with administrator privileges.
5. Use the grant command in Server Manager to grant SYSOPER or SYSDBA privileges to users that need to connect to the database as administrators. Table 1.1 describes the SYSOPER and SYSDBA privileges; Figure 1.7 illustrates grant statements that will assign these privileges to the WAITE user account.
Table 1.1 Operations enabled via the SYSOPER and SYSDBA privilege
6. In Server Manager, connect to Oracle8 as WAITE and use the as sysoper clause. Figure 1.8 shows the syntax of the connect command, illustrates how the SYSOPER privilege does not grant access to the Oracle data dictionary, and finally demonstrates that the WAITE user can now shut down the database.
Privilege Operation SYSOPER STARTUP, SHUTDOWN, ALTER DATABASE OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, RECOVER, RESTRICTED SESSION SYSDBA All system privileges with accompanying ADMIN option, all privileges from the SYSOPER role, point-in-time- based recovery, CREATE DATABASE Comments
If the Oracle Instance Manager created the database under Microsoft Windows NT, it automatically created a password file in the default location \orant\ database with default name PWDSID.ORA. If you neglect to alter the value of initialization parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE, you will receive an ORA-01994 error when you try to grant SYSOPER or SYSDBA to a user account.