Liquibase migration for Spring Security tables

Spring security default implementation requires to access to a database in order to do its job.

Here is the liquibase migration file for creating these tables.

The changesets 1, 2 and 2a create tables for authentication and authorization.
Changesets 3, 3a, 4, 5, 6 and 6a create tables for domain object security (ACL).
Tha last changeset, 7, insert some data in tables (2 users wioht their roles).

	<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
	http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">
	<changeSet id="1" author="sinossi">
	<comment>Spring Security Authentication - Users Table</comment>
	<createTable tableName="users">
	<column name="username" type="varchar(50)">
	<constraints primaryKey="true" nullable="false" />
	</column>
	<column name="password" type="varchar(50)">
	<constraints nullable="false" />
	</column>
	<column name="enabled" type="BOOLEAN">
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="2" author="sinossi">
	<comment>Spring Security Authentication - Authorities Table</comment>
	<createTable tableName="authorities">
	<column name="username" type="varchar(50)">
	<constraints nullable="false" />
	</column>
	<column name="authority" type="varchar(50)">
	<constraints nullable="false" />
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="2a" author="sinossi">
	<comment>Spring Security Authentication - Authorities Constraints</comment>
	<addPrimaryKey constraintName="pk_authorities" tableName="authorities" columnNames="username,authority" />
	<addForeignKeyConstraint constraintName="fk_authorities_users" baseTableName="authorities"
	baseColumnNames="username" referencedTableName="users" referencedColumnNames="username" />
	</changeSet>
	<changeSet id="3" author="sinossi">
	<comment>Spring Security ACL - Sid Table</comment>
	<createTable tableName="acl_sid">
	<column name="id" type="bigint" autoIncrement="true">
	<constraints primaryKey="true" nullable="false" />
	</column>
	<column name="sid" type="varchar(100)">
	<constraints nullable="false" />
	</column>
	<column name="principal" type="boolean">
	<constraints nullable="false" />
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="3a" author="sinossi">
	<comment>Spring Security ACL - Sid Constraints</comment>
	<addUniqueConstraint constraintName="unique_acl_sid" tableName="acl_sid" columnNames="sid, principal" />
	</changeSet>
	<changeSet id="4" author="sinossi">
	<comment>Spring Security ACL - Class Table</comment>
	<createTable tableName="acl_class">
	<column name="id" type="bigint" autoIncrement="true">
	<constraints primaryKey="true" nullable="false" />
	</column>
	<column name="class" type="varchar(100)">
	<constraints unique="true" nullable="false" />
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="5" author="sinossi">
	<comment>Spring Security ACL - Object Identity Table</comment>
	<createTable tableName="acl_object_identity">
	<column name="id" type="bigint" autoIncrement="true">
	<constraints primaryKey="true" nullable="false" />
	</column>
	<column name="object_id_class" type="bigint">
	<constraints nullable="false" />
	</column>
	<column name="object_id_identity" type="bigint">
	<constraints nullable="false" />
	</column>
	<column name="parent_object" type="bigint" />
	<column name="owner_sid" type="bigint" />
	<column name="entries_inheriting" type="boolean">
	<constraints nullable="false" />
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="5a" author="sinossi">
	<comment>Spring Security ACL - Object Identity Constraints</comment>
	<addUniqueConstraint constraintName="unique_acl_object_identity" tableName="acl_object_identity"
	columnNames="object_id_class, object_id_identity" />
	<addForeignKeyConstraint constraintName="fk_acl_oid_parent" baseTableName="acl_object_identity"
	baseColumnNames="parent_object" referencedTableName="acl_object_identity" referencedColumnNames="id" />
	<addForeignKeyConstraint constraintName="fk_acl_oid_class" baseTableName="acl_object_identity"
	baseColumnNames="object_id_class" referencedTableName="acl_class" referencedColumnNames="id" />
	<addForeignKeyConstraint constraintName="fk_acl_oid_sid" baseTableName="acl_object_identity"
	baseColumnNames="owner_sid" referencedTableName="acl_sid" referencedColumnNames="id" />
	</changeSet>
	<changeSet id="6" author="sinossi">
	<comment>Spring Security ACL - Entry Table</comment>
	<createTable tableName="acl_entry">
	<column name="id" type="bigint" autoIncrement="true">
	<constraints primaryKey="true" nullable="false" />
	</column>
	<column name="acl_object_identity" type="bigint">
	<constraints nullable="false" />
	</column>
	<column name="ace_order" type="bigint">
	<constraints nullable="false" />
	</column>
	<column name="sid" type="bigint">
	<constraints nullable="false" />
	</column>
	<column name="mask" type="integer">
	<constraints nullable="false" />
	</column>
	<column name="granting" type="boolean">
	<constraints nullable="false" />
	</column>
	<column name="audit_success" type="boolean">
	<constraints nullable="false" />
	</column>
	<column name="audit_failure" type="boolean">
	<constraints nullable="false" />
	</column>
	</createTable>
	<modifySql dbms="mysql">
	<append value=" engine innodb" />
	</modifySql>
	</changeSet>
	<changeSet id="6a" author="sinossi">
	<comment>Spring Security ACL - Entry Constraints</comment>
	<addUniqueConstraint constraintName="unique_acl_entry" tableName="acl_entry" columnNames="acl_object_identity, ace_order" />
	<addForeignKeyConstraint constraintName="fk_acl_entry_oid" baseTableName="acl_entry"
	baseColumnNames="acl_object_identity" referencedTableName="acl_object_identity" referencedColumnNames="id" />
	<addForeignKeyConstraint constraintName="fk_entry_sid" baseTableName="acl_entry"
	baseColumnNames="sid" referencedTableName="acl_sid" referencedColumnNames="id" />
	</changeSet>
	<changeSet id="7" author="sinossi">
	<comment>Insert admin user</comment>
	<insert tableName="users">
	<column name="username" value="admin" />
	<!-- md5("admin") = "21232f297a57a5a743894a0e4a801fc3" -->
	<column name="password" value="21232f297a57a5a743894a0e4a801fc3" />
	<column name="enabled" value="1" /> <!-- 1 => true for mysql -->
	</insert>
	<insert tableName="authorities">
	<column name="username" value="admin" />
	<column name="authority" value="ROLE_ADMIN" />
	</insert>
	<insert tableName="authorities">
	<column name="username" value="admin" />
	<column name="authority" value="ROLE_USER" />
	</insert>
	<insert tableName="users">
	<column name="username" value="user" />
	<!-- md5("secret") = "5ebe2294ecd0e0f08eab7690d2a6ee69" -->
	<column name="password" value="5ebe2294ecd0e0f08eab7690d2a6ee69" />
	<column name="enabled" value="1" /> <!-- 1 => true for mysql -->
	</insert>
	<insert tableName="authorities">
	<column name="username" value="user" />
	<column name="authority" value="ROLE_ADMIN" />
	</insert>
	</changeSet>
	</databaseChangeLog>

view raw spring_security_liquibase_changeset.xml hosted with ❤ by GitHub

This migration is customized for MySql database, enforces the InnoDB engine when creates tables, but should work also on other databases.

See also:
Spring Security
Security Database Schema
Liquibase