Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Disable 'user options' Flag for SQL Server Instances

Trend Micro Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 750 automated best practice checks.

Risk Level: Medium (should be achieved)
Rule ID: CloudSQL-021

Ensure that the "user options" database flag is not configured for your Google Cloud SQL Server database instances in order to avoid defining global defaults for all database users.

This rule resolution is part of the Conformity Security & Compliance tool for GCP.

Security
Operational
excellence

Once enabled, the "user options" database flag configures global defaults for all database users. A list of default query processing options is established for the duration of a user's work session. The "user options" configuration flag allows you to change the default values of the SET options (if the database server's default settings are not appropriate).

Note: Some database flag settings can affect instance availability and/or stability, and eventually remove the SQL Server instance from the Google Cloud SQL Service Level Agreement (SLA).


Audit

To determine if the "user options" flag is configured for your SQL Server database instances, perform the following operations:

Using GCP Console

01 Sign in to the Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter box, select Type and SQL Server <version>, then press Enter to list only the SQL Server database instances provisioned for the selected GCP project.

05 Click on the name (ID) of the database instance that you want to examine.

06 In the navigation panel, select Overview to access the configuration details available for the selected SQL Server instance.

07 In the Configuration section, under Database flags, check for the user options database flag. If the user options flag is available in the Database flags list, the "user options" database flag is enabled and configured for the selected Google Cloud SQL Server database instance, therefore the instance configuration is not compliant.

08 Repeat step no. 5 – 7 to check the "user options" flag value for other SQL Server instances available within the selected project.

09 Repeat steps no. 2 – 8 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) with custom query filters to list the IDs of all the GCP projects available within your Google Cloud account:

gcloud projects list
  --format="table(projectId)"

02 The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-ms-web-project-123123
cc-gcp-data-project-123123

03 Run sql instances list command (Windows/macOS/Linux) with custom filtering to describe the name of each SQL Server database instance provisioned for the selected Google Cloud project:

gcloud sql instances list
  --project cc-ms-web-project-123123
  --filter='DATABASE_VERSION:SQLSERVER*'
  --format="(NAME)"

04 The command output should return the requested database instance name(s):

NAME:
cc-web-sql-server-instance
cc-app-sql-server-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the SQL Server database instance that you want to examine as the identifier parameter and custom query filters to describe the "user options" flag configuration value set for the selected database instance:

gcloud sql instances describe cc-web-sql-server-instance
  --format=json | jq '.settings.databaseFlags[] | select(.name=="user options")|.value'

06 The command output should return the requested flag configuration value:

"10"

If the sql instances describe command output returns a configuration value, as shown in the output example above, the "user options" database flag is enabled and configured for the selected Google Cloud SQL Server database instance, therefore the instance configuration is not compliant.

07 Repeat steps no. 5 and 6 to verify the "user options" flag status and configuration for other SQL Server instances created for the selected project.

08 Repeat steps no. 3 – 7 for each project created within your Google Cloud account.

Remediation / Resolution

To disable the "user options" database flag for your Google Cloud SQL Server database instances, perform the following operations:

Using GCP Console

01 Sign in to the Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter box, select Type and SQL Server <version>, then press Enter to display only the SQL Server database instances provisioned for the selected GCP project.

05 Click on the name (ID) of the database instance that you want to reconfigure.

06 In the navigation panel, select Overview to access the configuration details available for the selected SQL Server instance.

07 Choose Edit from the dashboard top menu to modify the instance configuration.

08 In the Customize your instance section, choose Flags and parameters to expand the panel with the database flags and parameters configured for the selected SQL Server instance.

09 Find the user options flag and remove it by using the Delete button associated with the flag.

10 Choose SAVE to apply the configuration changes.

11 Repeat steps no. 5 – 10 to disable the specified flag for other SQL Server database instances available within the selected GCP project.

12 Repeat steps no. 2 – 11 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name of the SQL Server database instance that you want to reconfigure as the identifier parameters, to disable the "user options" database flag for the selected SQL Server instance by removing the flag from the instance configuration. The following command will overwrite all the SQL Server database flags previously set. If you need to keep the existing flags and add new ones, include the values for all the flags you want set on the instance. Any flag not specifically included is set to its default value. For flags that do not take a value, specify the flag name followed by an equals sign ("="):

gcloud sql instances patch cc-web-sql-server-instance
  --clear-database-flags

IMPORTANT: Removing/disabling database flags using the --clear-database-flags command parameter automatically restarts the selected database instance.

02 Type Y to confirm the database configuration change:

The following message will be used for the patch API method.

{"name": "cc-web-sql-server-instance", "project": "cc-ms-web-project-123123", "settings": {"databaseFlags": []}} WARNING: This patch modifies database flag values, which may require your instance to be restarted. Check the list of supported flags - https://cloud.google.com/sql/docs/sqlserver/flags - to see if your instance will be restarted when this patch is submitted.

Do you want to continue (Y/n)? Y

03 The output should return the sql instances patch command request status:

Patching Cloud SQL instance...done.

Updated
[https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-ms-web-project-12312/instances/cc-web-sql-server-instance].

04 Repeat steps no. 1 – 3 to disable the specified flag for other SQL Server database instances provisioned for the selected GCP project.

05 Repeat steps no. 1 – 4 for each project created within your Google Cloud account.

References

Publication date Apr 8, 2022

Unlock the Remediation Steps


Free 30-day Trial

Automatically audit your configurations with Conformity
and gain access to our cloud security platform.

Confirmity Cloud Platform

No thanks, back to article

You are auditing:

Disable 'user options' Flag for SQL Server Instances

Risk Level: Medium