Beta - SQL DB Instance With Global User Options

• Query id: c8e4444e-d9a9-4426-be8e-9f1b8c43133c
• Query name: Beta - SQL DB Instance With Global User Options
• Platform: Terraform
• Severity: Medium
• Category: Insecure Configurations
• CWE: 250
• Risk score: 3.0
• URL: Github

Description

No 'google_sql_database_instance' resource based on SQLSERVER should define the 'user options' flag
Documentation

Code samples

Code samples with security vulnerabilities

Positive test num. 1 - tf file

resource "google_sql_database_instance" "positive_1" {
  name             = "sqlserver-instance-with-flag"
  database_version = "SQLSERVER_2017_EXPRESS"
  region           = "us-central1"

  settings {
    database_flags {
      name = "sample_flag1"
      value = "off"
      }

    database_flags {                          # Flag is not set to "0" - "32" triggers "ANSI_NULLS" option
      name = "user options"
      value = "32"
      }

    database_flags {
      name = "sample_flag2"
      value = "off"
      }
  }
}

resource "google_sql_database_instance" "positive_2" { # Single object support test
  name             = "sqlserver-instance-with-flag"
  database_version = "SQLSERVER_2017_EXPRESS"
  region           = "us-central1"

  settings {
    database_flags {
      name = "user options"
      value = "16"
      }  # Flag is not set to "0" - "16" triggers "ANSI_PADDING" option
  }
}

Code samples without security vulnerabilities

Negative test num. 1 - tf file

resource "google_sql_database_instance" "negative_1" {
  name             = "main-instance"
  database_version = "MYSQL_8_0"      # Is not a SQLSERVER instance
  region           = "us-central1"

  settings {
    tier = "db-f1-micro"

    database_flags{
      name = "user options"
      value = "2048"        # ANSI_NULL_DFLT_OFF option
      }
  }
}

resource "google_sql_database_instance" "negative_2" {
  name             = "mysql-instance-without-flag"
  database_version = "SQLSERVER_2017_STANDARD"
  region           = "us-central1"

  # Defaults to "0"
}

resource "google_sql_database_instance" "negative_3" {
  name             = "sqlserver-instance-without-flag"
  database_version = "SQLSERVER_2017_STANDARD"
  region           = "us-central1"

  settings {}  # Defaults to "0"
}

resource "google_sql_database_instance" "negative_4" {
  name             = "sqlserver-instance-without-flag"
  database_version = "SQLSERVER_2017_STANDARD"
  region           = "us-central1"

  settings {
    database_flags {
      name = "sample_flag1"
      value = "off"
      }
      # Defaults to "0"
  }
}

resource "google_sql_database_instance" "negative_5" {
  name             = "mysql-instance-with-flag"
  database_version = "SQLSERVER_2019_STANDARD"
  region           = "us-central1"

  settings {
    tier = "db-f1-micro"

    database_flags {
      name = "sample_flag1"
      value = "off"
      }

    database_flags {                                  # Has flag set to "0"
      name = "user options"
      value = "0"
      }

    database_flags {
      name = "sample_flag2"
      value = "off"
      }
  }
}

resource "google_sql_database_instance" "negative_6" { # Single object support test
  name             = "mysql-instance-with-flag"
  database_version = "SQLSERVER_2019_STANDARD"
  region           = "us-central1"

  settings {
    tier = "db-f1-micro"

    database_flags {
      name = "user options"
      value = "0"
      }   # Has flag set to "0"
  }
}