sql_server_va.pt

name "Azure SQL Servers Without Vulnerability Assessment (VA) Enabled"
rs_pt_ver 20180301
type "policy"
short_description "Reports any SQL server instances that do not have Vulnerability Assessment (VA) enabled. See the [README](https://github.com/flexera-public/policy_templates/tree/master/security/azure/sql_server_va) and [docs.rightscale.com/policies](https://docs.rightscale.com/policies/) to learn more."
long_description ""
category "Security"
severity "high"
default_frequency "daily"
info(
  version: "3.0.1",
  provider: "Azure",
  service: "SQL",
  policy_set: "CIS",
  cce_id: "",
  benchmark_control: "4.2.2",
  benchmark_version: "1.4.0",
  cis_controls: "[\"7.5v8\", \"7.6v8\", \"3.1v7\"]",
  nist: "CA-2",
  hide_skip_approvals: "true"
)

###############################################################################
# Parameters
###############################################################################

parameter "param_email" do
  type "list"
  category "Policy Settings"
  label "Email Addresses"
  description "A list of email addresses to notify."
  default []
end

parameter "param_azure_endpoint" do
  type "string"
  category "Policy Settings"
  label "Azure Endpoint"
  description "Select the API endpoint to use for Azure. Use default value of management.azure.com unless using Azure China."
  allowed_values "management.azure.com", "management.chinacloudapi.cn"
  default "management.azure.com"
end

parameter "param_subscriptions_allow_or_deny" do
  type "string"
  category "Filters"
  label "Allow/Deny Subscriptions"
  description "Allow or Deny entered Subscriptions. See the README for more details."
  allowed_values "Allow", "Deny"
  default "Allow"
end

parameter "param_subscriptions_list" do
  type "list"
  category "Filters"
  label "Allow/Deny Subscriptions List"
  description "A list of allowed or denied Subscription IDs/names. See the README for more details."
  default []
end

parameter "param_regions_allow_or_deny" do
  type "string"
  category "Filters"
  label "Allow/Deny Regions"
  description "Allow or Deny entered regions. See the README for more details."
  allowed_values "Allow", "Deny"
  default "Allow"
end

parameter "param_regions_list" do
  type "list"
  category "Filters"
  label "Allow/Deny Regions List"
  description "A list of allowed or denied regions. See the README for more details."
  default []
end

parameter "param_exclusion_tags" do
  type "list"
  category "Filters"
  label "Exclusion Tags"
  description "Cloud native tags to ignore resources that you don't want to produce recommendations for. Enter the Key name to filter resources with a specific Key, regardless of Value, and enter Key==Value to filter resources with a specific Key:Value pair. Other operators and regex are supported; please see the README for more details."
  default []
end

parameter "param_exclusion_tags_boolean" do
  type "string"
  category "Filters"
  label "Exclusion Tags: Any / All"
  description "Whether to filter instances containing any of the specified tags or only those that contain all of them. Only applicable if more than one value is entered in the 'Exclusion Tags' field."
  allowed_values "Any", "All"
  default "Any"
end

###############################################################################
# Authentication
###############################################################################

credentials "auth_azure" do
  schemes "oauth2"
  label "Azure"
  description "Select the Azure Resource Manager Credential from the list."
  tags "provider=azure_rm"
end

credentials "auth_flexera" do
  schemes "oauth2"
  label "Flexera"
  description "Select Flexera One OAuth2 credentials"
  tags "provider=flexera"
end

###############################################################################
# Pagination
###############################################################################

pagination "pagination_azure" do
  get_page_marker do
    body_path "nextLink"
  end
  set_page_marker do
    uri true
  end
end

###############################################################################
# Datasources & Scripts
###############################################################################

# Get applied policy metadata for use later
datasource "ds_applied_policy" do
  request do
    auth $auth_flexera
    host rs_governance_host
    path join(["/api/governance/projects/", rs_project_id, "/applied_policies/", policy_id])
    header "Api-Version", "1.0"
  end
end

datasource "ds_azure_subscriptions" do
  request do
    auth $auth_azure
    pagination $pagination_azure
    host $param_azure_endpoint
    path "/subscriptions/"
    query "api-version", "2020-01-01"
    header "User-Agent", "RS Policies"
    # Header X-Meta-Flexera has no affect on datasource query, but is required for Meta Policies
    # Forces `ds_is_deleted` datasource to run first during policy execution
    header "Meta-Flexera", val($ds_is_deleted, "path")
    # Ignore status 400, 403, and 404 which can be returned in certain (legacy) types of Azure Subscriptions
    ignore_status [400, 403, 404]
  end
  result do
    encoding "json"
    collect jmes_path(response, "value[*]") do
      field "id", jmes_path(col_item, "subscriptionId")
      field "name", jmes_path(col_item, "displayName")
      field "state", jmes_path(col_item, "state")
    end
  end
