3 Replies Latest reply: Mar 28, 2013 4:22 AM by Justin_Mungal RSS

    Unprivilege user cannot implement Tuning set recommandation via OEM/Grid C.

    GReboute
      Hi,

      We granted acces to our OMS to our Siebel admininsitrator. He used it to compose SQL tuning set, without issues. But when he tried to implement the recommendations through Grid Control he had the following errors :
      Problème lors de la création du profil SQL ORA-38171: Privilèges insuffisants pour l'opération d'objet de gestion SQL
      ORA-06512: à "SYS.DBMS_SYS_ERROR", ligne 79
      ORA-06512: à "SYS.DBMS_SMB", ligne 83
      ORA-06512: à "SYS.DBMS_SQLTUNE", ligne 7657
      ORA-06512: à "SYS.DBMS_SQLTUNE", ligne 7541
      ORA-06512: à ligne 1

      (even if the message is in french, I think you can understand what happens).

      I thought "easy, he hasn't got the corrects privileges", but :
      select * from dba_role_privs where GRANTEE=upper('siebel');

      GRANTEE GRANTED_ROLE ADM DEF
      ------------------------------ ------------------------------ --- ---
      SIEBEL OEM_MONITOR NO YES
      SIEBEL OEM_ADVISOR NO YES
      SIEBEL SBL_ADMIN NO YES
      SIEBEL TBLO_ROLE NO YES

      select * from dba_sys_privs where GRANTEE=upper('siebel');

      GRANTEE PRIVILEGE ADM
      ------------------------------ ---------------------------------------- ---
      SIEBEL ADMINISTER SQL TUNING SET NO
      SIEBEL SELECT ANY DICTIONARY NO
      SIEBEL ADVISOR NO

      alter user siebel default role all;
      User altered.

      And same issue.

      I tried :
      grant execute on DBMS_SYS_ERROR to siebel;
      grant execute on DBMS_SQLTUNE to siebel;
      grant execute on DBMS_SMB to siebel;
      grant select on v_$sqlarea_plan_hash to siebel;
      grant select on v_$SQL_BIND_CAPTURE to siebel;
      GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO siebel;
      GRANT SELECT ANY DICTIONARY TO siebel;
      grant SELECT_CATALOG_ROLE to siebel;
      grant ANALYZE ANY to siebel;
      alter user siebel default role all;
      -- some of them by searching internet

      but without any success.

      Which grants are exactly needed to perform SQL Tuning through OEM for an unprivilege user ?

      Edited by: GReboute on Mar 27, 2013 5:51 PM
        • 1. Re: Unprivilege user cannot implement Tuning set recommandation via OEM/Grid C.
          damorgan
          For OEM you may need to grant those privileges to the OEM user account separately.
          • 2. Re: Unprivilege user cannot implement Tuning set recommandation via OEM/Grid C.
            GReboute
            I don't understand what you mean exactly.
            The guy connects to OEM with his account (account_guy_siebeladmin for ex.) navigate to the instance we granted him rights on, then he has to connect to the instance, OE asks for user/passwd, he inputs the siebel user/passwd => OEM is connected to the DB with siebel login.
            account_guy_siebeladmin launch the SQL Tuning advisor, which runs its task, so far, so good. It is when he wants to APPLY tuning reco, he receive the errors.
            So in my analyze, the priviliege issue is on the database siebel account => the one I provided grants extract in 1st post.
            • 3. Re: Unprivilege user cannot implement Tuning set recommandation via OEM/Grid C.
              Justin_Mungal
              GReboute wrote:
              I don't understand what you mean exactly.
              The guy connects to OEM with his account (account_guy_siebeladmin for ex.) navigate to the instance we granted him rights on, then he has to connect to the instance, OE asks for user/passwd, he inputs the siebel user/passwd => OEM is connected to the DB with siebel login.
              account_guy_siebeladmin launch the SQL Tuning advisor, which runs its task, so far, so good. It is when he wants to APPLY tuning reco, he receive the errors.
              So in my analyze, the priviliege issue is on the database siebel account => the one I provided grants extract in 1st post.
              If you wanted to test Dan's suggestion, you could always grant the user account DBA access temporarily, and see if the failure still occurs. If it doesn't, then you know the problem relates to the siebel account, and that you're missing a grant somewhere. If you're concerned about doing that, you could create an account with the same permissions and grants as the siebel user, verify that the failure occurs, then grant that user the DBA role, and finally test to see if the failure occurs even with DBA permissions.