Liquibase Pro and MySQL Security Context Limitation

MySQL provides security contexts to determine how stored logic behaves when being executed during a deployment.

The three configuration options are:

  • DEFINER
  • ALGORITHM
  • SQL SECURITY

When you generate a changelog using the Liquibase generateChangeLog command, the generateChangeLog command does not retrieve MySQL security context settings defined for functions, procedures, triggers, or views.

Deploying the changelog may result in the unexpected behavior during stored logic execution due to security context differences. The limitation affects views, procedures, functions, and triggers.

To have the security context defined for functions, procedures, triggers, or views in your changelog file, you need to examine and edit the generated changelog to include security contexts omitted by generateChangeLog.

Note: If there are views, procedures, functions, or triggers that need a security context defined prior to deploying to another database, update the generated SQL to include the context.

An example of the syntax for a procedure with a security context

You can use the same example for a function, however, replace the PROCEDURE object type with FUNCTION.

CREATE
	[OR REPLACE]
	[DEFINER = user]
	PROCEDURE sp_name ([func_parameter[,...]])
	RETURNS type
	[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	[DEFINER = user]
	[SQL SECURITY { DEFINER | INVOKER }]
routine_body

An example of the syntax for a view with a security context

CREATE
	[OR REPLACE]
	[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
	[DEFINER = user]
	[SQL SECURITY { DEFINER | INVOKER }]
	VIEW view_name [(column_list)]
	AS select_statement
	[WITH [CASCADED | LOCAL] CHECK OPTION]

An example syntax for a trigger with a security syntax

CREATE
	[DEFINER = { user | CURRENT_USER }]
	TRIGGER trigger_name trigger_time trigger_event
	ON tbl_name FOR EACH ROW trigger_stmt

Related links