Prevent applications to log in on Oracle Database

Question

Hello,

Does anybody knows how could I make a trigger or anything else to prevent people to connect on my database with any kind of applications besides mine?

Note that the super-old-and-unsecure trigger to block few .exe such TOAD or watever does NOT really works, since you can just rename the EXE to MyApplication.exe.

Hints?

Answer 1

If you don't trust the program name in v$session then the only options that come to mind are to have your application encode the password, so that what they type in isn't actually what's used to connect to the DB; or have your app log in with a private username/password and authenticate users against your own users table instead of having Oracle user accounts for them. Both options make management of accounts more complicated though.

Answer 2

I don't know that Oracle has any functionality to help with this (I could be wrong though) so the next best thing might be to write a small server app that lets you have much better control over the login process and acts as the middle-man between the client apps and the database server. That way, all connections to the database come through your server app, and you can control how your server identifies which client app is legit. This will add a bit of complexity to the system though.

Answer 3

When your application logs on, you call a stored procedure that associates the current oracle session as a "trusted" session. Do this by creating a trusted sessions table with a field for sessionID and trusted bit (and optionally a random hash to prevent user tampering).

Create a system wide trigger, that checks the your current session id (and random hash) to detect if it is trusted. If the session doesn't exist in the table, you don't allow the query, and log off the user.

You should also setup a shutdown trigger to clear the trusted session table on exit.

Answer 4

You may wish to consider Oracle's Secure Application Roles -- it won't prevent people from logging into the database through a rogue application, but it can prevent them from accessing tables and packages if the application doesn't set the role using the password that only it knows.

You can find an tutorial on deploying it here, although to secure it, you'd have to create the role with a password, and your application would have to know the password when issuing the SET ROLE rolename IDENTIFIED BY rolepassword; statement.

Answer 5

An easier method would be to move the security to a role that can be enabled only by your application - see a previous answer of mine here

WIth this method another application may create a session but has no other privileges since the role is not enabled.