LATEST TOPICS

What’s New in Oracle Database 12c Password file?

Introduction

Oracle has introduced a number of new and important features with respect to a password file in Oracle Database 12c release.
The noted improvement is, we can now store a password file directly in to ASM Diskgroup.

Oracle has also deprecated the IGNORECASE option of a password file as starting with Oracle Database 12c, Oracle has made it default for a password to be case sensitive in the password file.

Today, I would be discussing few of the new features with respect to Oracle Database 12c password file.

Syntax for creating password file:

As we know, ORAPWD is the utility to a create password file. Lets take a look at the syntax of ORAPWD in Oracle Database 12c .

orapwd file=<fname> entries=<users> force=<y/n> asm=<y/n> password=<SYS password>
       dbuniquename=<dbname> format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>
       syskm=<y/n> delete=<y/n> input_file=<input-fname>

As we can see, there are a number of additional command line options for ORAPWD when compared to pre 12c database release.

Will discuss few of these additional options.

Storing password file in ASM diskgroup:

I will discuss storing of password file in ASM diskgroup for both standalone and RAC database as well as for ASM Instance.

Storing password file in ASM diskgroup for Standalone database:

Lets start with the amazing option of storing the password file in ASM Diskgroup.

I have a database ‘labdb‘. I would be creating a password file in ASM Diskgroup for this database

While creating password file in ASM diskgroup, we always need to mention the dbuniquename parameter of ORAPWD utility. It is the database unique name by means of which Oracle maps a password file to a specific database.

[oracle@labserver dbs]$ echo $ORACLE_SID
labdb

[oracle@labserver dbs]$ echo $ORACLE_HOME
/app/oracle/db/12.1.0.1

[oracle@labserver dbs]$ pwd
/app/oracle/db/12.1.0.1/dbs

--- Let me remove the existing password file from file system
---
[oracle@labserver dbs]$ ls -lrt orapwlabdb
-rw-r----- 1 oracle dba 7680 Oct 13 03:38 orapwlabdb

[oracle@labserver dbs]$ rm orapwlabdb
[oracle@labserver dbs]$

--- Now, lets create the password file to be stored in ASM Diskgroup
---
[oracle@labserver dbs]$ orapwd file='+DATA/labdb/orapwlabdb' entries=10 dbuniquename=labdb password=Oracle123#

Now, lets take a look at the created password file

ASMCMD> cd +DATA
ASMCMD> cd labdb
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/
---
--- We can see the password file is now stored in ASM
---												 
PASSWORD       HIGH    COARSE   OCT 19 23:00:00  N    orapwlabdb => +DATA/LABDB/PASSWORD/pwdlabdb.283.861404835
---
PARAMETERFILE  MIRROR  COARSE   OCT 19 22:00:00  N    spfilelabdb.ora => +DATA/LABDB/PARAMETERFILE/spfile.269.860816353

Now, lets check if we are able to connect using the password file.

[oracle@labserver admin]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 19 23:18:49 2014

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

Enter user-name: sys/Oracle123#@labdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

Though Oracle mentioned in its documentation to have specific naming conventions for a password file; It is not necessary to provide an absolute name for the password file while storing it in ASM diskgroup. For example, Lets create a password file for the database ‘prodcdb’

orapwd file='+DATA' entries=10 dbuniquename=prodcdb password=oraclE123#

In this case, Oracle will create the password file with OMF standards and it will be used for validating logins through password file.

[oracle@labserver dbs]$ asmcmd
ASMCMD> cd DATA
ASMCMD> cd prodcdb
ASMCMD> cd password
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
---
--- Here, password file is stored with OMS naming standards
---
PASSWORD  HIGH    COARSE   OCT 19 23:00:00  Y    pwdprodcdb.288.861406847
ASMCMD>

Lets validate, if password file logins work without an absolute password file name.

[oracle@labserver dbs]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 19 23:43:26 2014

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

Enter user-name: sys/oraclE123#@prodcdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>

How Oracle is able to use the password file without a absolute filename (as per the naming convention) is probably due to the dbuniquename parameter being attached to the password file while creating it through ORAPWD

Storing password file in ASM diskgroup for RAC:

Prior to 12c, we had to create and maintain individual password files for each of the RAC database instance. Adding a new user in the password file required either copy the password file from the instance where the user was being added to the remaining instances or to add the user in password file from each of RAC database instance.

However, starting with Oracle Database 12c; we can have a centralized single copy of password file in ASM diskgroup shared by all the RAC instances.

Lets create a centralized password file for a two node RAC database ‘proddb’ in ASM diskgroup.

---
--- Creating the password file for RAC database
--- in ASM diskgroup from Cluster node 1
---
[oracle@labcluster1 ~]$ orapwd file='+DATA/proddb/orapwproddb' entries=10 dbuniquename=proddb password=Oracle123#

Lets validate the password file from ASMCMD

ASMCMD> cd +DATA
ASMCMD> cd proddb
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
                                                 Y    CONTROLFILE/
                                                 Y    DATAFILE/
                                                 Y    ONLINELOG/
                                                 Y    PARAMETERFILE/
                                                 Y    PASSWORD/
                                                 Y    TEMPFILE/
