Why locking Oracle accounts is a bad idea

Time and time again I run into database accounts which are marked "LOCKED" or "EXPIRED & LOCKED". The main problem here lies with how Oracle handles a failed login attempt when the account is locked. In this blog I will discuss why locking Oracle accounts is a bad idea.

Let's consider the following scenario:

create user scott identified by tiger account lock; User created.  select username, account_status from dba_users where username='SCOTT';  USERNAME                       ACCOUNT_STATUS ------------------------------ -------------------------------- SCOTT                          LOCKED 

The Information Leak: Why "LOCKED" is a Security Risk

So what happens if I put on my black hat and try to get into this database? I may probe for some common users, and just happen to come across this:

connect scott/abc ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. 

What Oracle does there is give me a very valuable piece of information: it tells me that this user exists in the database. Why is that important? Let's see what we can find out—without even being able to connect—based solely on the account status of some common accounts:

Identifying Installed Components through Account Probing

USERNAME                       ACCOUNT_STATUS ------------------------------ -------------------------------- ANONYMOUS                      EXPIRED & LOCKED APEX_030200                    LOCKED APEX_PUBLIC_USER               LOCKED CTXSYS                         EXPIRED & LOCKED DIP                            EXPIRED & LOCKED EXFSYS                         EXPIRED & LOCKED FLOWS_FILES                    LOCKED OLAPSYS                        EXPIRED & LOCKED ORACLE_OCM                     EXPIRED & LOCKED OUTLN                          EXPIRED & LOCKED SQLTXADMIN                     EXPIRED & LOCKED WMSYS                          EXPIRED & LOCKED XDB                            EXPIRED & LOCKED XS$NULL                        EXPIRED & LOCKED 

Simply by trying to connect to some of these, and Oracle telling me that the account is locked, I now know that the database has all of the following installed:

• APEX
• OLAP
• Oracle Text
• XML Database

That's a lot of information I was just given for free. Depending on the components I'd find, I could also deduce that the Oracle JVM is installed in the database. And this frequently hits the news with newly discovered vulnerabilities.

In essence, this means that by locking your accounts, you leave the door open way wider than you're thinking. It's a totally counter-productive way of doing things.

The Secure Alternative: The "Impossible" Hash Approach

So what's better? The best approach is a very simple one. Putting my white hat back on, I just assign the user an impossible password hash, like so:

Masking User Existence

alter user scott account unlock identified by values 'impossible'; 

It's not possible for this user to ever log in while this hash is in place. And if we try, all we get is:

SQL> connect scott/abc ERROR: ORA-01017: invalid username/password; logon denied 

Warning: You are no longer connected to ORACLE.

Preventing Account Relapse

The second thing you'd want to do is ensure that those users' passwords never expire. Or you'd end up with the same EXPIRED & LOCKED status again.

Happy unlocking, and stay secure! :)

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?