[Joe Patton]

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

[chrisplough]

Joe,

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

exec vpd.set_user('DBA.ADMIN')

(see http://www.otmfaq.com/forums/f21/ins...rformance-513/ ([INSTRUCTIONS] Testing VPD Query Performance) 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

[Joe Patton]

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

[acuartero]

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

[Joe Patton]

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?