Announcement

Collapse
No announcement yet.

Create OTM 5.5 DB - need SYS privileges

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

  • Create OTM 5.5 DB - need SYS privileges

    While helping a friend setup an OTM 5.5 instance, I ran into a problem where the create_glog_roles.sql and create_glog_users.sql scripts were failing because the SYSTEM user didn't have privileges to run some of the required commands.

    The fix is simple, rather than running both scripts as SYS (which I avoid doing - much like running commands as root on Unix / Linux boxes), or granting more privilleges to the SYSTEM user, you can get buy with just running certain commands as SYS.

    Here's the course of events:
    1. Login to your OTM app server
    2. Change directories to <otm_home_dir>/glog/oracle/script8
    3. Sqlplus into your OTM database as the SYSTEM user
    4. Run
      Code:
      @create_glog_roles.sql
    5. Sqlplus into your OTM database as the SYS user
    6. Run:
      Code:
      grant SELECT ON DBA_SYNONYMS to app_owner;
      grant execute on dbms_rls to app_owner;
      grant select on sys.v_$context to app_owner;
      GRANT execute on dbms_rls to rep_owner;
      commit;
    7. Sqlplus into your OTM database as the SYSTEM user
    8. Run:
      Code:
      @create_glog_users.sql
    9. Sqlplus into your OTM database as the SYS user
    10. Run:
      Code:
      grant DBA to glogowner;
      grant DBA to glogdba;
      grant execute on dbms_aqin to glogdba;
      grant execute on dbms_rls to glogowner;
      GRANT EXECUTE ON DBMS_AQIN TO glogowner;
      GRANT execute on dbms_pipe TO glogowner;
      commit;
    Hope this helps!
    --Chris
    Last edited by chrisplough; June 8, 2007, 16:38.

  • #2
    Re: Create OTM 5.5 DB - need SYS privileges

    Just a quick update. While setting up the OTM database, you will get the following error while running the create_public_synonyms.sql script as the reportowner user:
    Code:
    SQL> @create_public_synonyms.sql
       SELECT owner, table_owner FROM DBA_SYNONYMS WHERE SYN *
    ERROR at line 8:
    ORA-06550: line 8, column 35:
    PL/SQL: ORA-00942: table or view does not exist
    ORA-06550: line 8, column 4:
    PL/SQL: SQL Statement ignored
    In order to resolve this issue, just login to the OTM database as the SYS user and run:
    Code:
    grant SELECT ON DBA_SYNONYMS to rep_owner;
    Then, re-run the create_public_synonyms.sql script as the reportowner user.

    This will avoid several problems later down the road.

    Hope this helps!

    --Chris

    Comment


    • #3
      Re: Create OTM 5.5 DB - need SYS privileges

      I had similar problems, but these were caused by the setting of O7_DICTIONARY_ACCESSABILITY. If this is set to false, the permissions for SYSTEM are tightened. If set to TRUE, the scripts work.

      Just my thoughts...

      Jonas

      Edit:
      To clear things up: The parameter is for the database, init.ora, and not in OTM...
      Last edited by jonash; October 27, 2007, 10:31.

      Comment

      Working...
      X