How to Allow Oracle User to connect from one IP address only

User_VPAEP

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?

We're using Oracle 11g.

EdStevens

1518698 wrote: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?We're using Oracle 11g.

limitations like this are are better handled with firewall rules, restricting communiction on the listener port (1521, by default) to selected IP adddresses.

pmdba

If you really are trying to limit login of a specific user to a specific IP address, then you're going to have issues. There are a couple of ways that you could attempt it using logon triggers and such, but IP addresses can be spoofed or masked using a VPN or SSH tunnel, so there's no guarantee that a malicious user wouldn't find a way around it. Is the purpose here to limit an application account to being used only from the proper application server? If so, I would attempt the following:

1. Set up an OracleNet service name just for the application server (not other clients) to use.
2. Create a new database account who's only purpose is to log in to your original TCS account as a proxy user. Almost any OracleNet session property can be spoofed by a good hacker - except for proxy username.
3. Use the "alter user [username] identified by value '[hash]'" command to set your original TCS account password has to something unusable: anything in lower case or with special characters, like 'no_access_for_you'. This will effectively prevent direct logins to the account, without actually locking it (which would also lock out the proxy user). This is only for 11g; in 12c there's a user property that makes accounts accessible by proxy only that you would use instead to accomplish the same thing.
4. Create a login trigger that looks at SYS_CONTEXT and gets the username, proxy username, network protocol (TCPS), and service name for the inbound connection. Make sure that all four are good, otherwise reject the login.
   
   pseudo-code: if proxy_user is not "TCS_PROXY" then ok; if proxy_user is "TCS_PROXY" then confirm service_name and network_protocol before ok
   
   
5. Configure your listener to accept TCPS connections, and your app server/client to make them (may require use of the JDBC-Thick driver if using JDBC).
6. Only distribute the wallet with the encryption keys to the app server (build it there with the -auto_login_local option to prevent it being used on another host).
7. Configure the application server to use the new service_name, proxy user account and password, connecting through TCPS. Your connection username would then look like this: "proxy_username[original_username]"
8. Don't advertise the use of the new service_name - keep it reserved just for the app server.

That way you have an encryption key that can't be spoofed or used from elsewhere (defeats IP spoofing), and a username/session property that can't be spoofed using Java.

User_VPAEP

Thanks EdStevens .

Agree, that's the current setup implemented in our company. 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.

User_VPAEP

Thank you pmdba.

Yeah, we've been trying logon triggers but not working. It's really much of user access control/security not application. Will keep point you provided for future reference. Thank you.

pmdba

Take a look at this. It might need a little work, but it should give you some ideas on how to get started. I would recommend only running the trigger against connecting users who must be whitelisted, as it can impose some overhead/delay to the login process if there are too many profiles in the whitelist. Hopefully you wouldn't need to do the full profile collection as described in this project. If you're not dealing with too many users and they won't change often then I'd try to hard-code everything in the trigger. If you're talking about more users, or users that change frequently, then a lookup table with minimal fields (just the ones mentioned above that would really matter) might be the way to go - then pin the table into your db buffer cache to keep i/o to an absolute minimum.

pmdba

Another possibility, if you want to limit users (plural) to a set of IP addresses like an internal corporate subnet where you're less worried about IP spoofing, and you have Enterprise Edition, is to use Oracle Connection Manager to filter the incoming connection requests as a reverse proxy. Have a service_name dedicated just to user connections and force them all through the same filter. You can have different filters for different applications, for admin connections, etc., differentiated by service_name. This won't limit a specific user to a specific IP address (unless you want each user to have their own service name - yikes!), but will still provide an additional layer of protection and obfuscation for your database server. I have used this extensively over the years myself.

EdStevens

1518698 wrote:Thanks EdStevens . Agree, that's the current setup implemented in our company. 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.

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

pmdba

That's one reason I love Connection Manager. You can set a single rule for the entire DHCP subnet and forget about it.

That said, if an IP address isn't permanent, then as Ed says, it doesn't make a very good filter condition. You'd have to use some other property that would show up in the sys_context info for the session. The problem is that all of those parameters other than proxy user name can be sliced by a clever Java programmer, so no whitelist based on those would be foolproof.

EdStevens

1518698 wrote:Thanks EdStevens . Agree, that's the current setup implemented in our company. 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.

In addition to the fact that a given workstation's IP address is subject to change at any time, ultimately, so is the workstation name.  Not by anything within the network admin, but by something as mundane as the worker getting a new workstation -  with a new workstation name - every few years.  When that happens, everyone will be pulling their hair out trying to figure out why said user can't log on with the same credentials as he did yesterday.  And who's going to even remember that there is a database logon trigger.  This is one reason why many consider triggers to be evil.

I fear that this is just another example of auditors who know nothing of the technology simply blindly following some checklist they picked up somewhere.

pmdba

Ultimately there is no perfect protection for this kind of thing. An important question to ask the auditors is, what is the problem you're trying to solve?

IP addresses change, workstations change, etc.; it doesn't make sense to make your security so tight that users are constantly locked out of their legitimate work by normal day-to-day operational changes. If you want to ensure that the user is who they claim to be when they log in, use TCPS for authentication and not just encryption (i.e. use a client certificate that can't be stolen, forged, or otherwise abused). If you want to make sure that clients are connecting from a specific subnet on the corporate network, use Connection Manager or just the system firewall on the database server to limit access to the listener port. If you want to ensure that users are only doing things at certain times of day, use smart application roles or build those rules into your application/database business logic.

As long as you're sure who is connecting and that they're using a legitimate corporate computing resource, what does it matter if DHCP changed their IP address or the IT department issued them a new laptop this morning? Having to track all of that manually could become a maintenance nightmare if you're not very careful.