connection settings for oracle user not owning schema

[cvillegas]

Hi, we want to deploy activiti in a Oracle database in which the application user that connects to the database is not the owner of the schema. Let's say ACT is the user owner of the Activiti tables and ACTAPP is the user that the engine uses to connect to the database (ACTAPP has all the required permissions to use the Activiti tables, but it cannot create or delete the schema).

In Oracle there are several ways to achieve this:1. create synonyms on ACTAPP for all Activiti tables in ACT, 2. use “alter session set current_schema” and 3. qualify objects with schema name, e.g. "ACT"."ACT_RE_PROCDEF".

We want to know if there's someway to configure Activiti to use options 2 or 3, someway to set the schema name of the Activiti tables.

We're deploying Activiti in JBoss together with other applications, for EJB3 beans, hibernate has a default_schema option which allows to qualify all objects with the schema name, but I don't see a similar option in Activiti configuration, or did I missed it?

[frederikherema1]

A new method has been added to the ManagementService:

/** programmatic schema update on a given connection returning feedback about what happened */
  String databaseSchemaUpgrade(Connection connection, String catalog, String schema);

You can set the database-upgrade property to "none", jobexecutor active by default to false and call this method with a connection that uses the ACT user to create the schema. After that, you can just start the job-executor.

CHeers,
Frederik

[fconrady]

I have exactly the same question and I don't understand the answer. Is there some way to configure Activiti to use options 2 or 3 pointed out by the OP?

I have tried databaseSchema and databaseTablePrefix without success. Either these are for a different purpose or I am applying them incorrectly.

Thanks
Florian

[trademak]

Hi Florian,

Can you explain in a bit more detail what exactly you are trying to do with Oracle?

Best regards,

[fconrady]

Hi Tijs,

I figured it out after inspecting the source code. The forum messages helped, but I did not fully understand it from them.

It works either like this
<blockcode>
                                <property name="databaseSchemaUpdate" value="false" />
                                <property name="databaseTablePrefix" value="<owner>." />
                                <property name="databaseSchema" value="<owner>" />
                                <property name="tablePrefixIsSchema" value="true" />
</blockcode>

or like that
<blockcode>
                                <property name="databaseSchemaUpdate" value="none" />
                                <property name="databaseTablePrefix" value="<owner>." />
</blockcode>
In the second case, when databaseSchemaUpdate is set to something else than “true” or “false”, it does not seem to check the presence and version of tables at all and the value of databaseSchema appears to be irrelevant.

The parameter tablePrefixIsSchema is available since 5.15 and it solves the Oracle-specific issue raised here:

http://forums.activiti.org/content/tables-missing-components-engine-history-identity

With tablePrefixIsSchema set to true one can have the prefix equal to the schema without having to turn off the version checking.

Thanks
Florian