DBSheriff for Snowflake — prerequisites and service users

DBSheriff connects to your account with five dedicated service users, each with only the privileges its job needs. They use key-pair authentication (no passwords) and may only connect from the DBSheriff host. deploy/onprem/snowflake_setup.sql, shipped with the product, does everything below in one pass if you'd rather run one static SQL script; this page explains what it creates and why, and also covers the interactive alternative below.

Run everything as ACCOUNTADMIN.

The three scripts

On an installed host there is no scripts/ folder, so read every scripts/<name>.py below as sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool <name> ..., for example sudo /opt/dbsheriff/current/deploy/onprem/stack.sh tool setup_dbsheriff snowflake myorg-myaccount. The scripts/ form is for a source checkout. The details are under "How to start the scripts" in Before you install: creating DBSheriff's database users.

All three connect as your ACCOUNTADMIN user — never a cloud credential. Without a flag they ask how you sign in (password is the default); every script takes the same options, so an administrator whose login has no password at all can run them:

install_prereqs.py and setup_dbsheriff.py ask for one thing no other engine's scripts need: the DBSheriff host's own public IP (not the Snowflake account's address), since §3's network policy pins logins to wherever DBSheriff actually runs; create_reset_dbuser.py does not ask for it. Snowflake has no on-prem/cloud-platform choice to make, so there's no platform prompt at all. install_prereqs.py automates creating the DBSHERIFF_HOST_POLICY object (§3), and setup_dbsheriff.py does too when given --optional-prereqs=network_policy; create_reset_dbuser.py binds each account it creates or resets to that policy automatically whenever it already exists, and otherwise skips the bind with a note rather than failing — run the prerequisite first (or re-run account creation afterward, choosing "reset") to pick it up. Add --dry-run to preview without changing anything, or --check for a read-only status report.

Which databases. setup_dbsheriff.py and create_reset_dbuser.py ask which databases DBSheriff may read (or take --databases ANALYTICS,SALES; all for every customer database, none for none). The monitor and kill users get USAGE on those databases only, and the classify user USAGE and SELECT on every table in them, now and in the future (§5). Snowflake's own databases, DBSHERIFF_DROP, the personal USER$<login> databases and SNOWFLAKE_LEARNING_DB are not offered, and nothing is granted on a database you did not choose; naming a database that is not in the account stops the run before anything is created.

A user that already exists. CREATE USER IF NOT EXISTS does nothing at all for a user that is already there — it keeps the old public key and says nothing, so a private key generated afterwards could never log in. When you choose "create" and a DBSheriff user already exists, the scripts set the new public key with ALTER USER … SET RSA_PUBLIC_KEY instead (the private key stored for that user before no longer works) and say so. Every key is then checked before it is stored: the fingerprint Snowflake holds for the user must be the generated key's, and one login with the key must not be refused as an invalid token. A key that fails is reported, not stored; run the script again. A login refused by a network policy (the machine you run the script on is not in DBSHERIFF_HOST_POLICY) is reported as not proven from here, because the fingerprint already matches.

0. What must be enabled in the account

The Without it column says what DBSheriff loses when an item is missing, so you can decide what to enable. Nothing optional or recommended stops the product: detection, response and the evidence pack run on the required items alone.

1. Generate one key pair per user (on the DBSheriff host)

for u in monitor kill lock posture classify; do
  openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out dbsheriff_$u.p8 -nocrypt
  openssl rsa -in dbsheriff_$u.p8 -pubout -out dbsheriff_$u.pub
done

The public key body (the lines between BEGIN and END, without the header lines) goes into the RSA_PUBLIC_KEY = '…' placeholders below. The private .p8 files of the monitor, kill, lock, posture and classify users are pasted into the matching boxes of the DBSheriff Admin Console (§8); a blank posture key uses the monitor key.

2. Compute and cost cap

DBSheriff polls on its own warehouse so its cost is visible and capped.

USE ROLE ACCOUNTADMIN;
CREATE WAREHOUSE IF NOT EXISTS DBSHERIFF_WH
  WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
       INITIALLY_SUSPENDED = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1;
CREATE RESOURCE MONITOR IF NOT EXISTS DBSHERIFF_RM
  WITH CREDIT_QUOTA = 60 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS ON 80 PERCENT DO NOTIFY ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE DBSHERIFF_WH SET RESOURCE_MONITOR = DBSHERIFF_RM;

60 credits a month is the starting quota this SQL and the scripts create. DBSheriff's own estimate is about 5 credits a day in balanced poll mode (about 150 a month) and about 24 a day with a fixed 10-second poll, so size CREDIT_QUOTA to the cadence you choose. If the quota is spent, Snowflake suspends the warehouse and DBSheriff reports it instead of spending more.

60 is too small for balanced mode (about 150 credits a month): at about 5 a day it is spent in about 12 days, and polling then stops until the monthly reset. Raise it before you register the server:

USE ROLE ACCOUNTADMIN;
ALTER RESOURCE MONITOR DBSHERIFF_RM SET CREDIT_QUOTA = 200;

3. Network policy for the product users

scripts/install_prereqs.py snowflake <account>   # asks for the DBSheriff host's public IP
# or, together with the five users:
scripts/setup_dbsheriff.py snowflake <account> --optional-prereqs=network_policy

CREATE NETWORK POLICY IF NOT EXISTS DBSHERIFF_HOST_POLICY
  ALLOWED_IP_LIST = ('<dbsheriff_host_public_ip>/32');

When the DBSheriff host's address changes, DBSheriff raises a finding. Re-run the same command with the new IP. The script reads the policy's current list (DESCRIBE NETWORK POLICY DBSHERIFF_HOST_POLICY); if the new address is not already covered by it, it adds <new ip>/32 and prints the statement it ran:

ALTER NETWORK POLICY DBSHERIFF_HOST_POLICY
  SET ALLOWED_IP_LIST = (<every entry already in the list>, '<new ip>/32');

It only ever adds an address. The old one stays allowed (so does any range you added yourself), so the old host keeps working until you retire its address by hand with the same ALTER and the list you want to keep. A re-run with an address the policy already allows (on its own or inside a range) changes nothing. With --check a policy that lacks the address is reported as "present but missing this host IP", together with the ALTER that would fix it. If the address is in the policy's BLOCKED_IP_LIST (Snowflake applies blocked addresses first) the script stops and leaves that to you. Never bind your own administrator user to this policy.

4. The five users and their roles

CREATE ROLE IF NOT EXISTS dbsheriff_monitor_role;
CREATE ROLE IF NOT EXISTS dbsheriff_kill_role;
CREATE ROLE IF NOT EXISTS dbsheriff_lock_role;
CREATE ROLE IF NOT EXISTS dbsheriff_posture_role;
CREATE ROLE IF NOT EXISTS dbsheriff_classify_role;
GRANT ROLE dbsheriff_monitor_role, dbsheriff_kill_role, dbsheriff_lock_role,
           dbsheriff_posture_role, dbsheriff_classify_role TO ROLE SYSADMIN;

-- CREATE USER IF NOT EXISTS leaves a user that already exists exactly as it is, public key included:
-- for such a user run  ALTER USER <name> SET RSA_PUBLIC_KEY = '<public_key_body>';  instead
CREATE USER IF NOT EXISTS dbsheriff_monitor  TYPE = SERVICE DEFAULT_ROLE = dbsheriff_monitor_role
  DEFAULT_WAREHOUSE = DBSHERIFF_WH RSA_PUBLIC_KEY = '<monitor_public_key_body>';
CREATE USER IF NOT EXISTS dbsheriff_kill     TYPE = SERVICE DEFAULT_ROLE = dbsheriff_kill_role
  DEFAULT_WAREHOUSE = DBSHERIFF_WH RSA_PUBLIC_KEY = '<kill_public_key_body>';
CREATE USER IF NOT EXISTS dbsheriff_lock     TYPE = SERVICE DEFAULT_ROLE = dbsheriff_lock_role
  DEFAULT_WAREHOUSE = DBSHERIFF_WH RSA_PUBLIC_KEY = '<lock_public_key_body>';
CREATE USER IF NOT EXISTS dbsheriff_posture  TYPE = SERVICE DEFAULT_ROLE = dbsheriff_posture_role
  DEFAULT_WAREHOUSE = DBSHERIFF_WH RSA_PUBLIC_KEY = '<posture_public_key_body>';
CREATE USER IF NOT EXISTS dbsheriff_classify TYPE = SERVICE DEFAULT_ROLE = dbsheriff_classify_role
  DEFAULT_WAREHOUSE = DBSHERIFF_WH RSA_PUBLIC_KEY = '<classify_public_key_body>';

GRANT ROLE dbsheriff_monitor_role  TO USER dbsheriff_monitor;
GRANT ROLE dbsheriff_kill_role     TO USER dbsheriff_kill;
GRANT ROLE dbsheriff_lock_role     TO USER dbsheriff_lock;
GRANT ROLE dbsheriff_posture_role  TO USER dbsheriff_posture;
GRANT ROLE dbsheriff_classify_role TO USER dbsheriff_classify;

ALTER USER dbsheriff_monitor  SET NETWORK_POLICY = DBSHERIFF_HOST_POLICY;
ALTER USER dbsheriff_kill     SET NETWORK_POLICY = DBSHERIFF_HOST_POLICY;
ALTER USER dbsheriff_lock     SET NETWORK_POLICY = DBSHERIFF_HOST_POLICY;
ALTER USER dbsheriff_posture  SET NETWORK_POLICY = DBSHERIFF_HOST_POLICY;
ALTER USER dbsheriff_classify SET NETWORK_POLICY = DBSHERIFF_HOST_POLICY;

create_reset_dbuser.py runs these five ALTER USER statements for you automatically whenever DBSHERIFF_HOST_POLICY already exists — one less thing to copy by hand if you're using the scripts instead of the static SQL file above.

5. Grants per role

<monitored_database> is each database you want covered, never every database of the account: the scripts grant on the databases you choose (§ The three scripts).

-- monitor: history, no data (with HONEYTOKENS=on the role of the read login is also granted SELECT on each decoy table DBSheriff plants, and on nothing else)
GRANT USAGE, MONITOR ON WAREHOUSE DBSHERIFF_WH TO ROLE dbsheriff_monitor_role;
GRANT USAGE ON DATABASE <monitored_database> TO ROLE dbsheriff_monitor_role;
GRANT MONITOR USAGE ON ACCOUNT TO ROLE dbsheriff_monitor_role;      -- statements that run on no warehouse
-- GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE dbsheriff_monitor_role;  -- only when no posture login is registered (the provisioning scripts grant it to both roles)

-- kill
GRANT USAGE, OPERATE ON WAREHOUSE DBSHERIFF_WH TO ROLE dbsheriff_kill_role;
GRANT USAGE ON DATABASE <monitored_database> TO ROLE dbsheriff_kill_role;

-- lock (least privilege alternative: GRANT OWNERSHIP ON USER <u> TO ROLE dbsheriff_lock_role COPY CURRENT GRANTS, per user)
GRANT USAGE ON WAREHOUSE DBSHERIFF_WH TO ROLE dbsheriff_lock_role;
GRANT ROLE SECURITYADMIN TO ROLE dbsheriff_lock_role;

-- posture / compliance evidence
GRANT USAGE ON WAREHOUSE DBSHERIFF_WH TO ROLE dbsheriff_posture_role;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE TO ROLE dbsheriff_posture_role;
GRANT APPLICATION ROLE SNOWFLAKE.TRUST_CENTER_VIEWER TO ROLE dbsheriff_posture_role;

-- decoy tables (only with HONEYTOKENS=on): the role that reads them (the posture role, or the monitor role when no posture login is set) also needs USAGE on
-- the decoy's database and schema, which DBSheriff never grants. Above, the monitor role has the database and neither role has the schema:
-- GRANT USAGE ON DATABASE <monitored_database> TO ROLE dbsheriff_posture_role;
-- GRANT USAGE ON SCHEMA <monitored_database>.<decoy_schema> TO ROLE dbsheriff_posture_role;

-- classification (optional value sampling)
GRANT USAGE ON WAREHOUSE DBSHERIFF_WH TO ROLE dbsheriff_classify_role;
GRANT USAGE ON DATABASE <monitored_database> TO ROLE dbsheriff_classify_role;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <monitored_database> TO ROLE dbsheriff_classify_role;
GRANT SELECT ON ALL TABLES IN DATABASE <monitored_database> TO ROLE dbsheriff_classify_role;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE <monitored_database> TO ROLE dbsheriff_classify_role;
GRANT SELECT ON FUTURE TABLES IN DATABASE <monitored_database> TO ROLE dbsheriff_classify_role;

6. Every warehouse and every user (re-run when you add some)

Snowflake shows another user's statements only on warehouses the monitor role can MONITOR, cancels only where the kill role can OPERATE, and shows logins only for users the role can MONITOR:

DECLARE c1 CURSOR FOR SELECT "name" AS name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
BEGIN
  SHOW WAREHOUSES;
  FOR w IN c1 DO
    EXECUTE IMMEDIATE 'GRANT MONITOR ON WAREHOUSE "' || w.name || '" TO ROLE dbsheriff_monitor_role';
    EXECUTE IMMEDIATE 'GRANT OPERATE ON WAREHOUSE "' || w.name || '" TO ROLE dbsheriff_kill_role';
  END FOR;
  RETURN 'done';
END;

DECLARE c2 CURSOR FOR SELECT "name" AS name FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
BEGIN
  SHOW USERS;
  FOR u IN c2 DO
    EXECUTE IMMEDIATE 'GRANT MONITOR ON USER "' || u.name || '" TO ROLE dbsheriff_monitor_role';
  END FOR;
  RETURN 'done';
END;

DBSheriff's posture checks report any warehouse or user still missing these grants.

7. Optional: sensitivity tag write-back

CREATE DATABASE IF NOT EXISTS DBSHERIFF_DROP;
CREATE SCHEMA IF NOT EXISTS DBSHERIFF_DROP.GOVERNANCE;
CREATE TAG IF NOT EXISTS DBSHERIFF_DROP.GOVERNANCE.SENSITIVITY
  ALLOWED_VALUES 'CRITICAL', 'SENSITIVE', 'INTERNAL', 'PUBLIC';
GRANT USAGE ON DATABASE DBSHERIFF_DROP TO ROLE dbsheriff_lock_role;
GRANT USAGE ON SCHEMA DBSHERIFF_DROP.GOVERNANCE TO ROLE dbsheriff_lock_role;
GRANT APPLY ON TAG DBSHERIFF_DROP.GOVERNANCE.SENSITIVITY TO ROLE dbsheriff_lock_role;
GRANT APPLY TAG ON ACCOUNT TO ROLE dbsheriff_lock_role;

Then attach your masking policy to the tag once; switch the write-back on per account in the Admin Console.

8. Register the account in DBSheriff

Admin Console → Monitored Servers → Add: engine Snowflake, account identifier, warehouse DBSHERIFF_WH, poll mode balanced, and the monitor, kill, lock, posture and classify users with their private keys pasted as text (leave passwords blank). create_reset_dbuser.py can store the same keys, and the user names, in DBSheriff's registry for a server that is already registered. A posture user name saved without a key makes the posture scan sign in as that user with the monitor's key, which Snowflake refuses; a classify user with neither a key nor a password is skipped for value sampling.

Saving does not test the logins. DBSheriff connects with the monitor login when it starts monitoring the server (within the registry reload interval, five minutes), and a login the account refuses shows as a connection error in the producer's log; the kill, lock, posture and classify logins are first used when a kill, lock, posture scan or value sampling runs.

DBSheriff leaves its own accounts out of scoring automatically (it reads them from the server's registration), so nothing needs to be added to the excluded-users list for them.

Which databases the classification scan reads

The Scan databases field of the server (Monitored Servers) decides it:

Columns found by ALL in a database other than the registered one are proposed and stay pending until a person approves them: the automatic approval (a column on which two independent evidence sources agree) does not apply in a database the scan found by browsing. In the registered database and in the ones you listed it does, unless CLASSIFICATION_AUTO_APPROVE=false.

The scan reads the catalog with the classification login (dbsheriff_classify), and falls back to the monitor login only when the classification login cannot connect. Snowflake's INFORMATION_SCHEMA lists a login only the objects it holds a privilege on, so the classification role needs USAGE on the database and its schemas and SELECT on the tables (§ 5 grants exactly that). If no classification login is registered, the monitor role is used and needs USAGE on the schemas and REFERENCES on the tables of every database to be scanned, which the monitor role does not get from the grants above. A database in which the login sees no table at all (empty, or no such grants) is named in the scan's error text, and a scan that read nothing anywhere ends FAILED with that reason instead of completing having read nothing.

Your own administrator login is monitored too

Your own administrator login is monitored like any other login of the account, ACCOUNTADMIN included: DBSheriff scores its statements and can cancel one that looks risky — a long read of SNOWFLAKE.ACCOUNT_USAGE is enough. Exempting a login (TRUSTED_SECURE in Privileged Users: alerted on, not cancelled) waits for a second approver, who is not the one who entered it; on an installation with no second approver the entry starts 24 hours after it is entered (RISK_LOWERING_DELAY_H). Settle it before you start using the account for administration: add a second approver in the Admin Console and classify your login TRUSTED_SECURE, or use a separate account for administrator work. setup_dbsheriff.py and create_reset_dbuser.py print this reminder, with your login name, when they finish.

9. Verify

SHOW GRANTS TO ROLE dbsheriff_monitor_role;
SHOW GRANTS TO ROLE dbsheriff_kill_role;
SHOW WAREHOUSES LIKE 'DBSHERIFF_WH';
SHOW RESOURCE MONITORS LIKE 'DBSHERIFF_RM';
DESCRIBE NETWORK POLICY DBSHERIFF_HOST_POLICY;

Paid Trust Center packages (CIS Benchmarks, Threat Intelligence) are enabled in Snowsight under Monitoring → Trust Center → Scanner Packages; their findings appear in DBSheriff within the next hourly import.

Optional: the Control account (native controls)

If you will let DBSheriff create standing native controls in this Snowflake account (masks, limits, time-boxed grants) through the database's own features, or write the SQL for your DBA to run, read native controls privileges (inside the package) first: it says what each tier can do, what the optional Control account needs here, and how to remove everything DBSheriff made. The account is created only on request, with create_reset_dbuser.py snowflake <host> --with-control; setup_dbsheriff.py never creates it, and native controls are off until an approver turns a server to script or managed.

What each user can do if its key leaks

The connections to Snowflake are HTTPS and always verified; the administrator connection of the provisioning scripts has nothing to choose. DBSheriff keeps these keys encrypted in its registry (SERVER_CREDENTIALS_KEY); whoever can read both the registry and that key holds every user above. Create only the users you will use.