How to Create Case Sensitive Password File in Oracle 11g?

In Oracle 11g DBAs can create the case sensitive passwords as well as case sensitive OS authentication which means you can create password file with case sensitive option. So once you upgrade your database to 11g you can implement case sensitive password files for better security.

$ orapwd  file=orapwprod9 entries=5 password=Welcome ignorecase=n

$ sqlplus sys/Welcome@prod9 as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jul 27 00:13:04 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ sqlplus sys/welcome@prod9 as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jul 27 00:13:22 2011

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: sys/Welcome@prod9 as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ orapwd help=y
Usage: orapwd file=<fname> password=<password> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  where
    file - name of password file (required),
    password - password for SYS, will be prompted if not specified at command line,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
How to deal Database links with enabled case sensitivity?
You have to be careful about the database links when you use the case sensitivity in Oracle 11g database.

DB links connecting from the database pre 11g to 11g database:
          In this situation mostly you will be getting the error "ORA-01017: invalid username/password; logon denied" because the password is stored in uppercase by default. You will have to recreate the database link with case sensitive password.

create database link scott connect to scott identified by "tiger" using 'prod2';
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google