Handling Secrets Safely in SQLcl Projects Deployments

TL;DR: for security reasons you cannot possibly store sensitive information in Git. There have been too many occasions where API keys, passwords, or other configuration data has been exposed. Abused API keys in particular can become a cost nightmare, but so is any working username and password combination in the wrong hands. Security must not come as an afterthought, it’s got to be built into the design right from the start.

Sometimes however, you really need to process sensitive information in your code.

This article tries to show you how to do that by covering a common use case: how to provision a schema as part of the SQLcl Projects workflow. To do that, a new database account must be created; as you know, you typically provide a username and a password to the create user command. Caveat: the password mustn’t be stored in your app at all. So what do you do?

Liquibase offers a pretty nifty feature: property substitution. Rather than hard-coding your changelogs, you add variables that are replaced at runtime.

This post is based on the work championed by Dan McGhan and Neil Fernandez. Huge kudos to both of them for their input and blog posts that acted as inspiration. If you haven’t read them yet, you definitely should:

They are pretty useful to better understand the rest of this article.

What is Property Substitution?

The Liquibase documentation describes property substitution as follows:

By substituting values for replacement tokens in the format of ${property-name}, you can use the same changesets to reflect small environmental changes.

For example, your tablespace name in Oracle may differ from environment to environment, but you want to only write one create table changeset that can be used in all of your environments.

Right, ok, so instead of hard-coding properties in changelogs, you can use variables: neat!

How does that work in SQLcl Projects?

SQLcl projects benefits from the feature as well, as explained by the references in the post’s preamble. For example, if you chose to set stage.substituteSchemas to true, which you most probably should, two things will happen automatically:

dist/env/default.properties is created. It contains the name of the schema(s) your application’s objects have been exported from.
files in the releases directory don’t feature a hard-coded owner in their respective changelog, the owner is replaced by a substitution

More to the point, you’ll find a line like this in default.properties if you exported emily’s schema

parameter.emily=emily

Changelogs in the dist/releases directory tree look similar to this create table statement:

			
create table ${emily}.t1 (
    id number generated always as identity minvalue 1 
    maxvalue 9999999999999999999999999999 increment by 1 
    cache 20 noorder nocycle nokeep
    noscale not null enable,
    vc varchar2(255 byte)
);
alter table ${emily}.t1 add primary key ( id )
    using index enable;

		

But how does SQLcl projects know which schema to deploy to? It is told via the defaults-file argument. If you have a look at dist/install.sql you find this call to Liquibase Update:

lb update -log -changelog-file releases/main.changelog.xml -search-path "." -def env/default.properties

That’s how property substitution works in SQLcl Projects. Please refer to the earlier posts for further details.

Using property substitution for administrative tasks in SQLcl Projects

There are typically 2 strategies deploying apps with SQLcl Projects:

App user installation
Admin user installation

Sometimes it makes sense to deploy an application into a database using a privileged account. There are many reasons for that: necessary grants have to be provided, or the app is deployed against multiple schemas.

If you’re going down the admin installation path and you have the necessary approvals to do so, you might as well do some setup work in Liquibase. As per the intro, a common requirement is schema provisioning. You don’t have to do that in Liquibase changelogs, but since everything else that affects your app is done via Liquibase you might as well go full circle.

Revised install.sql

install.sql is the main driver behind the deployment of each SQLcl Projects artifact. Initially it concerns itself with the app deployment, but as the inline comments suggest, you can extend its functionality. Here is an updated install.sql file used for this article.

			
-- SQLcl uses the SQLCL engine for formatted sql changelog not the JDBC engine
-- By default, a project will not use substitution variables and allows blank
-- lines in sql statements.
set define off
set sqlblanklines on
-- Prechecks modifiable helper
-- Check running with SQLcl
-- Check minimum DB version
-- Check character set
--@utils/prechecks.sql
-- SLOT
-- Custom pre Liquibase code here (perhaps creation of a schema)
-- This is MINIMAL pre setup, everything that can go through Liquibase - SHOULD
prompt "Creating the schema using a SQL changelog"
lb update -log -changelog-file utils/schema-provisioning.sql -search-path "." -def env/default.properties
-- prompt "Creating the schema using a XML changelog"
-- lb update -log -changelog-file utils/schema-provisioning.xml -search-path "." -def env/default.properties
-- Kick off Liquibase
prompt "Installing/updating schemas"
lb update -log -changelog-file releases/main.changelog.xml -search-path "." -def env/default.properties
-- @utils/recompile.sql

		

