1

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.

Improve this question
8
  • 3
    Keep in mind this is nothing more than security through obscurity. I can just rename excel.exe to excel2.exe and I get in.
    – Balazs Papp
    Commented Mar 29, 2019 at 16:49
  • @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?
    – ImaginaryHuman072889
    Commented Mar 29, 2019 at 17:25
  • 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'.
    – EdStevens
    Commented Mar 30, 2019 at 0:01
  • @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.
    – ImaginaryHuman072889
    Commented Mar 30, 2019 at 1:45
  • 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.
    – EdStevens
    Commented Mar 30, 2019 at 13:09

2 Answers 2

Reset to default
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.

Improve this answer
7
  • Excellent, going to test now, will mark as correct if works.
    – ImaginaryHuman072889
    Commented Mar 29, 2019 at 16:20
  • 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?
    – ImaginaryHuman072889
    Commented Mar 29, 2019 at 17:26
  • Since the trigger runs with the authority of the connecting user, it may not have access to gv$session -- try v$session instead.
    – mustaccio
    Commented Mar 29, 2019 at 17:34
  • Just tried v$session, but it had the same result.
    – ImaginaryHuman072889
    Commented Mar 29, 2019 at 17:39
  • 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.
    – mustaccio
    Commented Mar 29, 2019 at 17:44
0

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

Improve this answer
1
  • Thanks for your answer but this won't work, the admins may or may not be coming from the same IP's.
    – ImaginaryHuman072889
    Commented Mar 29, 2019 at 17:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.