Liquibase Pro and MariaDB GRANT Best Practices

Liquibase Pro supports native MariaDB Stored Logic for all Liquibase operations. However, the MariaDB Oracle-compatible PL/SQL stored logic objects are not supported for the Liquibase generate-changelog or diff-changelog operations. The generate-changelog and diff-changelog commands are unable to correctly generate the SQL to recreate Oracle-compatible PL/SQL stored logic objects. Any changelogs that contain the malformed SQL for Oracle-compatible PL/SQL stored logic objects will be unable to deploy without an error.

You can still deploy Oracle-compatible PL/SQL stored logic you created using a Liquibase formatted SQL changelog or the sqlFile Change Type in a Liquibase XML changelog.

To deploy an Oracle-compatible stored logic from an XML changelog, use the sqlFile Change Type, and then reference an SQL file which includes the Oracle-compatible stored logic SQL. The SQL file referenced by the sqlFile Change Type needs to have the SET configured to SET sql_mode=oracle at the beginning of the script and SET sql_mode=" " at the end of the script.

The SQL, which is directly written in a formatted SQL changelog, must also include the SET commands at the beginning and the end of the stored logic definition. If you don’t include the SET statements, Liquibase won’t deploy an update that has native MariaDB stored logic.

Liquibase Pro Stored Logic and GRANTs in MariaDB 10.4

In MariaDB, configure the following settings and permissions required to capture procedures, functions, triggers, views, and check constraints:

Server-side Settings (Standard)

set global log_bin_trust_function_creators=1;
set global automatic_sp_privileges=1;

Server-side Settings for an AWS Instance of MariaDB 10.4

The user-level GRANTs are the same for MariaDB 10.4 on AWS and the local instances. However, due to AWS's security policies, configure the server-side settings in a MariaDB parameter group, and apply them to the instance with a reboot.

Note: For more information, see Working with DB parameter groups.

Minimum GRANTs for Liquibase Pro and MariaDB 10.4 (Standard and AWS)

GRANT CREATE ROUTINE, ALTER ROUTINE ON database.* TO 'user'@'hostname';
GRANT CREATE VIEW, SHOW VIEW ON database.* to 'user'@'hostname';
GRANT TRIGGER ON database.* to 'user'@'hostname';
GRANT ALTER,CREATE,DELETE,DROP,INDEX,INSERT,SELECT,REFERENCES,UPDATE on database.* to 'user'@'hostname';
GRANT SELECT ON mysql.proc TO 'user'@'hostname';

How to See User GRANTs in MariaDB 10.4

You can check user GRANTs in MariaDB 10.4 by running the following commands:

USE YourDatabaseNameGoesHere;
SHOW GRANTS FOR 'your_username'@'your_hostname'