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
- 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.
How it works
Which view for which question
| Question | Source |
|---|---|
| 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 day | ACCOUNT_USAGE.METERING_HISTORY, STORAGE_USAGE |
| Usage and billing across accounts | ORGANIZATION_USAGE views such as METERING_DAILY_HISTORY and USAGE_IN_CURRENCY_DAILY |
| Recent queries with no delay | INFORMATION_SCHEMA.QUERY_HISTORY table function |
Key properties of ACCOUNT_USAGE:
- Data lags.
QUERY_HISTORYcan be about 45 minutes behind,LOGIN_HISTORYandUSERSup to about two hours, andACCESS_HISTORYlonger. - History is kept for one year, and it includes dropped objects, unlike
INFORMATION_SCHEMA. ACCESS_HISTORYneeds Enterprise Edition or higher.INFORMATION_SCHEMAhas 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';
"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 role | Covers |
|---|---|
SNOWFLAKE.SECURITY_VIEWER | Login history, users, roles, grants, sessions |
SNOWFLAKE.GOVERNANCE_VIEWER | Access history, query history, policies, tag references |
SNOWFLAKE.USAGE_VIEWER | Metering, storage and query metering |
SNOWFLAKE.OBJECT_VIEWER | Object 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.
- 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). - Narrow it: replace direct or broad grants with an access role, and revoke the old grant.
- Check second paths: a revoke does nothing if another role still grants the privilege.
- Move ownership carefully:
GRANT OWNERSHIP ... TO ROLE r REVOKE CURRENT GRANTSremoves the old owner's grants;COPY CURRENT GRANTSkeeps them. - Repeat: the Trust Center scanners (a Snowflake feature that evaluates the account against security benchmarks) flag risks such as ACCOUNTADMIN without MFA.
Billing and usage for non-administrators
Billing views live in the SNOWFLAKE database, so a finance team needs access without ACCOUNTADMIN.
| Need | Grant |
|---|---|
| Account usage and cost views | Database role SNOWFLAKE.USAGE_VIEWER |
| Account level monitoring of usage and history | The MONITOR USAGE privilege |
| Organization billing views | ORGANIZATION_BILLING_VIEWER |
| Organization usage views | ORGANIZATION_USAGE_VIEWER |
| View queries and usage of one warehouse | MONITOR on that warehouse |
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 USAGEand the organization billing roles serve non-administrators.REVOKE CURRENT GRANTSorCOPY CURRENT GRANTSdecides what happens to old grants on an ownership move.
Check yourself
Which view lists failed logins from last month?
ACCOUNT_USAGE.LOGIN_HISTORY, filtered onIS_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.