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

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.

&lt;jira-database-config&gt;
    &lt;name&gt;defaultJira&lt;/name&gt;
    &lt;delegator-name&gt;default&lt;/delegator-name&gt;
    &lt;database-type&gt;oracle10g&lt;/database-type&gt;
    &lt;schema-name&gt;&lt;MY_SCHEMA_NAME&gt;&lt;/schema-name&gt;
...

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. 

&lt;property name="hibernate.default_schema"&gt;&lt;MY_SCHEMA_NAME&gt;&lt;/property&gt;

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 

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

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.

1 answer

0 vote

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

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published Monday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

492 views 6 11
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you