Detections
Analytics

1.16 Ensure that Snowflake stored procedures are not owned by the ACCOUNTADMIN or SECURITYADMIN roles

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

The ACCOUNTADMIN system role is the most powerful role in a Snowflake account and is intended for performing initial setup and managing account-level objects. SECURITYADMIN role can trivially escalate their privileges to that of ACCOUNTADMIN Neither of these roles should be used for running Snowflake stored procedures. A stored procedure should be running using a custom role containing only those privileges that are necessary for successful execution of the stored procedure.

Snowflake executes stored procedures with the privileges of the stored procedure owner or the caller. Role that has OWNERSHIP privilege on the stored procedure owns it.

To avoid granting a stored procedure inappropriate privileges, the OWNERSHIP privilege on the stored procedure run as owner should be assigned to a custom role containing only those privileges that are necessary for successful execution of the stored procedure.

The principle of least privilege requires that every identity, including service identities, is only given privileges that are necessary to complete its job.

If a threat actor finds a way to influence or hijack the stored procedure execution flow, they may be able to exploit privileges given to the stored procedure. In the case of an ACCOUNTADMIN or SECURITYADMIN roles, that may lead to a full account takeover. Additionally, a mistake in the stored procedure implementation coupled with excessive privileges may lead to a reliability incident, e.g. accidentally dropping database objects.

Solution

Programmatically:

In a Snowsight worksheet or through the SnowSQL CLI:

 -

For each stored procedure <procedure_name> that runs with ACCOUNTADMIN or SECURITYADMIN privileges, create a new role <procedure_specific_role> and assign it to the stored procedure:

CREATE ROLE <procedure_specific_role>;GRANT OWNERSHIP ON PROCEDURE <procedure_name> TO ROLE <procedure_specific_role>;
 -

After creating a new role and granting ownership of each stored procedure to it, for each stored procedure that is owned by ACCOUNTADMIN or SECURITYADMIN roles, ensure all privileges on the stored procedure are revoked from the roles:

REVOKE ALL PRIVILEGES ON PROCEDURE <procedure_name> FROM ROLE ACCOUNTADMIN;REVOKE ALL PRIVILEGES ON PROCEDURE <procedure_name> FROM ROLE SECURITYADMIN;

Impact:

Existing stored procedures that are owned by the ACCOUNTADMIN or SECURITYADMIN roles and run with their privileges will need to be updated to use a stored procedure specific custom role. If that role does not have all the privileges required by the stored procedure, the stored procedure execution may fail.

See Also

https://workbench.cisecurity.org/benchmarks/14781

Item Details

References: CSCv7|4.3

Plugin: Snowflake

Control ID: 64073897d5f3ecf1f75df7ae1f6e747366857ae3502652dbad257ee9ac6c30ce