end

datasource "ds_azure_subscriptions_filtered" do
  run_script $js_azure_subscriptions_filtered, $ds_azure_subscriptions, $param_subscriptions_allow_or_deny, $param_subscriptions_list
end

script "js_azure_subscriptions_filtered", type: "javascript" do
  parameters "ds_azure_subscriptions", "param_subscriptions_allow_or_deny", "param_subscriptions_list"
  result "result"
  code <<-EOS
  if (param_subscriptions_list.length > 0) {
    result = _.filter(ds_azure_subscriptions, function(subscription) {
      include_subscription = _.contains(param_subscriptions_list, subscription['id']) || _.contains(param_subscriptions_list, subscription['name'])

      if (param_subscriptions_allow_or_deny == "Deny") {
        include_subscription = !include_subscription
      }

      return include_subscription
    })
  } else {
    result = ds_azure_subscriptions
  }
EOS
end

datasource "ds_azure_sql_servers" do
  iterate $ds_azure_subscriptions_filtered
  request do
    auth $auth_azure
    pagination $pagination_azure
    host $param_azure_endpoint
    path join(["/subscriptions/", val(iter_item, "id"), "/providers/Microsoft.Sql/servers"])
    query "api-version", "2022-02-01-preview"
    header "User-Agent", "RS Policies"
    ignore_status [400, 403, 404]
  end
  result do
    encoding "json"
    collect jmes_path(response, "value") do
      field "id", jmes_path(col_item, "id")
      field "kind", jmes_path(col_item, "kind")
      field "region", jmes_path(col_item, "location")
      field "name", jmes_path(col_item, "name")
      field "tags", jmes_path(col_item, "tags")
      field "fqdn", jmes_path(col_item, "properties.fullyQualifiedDomainName")
      field "state", jmes_path(col_item, "properties.state")
      field "version", jmes_path(col_item, "properties.version")
      field "resourceGroup", get(4, split(jmes_path(col_item, "id"), '/'))
      field "subscriptionID", val(iter_item, "id")
      field "subscriptionName", val(iter_item, "name")
    end
  end
end

datasource "ds_azure_sql_servers_tag_filtered" do
  run_script $js_azure_sql_servers_tag_filtered, $ds_azure_sql_servers, $param_exclusion_tags, $param_exclusion_tags_boolean
end

script "js_azure_sql_servers_tag_filtered", type: "javascript" do
  parameters "ds_azure_sql_servers", "param_exclusion_tags", "param_exclusion_tags_boolean"
  result "result"
  code <<-EOS
  comparators = _.map(param_exclusion_tags, function(item) {
    if (item.indexOf('==') != -1) {
      return { comparison: '==', key: item.split('==')[0], value: item.split('==')[1], string: item }
    }

    if (item.indexOf('!=') != -1) {
      return { comparison: '!=', key: item.split('!=')[0], value: item.split('!=')[1], string: item }
    }

    if (item.indexOf('=~') != -1) {
      value = item.split('=~')[1]
      regex = new RegExp(value.slice(1, value.length - 1))
      return { comparison: '=~', key: item.split('=~')[0], value: regex, string: item }
    }

    if (item.indexOf('!~') != -1) {
      value = item.split('!~')[1]
      regex = new RegExp(value.slice(1, value.length - 1))
      return { comparison: '!~', key: item.split('!~')[0], value: regex, string: item }
    }

    // If = is present but none of the above are, assume user error and that the user intended ==
    if (item.indexOf('=') != -1) {
      return { comparison: '==', key: item.split('=')[0], value: item.split('=')[1], string: item }
    }

    // Assume we're just testing for a key if none of the comparators are found
    return { comparison: 'key', key: item, value: null, string: item }
  })

  if (param_exclusion_tags.length > 0) {
    result = _.reject(ds_azure_sql_servers, function(resource) {
      resource_tags = {}
      if (typeof(resource['tags']) == 'object') { resource_tags = resource['tags'] }

      // Store a list of found tags
      found_tags = []

      _.each(comparators, function(comparator) {
        comparison = comparator['comparison']
        value = comparator['value']
        string = comparator['string']
        resource_tag = resource_tags[comparator['key']]

        if (comparison == 'key' && resource_tag != undefined) { found_tags.push(string) }
        if (comparison == '==' && resource_tag == value) { found_tags.push(string) }
        if (comparison == '!=' && resource_tag != value) { found_tags.push(string) }

        if (comparison == '=~') {
          if (resource_tag != undefined && value.test(resource_tag)) { found_tags.push(string) }
        }

        if (comparison == '!~') {
          if (resource_tag == undefined) { found_tags.push(string) }
          if (resource_tag != undefined && value.test(resource_tag)) { found_tags.push(string) }
        }
      })

      all_tags_found = found_tags.length == comparators.length
      any_tags_found = found_tags.length > 0 && param_exclusion_tags_boolean == 'Any'

      return all_tags_found || any_tags_found
    })
  } else {
    result = ds_azure_sql_servers
  }
