How to Configure an Oracle Service Account for Liquibase Multi-Schema Projects

Applies to: 

• Liquibase Pro
• Liquibase Open Source (Community)

Summary:

Using a service account to run Liquibase simplifies automation jobs by allowing changes to be deployed to all schemas using a single database connection.  The service account will typically own the Liquibase tracking tables.  

Environment Conditions:

• Any version of Liquibase
• Any supported version of Oracle

How to:

The service account must be granted system-level privileges to deploy objects to other schemas. The provided scripts can be used to create a service account and grant the appropriate privileges.  The Liquibase tracking tables will automatically be created in the default tablespace of the service account. You should update the tablespace appropriately for your database.

Step 1: Create a user account

The first step is to determine the user account that is going to be the service account. The account is going to need to have the default table of USERS and an unlimited quota.

Below is an example of creating a liquibase user.

CREATE USER liquibase IDENTIFIED BY 'lb_passwd' DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;

Step 2: Grant needed permissions

Once the user has been created, the following grants need to be applied to the service account.

GRANT CREATE SESSION TO LIQUIBASE;
GRANT CREATE ANY DIRECTORY TO LIQUIBASE;
GRANT CREATE ANY INDEX TO LIQUIBASE;
GRANT CREATE ANY MATERIALIZED VIEW TO LIQUIBASE;
GRANT CREATE ANY PROCEDURE TO LIQUIBASE;
GRANT CREATE ANY SEQUENCE TO LIQUIBASE;
GRANT CREATE ANY SYNONYM TO LIQUIBASE;
GRANT CREATE PUBLIC SYNONYM TO LIQUIBASE;
GRANT CREATE ANY TABLE TO LIQUIBASE;
GRANT CREATE ANY TRIGGER TO LIQUIBASE;
GRANT CREATE ANY TYPE TO LIQUIBASE;
GRANT CREATE ANY VIEW TO LIQUIBASE;
GRANT ALTER ANY INDEX TO LIQUIBASE;
GRANT ALTER ANY MATERIALIZED VIEW TO LIQUIBASE;
GRANT ALTER ANY PROCEDURE TO LIQUIBASE;
GRANT ALTER ANY SEQUENCE TO LIQUIBASE;
GRANT ALTER ANY TABLE TO LIQUIBASE;
GRANT ALTER ANY TRIGGER TO LIQUIBASE;
GRANT ALTER ANY TYPE TO LIQUIBASE;
GRANT DROP ANY DIRECTORY TO LIQUIBASE;
GRANT DROP ANY INDEX TO LIQUIBASE;
GRANT DROP ANY MATERIALIZED VIEW TO LIQUIBASE;
GRANT DROP ANY PROCEDURE TO LIQUIBASE;
GRANT DROP ANY SEQUENCE TO LIQUIBASE;
GRANT DROP ANY SYNONYM TO LIQUIBASE;
GRANT DROP PUBLIC SYNONYM TO LIQUIBASE;
GRANT DROP ANY TABLE TO LIQUIBASE;
GRANT DROP ANY TRIGGER TO LIQUIBASE;
GRANT DROP ANY TYPE TO LIQUIBASE;
GRANT DROP ANY VIEW TO LIQUIBASE;
GRANT SELECT ANY TABLE TO LIQUIBASE;
GRANT INSERT ANY TABLE TO LIQUIBASE;
GRANT UPDATE ANY TABLE TO LIQUIBASE;
GRANT DELETE ANY TABLE TO LIQUIBASE;
GRANT COMMENT ANY TABLE TO LIQUIBASE;
GRANT GRANT ANY OBJECT PRIVILEGE TO LIQUIBASE;

Related Article(s):

Best Practices for Multiple Teams Deploying to the Same Database (Oracle)

How to Configure an Oracle Account for Liquibase Single Schema Projects