---
--- We can see the password file is now stored in ASM
---												 
PASSWORD       HIGH    COARSE   OCT 20 00:00:00  N    orapwproddb => +DATA/PRODDB/PASSWORD/pwdproddb.294.862404783
---
PARAMETERFILE  MIRROR  COARSE   OCT 20 01:00:00  N    spfileproddb.ora => +DATA/PRODDB/PARAMETERFILE/spfile.262.860537154

Lets query the password file users from the database.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

As of now, we have just the SYS user in the password file.
Lets create a user and grant it SYSDBA role from first cluster instance.

SQL>  select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------
proddb1          labcluster1.home.com


SQL> create user abbas identified by abbas;

User created.

SQL> grant sysdba to abbas;

Grant succeeded.

Lets validate if we can see the user ‘ABBAS’ in password file from both the cluster instances.

SQL>  select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------
proddb1          labcluster1.home.com

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
ABBAS			               TRUE  FALSE FALSE FALSE FALSE FALSE	        0
	

SQL>  select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- --------------------
proddb2          labcluster2.home.com


SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
ABBAS			               TRUE  FALSE FALSE FALSE FALSE FALSE          0

As we can see, we need not copy the password file across RAC servers or grant the user privilege on each RAC instance to make the password file consistent across the cluster servers.

Storing password file in ASM diskgroup has a great advantage over the file system storage.

First of all, since the file is in ASM Diskgroup; it is secured against unintentional and unauthorized access and file corruption.
Another major advantage is for a Cluster (RAC) database, where we can have a centralized copy of the password file for all the RAC instances without the need of maintaining individual password file for each RAC instance. Again the dbuniquename parameter facilitates in creating a single centralized password file for all the RAC instances.

Storing password file in ASM diskgroup for ASM Instance:

Starting from Oracle Database 12c, we can also store password file for a ASM instance in ASM diskgroup. We will use the option asm=y of ORAPWD utility to indicate that the password file to be created is for ASM Instance.

---
--- We will use the asm=y option of ORAPWD to indicate it is an ASM Instance password file
---
[oracle@labserver dbs]$ orapwd file='+DATA' entries=10 password=Asm123# asm=y

Lets validate the password file creation in ASM diskgroup.

[oracle@labserver dbs]$ asmcmd
ASMCMD> cd DATA
ASMCMD> cd ASM
ASMCMD> cd PASSWORD
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
PASSWORD  HIGH    COARSE   OCT 20 03:00:00  Y    pwdasm.290.861421147
ASMCMD>

Lets validate, if we can connect to the ASM Instance using password file authentication.

[oracle@labserver dbs]$ tnsping asm

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-OCT-2014 03:48:35

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/app/oracle/grid/12.1.0.2/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = +ASM) (UR=A)))
OK (0 msec)

[oracle@labserver dbs]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 20 03:48:39 2014

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

Enter user-name: sys/Asm123#@asm as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0

SQL>

Passwords are now case sensitive by default:

Oracle has deprecated the IGNORECASE parameter of a password file in Oracle database 12c and the passwords are always case sensitive by default. However, ORAPWD still supports IGNORECASE parameter for background compatibility.

Lets check if we can bypass the case sensitivity while login through password file.

[oracle@labserver admin]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Sun Oct 19 23:18:49 2014

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

Enter user-name: sys/Oracle123#@labdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL>
----- As in 12c, password is case sensitive by default;
----- Trying to connect ignoring the CASE would result in denied login
-----
SQL> conn sys/oracle123#@labdb as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>

New SYS users in password file:

As we know, starting from Oracle database 12c; we have additional SYS users/roles to segregate Backup, Dataguard and TDE administration. Oracle Database 12c password file supports to have different password for each of these new SYS roles/users.

For example.

[oracle@labserver dbs]$  orapwd file='+DATA/labdb/orapwlabdb' entries=20 sysbackup=y sysdg=y syskm=y dbuniquename=labdb

Enter password for SYS:

Enter password for SYSBACKUP:

Enter password for SYSDG:

Enter password for SYSKM:

Lets analyze the password file users.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          0
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           0

So, we have three additional users in the password file which are mapped to the new SYS roles/users. We need to use the respective password to login as the new SYS users.

[oracle@labserver dbs]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 20 00:45:23 2014

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

----
---- Connect as the SYS user using password file authentication
----
Enter user-name: sys/Oracle123#@labdb as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

----
---- Connect as SYSBACKUP user using password file authentication
----
SQL>  conn SYSBACKUP/orabackup12@labdb as SYSBACKUP
Connected.
----
---- Conenct as SYSDG user using password file authentication
----
SQL> conn SYSDG/oradg123@labdb as SYSDG
Connected.
----
---- Connect as SYSKM user using password file authentication
----
SQL> conn SYSKM/orakm123@labdb as SYSKM
Connected.
SQL>

Conclusion:

Oracle Database 12c password file has a lot of improvements to offer with respect to database login authentication and security.

2 Comments
  1. venkatesh
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed