Detections
Analytics
2.8 Ensure monitoring and alerting exists for sessions from unsupported and vulnerable driver versions
Information
Snowflake provides client software (drivers, connectors, etc.) for connecting to Snowflake and using certain Snowflake features (e.g. Apache Kafka for loading data, Apache Hive metadata for external tables). The clients must be installed on each local workstation or system from which you wish to connect. The Snowflake Connector for Python, JDBC and ODBC drivers are some of the most used Snowflake clients.Old versions of drivers and connectors may contain security vulnerabilities that have been fixed in the latest version. To ensure that only up-to-date software is used, you should actively monitor session logins coming from unsupported clients and upgrade those to the latest available versions.
Using out-of-date Snowflake clients can expose your account to security risks. You should monitor for connections from unsupported Snowflake Connector drivers and upgrade to the latest versions available.
NOTE: Nessus has not performed this check. Please review the benchmark to ensure target compliance.
Solution
Programmatically:In a Snowsight worksheet or through the SnowSQL CLI:
- Create a UDF to help you compare version numbers:
CREATE OR REPLACE FUNCTION COMPARE_VERSIONS(v1 VARCHAR, v2 VARCHAR)
RETURNS NUMERIC
AS
$$
SELECT
CASE
WHEN TRY_CAST(split(v1, '.')[0]::string AS NUMBER) < TRY_CAST(split(v2, '.')[0]::string as number) then -1
WHEN TRY_CAST(split(v1, '.')[0]::string AS NUMBER) > TRY_CAST(split(v2, '.')[0]::string as number) then 1
WHEN TRY_CAST(split(v1, '.')[1]::string AS NUMBER) < TRY_CAST(split(v2, '.')[1]::string as number) then -1
WHEN TRY_CAST(split(v1, '.')[1]::string AS NUMBER) > TRY_CAST(split(v2, '.')[1]::string as number) then 1
WHEN TRY_CAST(split(v1, '.')[2]::string AS NUMBER) < TRY_CAST(split(v2, '.')[2]::string as number) then -1
WHEN TRY_CAST(split(v1, '.')[2]::string AS NUMBER) > TRY_CAST(split(v2, '.')[2]::string AS NUMBER) then 1
ELSE 0
END
$$;
- Configure your security monitoring solution to alert on sessions from unsupported or vulnerable driver versions. The following query can be run periodically.
SELECT
ARRAY_AGG(
OBJECT_CONSTRUCT(
'cve', CVE,
'severity', SEVERITY,
'min_affected', MIN_AFFECTED,
'max_affected', MAX_AFFECTED)
) AS CVE_INFO,
USER_NAME,
CREATED_ON,
CLIENT_APP,
CLIENT_APPLICATION_VERSION,
MINIMUM_SUPPORTED_VERSION,
RECOMMENDED_VERSION,
IS_UNSUPPORTED_VERSION,
BOOLOR_AGG(VULNERABLE_VERSION) AS IS_VULNERABLE_VERSION
FROM
(
WITH CVE AS (
SELECT
DRIVER_DATA.value:clientId::varchar AS CLIENT_ID,
VULNERABILITY.value:cve::varchar AS CVE,
VULNERABILITY.value:severity::varchar AS SEVERITY,
VULNERABILITY.value:minAffected::varchar AS MIN_AFFECTED,
VULNERABILITY.value:maxAffected::varchar AS MAX_AFFECTED
from (
SELECT PARSE_JSON(SYSTEM$CLIENT_VULNERABILITY_INFO()) AS JSON_COLUMN
) AS JSON_TABLE,
LATERAL FLATTEN(input => json_table.json_column) AS DRIVER_DATA,
LATERAL FLATTEN(input => driver_data.value:vulnerabilities) AS VULNERABILITY
)
SELECT
MAX(S.CREATED_ON) AS CREATED_ON,
S.USER_NAME,
SPLIT(S.CLIENT_APPLICATION_ID, ' ')[0]::VARCHAR AS CLIENT_APP,
S.CLIENT_APPLICATION_VERSION,
V.value:"minimumSupportedVersion"::string AS MINIMUM_SUPPORTED_VERSION,
V.value:"recommendedVersion"::string AS RECOMMENDED_VERSION,
FALSE AS STATUS,
CVE.*,
COMPARE_VERSIONS(S.CLIENT_APPLICATION_VERSION, V.value:"minimumSupportedVersion") < 0 AS UNSUPPORTED_VERSION,
COMPARE_VERSIONS(S.CLIENT_APPLICATION_VERSION, CVE.MIN_AFFECTED) >= 0
AND COMPARE_VERSIONS(S.CLIENT_APPLICATION_VERSION, CVE.MAX_AFFECTED) <= 0 AS VULNERABLE_VERSION,
COUNT(*) AS NUM_SESSIONS
FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS S
LEFT JOIN TABLE(FLATTEN(INPUT => PARSE_JSON(SYSTEM$CLIENT_VERSION_INFO()))) V
ON CLIENT_APP = V.value:"clientId"
LEFT JOIN CVE
ON CLIENT_APP = CVE.CLIENT_ID
WHERE MINIMUM_SUPPORTED_VERSION IS NOT NULL
AND S.CLIENT_APPLICATION_VERSION IS NOT NULL
AND LEN(S.CLIENT_APPLICATION_VERSION) > 0
AND (IS_UNSUPPORTED_VERSION OR IS_VULNERABLE_VERSION)
GROUP BY ALL
)
GROUP BY ALL;
- If you identify sessions from an outdated driver version, you can configure your authentication policy to require a minimum version for that driver using the query:
ALTER AUTHENTICATION POLICY <policy_name> SET CLIENT_POLICY = <client_type> = (MINIMUM_VERSION = '<version>');
- When detecting the use of unsupported clients, upgrade to the latest,
recommended version.
Impact:
None.
See Also
Item Details
Audit Name: CIS Snowflake Foundations v2.0.0 L2
Category: AUDIT AND ACCOUNTABILITY
References: 800-53|AU-6, 800-53|AU-6(1), 800-53|AU-7(1), CSCv7|6.7
Plugin: Snowflake
Control ID: 67e9d13ef79ef544d08d2a8c7b73b34681b5187d503096d4189b6d221e237393