The instructions in lines 23-25 are standard. What’s new is the section in lines 17-18, referencing a changelog in SQL format. If you prefer XML changelogs you can use the lb update command as per line 21.

Every schema provisioning script typically requires the creation of a user (unless it exists, in which case the password might need updating), quota assignment, grants, etc. Here is a very basic schema setup changelog in SQL format

			
-- liquibase formatted sql
-- changeset martin:sqltest
declare
    l_cnt number;
begin
    select
        count(*) into l_cnt
    from
        dba_users
    where
        username = upper('${emily}');
    if l_cnt = 1 then
        execute immediate 'alter user ${emily} identified by "${DB_PASSWORD}"';
    elsif l_cnt = 0 then
        execute immediate 'create user ${emily} identified by "${DB_PASSWORD}" quota 1g on users';
    else
        raise_application_error(-20001, 'something went terribly wrong looking for ${emily}');
    end if;
end;
/
grant CREATE MLE to ${emily};
grant CREATE PROPERTY GRAPH to ${emily};
grant EXECUTE DYNAMIC MLE to ${emily};
grant CREATE JOB to ${emily};
grant CREATE DIMENSION to ${emily};
grant CREATE INDEXTYPE to ${emily};
grant CREATE OPERATOR to ${emily};
grant CREATE TYPE to ${emily};
grant CREATE MATERIALIZED VIEW to ${emily};
grant CREATE TRIGGER to ${emily};
grant CREATE PROCEDURE to ${emily};
grant CREATE SEQUENCE to ${emily};
grant CREATE VIEW to ${emily};
grant CREATE SYNONYM to ${emily};
grant CREATE CLUSTER to ${emily};
grant CREATE TABLE to ${emily};
grant CREATE SESSION to ${emily};

		

As you can see the SQL script makes proper use of the ${emily} substitution variable. But what about ${DB_PASSWORD}? Where does that come from? The same changelog can be written in XML, too, there is an example in the appendix.

The answer is surprisingly simple: use environment variables. So before you invoke SQLcl, export sensitive environment variables you need to set like DB_PASSWORD and anything else that’s sensitive. Things like a user quota can easily go into the defaults.properties file, or an environment specific properties file (refer to Neil’s blog posts for details).

			
$ export DB_PASSWORD=getVaultPassword("ocid1.vaultsecret.oc1...ha")
$ sql -name dev_db_system
...

