Recently, Deb got tickets for us to go to an outdoor cinema.
“It’ll be lovely”, she said, “we can have a picnic on a warm summer’s evening whilst watching a film”.
“Sounds good”, I said, trying hard to overlook the fact that, in England, a summer’s evening is as likely to be wet as it is to be warm.
Fortunately the weather held, the picnic was delicious and the company, needless to say, was divine.
As for the film…”I can’t believe that you’ve never seen Mama Mia before”, exclaimed my better half.
Some intensive negotiations followed on the subject of Brownie Points. As a result, my late Saturday evenings for the next three months will definitely include watching Match of the Day. Yes, I will get to watch A Man After Midnight.
Anyway, I recently wrote about how to disable password complexity in the database.
To redress the balance, this post is about how to enforce password complexity and how to encourage the use of strong(er) passwords.
At this point, you may be a bit concerned about the number of Abba references that may be contained in the rest of this post.
If you are, then all I can say is come on, Take A Chance on Me…
Entropy
A common way of measuring password strength is by means of Entropy.
Essentially, this boils down to the fact that a password’s strength is determined by :
the size of the characterset that the password can be made up from
the length of the password
The main issue here is that passwords, generally speaking, are chosen by people. Naturally, they will tend to stick to letters rather than utilising other characters.
In order to ensure that passwords are strong enough not to be easily guessed, we need to enforce policies to ensure
a wider range of characters than simply lowercase letters are used
the password is of a minimum length
the password is changed regularly
the password cannot be re-used for a period of time
The mechanism that Oracle provides for doing this is the PROFILE.
The Default Profile
Profiles serve two purposes. They can determine resource limits for a group of users. More pertinent here, is that they also determine various aspects of security for a group of users.
By default all users are assigned to a single profile called, fittingly enough, DEFAULT.
The security settings for this profile can be found like this…
The resulting list of resource_names probably requires some explaining at this point…
Resource Name
Description
FAILED_LOGIN_ATTEMPTS
Number of consecutive failed login attempts before an account is locked
PASSWORD_LOCK_TIME
Amount of time (days) that an account will be locked for after the failed login attempts threshold is reached
PASSWORD_LIFE_TIME
Maximum number of days allowed between password changes
PASSWORD_GRACE_TIME
Number of days grace after PASSWORD_LIFE_TIME is reached that a user will have to change their password
PASSWORD_REUSE_TIME
Number of days before a password can be reused
PASSWORD_REUSE_MAX
Number of times a password must be changed before it can be reused
PASSWORD_VERIFY_FUNCTION
a function to enforce complexity rules
Creating a new Profile
Say we have a member of the support – chiquitita for the sake of argument.
To determine which profile this user has been allocated to you can run the following query :
Because this account is more highly privileged than a normal user account, we want to enforce more rigourous password controls (e.g. change every 30 days, rather than every 60 days, fewer failed login attempts etc).
To enforce this, we can first create a new profile :
Now we can simply add Chiquitita to the profile :
If we now have a look at what settings are applicable for this user…
In terms of enforcing password complexity, it is the PASSWORD_VERIFY_FUNCTION that’s of interest to us.
The Password Verification Function
The password verify function must exist in the SYS schema.
It will accept three parameters – username, old_password, and new_password.
It is assigned to the profile by means of the command :
…where profile_name is the name of the profile we’re assigning the function to and function_name is the name of the verify function that we want to use.
Oracle provides the code for such a function, ready for to use. This can be found in :
The 11g function performs checks to ensure that the password
is at least 8 characters in LENGTH
is not username or username reversed
is not the servername (or servername appended by a number between 1 and 100)
is not in a small dictionary
not oracle (or oracle1-100)
password contains at least one digit and at least one letter
differs from the old password by at least 3 characters
We could just implement this script, but writing our own is more fun…and also gives us the chance to implement further policies.
A Customised Password Verification Function
The policies I want to implement are :
Password is at least 8 characters long
Password is not the same or similar to the username
Password is not the same or similar to the previous password
Password is not the same or similar to a word in the (equally small, but slightly modified) dictionary
Password differs to any of the above by at least 3 characters
password contains at least one digit and at least one letter
As a slight modification, I also want to account for any common replacements of characters with numbers.
So, with this function at least, a user won’t be able to set their password to be “P455w0rd”.
In terms of the design approach, I’ve followed Oracle’s lead here and ensured that everything is encapsulated in a single
function.
This means that there are some private helper functions declared and the dictionary itself is hard-coded in the function.
Here we go then. As SYSDBA…
Now, before unleashing this on the unsuspecting user community, it’s probably a good idea to run a few tests…
When we run this we get…
I’ll be the first to admit that there are probably going to be one or two values that aren’t caught by this function. In my defence,I’ve had to code this whilst trying to block out the memory of Piers Brosnan wading through the Abba back catalogue like a very angry man with a bit of a sore throat.
Anyway, once we’re happy with the function, we can assign it to the profile :
Protecting the Password Verify Function
One thing you may have noticed is that the password verify function, by it’s very nature, has access to passwords in clear text.
This makes it a potential target for attackers looking to save time mucking about with rainbow tables and the like.
There would appear to be two possible courses of action for an attacker.
First, they could modify the function.
Secondly, they could change the profile to simply use another function.
It’s probably worth pointing out that, as far as I can see, the password verify function must be owned by SYS and therefore created with an account with SYSDBA privileges.
Creating a function in another schema and then setting up a synonym for it does not work.
Neither does attempting to create a function in SYS as a user with CREATE/DROP/ALTER ANY PROCEDURE privileges.
Anyway, lets assume that our attacker has managed to gain the SYSDBA privilege. He or she can now do the following :
…and now the function…
…which they then assign to the profile :
Now, when a user changes their password…
…. the attacker can now check the table…
NOTE – this will work for any password change, including creation of a new user using CREATE or ALTER USER statements.
Of course, the attacker has a number of options, of which dumping the new credentials to a table is only the simplest.
They could e-mail their ill-gotten gains (using UTL_SMTP) or event send them to a web address (using UTL_HTTP).
In terms of defending against this, things get a little tricky. The attacker will have SYSDBA privileges so can disable any DDL triggers we put on the alter profile statement or on the function itself.
Bear in mind that this kind of attack will take place over time, as not everyone will be changing their password every day.
We will need to check regularly that both the function and the profile(s) have not been tampered with.
To this end, we may want to run a check on a daily basis to ensure that all is well.
There’s a bit of a conundurum here. An attacker with SYSDBA privileges will have the ability to disable ddl triggers and stop scheduler jobs.
At this point, it’s probably worth considering the advice of a security expert in the shape of this paper by Alexander Kornbrust.
The safest way to check that nothing untoward is happening would appear be to manually run check scripts.
First, we’ll need to make sure that users are assigned to profiles we expect them to be, and that each profile is assigned the expected PASSWORD_VERIFY_FUNCTION :
Run this and we could become a bit curious about exactly what your Mother does know…
Assuming we don’t get any nasty surprises with the first query, we should then make sure that the function itself has not been changed :
If there has been any recent DDL activity on this function, once again, you’ll probably want to investigate further.
To summarise then, the password verify function does offer a very granular control over your password policy.
Whilst protecting it is something of a challenge, it’s a challenge you have even if you don’t bother with one.
After all, you still need to make sure that this particular back-door has not been left ajar.
In a desparate attempt to find an antidote to all these ABBA songs floating around my head, I’m off to watch some footie.
DISCLAIMER : No Dad-dancing was perpetrated in the writing of this post.
Filed under: Oracle, SQL Tagged: dba_profiles, default profile, defending the password_verify_function, failed_login_attempts, password_grace_time, password_life_time, password_reuse_max, password_reuse_time, password_verify_function, sql reverse function 