Auditing Users, Logins and Queries with ACCOUNT_USAGE and ORGANIZATION_USAGE

1.ACCOUNT_USAGE: the long-term audit record

The shared SNOWFLAKE database contains an ACCOUNT_USAGE schema with object metadata and historical usage for your account. A READER_ACCOUNT_USAGE schema does the same for any reader accounts you have created. ACCOUNT_USAGE views have the same structure and names as their Information Schema counterparts. Three differences decide which one an audit question needs.

Because dropped objects stay in the views, an object name can repeat. ID columns tell apart records that share a name, and many object views have a DELETED timestamp. When an object-name column is NULL, the object has been dropped. These views cover the user and query activity this objective asks about.

Sources1

2.Giving an auditor role access to ACCOUNT_USAGE

Every user can see the SNOWFLAKE database, but its schemas need a grant from a user with ACCOUNTADMIN. There are two ways to give that grant. IMPORTED PRIVILEGES on the SNOWFLAKE database gives broad access in a single statement. SNOWFLAKE database roles are narrower: each one has SELECT on a defined set of ACCOUNT_USAGE views, and you grant it to an account role. Snowflake recommends database roles so that you do not expose organization-level data by accident.

USE ROLE ACCOUNTADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE SYSADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE customrole1;

USE ROLE customrole1; SELECT database_name, database_owner FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

For an auditor, which database role to grant depends on the question being asked. A role that has SECURITY_VIEWER can read LOGIN_HISTORY, USERS and the GRANTS_TO_* views, but it cannot read QUERY_HISTORY, which belongs to GOVERNANCE_VIEWER. If the auditor needs both logins and queries, grant both database roles. There is also READER_USAGE_VIEWER, which covers the READER_ACCOUNT_USAGE views for reader accounts.

Sources1

3.Auditing login activity

LOGIN_HISTORY is the starting point for user-activity audits. The documented examples calculate failed logins per user and the login failure rate for the month so far. A variant groups by reported_client_type as well, which shows which client the failures came from. Write the query to select only the columns you need. Snowflake may change these views, so avoid SELECT *.

select user_name, sum(iff(is_success = 'NO', 1, 0)) as failed_logins, count(*) as logins, sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate from login_history where event_timestamp > date_trunc(month, current_date) group by 1 order by 4 desc;

select user_name, sum(iff(is_success = 'NO', 1, 0)) as failed_logins, count(*) as logins, sum(iff(is_success = 'NO', 1, 0)) / nullif(count(*), 0) as login_failure_rate from  ?  where event_timestamp > date_trunc(month, current_date) group by 1 order by 4 desc;

Sources1

4.ORGANIZATION_USAGE: auditing across accounts

ACCOUNT_USAGE covers a single account. ORGANIZATION_USAGE, also in the shared SNOWFLAKE database, provides historical usage data for every account in the organization. The schema exists in the organization account and in any regular account where the ORGADMIN role is enabled. By default, only users with GLOBALORGADMIN can query its views in the organization account. To give access to other users, the organization administrator grants an application role, such as SNOWFLAKE.ORG_USAGE_ADMIN, which opens every view in the schema.

USE ROLE GLOBALORGADMIN;

GRANT APPLICATION ROLE SNOWFLAKE.ORG_USAGE_ADMIN TO ROLE custom_role;

GRANT ROLE custom_role TO USER joe;

When you compare cost-related ACCOUNT_USAGE views with the matching ORGANIZATION_USAGE views, set the session time zone to UTC before querying the ACCOUNT_USAGE view, using ALTER SESSION SET TIMEZONE = UTC;. The sources behind this lesson do not list the individual ORGANIZATION_USAGE views. The per-user login and query views described above are documented in ACCOUNT_USAGE, so audit those there.

USE ROLE GLOBALORGADMIN;

GRANT APPLICATION ROLE SNOWFLAKE. ?  TO ROLE custom_role;

GRANT ROLE custom_role TO USER joe;

Sources21

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.ACCOUNT_USAGE views show activity in real time, just like the Information Schema.Why is that wrong?

   ACCOUNT_USAGE views lag by between 45 minutes and 3 hours depending on the view. For activity from the last few minutes, use the Information Schema.

   Covered in ACCOUNT_USAGE: the long-term audit record

2. 2.Granting IMPORTED PRIVILEGES on SNOWFLAKE is the least-privilege way to give an auditor ACCOUNT_USAGE access.Why is that wrong?

   Snowflake recommends SNOWFLAKE database roles such as SECURITY_VIEWER, which limit access to particular views and avoid exposing organization-level data.

   Covered in Giving an auditor role access to ACCOUNT_USAGE

3. 3.Any ACCOUNTADMIN can query ORGANIZATION_USAGE in the organization account by default.Why is that wrong?

   By default, only users with GLOBALORGADMIN can access those views. Anyone else needs an application role such as ORG_USAGE_ADMIN.

   Covered in ORGANIZATION_USAGE: auditing across accounts

Practise it for real

Give a custom role ACCOUNT_USAGE access and run a month-to-date failed-login audit

1. 1.Run USE ROLE ACCOUNTADMIN; GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE customrole1;

   Why: SNOWFLAKE schemas can only be queried after a grant from ACCOUNTADMIN.

   You should see: The statements succeed and customrole1 can now read ACCOUNT_USAGE.

2. 2.Switch with USE ROLE customrole1; and run SELECT database_name, database_owner FROM SNOWFLAKE.ACCOUNT_USAGE.DATABASES;

   Why: This confirms the grant works before you run audit queries.

   You should see: Rows listing databases and the roles that own them.

3. 3.Run the failed-logins-by-user query against login_history.

   Why: LOGIN_HISTORY records each login attempt along with whether it succeeded.

   You should see: One row per user showing failed_logins, logins and login_failure_rate, highest failure rate first. Activity from the last two hours may not appear yet.