It is quite a shocking fact that all the databases I have ever worked on have had areas of security that would benefit from a few simple changes, but without fail there has always been a “decision maker” above me that has been too concerned about the unknown and too complacent about security to agree to some testing that would allow some simple best practices to be applied.
So when I was asked to implement some security profiles for the Oracle users on the databases I’m currently supporting I was keen to get started…
This was pretty simple stuff. We wanted password expiry, but didn’t see the need for grace times; password complexity rules and life time that matched the company standards. And, that was about it.
I created a password function, profile and user in a test system and satisfied myself that everything worked as I expected. There was one horror story regarding profiles from the other members of the team, but the people involved couldn’t remember the detail. The general gist of the problem was the profiles had been applied and all the accounts expired immediately. Which didn’t match what I’d observed, as described below.
When the user was initially created with default profile DBA_USERS.EXPIRY_DATE was null. When the profile was applied DBA_USERS.EXPIRY_DATE was showed a value X days in the future. I’m a little embarassed to say that that is where my testing finished. I had envisaged two possible scenarios: 1) the account would be expired immediately; 2) the account would be set to expiry X days in the future… I’ll put it down to time pressures, but it had never occurred to me that Oracle was keeping a track of when password were changed. After all it wasn’t a field in DBA_USERS. Doh!
Anyway, when implemented, which was communicated to the end users, there were a few calls from people stating that their password had expired and they couldn’t change it – the application they use relies on Oracle authentication, but does not allow password changes directly from the application. Not the best design.
After telling a few end-users how to change their password via SQL*Plus, all the time wondering what had gone wrong, I was able to get back to the next most pressing task.
I was intrigued. Why were accounts expiring that I thought wouldn’t? It didn’t take me long to find the answer and I kicked myself for not digging deeper sooner.
I’d assumed that the expiry_date was set rather than derived. The view definiton for DBA_USERS gives it all away…
select u.name, u.user#, u.password,
m.status,
decode(u.astatus, 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
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)))),
dts.name, tts.name, u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and u.astatus = m.status#
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
and pr.resource# = 1
SYS.USER$.PTIME was the field I hadn’t know about, but will not forget…
I feel happier now I understand where user expiry dates come from and the more I think about it the more it makes sense. After all it wouldn’t be good design if Oracle needed to update a table holding user data every time a profile was changed.
ORAganism 