Best Practices Liquibase Secure and MySQL GRANT

Liquibase Secure Stored Logic and GRANTs in MySQL 5.7 and MySQL 8.0

As permissions are different for MySQL 5.7 and MySQL 8.0, follow the information for the version that you are using.

Minimum GRANTs for Liquibase Secure with MySQL 5.7 (Standard or RDS)

In MySQL, set the following permissions required to capture procedures, functions, triggers, views, and check constraints:

GRANT SELECT ON mysql.proc TO 'your_user'@'your_host'; GRANT SELECT, SHOW VIEW, CREATE VIEW ON your_db.* TO 'your_user'@'your_host'; GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO 'your_user'@'your_host'; GRANT TRIGGER ON your_db.* TO 'your_user'@'your_host'; GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON your_db.* TO 'your_user'@'your_host'; GRANT PROCESS ON *.* TO 'your_user'@'your_host';

Note: For more information about GRANT statement in MySQL 5.7, which grants privileges to MySQL user accounts, see GRANT Statement.

Minimum GRANTs for Liquibase Secure with MySQL 8.0 (Standard or RDS)

In MySQL, set the following permissions required to capture procedures, functions, triggers, views, and check constraints:

GRANT CREATE ROUTINE, ALTER ROUTINE ON your_db.* TO 'your_user'@'your_host'; GRANT CREATE VIEW, SHOW VIEW ON your_db.* TO 'your_user'@'your_host'; GRANT TRIGGER ON your_db.* to 'your_user'@'your_host'; GRANT ALTER, CREATE, DELETE, DROP, INDEX, INSERT, SELECT ,REFERENCES, UPDATE ON your_db.* TO 'your_user'@'your_host'; GRANT PROCESS ON *.* TO 'your_user'@'your_host'; GRANT SELECT ON *.* TO 'your_user'@'your_host';

Note: For more information about GRANT statement in MySQL 8.0, which grants privileges to MySQL user accounts, see GRANT Statement.

Additionally, there are a few configuration requirements and notes:

• In MySQL 5.7, if a user does not have the SELECT ON mysql.proc permission, Liquibase Secure cannot get procedure or function bodies.

• In MySQL 8.0, if a user does not have the GRANT CREATE ROUTINE and ALTER ROUTINE permissions, Liquibase Secure cannot get procedure or function bodies. The DEFINER—the user who created the procedure or function—can only retrieve stored logic bodies they created if they lack those permissions.

• If a user does not have GRANTs to SELECT and SHOW views, Liquibase Secure cannot access the view definition. Liquibase Secure will also not capture the view configuration for ALGORITHM, DEFINER, or SQL SECURITY. Review the generated changelog and update the view SQL to include the additional configuration manually.

• If the stored logic bodies or views are null due to lack of permissions, Liquibase Secure writes the string [CANNOT READ OBJECT BODY]; to the generated SQL. A changelog with this placeholder is not deployable. To resolve this, either expand your Liquibase Secure connection user permissions or manually edit the changelog to include the stored logic bodies.

• If you use AWS RDS, configure the MySQL instance to allow the creation of stored logic objects. Use a parameter group that sets log_bin_trust_function_creators=1, and restart the MySQL instance to apply the change.

How to See User GRANTs in MySQL 5.7 and MySQL 8.0

Both MySQL 5.7 and MySQL 8.0 have the same syntax for checking user GRANTs:

SHOW GRANTS FOR 'YOUR_USER'@'YOUR_HOST';

Known issues in MySQL 5.7 and MySQL 8.0

Liquibase Secure is not able to snapshot view definitions if the Liquibase Secure user isn't the DEFINER of the view. This issue is caused due to a bug in MySQL, which you can review in the MySQL bug reporting page.

As a workaround, grant the SHOW VIEW permissions to your Liquibase Secure user on a specific view after this view has been created. For example:

GRANT SHOW VIEW ON YOUR_DB.YOUR_VIEW TO 'YOUR_USER'@'YOUR_HOST';

Another way is to configure the Liquibase Secure connection user to be the user that creates views. In this case, the user becomes the DEFINER and can see all objects they create.

For more information, see the CREATE VIEW Statement.