• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

HowTos | Basics | Concepts

  • Solaris
    • Solaris 11
    • SVM
    • ZFS
    • Zones
    • LDOMs
    • Hardware
  • Linux
    • CentOS/RHEL 7
    • RHCSA notes
    • SuSE Linux Enterprise
    • Linux Services
  • VCS
    • VxVM
  • Interview Questions
  • oracle
    • ASM
    • mysql
    • RAC
    • oracle 12c
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Hadoop
    • Hortonworks HDP
      • HDPCA
    • Cloudera
      • CCA 131

Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)

By admin

In a Unix environment you can use the following command to create a password file and to add user SYS and it’s password to the file:

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=secret entries=20

On Windows you can use the same command. The only differences are:

  • The password file should be created in %ORACLE_HOME%\database.
  • The password file’s name should be pwd%oracle_sid%.ora.

As soon as the password file is created you can add privileged users by granting the roles SYSDBA or SYSOPER to those users:

SQL> create user test identified by test; 
SQL> grant sysdba to test;
SQL> create user test2 identified by test2;
SQL> grant sysoper to test2;
Note: When this user is added, the user’s password from the data dictionary is added to the password file.

Verify

1. Check if user is added to the password file:

SQL> select * from V$pwfile_users;

USERNAME                       SYSDB      SYSOP
------------------------------ -------    --------
INTERNAL                       TRUE       TRUE
SYS                            TRUE       TRUE
test                           TRUE       FALSE
test2                          FALSE      TRUE

2. Check if the user can connect as sysdba:

SQL> connect test/secret@ as sysdba
ORA-01031: insufficient privileges
SQL> connect test/test as sysdba
Connected.
Note: This must be tested with an os user that is not a member of the dba group to make sure that OS authentication will not be used. Also, you can connect through the listener.

Conclusion

When OS authentication is enabled (the os user is a member of the dba group) the following commands executed on the RDBMS Server host machine will work:

SQL> connect / as sysdba
Connected.
SQL> connect who_am_i/does_not_matter as sysdba
Connected.

This is because OS authentication takes precedence over passwordfile authentication.

How to Create the Oracle Password File using orapwd Command

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle Interview Questions – Flash Recovery Area
  2. How to get the Values Assigned by Default to a Profile in Oracle Database
  3. Common Init.ora Parameters and Unix, Linux Kernel Parameters and Relationship Between Them
  4. Oracle Interview Questions : Using srvctl V/s sqlplus and pfile V/s spfile in RAC
  5. How To Find When The Spfile Was Created On Linux Server
  6. Understanding Flashback Table Feature in Oracle Database
  7. Unable to create spfile for Oracle ASM instance
  8. Managing Oracle Database Backup with RMAN (Examples included)
  9. DNS and DHCP Setup Example for Oracle Grid Infrastructure GNS
  10. Oracle Database : Understanding Dead Connection Detection, Resource Limits, V$SESSION, V$PROCESS and OS processes

You May Also Like

Primary Sidebar

Recent Posts

  • How to change the default IP address of docker bridge
  • “su: Authentication failure” – in Docker
  • How to Pause and Resume Docker Containers
  • How to find docker storage device and its size (device mapper storage driver)
  • Understanding “docker stats” Command Output
  • Archives
  • Contact Us
  • Copyright

© 2019 · The Geek Diary