SAP HANA — prerequisites and accounts

What to create on your SAP HANA instance (SAP HANA, express edition or any HANA 2.0 SPS0x deployment — single-container or multi-tenant/MDC) so DBSheriff can monitor it. Everything below is a standard user, grant, or audit policy; DBSheriff installs nothing on the server. Run the statements inside the tenant database your data lives in (connect via hdbsql -d <tenant> or the SYSTEMDB tenant-routing database= option), not against SYSTEMDB. HANA is always self-managed — there is no cloud-managed HANA variant this guide needs to branch on.

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 hana hana-01.acme.internal. 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 ask for the tenant database name (never SYSTEMDB) and connect only as a database user — never a cloud credential. install_prereqs.py offers to apply global_auditing_state and the DBSHERIFF_AUTH policy (§3), asking "Apply now?" for each; setup_dbsheriff.py applies global_auditing_state, and applies the DBSHERIFF_AUTH policy only when you add --optional-prereqs=dbsheriff_auth_policy — both apply live, no restart needed. The DBSHERIFF_DML/DBSHERIFF_DDL policies stay manual, since each needs an explicit schema/table target list only you can choose (§3). Add --dry-run to any of them to preview without changing anything, or --check for a read-only status report — see pre installation database users for the full flag reference shared by every engine.

0. What must be enabled on the server

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.

The floor. The five accounts with CATALOG READ + SESSION ADMIN (kill) + USER ADMIN (lock) gives you live statement monitoring, kill, and lock — DBSheriff's core detection and response. The audit policies and AUDIT READ add the compliance/evidence layer (auth failures, statement history with real identity) that M_ACTIVE_STATEMENTS/M_SQL_PLAN_CACHE alone can't provide. WORKLOAD ADMIN and the schema WITH GRANT OPTION add the two graduated-response rungs (throttle, revoke).

1. The five accounts

If you want a test or victim account for your own validation, create it yourself — it is not one of DBSheriff's accounts and no script creates it. None of the five holds SYSTEM ADMIN, DATA ADMIN, AUDIT ADMIN, or CATALOG ADMIN. Posture (PS63) flags any other account holding AUDIT ADMIN/CATALOG ADMIN as a tampering-exposure risk — DBSheriff's own accounts are exempt by name pattern.

CREATE USER hana_monitor PASSWORD "<pick a strong password>" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT CATALOG READ TO hana_monitor;
GRANT AUDIT READ TO hana_monitor;

CREATE USER hana_kill_user PASSWORD "<pick a strong password>" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT SESSION ADMIN TO hana_kill_user;
GRANT CATALOG READ TO hana_kill_user;

CREATE USER hana_lock_user PASSWORD "<pick a strong password>" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT USER ADMIN TO hana_lock_user;
GRANT SESSION ADMIN TO hana_lock_user;
GRANT CATALOG READ TO hana_lock_user;
GRANT WORKLOAD ADMIN TO hana_lock_user;
-- per classified schema (repeat for every schema holding a CRITICAL/SENSITIVE table):
GRANT SELECT ON SCHEMA <schema> TO hana_lock_user WITH GRANT OPTION;

CREATE USER hana_posture PASSWORD "<pick a strong password>" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT CATALOG READ TO hana_posture;
GRANT AUDIT READ TO hana_posture;

CREATE USER dbsheriff_classify PASSWORD "<pick a strong password>" NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT SELECT ON SCHEMA <schema> TO dbsheriff_classify;   -- repeat per schema to scan

2. Grant reference (why each one is needed)

