Regular expression rules format

When using regular expression rules for static code analysis through check -code, the format of the TOML rules files is as follows:

Dialects

The way your regex rule is structured will vary depending on the dialect of SQL in use with your database (different keywords and syntax) so you may need explicitly declare the dialect that this rule is relevant for.

Flyway will identify the variety of SQL relevant to database based on the JDBC connection string and only apply relevant rules (so a rule declared for the Oracle dialect won't be applied when using a PostgreSQL database).

• The TEXT dialect means the rule will be applied to all migrations regardless of the DB type Flyway is configured to use.

passOnRegexMatch

Your regular expression will return one of two values:

• The pattern matched something in the migration
• The pattern did not match anything in the migration

You can modify this to alter whether this flags a violation or not (inverting the logic of the regular expression)

Regular expression considerations

• Does case sensitivity matter to you ? If it doesn't then make the regex rules insensitive too with the prefix (?i)
• You will need to use the Java dialect of regex

File content example

dialects = ["TEXT"]
rules = ["(?i)(^|\\s)TO\\s+DO($|\\s|;)"]
passOnRegexMatch = false
description = "Phrase 'to do' remains in the code"

Built in enterprise rules

Redgate has added a set of rules that are of interest to customers with a larger and more complex database infrastructure.

RX001

DROP TABLE statement

Dialects: All

Dropping a table is likely to result in the loss of data so should be investigated before continuing.

RX002

Attempt to change password

Dialects: Oracle/PostgreSQL/TSQL

Changing passwords through a DB migration is not considered best practice

RX003

TRUNCATE statement used

Dialects: All

This operation is likely to result in a loss of data so should be investigated before continuing

RX004

DROP COLUMN statement used

Dialects: All

This operation is likely to result in a loss of data so should be investigated before continuing

RX005

GRANT TO PUBLIC statement used

Dialects: All

It is not common to access to this degree so should be investigated before continuing

RX006

GRANT WITH GRANT OPTION statement used

Dialects: All

Allows grantee to grant additional permissions and so it becomes difficult to track the scope of permissions

RX007

GRANT WITH ADMIN OPTION statement used

Dialects: All

Allows grantee to grant administrative permissions and so it becomes difficult to control the scope of permissions

RX008

ALTER USER statement used

Dialects: All

Modifies the properties of an existing user and should be investigated before continuing

RX009

GRANT ALL statement used

Dialects: All

It is not common to access to this degree so should be investigated before continuing

RX010

CREATE ROLE statement used

Dialects: All

RX011

ALTER ROLE statement used

This is used to modify user accounts so should be investigated before continuing

Dialects: All

RX012

DROP PARTITION statement used

Dialects: All

RX013

CREATE TABLE statement without a PRIMARY KEY constraint

Dialects: All

RX014

A table has been created but has no MS_Description property added

Dialects: TSQL

This operation is likely to result in a loss of data so should be investigated before continuing