1. The default location for the password file is:
$ORACLE_HOME/dbs/orapw$ORACLE_SID ---------------- Unix
%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora ------- Windows
2. Database parameter which play major role in password file managmenet is
REMOTE_LOGIN_PASSWORDFILE.
REMOTE_LOGIN_PASSWORDFILE= NONE/EXCLUSIVE/SHARED
NONE - Oracle ignores the password file if it exists i.e. no privileged connections are allowed over non secure connections. If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.
EXCLUSIVE - Password file is exclusively used by only one (instance of the) database. Any user can be added to the password file. Only an EXCLUSIVE file can be modified. EXCLUSIVE password file enables you to add, modify, and delete users. It also enables you to change the SYS password with the ALTER USER command.
SHARED - The password file is shared among databases. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of an Oracle Real Application Clusters (RAC) database. However, the only user that can be added/authenticated is SYS.
3. orapwd:
You can create a password file using orapwd utility.
Users are added to the password file when they are granted the SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privilege.
The Oracle orapwd utility assists the DBA while granting SYSDBA, SYSOPER, SYSASM, SYSKM, SYSDG or SYSBACKUP privileges to other users. By default, SYS is the only user that has SYSDBA and SYSOPER privileges.
Creating a password file, via orapwd, enables remote users to connect with administrative privileges.
Oracle 11g:
$ orapwd file=password_file_name [password=the_password] [entries=n] [force=Y|N] [ignorecase=Y|N] [nosysdba=Y|N]
Oracle 12c release 2:
orapwd FILE = filename
[FORCE = {y|n}]
[ASM = {y|n}]
[DBUNIQUENAME = dbname]
[FORMAT = {12.2|12}]
[SYS = {y|n|password|external('sys-external-name')|global('sys-directory-DN')}]
[SYSBACKUP = {y|n|password|external('sysbackup-external-name')|global('sysbackup-directory-DN')}]
[SYSDG = {y|n|password|external('sysdg-external-name')|global('sysdg-directory-DN')}]
[SYSKM = {y|n|password|external('syskm-external-name')|global('syskm-directory-DN')}]
[DELETE = {y|n}]
[INPUT_FILE = input-fname]
4. Examples
$ orapwd file=orapwSID password=sys_password force=y nosysdba=y
$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret
5. login to database and verify the pwfile_users before creating password file:
pwfile_users is the dynamic view used at database to verify the admin previlaged users.
SQL> select * from v$pwfile_users;
no rows selected
SQL>
6. Create password file
$ orapwd file=orapwDEVDB entries=2 force=y
--- Maximum entries for the admin userd to login to database remotely.
[oracle@rac1 dbs]$ orapwd file=orapwDEVDB entries=2 force=y
Enter password for SYS:
[oracle@rac1 dbs]$ ll orapwDEVDB
-rw-r----- 1 oracle oinstall 1.5K May 29 17:33 orapwDEVDB
[oracle@rac1 dbs]$
7. Verify the pwfile_users file at database, dafult SYS user is added as soon as you creat a password file.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
8. Grant sysdba to 3 or 4 users and verify pwfile_users file at database
SQL> grant sysdba to user1;
Grant succeeded.
SQL> grant sysdba to user2;
Grant succeeded.
SQL> grant sysdba to user3;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE
USER1 TRUE FALSE FALSE
USER2 TRUE FALSE FALSE
USER3 TRUE FALSE FALSE
SQL> exit
9. Connect to remote machine and login to database as sysdba with user1/user2/user3
[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:37:33 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$ sqlplus user2/user2@DEVDB as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:37:52 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
10. Remove the password file at database server and try to login to database from remote maching as sysdba you will get error message.
[oracle@rac1 dbs]$ mv orapwDEVDB orapwDEVDB_backup
[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:38:28 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name: ^C
[oracle@rac2 ~]$
11. Try to login to database without sysdba you will be able to login since you are login as normal user not sysdba administrator user
[oracle@rac2 ~]$ sqlplus user1/user1@DEVDB
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 29 17:38:57 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
SQL>
12. Verify the what all users are available in password file using OS strings command
Restore password file back.
[oracle@rac1 dbs]$ mv orapwDEVDB_backup orapwDEVDB
[oracle@rac1 dbs]$ strings orapwDEVDB
]\[Z
ORACLE Remote Password file
INTERNAL
71B9E71760673BBA
3EF87B7945B97475
USER1
BBE7786A584F9103
USER2
1718E5DBB8F89784
USER3
94152F9F5B35B103
[oracle@rac1 dbs]$
Conclusion:
1. Password file will be used for remote database login as admin user for administration activities
2. Entries what you mention while creating the password file is the maximum number of admin users are allowed to login remotely as the same time.
3. You can grant the sysdba privilege to N-number of users irrespective of maximum entries mentioned in password file.
Please refer the YoutTube video for more information
https://youtu.be/hdhoL2j2ePQ
Regards,
Mallik
is entries option still available in 19c?
ReplyDelete