Password Policy

Our system at work already has a policy for password use. There is a database profile which is common to all database users. This profile has a PASSWORD_VERIFY_FUNCTION set to a stand-alone function owned by sys. Trouble is this function is not complete. It does not implement all of our customer's security requirements. I am a programmer. So the DBAs turned to me to fix this problem.

Some of the changes were trivial. Ensure the password has certain characteristics. I just mowed through these requirements by writing PL/SQL code in the password verify function. Then came a troubling requirement - the password had to have upper and lower case characters. You would think this was no trouble for an experienced PL/SQL programmer. But coding these requirements into the function caused a lot of things to break.

To better understand the problem I traced how our applications actually changed the user passwords. Turns out they just issue an ALTER USER IDENTIFIED BY . I sprinkled debug statements all throughout the password verify function, writing the output to a database table. And the output showed me exactly what was wrong. Turns out that when Oracle calls the password verify function while processing the ALTER USER command, it converts the password to upper case first! Damn.

I googled the web for information on this suspicious behavior. Could not really find much info on it. I already knew that Oracle passwords were not case sensitive. But who knew they would be doing an UPPER behind the scenes before my password verify function got called. Note that this did not happen when trying to change the password in SQL*Plus by executing the PASSWORD command. Go figure.

In the end I talked this over with our database manager. He looked at some password verify function samples from Oracle. None of them did upper/lower case enforcement. So we decided to skip implementation of this requirement. Normally I would protest, since us developer should be able to do anything. But I am on a tight deadline at work, and researching this problem has already taken too much time.

P.S. "Oracle PL/SQL Programming" written by Feurstein and published by O'Reilly is excellent . I have around 50 books on my bookcase at work. But I keep this book on a separate bookshelf reserved for the best.