Allow multi-schema database installations without need for elevated (DBA) rights

You have done a huge amount of good work with Liquibase.
So far, I miss only one feature.
Background (terms and examples are Oracle centric):
Most of "logical databases" (that I have been dealing with) do not consist of 1 schema only.
There are many schemas and usually there are cross dependencies between schemas.
More precisely, there are 2 types of schemas:
  1. Schemas (include tables, views, procedures etc) with cross-dependencies
  2. Users for application servers and for batch processes (include received object grants and optionally synonyms)
Bottom line: cross dependencies mean that there is no way to install schemas using schema based "changelog.xml" files and fixed sequential order of installation.
Expectation for database installation procedure:
  1. first, DBA will create everything that is not application specific: schemas, users, tablespaces, system grants etc
  2. Liquibase installs "changelog.xml" (which contains all schemas of "logical database") with one run.
  3. for each next release Liquibase installs "changelog.xml" (which contains all schemas of "logical database") with one run.
Current situation / Issues with alternatives:
  1. Recommendation: use DBA rights for installation. Unfortunately, there exist SQL commands that can only be executed by schema owner. For example: CREATE DATABASE LINK ... You may have DBA rights but you still can not create database link into another schema. There is (smaller) issue with grants as well. If DBA executes "grant select on schema1.table to schema2" then grantor is registered as DBA. From application point of view DBA is nobody.
  2. Security issue. DBA right means that you can do everything in that physical database Today, many "logical databases" are consolidated into one "physical database". It means, each "logical database" developer/deployer/installer have DBA rights in physical database. They can see and change everything in all "logical databases" This is totally NOT acceptable for Security Officers / IT Risks and for DBA.
Need:
For multi-schema "changelog.xml" installation, each schema needs its own connection to database to have/get proper permissions and proper context for SQL execution.
"Proper permission" means here as well that permissions are strictly limited to schema owner permissions. DBA rights must not be available unless it is intentionally granted.
Proposal:
I would like to have optional possibility to specify schema name for each "changeSet".
For example this way:
...
...
...
...
...
In configuration there is need for one entry for each referred schema name to set password.
"DB_USER_1" can also be alias and in configuration there will be matching database username and password.
I still expect that there is single central DATABASECHANGELOG table for one "logical database" as it is already today.
PS:
We would like to use Liquibase for all database deployments on Oracle databases.
Today, we can do it for single-schema deployments only. Unfortunately, this cover ~20% of cases.
.
I think this proposed (in my opinion small) improvement would remove last big barrier to start using Liquibase for the rest 80% of cases as well.
Thank you in advance,
Marek Läll

I think that is a common problem and being able to control who executes each changeset is a good option. 

It would make the output of updateSql more complex because you wouldn’t be able to execute that directly, but we could add comments in there when there is a change to who is running the changeSet. You couldn’t collect the sql for each user in a separate file because the order can matter, it will still need to be a single sql file, just with comments that need to be watched for.

Rather than setting “runAs”  where you would define the username, I’m wondering if it should be “connection” where you set it to a key you define that maps to a separate connection which can have it’s own username and URL. For example:

...
...
...
...
That would allow cross-database changeSets and also an abstraction on the usernames so if your usernames are different between dev and qa for example, it can still work.
It would add some confusion on what the default schema/catalog is. For example, if changeSet 1 above is a createTable and define a schemaName attribute, does liquibase create the table in the sys schema or the schema that the "default" connection uses?
Create a Jira issue and set the fix version to 4.0. I don't think it is a change that I would want to put into the 3.x versions, but it would be a good 4.0 feature.
Nathan

Thank you for answer.

> It would make the output of updateSql more complex

In case of oracle and sql*plus/sqldeveloper it is possible to use connect command like this:

connect user1/pswd1@conn_str1
sql_1;
connect user2/pswd2@conn_str1
sql_2;
sql_3;
connect user1/pswd1@conn_str1
sql_4;

> Rather than setting “runAs”  where you would define the username, I’m wondering if it should be “connection”

“connection” is more general solution and makes that feature even more flexible.

> It would add some confusion on what the default schema/catalog is

I think this behaviour should remain as it is today.
There must be one central schema/catalog.
Default schema/catalog location is “Default connection” which can be overrided by argument “–defaultSchemaName=”

Marek

I created https://liquibase.jira.com/browse/CORE-2085 to track the feature

Nathan

I have 4 db_users (on different oracle instances) and uses Ant to call 4 targets. So I call 4 times liquibase having for each a connection and separate changesets files. As I manage scripts by versions (a folder by version), i can easily see which changes are related.

Moreover I tag each database at each version even if nothing has change for one of them, so I can rollback to any version without error.