Announcement

Collapse
No announcement yet.

DB Security Question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • DB Security Question

    Hello All,

    I was asked to create an oracle account with nothing but SELECT access to GLOGOWNER.GL_LOGIN_HISTORY. I created the account with the requested access but found that i would receive zero rows when querying the table with that account. I did some research & it is looking like OTM uses VPD. I'm not familiar with this level of security. I was just wondering if anyone had a quick answer on how to allow the account that I created to read all the rows in glogowner.gl_login_history. In the meantime, I will read up on the VPD/fine grain access control stuff.

    Thanks,

    Joe Patton
    Joe Patton

    Technical Specialist, DB2-Oracle DBA
    Database Management and Support
    Parker Hannifin Corporation

  • #2
    Re: DB Security Question

    Joe,

    For a quick and dirty, all-access-allowed approach, type the following after logging in as that user via sql:

    Code:
    exec vpd.set_user('DBA.ADMIN')
    (see http://www.otmfaq.com/forums/f21/ins...rformance-513/ for more details.)

    You should now have access to all data in all tables. Ensure that you're getting results back. From there, you'll have to dig further into VPD in order to refine the access and trim back the access to just the GLOGOWNER.GL_LOGIN_HISTORY data.

    See the following URL for a brief overview and sample info on VPD:
    VPD

    --Chris
    Chris Plough
    twitter.com/chrisplough
    MavenWire

    Comment


    • #3
      Re: DB Security Question

      Thanks for the reply, Chris. The following command works if I open up a SQL*Plus session:

      exec vpd.set_user('DBA.ADMIN')

      The account seems to lose the profile setting when the session is closed. Is there a way to permanently assign the user that I created to the 'DBA.ADMIN' profile? Thank you for the link to the tutorial. I will be trying to educate myself on VPD next week if I have the time.

      Joe Patton
      Joe Patton

      Technical Specialist, DB2-Oracle DBA
      Database Management and Support
      Parker Hannifin Corporation

      Comment


      • #4
        Re: DB Security Question

        Joe,

        OTM sets the VPD profile for non-application users via the use of database logon triggers. Using a logon trigger will eliminate the need to set the profile manually each time the user logs on to the database. For an example on how to do this you can take a look at the script create_logon_triggers.sql under your OTM home /glog/oracle/script8 directory.

        Thanks,
        Alan

        Comment


        • #5
          Re: DB Security Question

          Thanks for the answers, Alan and Chris. I took a look at the trigger code example in the file that you specified and was able to get the proper access for the user in question. This account can only select from glogowner.gl_login_history. The user in question has execute privileges on the GLOGOWNER.VPD package so that the SET_USER procedure can be executed within the logon trigger. Are there any security concerns regarding execute priv's against the VPD package?
          Joe Patton

          Technical Specialist, DB2-Oracle DBA
          Database Management and Support
          Parker Hannifin Corporation

          Comment

          Working...
          X