4 Replies Latest reply: Jan 25, 2013 7:31 AM by EdStevens RSS

    Restrict Login By Machine and OS User

    jeff81
      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
          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
            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
              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
                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.