JIRA, Confluence, and Crucible+Fisheye Set Database Schema Qualifier

[Jonathan Martin]

We're using Oracle 11.x as the database backing JIRA, Confluence, and Crucible+Fisheye.  We've run into a possible issue related to security constraints imposed by our DBA group.  We're required to connect to the database using a different user/schema name than the one in which the tables reside.  

We still have all of the required permissions we just need to qualify every SQL statement with the schema name.

For example, instead of the app executing the following query:

select * from CHANGEITEM;

It needs to execute the query qualified with the schema name like this:

select * from <SCHEMA NAME>.CHANGEITEM;

I know one way around this is to use Oracle public synonyms but from what I've read this is unsupported.  

I found in the <JIRA_HOME>/dbconfig.xml the following a schema-name element.

<jira-database-config>
    <name>defaultJira</name>
    <delegator-name>default</delegator-name>
    <database-type>oracle10g</database-type>
    <schema-name><MY_SCHEMA_NAME></schema-name>
...

I'm hoping this will solve the issue for JIRA.

For Confluence and Crucible+Fisheye it's a little less clear.  For Confluence I'm hoping I can add the following property element to the <CONFLUENCE_HOME>/confluence.cfg.xml file. 

<property name="hibernate.default_schema"><MY_SCHEMA_NAME></property>

For Crucible.  I'm really not sure if it'd even be supported.  I see some database configuration information in the <CRUCIBLE/FISHEYE HOME>/config.xml 

<database type="oracle">
	<connection dialect="org.hibernate.dialect.Oracle10gDialect" 	driver="oracle.jdbc.driver.OracleDriver" 
driverSource="bundled" jdbcurl="<MY_CONNECTION>" 
maxPoolSize="20" 
minPoolSize="5" 
password="<MY_PASS>" 
username="<MY_USER>"/>
	<parameters>useUnicode=true characterEncoding=UTF8 connectionCollation=UTF8_bin</parameters>
</database>

However, not sure if there's a way to set the schema.  

Again, any help with qualifying the schema for all three apps would be greatly appreciated.

[Matheus Fernandes]

Hi, Jonathan.

For JIRA and Confluence it is possible to achieve this, albeit it won't be possible to configure this (in the application side) for FishEye/Crucible.

Please allow me to share below the steps to achieve this in each of those, as well as explain why this won't be possible for FeCru.

JIRA

In JIRA this is the easiest one to configure. After installing JIRA (and before configuring the database), make sure that JIRA is stopped. Then, run the $JIRA-Install/bin/config.sh file (or .bat if you are running on Windows).

This will bring up a tool to help you configure the database, so just fill in the username of the user that will be accessing JIRA (and its password) as well as the path to the database. After that, save the configuration and exit the tool.

There is still one more configuration we'll need to do, which is to add a property below the "database-type" in the $JIRA-Home/dbconfig.xml file:

<schema-name>jira_owner</schema-name>

Just replace jira_owner with the actual schema.

CONFLUENCE

This is somewhat more complicated for Confluence as Confluence does not allow you to pre-configure its database so the database will first have to be populated by Confluence to then change the user that will access it. So the steps would be:

1. Install Confluence;
2. Connect to the Oracle database as per Database Setup for Oracle, using the owner of the schema/objects (e.g. confluence_owner)
3. After Confluence is completely setup, stop it.
4. Add the following property to the confluence.cfg.xml file:

   <property name="hibernate.default_schema">confluence_owner</property>
   

5. In the same file, change the values of username and password to the user that will be only accessing the schema.
6. Restart Confluence.

From my local tests, Confluence will show some unique key violation errors on the system tables when it first start after making this change, but it seems to work despite those errors.

Also, please note that you can possibly face bug when using this configuration as for example CONF-24113.

FISHEYE/CRUCIBLE

I took a look inside the source code of FeCru, but this application uses a different model for configuring the database and does not have any property that allows for the schema to be configured. 

---

I hope this helps! 

Best regards,
Matheus Fernandes