EOS
end

datasource "ds_azure_sql_servers_region_filtered" do
  run_script $js_azure_sql_servers_region_filtered, $ds_azure_sql_servers_tag_filtered, $param_regions_allow_or_deny, $param_regions_list
end

script "js_azure_sql_servers_region_filtered", type: "javascript" do
  parameters "ds_azure_sql_servers_tag_filtered", "param_regions_allow_or_deny", "param_regions_list"
  result "result"
  code <<-EOS
  if (param_regions_list.length > 0) {
    result = _.filter(ds_azure_sql_servers_tag_filtered, function(ip) {
      include_db = _.contains(param_regions_list, db['region'])

      if (param_regions_allow_or_deny == "Deny") {
        include_db = !include_db
      }

      return include_db
    })
  } else {
    result = ds_azure_sql_servers_tag_filtered
  }
EOS
end

datasource "ds_sql_servers_with_va" do
  iterate $ds_azure_sql_servers_region_filtered
  request do
    auth $auth_azure
    pagination $pagination_azure
    host $param_azure_endpoint
    path join([val(iter_item, 'id'), "/vulnerabilityAssessments/default"])
    query "api-version", "2020-11-01-preview"
    header "User-Agent", "RS Policies"
    # Ignore status 400, 403, and 404 which can be returned in certain (legacy) types of Azure Subscriptions
    ignore_status [400, 403, 404]
  end
  result do
    encoding "json"
    field "vaState", jmes_path(response, "properties.recurringScans.isEnabled")
    field "vaEmailSubscriptionAdmins", jmes_path(response, "properties.recurringScans.emailSubscriptionAdmins")
    field "vaEmails", jmes_path(response, "properties.recurringScans.emails")
    field "vaStorageContainerPath", jmes_path(response, "properties.storageContainerPath")
    field "id", val(iter_item, "id")
    field "kind", val(iter_item, "kind")
    field "region", val(iter_item, "region")
    field "name", val(iter_item, "name")
    field "tags", val(iter_item, "tags")
    field "fqdn", val(iter_item, "fqdn")
    field "state", val(iter_item, "state")
    field "version", val(iter_item, "version")
    field "resourceGroup", val(iter_item, "resourceGroup")
    field "subscriptionID", val(iter_item, "subscriptionID")
    field "subscriptionName", val(iter_item, "subscriptionName")
  end
end

datasource "ds_bad_azure_sql_servers" do
  run_script $js_bad_azure_sql_servers, $ds_sql_servers_with_va, $ds_applied_policy
end

script "js_bad_azure_sql_servers", type: "javascript" do
  parameters "ds_sql_servers_with_va", "ds_applied_policy"
  result "result"
  code <<-EOS
  bad_servers = _.filter(ds_sql_servers_with_va, function(db) {
    return db['vaStorageContainerPath'] == '' || db['vaStorageContainerPath'] == null || db['vaStorageContainerPath'] == undefined
  })

  result = _.map(bad_servers, function(db) {
    tags = []

    if (typeof(db['tags']) == 'object') {
      tags = _.map(_.keys(db['tags']), function(key) { return key + '=' + db['tags'][key] })
    }

    return {
      accountID: db['subscriptionID'],
      accountName: db['subscriptionName'],
      resourceGroup: db['resourceGroup'],
      id: db['id'],
      resourceName: db['name'],
      kind: db['kind'],
      region: db['region'],
      fqdn: db['fqdn'],
      state: db['state'],
      version: db['version'],
      vaState: db['vaState'],
      tags: tags.join(', '),
      policy_name: ds_applied_policy['name'],
      service: 'Microsoft.Sql'
    }
  })

  // Dummy entry to ensure validation occurs at least once
  result.push({
    accountID: '',
    accountName: '',
    resourceGroup: '',
    id: '',
    resourceName: '',
    kind: '',
    region: '',
    fqdn: '',
    state: '',
    version: '',
    vaState: '',
    tags: '',
    policy_name: '',
    service: ''
  })
EOS
end

###############################################################################
# Policy
###############################################################################

policy "pol_bad_azure_sql_servers" do
  validate_each $ds_bad_azure_sql_servers do
    summary_template "{{ with index data 0 }}{{ .policy_name }}{{ end }}: {{ len data }} Azure SQL Servers Without Vulnerability Assessment (VA) Enabled Found"
    check logic_or($ds_parent_policy_terminated, eq(val(item, "id"), ""))
    escalate $esc_email
    hash_exclude "tags"
    export do
      resource_level true
      field "accountID" do
        label "Subscription ID"
      end
      field "accountName" do
        label "Subscription Name"
      end
      field "resourceGroup" do
        label "Resource Group"
      end
      field "resourceName" do
        label "Resource Name"
      end
      field "id" do
        label "Resource ID"
      end
      field "region" do
        label "Region"
      end
      field "tags" do
        label "Tags"
      end
      field "vaState" do
        label "Vulnerability Assessment Status"
      end
      field "fqdn" do
        label "FQDN"
      end
      field "state" do
        label "State"
      end
      field "version" do
        label "Version"
      end
      field "service" do
        label "Service"
      end
    end
  end
end

###############################################################################
# Escalations
###############################################################################

escalation "esc_email" do
  automatic true
  label "Send Email"
  description "Send incident email"
  email $param_email
end

###############################################################################
# Meta Policy [alpha]
# Not intended to be modified or used by policy developers
###############################################################################

# If the meta_parent_policy_id is not set it will evaluate to an empty string and we will look for the policy itself,
# if it is set we will look for the parent policy.
datasource "ds_get_policy" do
  request do
    auth $auth_flexera
    host rs_governance_host
    ignore_status [404]
    path join(["/api/governance/projects/", rs_project_id, "/applied_policies/", switch(ne(meta_parent_policy_id, ""), meta_parent_policy_id, policy_id) ])
    header "Api-Version", "1.0"
  end
  result do
    encoding "json"
    field "id", jmes_path(response, "id")
  end
end

datasource "ds_parent_policy_terminated" do
  run_script $js_decide_if_self_terminate, $ds_get_policy, policy_id, meta_parent_policy_id
end

# If the policy was applied by a meta_parent_policy we confirm it exists if it doesn't we confirm we are deleting
# This information is used in two places:
# - determining whether or not we make a delete call
# - determining if we should create an incident (we don't want to create an incident on the run where we terminate)
script "js_decide_if_self_terminate", type: "javascript" do
  parameters "found", "self_policy_id", "meta_parent_policy_id"
  result "result"
  code <<-EOS
  var result
  if (meta_parent_policy_id != "" && found.id == undefined) {
    result = true
  } else {
    result = false
  }
  EOS
end

# Two potentials ways to set this up:
# - this way and make a unneeded 'get' request when not deleting
# - make the delete request an interate and have it iterate over an empty array when not deleting and an array with one item when deleting
script "js_make_terminate_request", type: "javascript" do
  parameters "should_delete", "policy_id", "rs_project_id", "rs_governance_host"
  result "request"
  code <<-EOS

  var request = {
    auth:  'auth_flexera',
    host: rs_governance_host,
    path: "/api/governance/projects/" + rs_project_id + "/applied_policies/" + policy_id,
    headers: {
      "API-Version": "1.0",
      "Content-Type":"application/json"
    },
  }

  if (should_delete) {
    request.verb = 'DELETE'
  }
  EOS
end

datasource "ds_terminate_self" do
  request do
    run_script $js_make_terminate_request, $ds_parent_policy_terminated, policy_id, rs_project_id, rs_governance_host
  end
end

datasource "ds_is_deleted" do
  run_script $js_check_deleted, $ds_terminate_self
end

# This is just a way to have the check delete request connect to the farthest leaf from policy.
# We want the delete check to the first thing the policy does to avoid the policy erroring before it can decide whether or not it needs to self terminate
# Example a customer deletes a credential and then terminates the parent policy. We still want the children to self terminate
# The only way I could see this not happening is if the user who applied the parent_meta_policy was offboarded or lost policy access, the policies who are impersonating the user
# would not have access to self-terminate
# It may be useful for the backend to enable a mass terminate at some point for all meta_child_policies associated with an id.
script "js_check_deleted", type: "javascript" do
  parameters "response"
  result "result"
  code <<-EOS
  result = {"path":"/"}
  EOS
end