Snowflake Security, RBAC, and User Administration · Topic 7 of 16

Auditing User and Query Activity, Fine-Tuning Access and Billing Visibility

Audit users and queries with ACCOUNT_USAGE and ORGANIZATION_USAGE, fine-tune access controls and give non-administrators billing and usage visibility.

  • 4 min
In a nutshell
  • ACCOUNT_USAGE: one year, lagged, includes dropped objects. INFORMATION_SCHEMA: current, short window.
  • ACCESS_HISTORY needs Enterprise Edition or higher.
  • SECURITY_VIEWER, GOVERNANCE_VIEWER, USAGE_VIEWER and OBJECT_VIEWER are the SNOWFLAKE database roles for audit and cost.

What it is

Access control is only as good as your ability to check it. Snowflake keeps a history of logins, queries, grants and usage in the shared SNOWFLAKE database. The ACCOUNT_USAGE schema covers one account, and the ORGANIZATION_USAGE schema covers the organization, mostly usage and billing.

The exam asks which view answers a question, how stale it can be, and how to give a finance or audit team access without handing out ACCOUNTADMIN.

Questionwho logged in, who queried whatchooseViewACCOUNT_USAGE or ORGANIZATION_USAGEgranted byDatabase roleSECURITY_VIEWER, USAGE_VIEWERgiven toCustom role

How it works

Which view for which question

QuestionSource
Who logged in, from where, with which method, and who failed?ACCOUNT_USAGE.LOGIN_HISTORY
What queries ran, by whom, how long?ACCOUNT_USAGE.QUERY_HISTORY
Which user and role read which columns?ACCOUNT_USAGE.ACCESS_HISTORY
Which roles and users exist, who has MFA or a key?ACCOUNT_USAGE.USERS, ROLES
Who holds which privileges?ACCOUNT_USAGE.GRANTS_TO_ROLES, GRANTS_TO_USERS
Credits and storage by warehouse or dayACCOUNT_USAGE.METERING_HISTORY, STORAGE_USAGE
Usage and billing across accountsORGANIZATION_USAGE views such as METERING_DAILY_HISTORY and USAGE_IN_CURRENCY_DAILY
Recent queries with no delayINFORMATION_SCHEMA.QUERY_HISTORY table function

Key properties of ACCOUNT_USAGE:

  • Data lags. QUERY_HISTORY can be about 45 minutes behind, LOGIN_HISTORY and USERS up to about two hours, and ACCESS_HISTORY longer.
  • History is kept for one year, and it includes dropped objects, unlike INFORMATION_SCHEMA.
  • ACCESS_HISTORY needs Enterprise Edition or higher.
  • INFORMATION_SCHEMA has no lag but a short window (the query history function covers 7 days).
SELECT user_name, client_ip, first_authentication_factor, is_success, error_message
FROM snowflake.account_usage.login_history
WHERE event_timestamp > DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND is_success = 'NO';
Exam tip

"Real time" or "just ran" points to INFORMATION_SCHEMA. "Last quarter", "dropped" or "across accounts" points to ACCOUNT_USAGE or ORGANIZATION_USAGE.

Who can read these schemas

By default only ACCOUNTADMIN can query the SNOWFLAKE database. Others get access through database roles, which keeps the grant narrow:

Database roleCovers
SNOWFLAKE.SECURITY_VIEWERLogin history, users, roles, grants, sessions
SNOWFLAKE.GOVERNANCE_VIEWERAccess history, query history, policies, tag references
SNOWFLAKE.USAGE_VIEWERMetering, storage and query metering
SNOWFLAKE.OBJECT_VIEWERObject metadata

Create a custom role, grant it the database role, then grant the custom role to users. Run these grants as ACCOUNTADMIN, which controls the SNOWFLAKE database by default.

CREATE ROLE auditor;
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE auditor;
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE auditor;

Fine-tuning access controls

Fine-tuning means finding and removing access that is too broad.

  1. Find it: list who holds powerful roles or privileges (GRANTS_TO_USERS, GRANTS_TO_ROLES, SHOW GRANTS OF ROLE), and compare with who really uses it (ACCESS_HISTORY, QUERY_HISTORY, LOGIN_HISTORY).
  2. Narrow it: replace direct or broad grants with an access role, and revoke the old grant.
  3. Check second paths: a revoke does nothing if another role still grants the privilege.
  4. Move ownership carefully: GRANT OWNERSHIP ... TO ROLE r REVOKE CURRENT GRANTS removes the old owner's grants; COPY CURRENT GRANTS keeps them.
  5. Repeat: the Trust Center scanners (a Snowflake feature that evaluates the account against security benchmarks) flag risks such as ACCOUNTADMIN without MFA.
Audit viewswho holds, who usesthenGapunused or too broadthenRevoke and regrantaccess rolethenVerifySHOW GRANTS

Billing and usage for non-administrators

Billing views live in the SNOWFLAKE database, so a finance team needs access without ACCOUNTADMIN.

NeedGrant
Account usage and cost viewsDatabase role SNOWFLAKE.USAGE_VIEWER
Account level monitoring of usage and historyThe MONITOR USAGE privilege
Organization billing viewsORGANIZATION_BILLING_VIEWER
Organization usage viewsORGANIZATION_USAGE_VIEWER
View queries and usage of one warehouseMONITOR on that warehouse
Watch out

Do not make the finance team ACCOUNTADMIN to see credits. A custom role with USAGE_VIEWER or MONITOR USAGE gives read access to usage with none of the administrative power.

Common mistakes

Auditing from INFORMATION_SCHEMA for old events. Its window is short and it omits dropped objects.

Expecting ACCOUNT_USAGE to be instant. Plan for the lag.

Giving ACCOUNTADMIN for read only needs. Use database roles.

Revoking and walking away. Check for second grant paths.

What to remember for the exam

  • ACCOUNT_USAGE: one year, lagged, includes dropped objects. INFORMATION_SCHEMA: current, short window.
  • ACCESS_HISTORY needs Enterprise Edition or higher.
  • SECURITY_VIEWER, GOVERNANCE_VIEWER, USAGE_VIEWER and OBJECT_VIEWER are the SNOWFLAKE database roles for audit and cost.
  • MONITOR USAGE and the organization billing roles serve non-administrators.
  • REVOKE CURRENT GRANTS or COPY CURRENT GRANTS decides what happens to old grants on an ownership move.

Check yourself

  • Which view lists failed logins from last month?

    ACCOUNT_USAGE.LOGIN_HISTORY, filtered on IS_SUCCESS.

  • How can an auditor read ACCOUNT_USAGE without ACCOUNTADMIN?

    Grant the matching SNOWFLAKE database role, such as SECURITY_VIEWER, to a custom role.

  • Which privilege lets a non-administrator monitor usage and billing history?

    MONITOR USAGE.

Topic 7 of 16 in Snowflake Security, RBAC, and User Administration

Test yourself on Snowflake Security, RBAC, and User Administration

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.