Query Execution Policy

Query Execution Policy in Bold Reports^® combines Security Policy and Permission Policy to validate and secure query execution. These policies help ensure that only approved queries are executed, reducing the risk of unauthorized access and potential security issues.

How Query Execution Policy Works

When enabled, Query Execution Policy validates incoming queries using both high-level permissions and granular command restrictions. Each query is checked in a specific order against a range of policy nodes to decide whether it should be executed, blocked, or flagged for review.

Configure Query Execution Policy

To enable Query Execution Policy, configure the IReportHelperSettings interface in your Report Designer controller, such as ReportingAPIController.cs.

1. Inherit the IReportHelperSettings interface in your ReportingAPIController class:

   public class ReportingAPIController : Controller, IReportDesignerController, IReportHelperSettings

2. Implement the InitializeSettings method in the controller to define SecurityOptions settings:

   [NonAction]
   public void InitializeSettings(ReportHelperSettings helperSettings)
   {
       helperSettings.SecurityOptions = new SecurityOptions
       {
           QueryExecutionPolicy = new QueryExecutionPolicy
           {
               Enabled = true,
               PermissionType = PermissionType.ReadOnly,
               IgnoredProviders = new List<string> { "Snowflake", "PostgreSQL" },
               PermissionOverrides = new PermissionOverrides
               {
                   AllowedCommands = new List<string>
                   {
                       "CREATE TEMP TABLE",
                       "CREATE GLOBAL TEMPORARY TABLE"
                   },
                   RestrictedCommands = new List<string>
                   {
                       "DROP STAGE",
                       "DROP TABLE"
                   },
                   RestrictedKeywords = new List<string>
                   {
                       "waitfor",
                       "delay"
                   }
               }
           }
       };
   }

Query Execution Policy

1. Enabled

• Purpose: Globally toggle Query Execution Policy validation for all incoming queries.
• Behavior when enabled: All configured policies are enforced. Unauthorized or non-compliant queries will be blocked with a validation message.
• Behavior when disabled: Query Execution Policy validation is not enforced. Queries are executed without policy restrictions.

2. Permission Types

• Overview: Defines the base set of commands and actions permitted to execute.
• Effect: Determines whether queries are allowed to run ReadOnly, ReadWrite, or Unrestricted commands. See Permission Types for detailed breakdowns.

3. Ignored Providers

• Purpose: Specify data providers that should bypass Query Execution Policy checks for specific scenarios. If a data provider is listed here, both Security Policy and Permission Policy checks are not enforced for that provider. See Supported Providers.
• Configuration: Set provider names or connection identifiers to ignore in policy settings.

4. Permission Overrides

• Defines global command restrictions and allowances that apply across the Query Execution Policy. Permission overrides do not bypass Security Policy restrictions.
• Includes Restricted Commands, Restricted Keywords, and Allowed Commands. See Permission Overrides for detailed configuration options.

Query Evaluation Order

Queries are validated in the following order, with security protections enforced first:

1. Dangerous Commands: Blocked immediately if the query contains any dangerous commands. See Dangerous Commands for detailed breakdowns.
2. Injection Patterns: If the query matches a known dangerous injection pattern, it is blocked. See Injected Patterns
3. Restricted Commands: Blocked if the query contains any restricted commands, regardless of permission type.
4. Restricted Keywords: Blocked if the query contains any restricted keywords.
5. Allowed Commands: The query is permitted if it only uses allowed commands (unless blocked for security reasons).
6. Permission Types: The configured Permission Type (ReadOnly, ReadWrite, Unrestricted) is evaluated last; if the command is permitted by type, it is allowed unless blocked above.

Supported Providers

These are the supported providers for Query Execution Policy:

Dataconnectors: SQL, MySQL, Oracle, PostgreSQL, SSAS, Snowflake, GoogleBigQuery, ODBC, Oledb