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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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. How to Shrink the datafile of Undo Tablespace in Oracle Database
  2. Beginners Guide to RMAN compression for backups
  3. How to Set Multiple Events in INIT.ORA file
  4. Oracle Multitenant: How to Create CDB and PDB
  5. How to Move OCR, Vote Disk File, ASM SPILE to new Diskgroup
  6. How to Enable Fast-Start Failover using Enterprise Manager
  7. How to Enable or Disable Veritas ODM for Oracle database 12.1.0.1
  8. Oracle RMAN – Restore and Recovery of a Noarchivelog Database
  9. Database Crashed With ORA-19815, ORA-19809, ORA-16038
  10. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network

You May Also Like

Primary Sidebar

Recent Posts

  • qmrestore Command Examples in Linux
  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright