vpd.set_user throws exception of 'Insufficient Privileges'

[[email protected]]

Hi Everybody,

Recently a test database of OTM 5.5 version was export and imported in Oracle 10g. However after import, after executing the after import steps as mention in Administration guide, on execution of vpd.set_user('DBA.ADMIN'); I am getting excecption as below -

ORA-01031: insufficient privileges
ORA-06512: at "GLOGOWNER.VPD", line 205
ORA-06512: at "GLOGOWNER.VPD", line 214
ORA-06512: at line 1

The creation of user and roles were created using the scripts provided by OTM script8 folder.. Even if I exec vpd.set_user('DBA.ADMIN') as user sys also, I am getting the same exception.

Appreciate if you can point out what is being missed out to fix this issue if you have encountered a similar scenario.

Thanks,
Suresh

[[email protected]]

Re: vpd.set_user throws exception of 'Insufficient Privileges'

Hi All,

Well in a way I was screwing up the user/role creation process I guess by using an old version of creation scripts. I recreated the database again from scratch and created the users/roles using the correct version and the issue was not reproduce able.

However still I am not sure the fix of this issue though, the grants to roles and users (including sys/system) was compared with the db which had this issue and the original one where its working fine and they were similar though. Previously when I encountered this same issue (i can say exactly two times), the problem was fixed by randomly providing certain grants to public and to glogowners. However since I wanted to know the exact fix, I didn't wanted to take this random approach we took last time, however its a shame that I didn't find a resolution though.
Let me perform a comparison of the user/roles creation scripts I have and check what are the addition grants which resolves the privileges issue reported.

As of now the issue is resolved, however will keep posted to know the exact fix so that I have a one of fix for this issue :-)

Regards,
Suresh

[catch_rajat]

Re: vpd.set_user throws exception of 'Insufficient Privileges'

Hi Suresh,

I hit the same issue today, were you able to identify how you fixed this other than recreating the entire schemas

{call vpd.set_user(?)=null}; inParams=[DBA.ADMIN]
java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges
ORA-06512: at "GLOGOWNER.VPD", line 337
ORA-06512: at "GLOGOWNER.VPD", line 346
ORA-06512: at line 1

Thanks,
Rajat

[Nagraj]

Re: vpd.set_user throws exception of 'Insufficient Privileges'

Revisit the grants for the user GLOGOWNER. I believe one of the grant is missing.
(Log in as glogowner and select * from session_privs)

[[email protected]]

Re: vpd.set_user throws exception of 'Insufficient Privileges'

Hi.

Please let me know the FIX for this i am struck at the same point after the import.

ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "GLOGOWNER.VPD", line 337
ORA-06512: at "GLOGOWNER.VPD", line 346
ORA-06512: at line 2


Please help i am in OTM version 6.1

Thanks

[[email protected]]

Re: vpd.set_user throws exception of 'Insufficient Privileges'

After database import, execute the following steps as below -

As glogowner - (from $GLOG_HOME/glog/oracle/script8 folder)
@create_vpd_package.sql
@glogowner_grants.sql
@create_public_synonyms.sql
@create_logon_triggers (enter connection string when prompted)

As archive user (If archiving exists then)
@archive_grants

As reportowner -
@reportowner_grants.sql
@create_public_synonyms.sql
@recompile_invalid_objects.sql

This should resolve the issue.. plz chk ..

Thanks,
Suresh