With the password exported and SQLcl started it is time to deploy the app (the step where the artifact was generated has been omitted)

			
SYSTEM@2326000000 🚥 > project deploy -file artifact/envvars-1.0.0.zip
Starting the migration...
Creating the schema using SQL changelog
--Starting Liquibase at 2026-05-25T21:37:22.750634 using Java 21.0.11 (version 4.33.0 #0 built at 2025-12-09 17:47+0000)
Running Changeset: utils/schema-provisioning.sql::sqltest::martin
PL/SQL procedure successfully completed.
...
Grant succeeded.
UPDATE SUMMARY
Run:                          1
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            1
Liquibase: Update has been successful. Rows affected: 0
Produced logfile: sqlcl-lb-1779737842749.log
Operation completed successfully.
Installing/updating schemas
--Starting Liquibase at 2026-05-25T21:37:23.728021 using Java 21.0.11 (version 4.33.0 #0 built at 2025-12-09 17:47+0000)
Running Changeset: release-1/emily/tables/t1.sql::1779375012151::EMILY
Table EMILY.T1 created.
Table EMILY.T1 altered.
Running Changeset: release-1/emily/mle_modules/demo_module.sql::1779375012128::EMILY
MLE module EMILY.DEMO_MODULE compiled
UPDATE SUMMARY
Run:                          2
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            2
Liquibase: Update has been successful. Rows affected: 0
Produced logfile: sqlcl-lb-1779737843727.log
Operation completed successfully.
SYSTEM@2326000000 🚥 > lb history
--Starting Liquibase at 2026-05-25T21:37:39.614279 using Java 21.0.11 (version 4.33.0 #0 built at 2025-12-09 17:47+0000)
Liquibase History for jdbc:oracle:thin:@//localhost:1521/freepdb1
+---------------+------------------+---------------------------------------------+------------------+---------------+-----+
| Deployment ID | Update Date      | Changelog Path                              | Changeset Author | Changeset ID  | Tag |
+---------------+------------------+---------------------------------------------+------------------+---------------+-----+
| 9737606313    | 5/25/26, 7:37 PM | utils/schema-provisioning.sql               | martin           | sqltest       |     |
+---------------+------------------+---------------------------------------------+------------------+---------------+-----+
| 9737606313    | 5/25/26, 7:37 PM | release-1/emily/tables/t1.sql               | EMILY            | 1779375012151 |     |
+---------------+------------------+---------------------------------------------+------------------+---------------+-----+
| 9737606313    | 5/25/26, 7:37 PM | release-1/emily/mle_modules/demo_module.sql | EMILY            | 1779375012128 |     |
+---------------+------------------+---------------------------------------------+------------------+---------------+-----+

		

That’s it! This is the schema created and app deployed.

Summary

In summary, SQLcl Projects and Liquibase property substitution provide a secure and flexible way to handle administrative deployment tasks without exposing secrets in source control. By combining substitution variables with environment variables, you can provision schemas, manage grants, and deploy applications safely while keeping sensitive information such as passwords outside your project artifacts.

Appendix: XML changelog

The SQL changelog you saw earlier can be expressed as XML, too, like so:

			
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
   xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
 
   <changeSet
    id="xmltest"
    author="martin"
    failOnError="true"
        runOnChange="false"
        runAlways="false">
        <n0:runOracleScript objectName="schema-provisioning" ownerName="${emily}" sourceType="STRING">
            <n0:source><![CDATA[
declare
    l_cnt number;
begin
    select
        count(*) into l_cnt
    from
        dba_users
    where
        username = upper('${emily}');
    if l_cnt = 1 then
        execute immediate 'alter user ${emily} identified by "${DB_PASSWORD}"';
    elsif l_cnt = 0 then
        execute immediate 'create user ${emily} identified by "${DB_PASSWORD}" quota 1g on users';
    else
        raise_application_error(-20001, 'something went terribly wrong looking for ${emily}');
    end if;
end;
/
grant CREATE MLE to ${emily};
grant CREATE PROPERTY GRAPH to ${emily};
grant EXECUTE DYNAMIC MLE to ${emily};
grant CREATE JOB to ${emily};
grant CREATE DIMENSION to ${emily};
grant CREATE INDEXTYPE to ${emily};
grant CREATE OPERATOR to ${emily};
grant CREATE TYPE to ${emily};
grant CREATE MATERIALIZED VIEW to ${emily};
grant CREATE TRIGGER to ${emily};
grant CREATE PROCEDURE to ${emily};
grant CREATE SEQUENCE to ${emily};
grant CREATE VIEW to ${emily};
grant CREATE SYNONYM to ${emily};
grant CREATE CLUSTER to ${emily};
grant CREATE TABLE to ${emily};
grant CREATE SESSION to ${emily};
]]>
            </n0:source>
        </n0:runOracleScript>
    </changeSet>
</databaseChangeLog>

		

Note that this snippet uses the runOracleScript ( 🔗 docs) changeset. It executes the SQL script using the SQLcl engine and therefore supports all SQLcl commands. Property substitution does not work for file and URL resources, though.