How to authorize a user/application combination in Oracle?

Question

I'd like to authorize the user/application combination, not only the user.

The scenario is that we've built an app that guides the user to safe updates of some data.

If the same user installs PL/SQL Dev, Toad, or any other Oracle management tool, she can edit the data in ways that the app prohibits.

Answer 1

The v$session view contains a column 'program'. This contains the name of the connected application. You may be able to use this information.

To determine the sessions ID use this:

select sys_context('USERENV','SID') from dual;

Answer 2

Hi Martin,

You can use a proxy user to restrict access to the database. Your users would only be able to connect (authentication) and activate a role (authorization) through the middle-tier account. They would not need to know their DB password. They can be authenticated externally (with AD for example).

See this thread on AskTom and the documentation for further reading.

Answer 3

Vincent's answer is good (and he also makes a good point in his comment about spoofing the executable).

For a more programmatic method (no mid tier), see my answer to a similar question. It basically involves coding an ALTER SESSION into your application that enables a role.