[SOLVED] Liquibase create/alter DB user

Liquibase create/alter DB user

I want to create and alter DB user using Liquibase. Following are the SQL queries to do so for Oracle DB.

change password:
ALTER USER ADMIN IDENTIFIED BY ${user.password};

create user:
CREATE USER appuser IDENTIFIED BY ${user.password};

I want to make the password parameterized, but changelog Property Substitution is not available in SQL format.

I am choosing XML as the fallback option, but not able to convert above queries into Liquibase XML format. I need help in converting alter/create user queries into XML.

Solution

You can use the SQL tag inside XML changeset and write any SQL query withing the tag as follows:

<?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"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet author="authorName" id="some-unique-id" dbms="${dbType}">
       <sql endDelimiter=";" splitStatements="true" stripComments="true">
            **My SQL query/ transactional logic goes here**
            CREATE USER appuser IDENTIFIED BY ${user.password};
       </sql>
    </changeSet>
    
</databaseChangeLog>

and then you can use Liquibase changelog property substitution with the password value. Using SQL tag will allow you to execute any SQL query (ofcourse in proper syntax) without you having to worry about the XML syntax for liquibase changeset.

For more details on property substitution, have a look at my answer on this post