Allow login to oracle db for a specific application only?

Question

We want to allow access to the database (Oracle) to our users only through our own application - let it "ourTool.exe", installed locally on users' computers. Currently, users must provide a username / password when starting ourTool. The provided password password is decrypted, and we use the username / decrypted password for final login to Oracle DB. This approach does not allow users to directly access our database using third-party tools (SQLplus, Excel, Access, ...), and everything in the database may have been entered / edited using our "ours".

Now one of our customers wants to allow their users "single sign-on" (using SmartCards / Oracle PKI). At the same time, the user will be able to connect to our database without providing any password each time they run "ourTool". But the same will be true for potentially dangerous tools such as SQLplus, Excel, Access, etc.

Is there any way to prevent this? How can we make sure that every record in our database is created or edited / deleted using "ourTool" in this script?

Answer 1

Since this application is and you control the source code, you can use either password protected roles or protected application roles that are included from our Tool.exe. (see http://www.oracle.com/technology/obe/obe10gdb/security/approles/approles.htm ).

For example, with the password-protected database role, the original connection will only have the CREATE SESSION privilege, and then our Tool.exe will issue SET ROLE with a password known only to you. Any other application does not have the information to install the role. Obviously, privileges are granted only to roles, and not directly to the user in this configuration.

Answer 2

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

 SELECT program FROM V$SESSION 

which you can use in the AFTER LOGON trigger.

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

Answer 3

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

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

Returns: myTool.exe

So be careful, as Kvasnoy said: although in some cases they can be used, this is certainly not proof.