Oracle Password file -> Complete reference for the new oracle dba




you are a new oracle dba and you would like to know about the oracle password file. In summary the oracle password file is used to allow some people to connect to the database with sysdba privileges. when someone wants to connect to the oracle database from a different client machine(remotely) then there should definetly be a oracle password file and the remote_login_passwordfile  parameter should be set to ‘exclusive’ in the init.ora file.

Below article contains more information and the details about how to find out the users in the passwordfile , how to grant privileges , how to revoke privileges etc.

detailed overview about the password file

and some discussion…

In a client/server environment, to connect using sysdba, you need the passwordfile.
and also remote_login_passwordfile=exclusive in the init.ora file.

In unix, you can do these things without the passwordfile, because your account (oracle), can be in the
“dba” group, it’s authenticated that way.

>>I created a password file with ENTRIES parameter is 3.
>>But then I can grant SYSDBA privilege to 5 different users. Why?

 The actual number of entries that can be entered may be somewhat higher, as the ORAPWD utility will continue to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it will hold 4 password entries and the number of password entries allocated will always be a multiple of 4.

>>After deleting password file, Oracle still recognized 5 users above as SYSDBA. Why?
think Oracle keeps this information in the Oracle dictionary. If you want to get rid of the old users then just run the following statement for every one of them:

REVOKE SYSDBA FROM scott;

Note: if the user has been granted the SYSOPER privilege then you have to revoke it also:

REVOKE SYSOPER FROM scott;

yes, if the number of users reached the maximum you have to recreate the pass file. In order to keep the information in it, just query the V$PWFILE_USERS view, BEFORE removing of the password file. In this way you can list all SYSDBA users, so after recreating of the file, you can add them again by:
GRANT SYSDBA TO user1;
GRANT SYSDBA TO user2;

no, this is not possible to have 2 password files. (for 1 instance).
the password file is linked directly with the ORACLE_SID, so there cannot be 2 password files.

oracle knows about the name, it is named orapw$ORACLE_SID
anything else is not possible to be used (ie is not considered)

You want to change the sys password in the password file?

Then simple  do a alter user sys identified by xxxx

Author: admin