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.
6 Comments. Leave new
Great info Stefan!
Note the slight changes in 12.1.0.2
https://connormcdonald.wordpress.com/2014/08/09/12-1-0-2-security-grrr/
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’ ;
Thanks Connor and Nenad for the added information!
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!
Hi,
What can I do if I have a locked workspace in apex.oracle.com and no one from oracle administrators want to help me to unlock it?