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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Unable to export realm protected table using data pump
  2. New Background Processes In Oracle 11g
  3. Oracle 11G RMAN – Understanding UNDO backup optimisation
  4. How to convert NON-OMF to OMF files ( Oracle Managed File conversion – NON-ASM )
  5. Oracle 11gR2 (11.2.0.1) Unable To Create ASM Spfile Standalone
  6. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  7. How to Merge Multiple Partitions in Oracle 12c
  8. 12c ASM: PRCR-1001 : Resource ora.proxy_advm Does Not Exist (Flex ASM with Cardinality = ALL)
  9. RMAN: SET NEWNAME Command Using SQL
  10. Oracle Interview Questions : Grid Infrastructure Single Client Access Name (SCAN)

You May Also Like

Primary Sidebar

Recent Posts

  • What are different Oracle Database Vault Roles
  • Unable to export realm protected table using data pump
  • Beginners Guide to Oracle Database Vault
  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary