Oracle Technical Track

Why locking Oracle accounts is a bad idea

2 min read

Dec 10, 2015 12:00:00 AM

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: [code lang="sql"] 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 [/code] 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: [code lang="sql"] connect scott/abc ERROR: ORA-28000: the account is locked Warning: You are no longer connected to ORACLE. [/code] 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: [code lang="sql"] 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 [/code] 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: [code lang="sql"] alter user scott account unlock identified by values 'impossible'; [/code] 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: [code lang="sql"] SQL> connect scott/abc ERROR: ORA-01017: invalid username/password; logon denied</code> [/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.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Why locking Oracle accounts is a bad idea

Share this

Share this

More resources

Getting started with the Oracle Database @ Google Cloud Service - Service Setup

Oracle Database 18c schema only accounts

Locks, Latches, Mutexes, and CPU Usage

Ready to unlock value from your data?