Where to put databasechangelog for a Oracle read-only user

Question

This sounds like a silly question but please bear with me.

I have an Oracle database, which is written to by the user OWNER. The database is created with liquibase, and the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables reside in the OWNER schema. So far, so good.

Now i'd like to create a read only user, READER. READER needs read access to the table OWNER.PERSON, but nothing else. Here's what i've done so far:

As database admin:

create user READER identified by "password";
grant create session to READER;  -- necessary for READER to connect to the DB
grant create synonym to READER;  -- this should be all READER needs to see

Then as OWNER:

grant select on PERSON to READER;

and finally, as READER:

create or replace synonym PERSON for OWNER.PERSON;

Now READER can connect to the database and

SELECT firstname, lastname from PERSON;

succeeds.

I'd like to liquify this. So in owner.xml i say:

<changeSet author="me" id="owner_grants">
  <preConditions onFail="CONTINUE">
    <dbms type="oracle"/>
  </preConditions>
  <sql>
    grant insert,select,update,delete on DATABASECHANGELOG to READER;
    grant insert,select,update,delete on DATABASECHANGELOGLOCK to READER;
    grant select on PERSON to READER;
  </sql>
  <rollback>
    revoke insert,select,update,delete on DATABASECHANGELOG from READER;
    revoke insert,select,update,delete on DATABASECHANGELOGLOCK from READER;
    revoke select on PERSON from READER;
  </rollback>
</changeSet>

This works without a hitch. Next, i add the synonym definitions:

<changeSet author="me" id="reader_synonyms">
  <preConditions onFail="CONTINUE">
    <dbms type="oracle"/>
  </preConditions>
  <sql>
    create or replace synonym PERSON for OWNER.PERSON;
  </sql>
  <rollback>
    drop synonym PERSON;
  </rollback>
</changeSet>

Next i set --liquibaseSchemaName and --liquibaseCatalogName in the call:

liquibase \
  --username=READER \
  --password=password \
  --url=jdbc:to:oracle \
  --defaultSchemaName=READER \
  --driver=com.oracle.jdbc.OracleDriver \
  --changeLogFile=reader.xml \
  --liquibaseSchemaName=OWNER \
  --liquibaseCatalogName=OWNER \
  updateSQL

The result is surprising: liquibase tries to locate the tables in the correct schema, fails to notice they are already there, and tries to create them:

SET DEFINE OFF;

-- Create Database Lock Table
CREATE TABLE OWNER.DATABASECHANGELOGLOCK (ID NUMBER(10) NOT NULL, LOCKED NUMBER(1) NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR2(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

-- Lock Database
-- Create Database Change Log Table
CREATE TABLE OWNER.DATABASECHANGELOG (ID VARCHAR2(255) NOT NULL, AUTHOR VARCHAR2(255) NOT NULL, FILENAME VARCHAR2(255) NOT NULL, DATEEXECUTED TIMESTAMP NOT NULL, ORDEREXECUTED NUMBER(10) NOT NULL, EXECTYPE VARCHAR2(10) NOT NULL, MD5SUM VARCHAR2(35), DESCRIPTION VARCHAR2(255), COMMENTS VARCHAR2(255), TAG VARCHAR2(255), LIQUIBASE VARCHAR2(20));

and this fails because of insufficient privileges, because READER doesn't have create table permission. But i am confused as to why liquibase thinks it has to create the tables in the first place, because they are there, since OWNER created them. I checked that the tables do indeed exist, and READER can read from and write to them.

What's going on here? Can i tell liquibase "trust me, the tables are there?" Or does liquibase check for existence without taking schema and catalog into account? Did i miss an obvious setting?

Nathan Voxland Nathan Voxland · Accepted Answer · 2014-10-22T20:06:53

It looks like a bug. I created https://liquibase.jira.com/browse/CORE-2087 with your information, and the fix will be in Liquibase 3.3.0 due out in the next week or so.

Where to put databasechangelog for a Oracle read-only user

1 Answers