• 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 Drop Existing Temporary Tablespace and create new in Oracle 11g
  2. How to split BCV and open oracle ASM database
  3. How to do a Synchronous Refresh with Staging Logs in Oracle 12c
  4. Oracle sql script to report the list of files stored in ASM and CURRENTLY NOT OPENED
  5. How to Create or Remove Restore Point on Oracle Standby database
  6. How to Verify if Oracle Active Data Guard is Enabled
  7. Oracle SQL Script to Detect Tablespace Fragmentation
  8. PL/SQL Nested Blocks
  9. Steps to relink Oracle Forms 12c in Linux/UNIX
  10. ORA-65010: maximum number of pluggable databases created

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright