Lately I was responsible for implementing Kerberos authentication at the company where I work. During the planning I have discovered some differences and features which are not well documented regarding external authentication in Oracle. This blog post will discuss the issue with password expiration for externally authenticated users in Oracle 11g and 12c.

When we change a password authenticated user to external authentication in Oracle 12c then we can see that the EXPIRY_DATE column becomes NULL for that user:

SQL> create user TESTUSER identified by password$;

User created.

SQL> grant connect to TESTUSER;

Grant succeeded.

col username format a15
 select username, expiry_date
 from dba_users
 3 where username = 'TESTUSER';

USERNAME EXPIRY_DAT
 --------------- ----------
 TESTUSER 04-12-2016

SQL> alter user TESTUSER identified externally as 'TESTUSER@TESTDOMAIN.COM';

User altered.

col username format a15
 select username, expiry_date
 from dba_users
 3 where username = 'TESTUSER';

USERNAME EXPIRY_DAT
 --------------- ----------
 TESTUSER

As the password management is now done externally this is what we expect. Now let’s see the same in a 11g database:

SQL> create user TESTUSER identified by password$;

User created.

SQL> grant connect to TESTUSER;

Grant succeeded.

col username format a15
 select username, expiry_date
 from dba_users
 3 where username = 'TESTUSER';

USERNAME EXPIRY_DAT
 --------------- ----------
 TESTUSER 06-08-2016

SQL> alter user TESTUSER identified externally as 'TESTUSER@TESTDOMAIN.COM';

User altered.

col username format a15
 select username, expiry_date
 from dba_users
 3 where username = 'TESTUSER';

USERNAME EXPIRY_DAT
 --------------- ----------
 TESTUSER 06-08-2016

So in 11g although the user is externally authenticated EXPIRY_DATE is not NULL. This difference comes for the way EXPIRY_DATE is displayed in the internal view DBA_USERS.

DBA_USERS EXPIRY_DATE in 11g:

decode(u.astatus,
 1, u.exptime,
 2, u.exptime,
 5, u.exptime,
 6, u.exptime,
 9, u.exptime,
 10, u.exptime,
 decode(u.ptime, '', to_date(NULL),
 decode(pr.limit#, 2147483647, to_date(NULL),
 decode(pr.limit#, 0,
 decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
 dp.limit#/86400),
 u.ptime + pr.limit#/86400))))

DBA_USERS EXPIRY_DATE in 12c:

decode(mod(u.astatus, 16),
 1, u.exptime,
 2, u.exptime,
 5, u.exptime,
 6, u.exptime,
 9, u.exptime,
 10, u.exptime,
 decode(u.password, 'GLOBAL', to_date(NULL),
 'EXTERNAL', to_date(NULL),
 decode(u.ptime, '', to_date(NULL),
 decode(pr.limit#, 2147483647, to_date(NULL),
 decode(pr.limit#, 0,
 decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
 dp.limit#/86400),
 u.ptime + pr.limit#/86400)))))

So now we can see that 12c shows the EXPIRY_DATE as NULL if the user is globally or externally authenticated. Does this mean that the user account will be locked when it is expired in an Oracle 11g database? We certainly do not want this as when we are managing the passwords outside of the database then the expiration should also happen outside the database. To test this I have built the following test case a 11g Database:

I have changed the password expiry of the DEFAULT profile to 2 minutes as follows:

SQL> alter profile default limit PASSWORD_GRACE_TIME 1/1440;

Profile altered.

SQL> alter profile default limit PASSWORD_LIFE_TIME 1/1440;

Profile altered.

After creating the user and waiting for 2 minutes I logged in:

SQL> create user user_test identified by password$;

User created.

SQL> grant connect to user_test;

Grant succeeded.

col username format a15
 select username, to_char(expiry_date, 'dd/mm/YYYY HH24:MI')
 from dba_users
 3 where username = 'USER_TEST';

USERNAME TO_CHAR(EXPIRY_D
 --------------- ----------------
 USER_TEST 12/06/2016 09:17

$ sqlplus user_test@TESTDB11G

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 12 09:23:33 2016

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

Enter password:
 ERROR:
 ORA-28002: the password will expire within 0 days

So now we tested that the password expiry works for a password authenticated user as expected.  Now we change the user to external password authentication:

SQL> alter user user_test identified externally as 'USER_TEST@TESTDOMAIN.COM';

User altered.

col username format a15
 select username, to_char(expiry_date, 'dd/mm/YYYY HH24:MI')
 from dba_users
 3 where username = 'USER_TEST';

USERNAME TO_CHAR(EXPIRY_D
 --------------- ----------------
 USER_TEST 12/06/2016 09:38

Here we see again that EXPIRY_DATE is not NULL. Let’s try to login with the user:

$sqlplus /@TESTDB11G

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 12 09:42:24 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL>

No error message. This means that even though an EXPIRY_DATE is displayed for the externally authenticated users in 11g during the login process this information is ignored by Oracle. Based on the above we can conclude that we do not need to worry about password expiration for externally authenticated users inside Oracle 11g.