• CATALOG READ — the single system privilege that unlocks every SYS.M_* monitoring view and SYS.* catalog view (M_ACTIVE_STATEMENTS, M_CONNECTIONS, M_SQL_PLAN_CACHE, M_BLOCKED_TRANSACTIONS, SYS.USERS, SYS.GRANTED_PRIVILEGES, SYS.GRANTED_ROLES, SYS.TABLE_COLUMNS, SYS.M_INIFILE_CONTENTS, SYS.M_ENCRYPTION_OVERVIEW, SYS.AUDIT_POLICIES, SYS.WORKLOAD_CLASSES, SYS.WORKLOAD_MAPPINGS). Read-only, no data access.
• AUDIT READ — live-verified as a separate privilege from CATALOG READ: without it, SELECT * FROM SYS.AUDIT_LOG runs without error but returns 0 rows regardless of how much audit data actually exists. This is easy to miss since nothing fails loudly.
• SESSION ADMIN — lets the holder ALTER SYSTEM DISCONNECT SESSION for a connection other than its own (the kill mechanism), and is also needed by the lock account to terminate a deactivated user's already-open sessions.
• USER ADMIN — ALTER USER ... DEACTIVATE/ACTIVATE USER NOW. Note: the identifier in this statement must be unquoted (ALTER USER HANA_TEST_VICTIM DEACTIVATE USER NOW, not ALTER USER "HANA_TEST_VICTIM" ...) — HANA's parser rejects a double-quoted identifier here with a syntax error, a real, live-confirmed quirk of this specific statement.
• WORKLOAD ADMIN — CREATE/DROP WORKLOAD CLASS and CREATE/DROP WORKLOAD MAPPING, the throttle response rung. Verified to need no edition gate on HANA Express.
• SELECT ... WITH GRANT OPTION on the classified schema — HANA has no broad "grant/revoke on anyone's behalf" system privilege the way Oracle's GRANT ANY OBJECT PRIVILEGE works. To REVOKE/GRANT SELECT on a table you don't own, you must hold that exact privilege yourself, WITH GRANT OPTION. Known limitation (matches Oracle's own documented caveat exactly): revoking a user's direct table grant does not touch a broader schema-level or role-inherited grant the same user might also hold — check both if a revoke doesn't appear to take effect.
• SELECT (classification account) — real value sampling for the review queue; dbsheriff_classify never needs write access.

3. Audit policies (recommended)

scripts/setup_dbsheriff.py hana <hostname> --optional-prereqs=dbsheriff_auth_policy   # automates global_auditing_state + DBSHERIFF_AUTH below (without the flag: global_auditing_state only)

Enable auditing once, instance-wide (by hand, or automated by the command above):

ALTER SYSTEM ALTER CONFIGURATION ('global.ini','SYSTEM')
  SET ('auditing configuration','global_auditing_state')='true' WITH RECONFIGURE;

Known gotcha, live-verified: SYS.M_INIFILE_CONTENTS returns one row per configuration layer (DEFAULT, SYSTEM, DATABASE, HOST) for the same key — after the ALTER SYSTEM above, a fresh instance shows DEFAULT='FALSE' (factory baseline) and SYSTEM='true'/DATABASE='true' (the active override) simultaneously. Always read the most specific layer present, never assume there's only one row.

Then three policies — login/logout (also automated by the command above, since it needs no schema/table target), DML on your classified tables (repeat the ON clause for every table/schema; there's no wildcard-schema form — stays manual), and the fallback fast-DDL detection on each schema you want covered (also manual, same reason; DBSheriff uses it only if M_EXECUTED_STATEMENTS cannot be read, see §5):

CREATE AUDIT POLICY DBSHERIFF_AUTH AUDITING ALL CONNECT LEVEL INFO TRAIL TYPE TABLE RETENTION 90;
ALTER AUDIT POLICY DBSHERIFF_AUTH ENABLE;

CREATE AUDIT POLICY DBSHERIFF_DML AUDITING ALL SELECT, INSERT, UPDATE, DELETE
  ON <schema1>.<table1>, <schema2>.<table2>
  LEVEL INFO TRAIL TYPE TABLE RETENTION 90;
ALTER AUDIT POLICY DBSHERIFF_DML ENABLE;

-- Fallback DDL feed (see §5): DBSheriff reads DDL from M_EXECUTED_STATEMENTS
-- and uses this policy only if that view cannot be read — repeat the ON
-- clause for every schema you want DDL coverage on; there's no
-- wildcard-schema form here either.
CREATE AUDIT POLICY DBSHERIFF_DDL AUDITING ALL CREATE TABLE, ALTER TABLE, DROP TABLE
  ON SCHEMA <schema1>
  LEVEL INFO TRAIL TYPE TABLE RETENTION 90;
ALTER AUDIT POLICY DBSHERIFF_DDL ENABLE;

Note TRAIL TYPE TABLE RETENTION <days> (not just TRAIL TYPE TABLE) is required on multi-tenant (MDC) systems — omitting the retention clause fails with feature not supported: No audit trail types on multidb systems, except TABLE with RETENTION.

Records land in SYS.AUDIT_LOG (columns: TIMESTAMP, USER_NAME, APPLICATION_NAME, CLIENT_IP, EVENT_ACTION, EVENT_STATUS, SCHEMA_NAME, OBJECT_NAME, STATEMENT_STRING, AUDIT_POLICY_NAME, ...) — readable by any account holding AUDIT READ.

HANA also ships a built-in MandatoryAuditPolicy that always logs security-relevant DDL (CREATE/ALTER/DROP AUDIT POLICY, and similar) — no setup needed for that one.

4. Verification

-- global auditing actually on (read the effective layer, not just any row)
SELECT LAYER_NAME, VALUE FROM SYS.M_INIFILE_CONTENTS
  WHERE SECTION='auditing configuration' AND KEY='global_auditing_state';

-- policies enabled
SELECT AUDIT_POLICY_NAME, IS_AUDIT_POLICY_ACTIVE, EVENT_ACTION FROM SYS.AUDIT_POLICIES;

-- audit trail actually has content (as hana_monitor / hana_posture, not SYSTEM)
SELECT COUNT(*) FROM SYS.AUDIT_LOG;

-- live query capture
SELECT s.CONNECTION_ID, c.USER_NAME, s.STATEMENT_STRING
  FROM SYS.M_ACTIVE_STATEMENTS s JOIN SYS.M_CONNECTIONS c ON s.CONNECTION_ID = c.CONNECTION_ID;

Expected producer log line on first successful poll: [<server>][live-poll] nothing new next poll in <n>s (or a captured statement if one was running) — no M_ACTIVE_STATEMENTS/M_CONNECTIONS/M_SESSION_CONTEXT query failed error.

5. Known limitations

• No true multi-instance connection pooling (Postgres/Snowflake/Databricks/ BigQuery-only) — one producer thread per registered HANA server, and the database= field only does SYSTEMDB tenant routing, not pooling across tenants.
• Kill has two modes, both live-verified: "terminate" goes straight to ALTER SYSTEM DISCONNECT SESSION (a hard, full-session disconnect). "cancel_then_terminate" first issues ALTER SYSTEM CANCEL WORK IN SESSION (stops the current statement and HANA rolls back its transaction; the session stays connected) and only escalates to DISCONNECT SESSION if the statement is still running after HANA_KILL_CANCEL_GRACE_S (default 1 s) — unlike SQL Server's KILL, HANA does have a real cancel-only primitive at this privilege level, and the two modes behave differently.
• Revoking a user's direct table grant does not remove a broader schema-level or role-inherited grant on the same object (§2).
• No table/index-level Query Health scanning (statement-delta and tablespace/connection/replication counters only) — same limitation as Oracle.
• Structured/analytic privileges (HANA's row-level-security primitive) are not yet used as an active response rung — the throttle (workload class) and revoke rungs are what's implemented today.

Fast DDL is read from SYS.M_EXECUTED_STATEMENTS, not from the live poll. SYS.M_SQL_PLAN_CACHE never caches DDL statements at all (live-reconfirmed: zero rows for any CREATE/ALTER/DROP TABLE), and the live poll of SYS.M_ACTIVE_STATEMENTS (every second by default; the interval can be changed in the Admin Console) is longer than a typical CREATE/ ALTER/DROP TABLE's real runtime (80-100ms) — so a normal-speed DDL statement is seen by neither. DBSheriff therefore reads DDL from SYS.M_EXECUTED_STATEMENTS, which HANA keeps by default: it needs no audit policy and no setup, and each statement goes through the exact same scoring pipeline every other completed HANA event uses. Only if that view cannot be read does DBSheriff fall back to the DBSHERIFF_DDL audit policy (§3), read from SYS.AUDIT_LOG; it never reads both at once, so a DDL statement is not scored twice. The fallback needs no new grant: the existing monitor account already holds AUDIT READ (§2). DDL slow or blocked enough to also be caught live via M_ACTIVE_STATEMENTS is unaffected — still scored and killed exactly as before.

Connecting to the target securely

setup_dbsheriff.py, create_reset_dbuser.py, install_prereqs.py and onboard_target.py connect as your administrator, and the administrator password and every new account's password cross that connection, so they verify the server's certificate by default (--ssl-mode verify-full; --ca-file <bundle> names the CA, for HANA a PEM trust store (sslTrustStore); --ssl-mode verify-ca skips only the host-name comparison). A refused certificate stops the run and the message names the flag; --ssl-mode require encrypts without verifying (an explicit opt-down that prints a warning) and --ssl-mode disable --allow-plaintext sends everything in clear text (a trusted private link only). A generated password is never printed: it is stored in the registry (encrypted), or written to a new mode-600 file (--credentials-file) whose path is shown. Account names must match [A-Za-z][A-Za-z0-9_]*, and every database, schema or warehouse name the scripts discover is quoted as an identifier before it goes into a statement.