Where to put the database for read-only Oracle user

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

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

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

As a database administrator:

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 a READER:

create or replace synonym PERSON for OWNER.PERSON;

      

Now READER can connect to the database and

SELECT firstname, lastname from PERSON;

      

is doing well.

I would like to merge 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>

      

It works without a hitch. Then add 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>

      

Then 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 amazing: Liquibase tries to find tables in the correct schema, doesn't notice that they already exist, 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 it fails due to insufficient privileges because READER does not have permission to create the table. But I am confused as to why Liquibase thinks it should create tables in the first place, because they are, since OWNER created them. I have verified that the tables do exist and the READER can read and write to them.

What's going on here? Can I say that the educational base "believe me, there are tables there?" Or does lipibase check for existence without considering the schema and catalog? Am I missing an obvious setup?

+3


source to share


1 answer


Seems 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 next week or so.



+1


source







All Articles