Why locking Oracle accounts is a bad idea

Posted in: Oracle, Technical Track

 

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

 

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:

 

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.

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:

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</code>

 

Warning: You are no longer connected to ORACLE.

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! :)

 

Discover more about our expertise in the world of Oracle.

email

Interested in working with Stefan? Schedule a tech call.

About the Author

Stefan is a passionate Oracle database researcher and has been focusing on understanding the Oracle database at its core for more than 10 years. His primary focus lies in the performance of the database, its security aspects, and using its various features to solve problems and architect efficient database designs. Stefan is also an avid technical writer and has written large documentation sets from scratch for massive Oracle projects.

5 Comments. Leave new

Great info Stefan!

Reply

Stefan,

Good point.

However, it throws an error in Oracle 12c:

SQL> alter user system account unlock identified by values ‘impossible’;
alter user system account unlock identified by values ‘impossible’
*
ERROR at line 1:
ORA-02153: invalid VALUES password string

The following values string should be used instead:

alter user system identified by values ‘S:000000000000000000000000000000000000000000000000000000000000;0000000000000000’ ;

Reply

Thanks Connor and Nenad for the added information!

Reply

Great tip! Keep on using the old technique of locking accounts that we used prior to the lock command being introduced with Oracle8.

Further, keep in mind that if a bad actor attempts a connection to an account such as GSMADMIN_INTERNAL and gets an ORA-1017 error they can further determine that the database is 12c.

Finally, one more good reason to implement this solution: it effectively locks the account while still allowing for proxy authenticated connections to it where as “properly locked” accounts do not!

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *