I have been asked this question a number of times over the years. Even someone emailed me to confirm the answer to this question this week and even though I emailed back I thought it would be a good subject for a blog post here.
If you specify password rules such as the key space to be used (the characters allowed to be used in the password) or the lifetime of the password in the database or the length or many more rules that you can conjure up then these rules can be applied to every user via profiles in the database so that the passwords can be enforced including the length. Oracle allows you to create as many profiles as you wish but there are only two layers in the hierarchy. There is user profiles and the DEFAULT profile. If a setting is not applied in the user profile then the default setting is used. Before we go further we can see what profiles exist in my 11.2.0.3 database:
SQL> @prof
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
PROFILE F T S L M G L V
================================================================================
PFCL_OTHER 5 U 2 1 U 2 60 PFCL_VF_OTHER
MONITORING_PROFILE U D D D D D D D
PFCL_SCHEMA 1 U 1 10 U 0 90 PFCL_VF_STDE
PFCL_ADMIN 3 U 3 .04 U 1 30 PFCL_VF_ADMIN
DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
PROFILE F T S L M G L V
PL/SQL procedure successfully completed.
SQL>
We can also see what profiles are assigned to each user:
SQL> @profiles
F = Failed Login Attempts
T = Password reuse time
S = Sessions per user
L = Password Lock Time
M = Pasword Reuse Max
G = Password Grace Time
L = Password Life Time
V = Password verify function name
USER Profile F T S L M G L V
================================================================================
SYSTEM DEFAULT 10 U U 1 U 7 180 NULL
SYS DEFAULT 10 U U 1 U 7 180 NULL
CCKEY DEFAULT 10 U U 1 U 7 180 NULL
PFTEST DEFAULT 10 U U 1 U 7 180 NULL
LOG4 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT1 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT3 DEFAULT 10 U U 1 U 7 180 NULL
ATKD DEFAULT 10 U U 1 U 7 180 NULL
ZULIA DEFAULT 10 U U 1 U 7 180 NULL
SHAREDDBA DEFAULT 10 U U 1 U 7 180 NULL
ATKR DEFAULT 10 U U 1 U 7 180 NULL
DBACLIENT1 DEFAULT 10 U U 1 U 7 180 NULL
SEED DEFAULT 10 U U 1 U 7 180 NULL
USER02 DEFAULT 10 U U 1 U 7 180 NULL
USER03 DEFAULT 10 U U 1 U 7 180 NULL
ORABLOGDBA DEFAULT 10 U U 1 U 7 180 NULL
HH DEFAULT 10 U U 1 U 7 180 NULL
CCADMIN DEFAULT 10 U U 1 U 7 180 NULL
BOF_SCHEMA DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG_SCHE DEFAULT 10 U U 1 U 7 180 NULL
DEV01 DEFAULT 10 U U 1 U 7 180 NULL
DEV02 DEFAULT 10 U U 1 U 7 180 NULL
BILL DEFAULT 10 U U 1 U 7 180 NULL
TKT_DEV DEFAULT 10 U U 1 U 7 180 NULL
ERIC DEFAULT 10 U U 1 U 7 180 NULL
VU DEFAULT 10 U U 1 U 7 180 NULL
FEED01 DEFAULT 10 U U 1 U 7 180 NULL
TKT DEFAULT 10 U U 1 U 7 180 NULL
PETE DEFAULT 10 U U 1 U 7 180 NULL
ILO DEFAULT 10 U U 1 U 7 180 NULL
TKT_TEST DEFAULT 10 U U 1 U 7 180 NULL
VA DEFAULT 10 U U 1 U 7 180 NULL
EMIL DEFAULT 10 U U 1 U 7 180 NULL
DBAUSER DEFAULT 10 U U 1 U 7 180 NULL
dev.xx();-- DEFAULT 10 U U 1 U 7 180 NULL
USER01 DEFAULT 10 U U 1 U 7 180 NULL
VB DEFAULT 10 U U 1 U 7 180 NULL
USER07 DEFAULT 10 U U 1 U 7 180 NULL
SFTK DEFAULT 10 U U 1 U 7 180 NULL
CORE DEFAULT 10 U U 1 U 7 180 NULL
USER04 DEFAULT 10 U U 1 U 7 180 NULL
USERGRANT2 DEFAULT 10 U U 1 U 7 180 NULL
BATCH01 DEFAULT 10 U U 1 U 7 180 NULL
JIM DEFAULT 10 U U 1 U 7 180 NULL
SQL92 DEFAULT 10 U U 1 U 7 180 NULL
DBACLIENT2 DEFAULT 10 U U 1 U 7 180 NULL
ATK DEFAULT 10 U U 1 U 7 180 NULL
RISK01 DEFAULT 10 U U 1 U 7 180 NULL
DEV03 DEFAULT 10 U U 1 U 7 180 NULL
ATKA DEFAULT 10 U U 1 U 7 180 NULL
USER05 DEFAULT 10 U U 1 U 7 180 NULL
UU DEFAULT 10 U U 1 U 7 180 NULL
USER06 DEFAULT 10 U U 1 U 7 180 NULL
DEV DEFAULT 10 U U 1 U 7 180 NULL
FRED DEFAULT 10 U U 1 U 7 180 NULL
JSON DEFAULT 10 U U 1 U 7 180 NULL
BACK01 DEFAULT 10 U U 1 U 7 180 NULL
FACADM DEFAULT 10 U U 1 U 7 180 NULL
ORABLOG DEFAULT 10 U U 1 U 7 180 NULL
OUTLN DEFAULT 10 U U 1 U 7 180 NULL
OLAPSYS DEFAULT 10 U U 1 U 7 180 NULL
OWBSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDPLUGINS DEFAULT 10 U U 1 U 7 180 NULL
XDB DEFAULT 10 U U 1 U 7 180 NULL
OWBSYS_AUDIT DEFAULT 10 U U 1 U 7 180 NULL
APPQOSSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDSYS DEFAULT 10 U U 1 U 7 180 NULL
SI_INFORMTN_ DEFAULT 10 U U 1 U 7 180 NULL
CTXSYS DEFAULT 10 U U 1 U 7 180 NULL
ORDDATA DEFAULT 10 U U 1 U 7 180 NULL
WMSYS DEFAULT 10 U U 1 U 7 180 NULL
MDSYS DEFAULT 10 U U 1 U 7 180 NULL
SPATIAL_WFS_ DEFAULT 10 U U 1 U 7 180 NULL
MDDATA DEFAULT 10 U U 1 U 7 180 NULL
ORACLE_OCM DEFAULT 10 U U 1 U 7 180 NULL
SPATIAL_CSW_ DEFAULT 10 U U 1 U 7 180 NULL
XS$NULL DEFAULT 10 U U 1 U 7 180 NULL
================================================================================
USER Profile F T S L M G L V
PL/SQL procedure successfully completed.
SQL>
So even though I have designed profiles in my database and even thought about the fact that the DEFAULT profile should take on the strongest settings of all other profiles as you can see no user has got a new profile in my database. This is something I see regularly, unfortunately.
So if we have specified good profiles and we have written good solid verify or complexity functions and added them to our profiles then we could be sure that end users have set strong and in this example passwords of the correct length - OR CAN WE?
Well no, as you can see above even though I have got profiles, they are not used. Also I see regularly two rather silly scenarios. The first is that profiles include a life time BUT no complexity function specifying a suitable password length. So whilst the password is forced to be changed on a time based basis there are no rules to make the password strong or long. The other scenario I see is a profile assigned to users with a complexity function specifying a suitable password length BUT there is no lifetime set so that complexity function never executes enforcing a suitable length of password. We end up with users with profiles but weak passwords still.
By the way, we can also have passwords of a suitable length - maybe 10 characters BUT they are still weak, i.e an easily guessed dictionary word. Password security is complex to manage and enforce.
So, back to the question. Can we test the length of a hashed password in the Oracle database - i.e. we do not know the clear text password. In other words could we be sure in a database that all passwords were at least 8 or 10 or 15 characters long? - The answer is no, or maybe in some cases BUT we can never prove this for all users unless we were lucky or we had NSA like hardware (i don't know what hardware they have BUT I guess that they can crack some passwords very fast or in real time).
We need to use a password cracker. The problem is that the longer the password and the bigger the keyspace used then it takes longer to crack passwords. If a password only uses characters A-Z then there are 26 characters then that would take less time to try all possible passwords for an 8 character password than for a password that includes A-Za-z0-9 - i.e. 62 characters. There are simply more options to try. If we try the complete keyspace, A-Za-z0-9 and all special characters then maybe we can verify that all passwords are at least 2 or 3 characters long with a normal PC and simple software written in C. BUT we cannot go much further and check for instance passwords of 8 characters and the complete key space on a normal PC; it simply takes too long and cannot be done.
What if we employed hardware? Dennis Yurichev created an FPGA cracker some years ago and exposed it to the public via his website - I wrote a blog post about it in 2009 but this tested ASCII passwords and it could crack 8 character passwords in 16.5 hours; this was fast at 65-85 Million hashes a second but its still no where near fast enough. A hardware cracker using an array of 25 GPU graphics cards uses HashCat and was shown on the arstechnica website in 2012. This is more impressive and can crack Windows NTLM passwords at 350 Billion hashes per second. This was 5 years ago so its likely that this speed has improved somewhat even for private teams but those three letter guys in the states with enormous budgets are likely to have a similar set up that can crack in real time.
So the problem gets bigger the more characters you need to try and also the bigger the length of password so for us its impossible to verify a password is 8 or 10 or 15 characters long from the hashed password. I did say that there is a "maybe" - if someone chose a weak password - a dictionary word or simple pattern then maybe we can crack it with a normal cracker using dictionaries or permutes SO we can show for those cracked passwords that they are maybe too short (actually they maybe longer than needed BUT still weak) and weak BUT we cannot verify length.
The only way we can verify an Oracle password is the correct length or more is to enforce with profiles and complexity functions and to enforce that all passwords are changed so that the verify function tests the length. Its the best we can do!!
Good Luck!!
