Allowing oracle db login only to specific application?

Question

We want to allow DB access (Oracle) to our users only through our own application - let's call it "ourTool.exe", installed locally on the users computers. Currently, the users must provide username/password whenever they start "ourTool". The provided password password gets decrypted and we use username/decrypted-password to finally log in to the Oracle DB. This approach prevents the users from directly accessing our DB using third party tools (SQLplus, Excel, Access, ...) and everything in the DB is guaranteed to have been entered/edited using "ourTool".

Now, one of our clients wants to allow its users "single sign-on" (with SmartCards/Oracle PKI). With this, the user will be able connect to our DB without providing any password every time they start "ourTool". But the same will be true for the potentially dangerous tools like SQLplus, Excel, Access, etc.

Is there a way to prevent this? How can we make sure that every record in our DB is only created/edited/deleted using "ourTool" in this scenario?

Answer 1

By default, OCI transmits the calling application EXE name and you can access it by querying v$session:

SELECT  program
FROM    V$SESSION

, which you can do in an AFTER LOGON trigger.

But this can be easily overriden and should not be relied upon.

Answer 2

Since it's your application and you have control of the source, you can use either password protected database roles or Secure Application Roles that are enabled from ourTool.exe. (see http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm ).

For example, with a password-protected database role, the initial connection would be with only the CREATE SESSION privilege, and then ourTool.exe would issue the SET ROLE with password known only to you. Any other application doesn't have the information to set the role. Obviously, the privileges are granted only to the role and not directly to the user in this configuration.

Answer 3

Can't you use SSO as an authentication method for your application and the rest would be just like before? I'm not sure the way it works (encryption/decryption of oracle password) but you should be able to use SSO as alternative to your current application authentication method (users login/password). Maybe if you can post some more details...?

Answer 4

I can not comment (why?) but in addition to Quassnoi:

I renamed my sqlplus.exe to myTool.exe and after making a connection with myTool.exe

SELECT  program
FROM    V$SESSION
where username = 'SYSTEM';

Returns: myTool.exe

So be aware, as Quassnoi said: although usable in some circumstances it's certainly not bullit proof.