Querying a Database from Layer 7 Policy

If you want to service requests through Layer 7 policy logic instead of routing to an application backend then you'll probably want to communicate with a database at some point.

The first thing you should get is a service account with appropriate level of access to the database in question.  The account should be added to the stored passwords in the Layer 7 environment; remember that you will need to use it via a context variable.

Then create a JDBC connection, select your driver, and set the URL appropriately.  The following example connects to the Layer 7 database (assuming the policy is running on your Layer 7 database node) over the default MySql TCP port.

jdbc:mysql://localhost:3306/ssg?sessionVariables=TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The session variables above set the execution context to read only and non-locking; equivalent to MSSql hint "with (nolock)".

In the connection manager set the username of your service account and the context variable for the password (${secpass.[name].plainetext}).

Now your connection is ready to be used to query your database.  Within your policy add the Perform JDBC Query assertion and select the connection you have just created.