5 Replies Latest reply on Apr 28, 2009 11:51 AM by mazz

    Jopr - Oracle10g database access issue.

    senthur_kumaran

      Hi All,

      I am in the process of configuring jopr in our organization.
      There are 2 types of database users.
      1. schema_owner
      2. schema_oper

      Shema owner has all access to the oracle schema.
      shema oper has only select,insert, update and delete access to all objects in the db.

      I have used schema owner account to install the jopr. All the schema objects created successfully.
      Now, I want to use oper account in jopr because of our organization policy prevents using owner accounts in applications.
      I have modified "rhq-server.properties" with jon oper account. But i am getting multiple errors when server startup.
      sample trace
      2009-04-24 11:36:41,147 WARN [org.jboss.system.ServiceController] Problem starting service jboss.mq:service=PersistenceManager
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: ORA-00942: table or view does not exist
      )
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:495)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1809)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
      at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)



      anybody please let me know how can I prevent these errors while using oper accounts?


      Thanks & Regards
      senthur

        • 1. Re: Jopr - Oracle10g database access issue.
          mazz

          I'm not sure if that is going to work.

          First, some of the work the Jopr Server needs to do requires truncating tables (IIRC, truncating requires additional permissions... I could be wrong here, but for some reason I think that).

          Two, in order to help recover from failures in database transactioning, Jopr Server needs certain accesses give to it by Oracle. Please see:

          http://jira.rhq-project.org/browse/RHQ-1368

          I'm unsure what other bad things would happen.

          • 2. Re: Jopr - Oracle10g database access issue.
            senthur_kumaran

            JON server starts without any issues after following updates
            1.
            GRANT SELECT ON sys.dba_pending_transactions TO JON_OPER_ROLE;
            GRANT SELECT ON sys.pending_trans$ TO JON_OPER_ROLE;
            GRANT SELECT ON sys.dba_2pc_pending TO JON_OPER_ROLE;
            GRANT EXECUTE ON sys.dbms_system TO JON_OPER_ROLE;
            (Required rights for transaction management)
            2.
            grant create any table to JON_OPER_ROLE;
            grant drop any table to JON_OPER_ROLE;
            (Required to purge the data and create table while server start up)

            3.
            Created synonym for all the database objects such as tables and sequences.
            (References to owner tables)

            I will further post if there is any updates on this..

            by the way , will there be any impact if I change CREATE_TABLES_ON_STARTUP = FALSE in rhq-jdbc2-service.xml and rhq-jdbc-state-service.xml files ?

            Thanks & Regards
            Senthur

            • 3. Re: Jopr - Oracle10g database access issue.
              mazz

               

              by the way , will there be any impact if I change CREATE_TABLES_ON_STARTUP = FALSE in rhq-jdbc2-service.xml and rhq-jdbc-state-service.xml files ?


              Off the top of my head, I would say no impact, only because we create those tables ahead of time. So the JMS subsystem should never have a need to create the tables when it starts because the tables should already exist.

              In fact, we might want to consider setting that to false in our distributions.

              Let us know if that does or does not cause problems - if it doesn't, we might want to just set that to false ourselves.

              • 4. Re: Jopr - Oracle10g database access issue.
                senthur_kumaran

                I have changed the value as FALSE and restarted the server. No issues till now.

                Thanks & Regards
                Senthur

                • 5. Re: Jopr - Oracle10g database access issue.
                  mazz