Restricting access to the database dependent upon access method

[sjwales]

One of the application administrators set foot in my office today (how dare he!) and started off a question with "In the SQL Server World....".

Apparently, in SQLServer, there is a way to say that if user Bob is trying to connect through MS-Excel, then we allow it, but if he tries to connect in some other way, then we deny it.

Does Oracle offer something similar? I haven't been able to find anything that would indicate that it does, but I thought it doesn't hurt to ask.

We have users that are supposed to access a database via a particular application only (it's called Corvu) and we don't want them getting in via SQLPlus, Access, Excel or any other method.

I suppose it could be done via a login trigger checking the program column in v$session, but it looks like this is set to "unknown" when users are connecting through corvu, so that's not going to help me.

Any other bright ideas out there?

Thanks
Steve

 

[carp]

Hmm. I was going to suggest the login trigger (that's worked for us before), but it sounds like you've hit a kink with that. Have you checked the MODULE value in v$session?

 

[sjwales]

Well, well, I had another look this morning, and now both program and module are reporting "CVORA8.EXE", which gives me something to play with.

Now, writing login triggers is not something I've done before - would someone have an example of a trigger at login that checks the program and/or module and if it's not what is expected then disconnects with an error message?

Thanks
Steve

 

[LKBrwnDBA]

Just put your 'check' code between 'begin' and 'end':

Create Or Replace Trigger Logon_Trg
After Logon On DATABASE]
Declare
  v_module Varchar2(40);
Begin
-- Check module (or program here) --
  Select Program Into V_module From V_$SESSION
   Where Username = User;
  If V_module != 'CVORA8.EXE' Then
    raise_application_error(-20001,V_module||' not allowed!');
  End If;
End;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb