Create OTM 5.5 DB - need SYS privileges

[chrisplough]

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

   @create_glog_roles.sql

5. Sqlplus into your OTM database as the SYS user
6. Run:

   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:

   @create_glog_users.sql

9. Sqlplus into your OTM database as the SYS user
10. Run:

    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

[chrisplough]

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:

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:

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

[jonash]

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...