Announcement

Collapse
No announcement yet.

vpd.set_user throws exception of 'Insufficient Privileges'

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

  • vpd.set_user throws exception of 'Insufficient Privileges'

    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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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)
        Last edited by Nagraj; June 6, 2012, 19:33.

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment

            Working...
            X