Jira: Change database user after installation

[Sebastian Bader]

Hello,

I installed Jira (latest version) on a Tomcat server (.WAR deployment) using an Oracle 11g database. For the installation I used a user who has permissions to create new tables etc. Everything went fine so far, Jira works well.

However, due to user restriction policies I need to change the database user for Jira. I have granted the new user SELECT, INSERT, UPDATE, DELETE permissions on all tables and the sequenes (was that nessecary?) created by Jira. Furthermore I created synonyms for all these objects so that the user should be able to access all objects just as the initial user does.

So now I changed the database user and password in the dbconfig.xml. When restarting the server following error occurs:

2012-11-14 09:43:10,746 main INFO      [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher
2012-11-14 09:43:14,505 main ERROR      [jira.upgrade.util.UpgradeUtils] JDBC get version number failed. SQL: SELECT ps.null FROM propertyentry pe, propertystring ps WHERE pe.null='jira.version.patched' AND pe.null=ps.null
java.sql.SQLSyntaxErrorException: ORA-01747: invalid user.table.column, table.column, or column specification

Does anyone have an idea how to solve this?

Many thanks in advance,

Sebastian

[Dave C · Accepted solution]

Hey Sebastian,

I've found a few edge cases of this in the past, and they all look to have been caused by using a synonym to access the database. Unfortunately we don't really support this when it comes to Oracle - the default DB connection needs to be made as in our Connecting JIRA to Oracle documentation. The problem actually appears to be that OfBiz is sending the incorrect SQL to the database (it's using null as a column name instead of propertyvalue).

To fix this, I would suggest setting the JIRA database connection back to using our required user (e.g.: jiradbuser) and ensuring that the permissions in Configuring Oracle are granted to that user. As Andy mentioned earlier, it is a requirement of JIRA that those permissions are granted as we now use the Active Objects persistence ORM in a lot of our plugins and also third-party plugins. If JIRA does not have the appropriate permissions to create objects within the database, it can cause plugin installation and upgrades to fail. It is known to cause critical errors with GreenHopper upgrades and can leave the AO tables in a state that is not recoverable, as documented in our ORA-01031 KB article. This is not something we would want to happen to your JIRA instance, which is why I would recommend to use our supported database connection methods.

Additionally, is there any particular reason for using a WAR deployment? We highly recommend using our standalone installer as it's easier to support, maintain, upgrade and install. I would suggest switching over, I trust you'll find it a lot easier to use!

[Sebastian Bader]

David,

thanks for the fast response.

We are installing Jira in a predefined customer environment with very limited permissions. That's why we have to use the WAR deployment. That's also why we should use read-only database users. But as this seems not possible for Jira, we have to deal with it :)

Regards,

Sebastian

[Dave C]

You're welcome! In addition to this, I have raised a KB so we have more exposure on this known issue: How to Fix Oracle Error ORA-01747: invalid user.table.column, table.column, or column specification

Cheers :)

[Nic Brough -Adaptavist-]

The new user clearly doesn't have the rights to read the tables (via synonym). You need to make sure that the new user can issue exactly the same SQL as the old user and get exactly the same results. The error you're getting implies that the new user does not have the right "select" privileges - they can't see the table columns, let alone read the data in them.

[Sebastian Bader]

Yes, the new user can select on all Jira tables without any problem when I execute commands in SQLDeveloper (or any other tool)

[Andy Brook [Plugin People]]

perhaps you tightened up the security too much? I have experienced 'lock down' situations that are due to the inability of JIRA to lookup table meta data (stored procedure calls). Generally I'd say JIRA needs r/w to all its tables, _and_ the ability to create new tables (plugins like Greenhopper /any need this to store data).

Try using sqlplus or similar to execute some queries, if the tables can be read, the answer is probably in meta data access.

I saw (http://stackoverflow.com/questions/4198052/oracle-table-or-view-does-not-exist-from-inside-stored-procedure) , which was interesting, I wonder if the grant was done through a role, does the jira user have the role? again, connecting by sqlplus will help...

[Sebastian Bader]

Unfortunately I do not have real admin rights on the database. So if anything would have have to be adjusted, I need to know what ;-)

As I posted below, I can run queries without a problem.

[Michael Stelzner _Communardo_]

Hi Sebastian,

You can put the scheme-name into dbconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
 
<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>oracle10g</database-type>
  <schema-name>SCHEME_NAME</schema-name>
  <jdbc-datasource>
    <url>jdbc:oracle:thin:@<server></url>
    <driver-class>oracle.jdbc.OracleDriver</driver-class>
    <username>xxx</username>
    <password>xxx</password>
    <...>
    </jdbc-datasource>
</jira-database-config>
 

With this configuration you can start JIRA without DDL permissions. You have to run all necessary DDL prio startup. Be aware that Plugins with Active Objects execute their DDL not at jira startup.

[Naren]

Though I haven't tried this on the .WAR JIRA deployment. Canyou try changing the entityengine.xml located under tomcat6/webapps/jira/WEB-INF/classes - by adding the following line -

schema-name="your_jira_schema_name" .

Hope this helps.

[Sebastian Bader]

where exactly would I have to put this? In which xml element?

Thanks!

[Naren]

As I am not sure with this one. Can you please trying adding it under the <datasource></datasource> element.

[Sebastian Bader]

Hm, it is stated there:

<!--

    DATASOURCE

    You should no longer define a datasource in this file, the database is now configured through the UI at setup time.
    The only time you would want to configure it here is when you migrate from an older version and need to point the
    new installation at an existing db. This is considered a legacy method and will not work if dbconfig.xml exists
    in the home directory.
     -->

So that's probably not really an option (anymore)

[Akeles]

To specify the scheme in dbconfig.xml instead of entityengine.xml

...

<database-type>oracle10g</database-type>
<schema-name>my-schema</schema-name>

...