This discussion is archived
4 Replies Latest reply: Jan 25, 2013 5:31 AM by EdStevens RSS

Restrict Login By Machine and OS User

jeff81 Newbie
Currently Being Moderated
I would like to use Data Vault to restrict login for a particular user to make sure that user is connecting from a specified Machine and from a specified OS User. All other database users can connect from where ever. It is just this one user that I need to restrict. I am a confused on how to get this working. Can anybody help me.
Thanks.

Oracle Version 11.2.0.2
Microsoft Server 2008 R2
  • 1. Re: Restrict Login By Machine and OS User
    Simon_DBA Journeyer
    Currently Being Moderated
    I know that you said that you want to use Data Vault but also consider a simpler solution: a logon trigger that checks SYS_CONTEXT('userenv','SESSION_USER'), SYS_CONTEXT('userenv','HOST') and SYS_CONTEXT('userenv','OS_USER').

    I've done it that way many times as a quick and simple solution to problems very similar to the one that you've described.
  • 2. Re: Restrict Login By Machine and OS User
    jeff81 Newbie
    Currently Being Moderated
    Yes I thought about doing it that way. It is much simpler. Data Vault is confusing. But they don't want the DBA to modify this code. So I either have to do it all in Data Vault or I do it in the logon trigger and then put a realm around that trigger. I would prefer not to use the trigger method because then I would be able to freely control that trigger if I want to add other logon things. I guess I could create a second logon trigger but that is messy.
  • 3. Re: Restrict Login By Machine and OS User
    Simon_DBA Journeyer
    Currently Being Moderated
    I see it like there's two main parts to Data Vault: protecting data in the database (realms) and the sort of firewall filtering rules type of stuff. It seems to me that it's best for that first part (data protection) and it's not so great for the second part (which is what you're trying to do).

    I tried extensively to get IP based filtering going with Data Vault about 5 years ago. I involved a number of Oracle internal product managers and support people etc and in the end went with the logon trigger.

    Maybe you'll get better advice then from someone else on this forum. But my opinion is that it's not going to be easy to implement what you want.

    Good luck!
  • 4. Re: Restrict Login By Machine and OS User
    EdStevens Guru
    Currently Being Moderated
    jeff81 wrote:
    Yes I thought about doing it that way. It is much simpler. Data Vault is confusing. But they
    who are "they"?
    don't want the DBA to modify this code.
    what code is it "they" don't want you ("the DBA") to modify? It sounds like "they" don't understand how things are structured and who is responsible for what.
    So I either have to do it all in Data Vault or I do it in the logon trigger and then put a realm around that trigger. I would prefer not to use the trigger method because then I would be able to freely control that trigger if I want to add other logon things. I guess I could create a second logon trigger but that is messy.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points