How to prevent specific applications from connecting to Oracle?

Question

Is there a way to prevent certain applications (e.g. MS Excel or Access) from connecting to Oracle database?

A bit of context:

My company uses an Oracle database as our live production database. We don't want any Excel or Access files being linked to this database. I don't really care if users use something like Toad to run a few queries by hand but I don't want a bunch of Excel or Access files linked to this database. We replicate this database and all users who need this data should get it from the replicated database, not the live database.

I can of course view the list of sessions and what program was used to connect with this query, but I'm not sure how to prevent the sessions from being opened in the first place:

SELECT SID,SCHEMANAME,OSUSER,TERMINAL,PROGRAM FROM GV$SESSION

The PROGRAM column of this query will tell me if something is opened with Excel or Access.

• Balazs Papp: Keep in mind this is nothing more than security through obscurity. I can just rename excel.exe to excel2.exe and I get in.
• ImaginaryHuman072889: @BalazsPapp That's a good point. And I should have clarified, this won't prevent the user from accessing the database, only with excel and msaccess specifically. I don't really care if they use something like Toad.exe to connect just to run a few queries by hand but I want to prevent Excel or Access files from being linked to the database. Is there a better way to accomplish this?
• EdStevens: I would re-emphasize @Balazs Papp. This "requirement" comes up regularly, and the only good response is to question the requirement. Why is it accepted to connect with Toad but not with Excel? To take Balazs's comment a bit more explicitly, nothing prevents me from renaming 'excel.exe' to 'toad.exe'. Whatever program name you try to filter out, I'll just rename to something else. You really, seriously, need to question the 'requirement'.
• ImaginaryHuman072889: @EdStevens You're correct, nothing is preventing that, but my intent here is more to minimize the amount of excel files out there connected to Oracle. It's not a hard requirement, it's just an attempt to reduce the amount of open connections to Oracle that shouldn't be there. I realize this isn't fool-proof, but for most/all of the users I am dealing with, they will not realize that renaming "excel.exe" is a way to bypass it.
• EdStevens: I understand, but the question remains -- why is it acceptable to connect with Toad but not with excel? Apart from the technical problem of filtering the connections, the very fact that you allow one and not the other is strongly suggestive that you are making assumptions about Oracle that simply are not true. I could understand if you wanted to limit connections to the "official" custom app, but I can't understand allowing Toad but not Excel.

Answer 1

You could use a logon trigger for that, something along these lines:

create or replace trigger logon_trigger 
 after logon on database
declare
 v_prog varchar2(50);
 v_sid number;
begin 
 v_sid := to_number(sys_context('USERENV', 'SID'));
 select program into v_prog FROM GV$SESSION where sid = v_sid;
 if lower(v_prog) like 'access' then
 raise_application_error(-20000, 'Login not allowed');
 end if;
end;
/

PS. Not tested.

• ImaginaryHuman072889: Excellent, going to test now, will mark as correct if works.
• ImaginaryHuman072889: This works, somewhat. Instead of Oracle raising this user-defined error, it is raising this: ORA-00604: error occurred at recursive SQL level %s. Any idea why?
• mustaccio: Since the trigger runs with the authority of the connecting user, it may not have access to gv$session -- try v$session instead.
• ImaginaryHuman072889: Just tried v$session, but it had the same result.
• mustaccio: Having googled around, it looks like it's normal -- the raised exception is nested, so ORA-00604 comes out on top. Use a client that shows the complete error stack. See here, for example.

Answer 2

Not sure if this may help, but may be (or not) could be an easier approach, if your users are from a different IPs than your admins, you can restrict the listener to allow connections only from specific IPs.

Take a look to this link. https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_network_secure.htm#TDPSG40045

• ImaginaryHuman072889: Thanks for your answer but this won't work, the admins may or may not be coming from the same IP's.