Profile Applicability:

  • Level 1

Description:

The user options database flag for Cloud SQL SQL Server instances should not be configured. This flag allows for the establishment of global default query processing options for all users during their work sessions. However, configuring this flag can lead to unintended changes in the query processing behavior for all users, which may affect database performance or security.

Rationale:

The user options flag is used to modify default settings for SQL Server query processing globally. While it may provide flexibility, it can also introduce risks by overriding default settings. Allowing individual users to modify these defaults with the SET statement ensures flexibility without impacting global configurations. Therefore, leaving the user options flag unset ensures default server configurations remain intact and unaffected.

Impact:

  • If the user options flag is configured, it may lead to unintended global changes in database behavior.

  • Modifying this flag via the command line will reset all other flags to their default values unless explicitly included in the update command.

  • Any changes to the flag configuration may require a database restart, potentially causing temporary downtime. Therefore, apply changes during periods of low usage.

Default Value:

By default, the user options flag is not configured.

Audit Steps:

Using Google Cloud Console:
  1. Navigate to the Cloud SQL Instances page.

                   

  1. Select the SQL Server instance to view its Instance Overview page.

       

  1. Under the Database flags section, ensure the user options flag is not listed.

         


Using Google Cloud CLI:

Run the following command to verify the user options flag is not configured:

gcloud sql instances describe <INSTANCE_NAME> --format=json | jq '.settings.databaseFlags[] | select(.name=="user options")|.value'
  1. Ensure no output is returned for the user options flag.

Remediation Steps:

Using Google Cloud Console:
  1. Navigate to the Cloud SQL Instances page.

                                 

  1. Select the SQL Server instance where the flag needs to be removed.

             

  1. Click Edit

           

  1. Scroll to the Flags section.

                 

  1. Click the X icon next to the user options flag to remove it.

                   

  1. Click Save to apply the changes.

                     

  1. Confirm the changes under the Flags section on the Overview page.

             

Using Google Cloud CLI:

To clear all database flags, run:

gcloud sql instances patch <INSTANCE_NAME> --clear-database-flags

To remove only the user options flag, specify the remaining flags explicitly, excluding user options:

gcloud sql instances patch <INSTANCE_NAME> --database-flags <FLAG1=VALUE1>,<FLAG2=VALUE2>
  1. Ensure the flag is no longer configured.

Backout Plan:

Step 1: Restore the user options Flag If Needed

If disabling user options causes operational issues, restore the flag:

gcloud sql instances patch <INSTANCE_NAME> --database-flags "user options=<VALUE>"

Step 2: Notify Stakeholders

  • Inform database administrators before re-enabling user options.

Step 3: Restore from Backup (If Necessary)

  • If removing user options causes unexpected failures, restore the SQL instance from a backup.

Step 4: Validate Rollback Success

  • Run the audit steps again to confirm that the intended user options setting is restored

References:

  1. Cloud SQL SQL Server Flags Documentation

  2. Microsoft Documentation: User Options Configuration

Additional Information:

  • Changes to database flags may require a restart of the SQL instance. Refer to the Cloud SQL SQL Server Flags Documentation for details.

  • Removing unnecessary configurations reduces the risk of unintended behavior and enhances database stability.

  • Ensure any modifications are planned during off-peak hours to minimize potential disruptions.

CIS Controls:

Control

Description

IG 1

IG 2

IG 3

4.1 Establish and Maintain a Secure Configuration Process

Establish secure configuration standards for enterprise assets and software. Update annually or as needed.

5.1 Establish Secure Configurations

Maintain standardized security configurations for authorized operating systems and software.