-- All queries accessing a specific table
SELECT
  query_id,
  query_text,
  user_name,
  role_name,
  start_time,
  direct_objects_accessed,
  objects_modified
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
WHERE direct_objects_accessed[0]:objectDomain::STRING = 'TABLE'
  AND direct_objects_accessed[0]:refs[0]:objectName::STRING = 'SENSITIVE_DATA'
ORDER BY start_time DESC;

-- Track column-level access
SELECT
  query_id,
  user_name,
  columns_accessed
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE columns_accessed IS NOT NULL
ORDER BY start_time DESC;

-- Find access to sensitive columns
WITH sensitive_columns AS (
  SELECT 'EMAIL' as column_name UNION ALL
  SELECT 'PHONE' UNION ALL
  SELECT 'SSN' UNION ALL
  SELECT 'CREDIT_CARD' UNION ALL
  SELECT 'BIRTH_DATE'
)
SELECT
  ah.query_id,
  ah.user_name,
  ah.start_time,
  ah.query_text,
  ca.value:objectName::STRING as table_name,
  ca.value:columnName::STRING as column_name
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
)) ah,
LATERAL FLATTEN(input => ah.columns_accessed) ca
WHERE ca.value:columnName::STRING IN (SELECT column_name FROM sensitive_columns)
ORDER BY ah.start_time DESC;

-- User access patterns
SELECT
  user_name,
  COUNT(*) as total_queries,
  COUNT(DISTINCT objects_accessed) as unique_tables,
  SUM(CASE WHEN columns_accessed IS NOT NULL THEN 1 ELSE 0 END) as column_level_queries,
  MIN(start_time) as first_access,
  MAX(start_time) as last_access
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -30, CURRENT_TIMESTAMP())
))
GROUP BY user_name
ORDER BY total_queries DESC;

-- Track data exports
SELECT
  query_id,
  user_name,
  query_text,
  start_time,
  bytes_scanned
FROM TABLE(INFORMATION_SCHEMA.ACCESS_HISTORY(
  START_TIME => DATEADD(day, -7, CURRENT_TIMESTAMP())
))
WHERE query_text LIKE '%COPY INTO%'
  OR query_text LIKE '%GET%'
  OR query_text LIKE '%LIST%'
ORDER BY start_time DESC;

-- Privilege usage analysis
SELECT
  grantee_name,
  privilege,
  grant_type,
  created_on,
  table_name
FROM TABLE(INFORMATION_SCHEMA.GRANTS_TO_USERS(
  START_TIME => DATEADD(day, -90, CURRENT_TIMESTAMP())
))
WHERE privilege IN ('SELECT', 'INSERT', 'UPDATE', 'DELETE')
ORDER BY created_on DESC;

-- Role hierarchy analysis
SELECT
  grantee_name as role_name,
  granted_to,
  granted_role
FROM TABLE(INFORMATION_SCHEMA.GRANTS_TO_ROLES())
WHERE granted_to = 'ROLE'
ORDER BY grantee_name;