How to Limit a User Connection to a Specific IP Address


Seriously, I find all the best questions about Oracle security on forums like Oracle Communities and AskTom. Sometimes I need to be careful, though. I have a tendency to jump right to implementation details in my head without always considering all of the ramifications of the original question. Sometimes the answers seem immediately obvious, but it doesn’t usually take long before someone offers an observation that makes me sit back and think that maybe my first instinct needs some qualification. Case in point: a recent post on Oracle Communities asked this:

“For audit findings remediation where we need to limit the logical access of the TCS user account on the designated workstation only , Is there’s a way we can implement it in Oracle?”

When pressed for more detail by another respondent, the original poster then replied:

Access to production is being restricted/controlled via port access.

But that still will not restrict database user being able to access from all those workstation with allowed db port. Example is  we have DB_USER1 user in Oracle database, that user can login using all those workstation. What the auditors want is to limit the access of DB_USER1 from the specific workstation he/she is assigned with.

My brain immediately jumped to the technical: how would I actually implement something like this myself? It’s a scenario I’ve considered in the past – I’ve even built some custom logon triggers to collect user session details and catalog them as a whitelist for later comparison. I replied with a variety of options that the OP might want to consider, including logon triggers referencing SYS_CONTEXT; Oracle Connection Manager; and using wallets and SSL encryption. And then another poster made a really obvious (in hindsight) observation. Maybe it has already occurred to you, too:

And what happens when the DHCP-assigned ip address of that workstation (upon which ANY and ALL network-based rules would be based) changes?

Boom. What’s the point of locking something down if it’s only going to change in a way that you can’t control?

This of course leads to comments about how workstation names and other things also change on a regular basis. I think some people change laptops almost as often as they change clothes. There’s also the point where you have to recognize that a savvy Java programmer can spoof almost every SYS_CONTEXT session parameter (other than proxy username) anyway. Like most security, any kind of session whitelist is only a delaying tactic at best. Your goal is to slow an attacker down enough, make them take enough false steps and mistakes, that you can detect their activity with monitoring and identify them.

Like my previous posting about putting anti-virus software on a database server, this is another case of otherwise well-meaning security auditors having no idea what they’re actually asking for. My final post on that Oracle Community thread posed the same question for the OP to ask their auditors that I posed on the anti-virus thread:

What is the problem that you’re trying to solve?

Are you trying to ensure that the user is who they claim to be? Simply tying their account to a specific workstation won’t prove that. Implementing strong authentication through SSL (i.e. TCPS) connections using wallets with client certificates would be the strongest way for the user to prove who they are. This would be two-factor identification (the certificate is something you have, and the password or pin number is something that you know).

Are we trying to ensure that the user is only using approved (and secured) corporate assets to connect to the database? Aside from using Oracle Connection Manager or even just the server’s local firewall to protect the Oracle Listener ports and limit access to approved network subnets (like ones used by workstations and laptops), there are other ways to ensure that only approved computing assets are able to connect to a corporate network that have nothing to do with the database or its server administration.

Are we trying to ensure that these strongly authenticated, legitimate users, using approved and secured corporate computing assets, are only doing what they’re supposed to be doing in the database, when they’re supposed to be doing it? That’s where things like auditing, secure application roles, and database-level business logic like Database Vault and Virtual Private Database come in to the picture. If you really want to go crazy, you can even use tools like Splunk to monitor your audit trails and evaluate unusual outliers in user behavior.

If I already have all of these things in place, then does it really matter if the VP’s laptop goes all BSOD and he gets a loaner from IT for a week? Do I want my security locked down so tightly that his new and/or temporary laptop won’t let him use his desktop analytics reporting software to prepare for the big meeting with the new account until I get his user profile updated in the whitelist to allow his connection through? Did I mention that he’s on the road several time zones away trying to use VPN and it never occurred to him that this would be a problem until minutes before the meeting?

Now imagine the maintenance requirements around maintaining a whitelist. If you’ve only got a few users and they don’t change often then maybe it’s not so bad. If you’re supporting an entire enterprise with thousands of users that are constantly coming, going, and getting new equipment, keeping that whitelist up to date could quickly become a nightmare. All of a sudden, the DBA is involved in every new hardware allocation to database users, which is generally not where they belong. What about our unlucky VP? You’re on call in the wee hours of the morning several timezones away from him when he realizes his predicament, and the call now comes to you to fix it now!

Chances are that the auditor was not considering any of these possible ramifications to cost, complexity, or workload when they asked about locking users down to specific IP addresses. This is where the DBA, in conjunction with system and network administrators, needs to understand that a requirement for locking users down to individual IP addresses is a solution (a technically unfeasible one at that!) in search of a problem. Diplomatically work with the auditor to determine what their actual concern is – the real problem that needs to be solved from their perspective – and then work to find a solution that actually works for everyone. You may still need to add some complexity to your architecture, but hopefully it makes rational sense and can be managed and maintained without adding undue burden to you or your team.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.