spring_security_liquibase_changeset.xml

<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>