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.

Severity

The severity field controls how violations of this rule are handled:

• error - Violations will cause the check command to fail when check.code.strictMode is enabled
• warning - Violations will be reported but will not cause the operation to fail
• disabled - The rule will be ignored and no violations will be reported

The disabled severity level allows you to temporarily disable specific rules without removing them from your configuration.

passOnRegexMatch [Deprecated]

The parameter is deprecated in Regex Code Analysis and will be ignored in future releases. Regex matches will always be treated as violations. If your previous configuration relied on passOnRegexMatch = true (i.e. expecting the pattern to be present), you will need to invert your Regex to match cases where the pattern is absent instead.

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"
severity = "error"

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

It is a good practice to include a description in the MS_Description extended property to document the purpose of a table.