DBSheriff for Oracle Database — prerequisites and accounts

DBSheriff connects with its own dedicated accounts — never your application's credentials — each limited to what its job needs (the last section says exactly what each one can do if its password leaks). Setup has two parts: server prerequisites (making sure the unified audit trail is actually writing records) and the accounts themselves. Three scripts automate all of it; Oracle Database 12.2+ is required (19c+ recommended), as is Amazon RDS for Oracle and Oracle Database Free. Run every statement below inside the pluggable database your data lives in (sqlplus system/…@//host:1521/PDB), never the CDB root.

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 oracle db1.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 are interactive (they ask which platform, which accounts, and for database credentials), connect only as a database user — never a cloud credential — and never restart the database server. If a change needs a restart to take effect, they tell you a restart is needed, point you to the restart command for your platform in this guide and stop there; you restart on your own schedule and re-run to finish. Every one of them also asks for the Oracle PDB service name (never the CDB root's — the value after the final / in sqlplus system/…@//host:1521/PDB) before connecting; a connection that resolves to CDB$ROOT is refused rather than acting on the wrong container. Add --dry-run to any of them to preview without changing anything. To create fewer than all five: pass --roles=monitor,kill to setup_dbsheriff.py, or answer create_reset_dbuser.py's own account-selection prompt with the numbers/names you want (or all).

Add --check to any of them for a read-only status report instead: what's already there, what's missing, and the exact command to fix each gap — nothing is ever written, not even if you say yes to something (there's no prompt to say yes to). Exits with a non-zero status if anything's missing, so it's usable in a script.

The 5 accounts

None of them holds DBA, SYSDBA or SELECT ANY TABLE, but oracle_lock_user holds ALTER USER and GRANT ANY OBJECT PRIVILEGE, which together are data-access-equivalent (it can re-password any account and grant itself or anyone SELECT on any object): see "What each account can do if its password leaks" at the end.

Creating the accounts

Identical on onprem, GCP and Azure (a VM you fully control, the same true-DBA privilege model everywhere) — Amazon RDS for Oracle is the one place platform matters, since its master user can grant neither AUDIT_VIEWER nor ALTER SYSTEM at all.

CREATE USER oracle_monitor IDENTIFIED BY "<password>";
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO oracle_monitor;
GRANT AUDIT_VIEWER TO oracle_monitor;                 -- RDS: see below
-- oracle_posture: the same two statements above (same account statement, same two grants).

CREATE USER oracle_kill_user IDENTIFIED BY "<password>";
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO oracle_kill_user;
GRANT ALTER SYSTEM TO oracle_kill_user;                -- RDS: see below

CREATE USER oracle_lock_user IDENTIFIED BY "<password>";
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO oracle_lock_user;
GRANT ALTER USER, ALTER SYSTEM, GRANT ANY OBJECT PRIVILEGE TO oracle_lock_user;   -- RDS: see below

CREATE USER dbsheriff_classify IDENTIFIED BY "<password>";
GRANT CREATE SESSION, SELECT_CATALOG_ROLE TO dbsheriff_classify;

On Amazon RDS for Oracle, the master user cannot grant AUDIT_VIEWER (ORA-01924) or ALTER SYSTEM (ORA-20997) — substitute these four statements for the three marked RDS: see below above:

GRANT READ ON audsys.unified_audit_trail TO oracle_monitor;   -- instead of AUDIT_VIEWER (oracle_posture too)
GRANT EXECUTE ON rdsadmin.rdsadmin_util TO oracle_kill_user;  -- instead of ALTER SYSTEM
GRANT ALTER USER, GRANT ANY OBJECT PRIVILEGE TO oracle_lock_user;
GRANT EXECUTE ON rdsadmin.rdsadmin_util TO oracle_lock_user;  -- instead of ALTER SYSTEM

dbsheriff_classify needs no per-table grant to function — classification runs from names, statistics, comments and native registrations alone. Granting READ on the tables to sample adds value-based evidence on top; see "Optional extras" below. Write-back for the lock account and the SQL Firewall viewer role are optional the same way — documented there, never auto-executed by the scripts.

Which schemas a classification scan reads. The registration's Scan databases field (the onboarding command asks it as Schemas to scan) is, for Oracle, a schema filter: schema (owner) names such as FINANCE, several separated by commas. Blank reads every schema the scan login can see except the ones Oracle maintains itself. A service or PDB name in the field (such as FREEPDB1) matches no schema, so it is ignored with a note, and a filter that matches no schema at all ends the scan COMPLETED with a warning that names the filter and the schemas that do exist, shown beside the scan in the scan history.

Prerequisites

DBSheriff needs the unified audit trail actually writing records (required — without it, only V$SQL execution deltas are visible, attributed to the parsing schema rather than the real user, and there is no failed-login evidence). How you confirm and, if needed, enable this is different by mode and platform — the scripts detect which applies automatically:

• Pure mode (Oracle 23ai and later — always on). Nothing to enable; install_prereqs.py --check reports this directly (V$OPTION's Unified Auditing is TRUE) and the audit_trail parameter below doesn't apply at all.
• Mixed mode (12.2–21c, and Amazon RDS for Oracle at every version). Requires audit_trail = 'DB,EXTENDED' and a restart — see your platform below.

scripts/setup_dbsheriff.py oracle <hostname>   # choose your platform when asked

Onprem / self-managed, GCP, Azure (self-managed Oracle on a VM)

ALTER SYSTEM SET audit_trail = 'DB,EXTENDED' SCOPE = SPFILE;

Multitenant (CDB/PDB) installs: this is an instance-level parameter — it can only be set from the CDB root, never from within a PDB (ORA-65040, confirmed live). Connect AS SYSDBA to the CDB's own service (not the PDB service you use for everything else in this guide) to run it. On a non-CDB install (12.1-, a non-multitenant Oracle Database Free/RDS instance) there is no such distinction — run it on the same connection you use for everything else.

Restart to apply (audit_trail is a static parameter):

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP;

Amazon RDS for Oracle

scripts/setup_dbsheriff.py oracle <hostname>   # choose "aws"; asks for the RDS instance identifier

Stages the change through your own authenticated aws CLI session — no cloud credential is asked for. From a source checkout, with the aws CLI installed and signed in, the script stages the change itself; through stack.sh tool on an installed host the container has no cloud CLI, so the script says it could not stage the change and sends you here: run the command under "By hand instead of the script" below yourself, with the comma escaped as shown there. The RDS master user gets ORA-01031 (insufficient privileges) on a direct ALTER SYSTEM SET, confirmed live — the parameter group is the only way in. It refuses only if the instance is still on AWS's uneditable default parameter group; create and attach a custom one first, then re-run:

aws rds create-db-parameter-group --db-parameter-group-name dbsheriff-oracle-se2-19 \
    --db-parameter-group-family oracle-se2-19 --description "DBSheriff settings"
aws rds modify-db-instance --db-instance-identifier <instance> \
    --db-parameter-group-name dbsheriff-oracle-se2-19 --apply-immediately

By hand instead of the script:

aws rds modify-db-parameter-group --db-parameter-group-name dbsheriff-oracle-se2-19 \
    --parameters "ParameterName=audit_trail,ParameterValue=DB\,EXTENDED,ApplyMethod=pending-reboot"
aws rds reboot-db-instance --db-instance-identifier <instance>

The \, matters: the AWS CLI splits a --parameters value at every comma, so ParameterValue=DB,EXTENDED is refused with Invalid type for parameter Parameters[0].ParameterValue.

Optional: the two shipped audit policies

ORA_SECURECONFIG (users, privileges, audit policy and ALTER SYSTEM changes) and the logon-failure policy — named ORA_LOGON_FAILURES on most installs, ORA_LOGIN_LOGOUT is used by some newer releases; the scripts detect live which one your server actually defines — extend DBSheriff's evidence pack with failed-login and privilege-change records. Plain SQL, identical on every platform including RDS (no parameter group involved, confirmed live), no restart:

AUDIT POLICY ORA_SECURECONFIG;
AUDIT POLICY ORA_LOGON_FAILURES;   -- or ORA_LOGIN_LOGOUT, whichever your server defines

Optional extras (never automated — apply by hand when you want them)

-- lock account write-back (approval decisions, VPD deny rung) — must be granted by SYS itself
-- (RDS: rdsadmin.rdsadmin_util.grant_sys_object), not by a DBA-privileged admin account
GRANT CREATE PROCEDURE, COMMENT ANY TABLE TO oracle_lock_user;
GRANT EXECUTE ON sys.dbms_rls TO oracle_lock_user;
GRANT ADMINISTER ROW LEVEL SECURITY POLICY TO oracle_lock_user;   -- 23ai+ only
GRANT EXECUTE ON sys.dbms_tsdp_manage TO oracle_lock_user;        -- Enterprise Edition / Free only

-- SQL Firewall viewer (23ai+, when oracle_firewall is on)
GRANT SQL_FIREWALL_VIEWER TO oracle_monitor;
GRANT SQL_FIREWALL_VIEWER TO oracle_posture;   -- if you created that account: it reads the firewall state for the posture check

-- classification: sample values directly (classification still works from
-- names/statistics/comments without this)
GRANT READ ON finance.customers TO dbsheriff_classify;

-- audit policy on the classified tables themselves (identity for every classified read)
CREATE AUDIT POLICY dbsheriff_dml ACTIONS SELECT ON finance.customers, INSERT ON finance.customers,
  UPDATE ON finance.customers, DELETE ON finance.customers;
AUDIT POLICY dbsheriff_dml EXCEPT oracle_monitor, oracle_kill_user, oracle_lock_user, dbsheriff_classify;

Register the server in DBSheriff

Admin Console → Monitored Servers → Add: engine oracle, host, port (1521, or the TCPS port of a TLS listener), service name (the PDB service) as the database, the accounts you created, ssl_mode (see "TLS to a self-managed listener" below for what each mode needs). The form's Oracle switches (each also an API field of the same name): Oracle statement source oracle_statement_source (auto: the audit trail first, V$SQL for the rest; audit; sqlstats), Oracle audit source oracle_audit_source (auto, unified, view, traditional, none), Oracle audit view oracle_audit_view (OWNER.VIEW_NAME, for the view source; blank looks for DBSHERIFF_AUDIT), read ASH oracle_diag_pack (needs a Diagnostics Pack licence), read SQL Firewall violations oracle_firewall (23ai and later) and oracle_tag_writeback. For RDS also aws_region + rds_instance_id (these two name the RDS instance DBSheriff reads with its own cloud identity, so only an unrestricted approver sets or changes them).

Verify

-- as oracle_monitor
SELECT COUNT(*) FROM v$session;                                  -- SELECT_CATALOG_ROLE
SELECT MAX(event_timestamp_utc) FROM unified_audit_trail;        -- AUDIT_VIEWER / READ; NULL if the trail isn't active yet. On 12.2 use event_timestamp: event_timestamp_utc does not exist there
SELECT policy_name FROM audit_unified_enabled_policies;          -- ORA_SECURECONFIG, ORA_LOGON_FAILURES at minimum

DBSheriff connects with oracle_monitor when it starts monitoring the server (at the next registry reload, within five minutes of saving it); a login the target refuses shows as a connection error in the producer's log. The other accounts connect when their job first runs (the posture/governance collector, a classification scan, a kill or a lock). Live data appears within a minute; the first posture/governance snapshot within the hour.

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 Oracle a PEM file under any name or a wallet directory that holds ewallet.pem; the connection uses tcps://, so a listener without TCPS needs --ssl-mode disable --allow-plaintext, or the TCPS endpoint that "TLS to a self-managed listener" below sets up). 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.

TLS to a self-managed listener (TCPS)

Every DBSheriff tool connects over tcps:// and checks the certificate by default, but a stock Oracle listener answers only in plain text on 1521: a package or installer install, and the gvenzl/oracle-free and gvenzl/oracle-xe container images, have no TCPS endpoint until you add one. (Amazon RDS for Oracle gets it from the SSL option of its option group, and an Autonomous Database always has it; this section is for a host you run yourself, on-premises or on a cloud VM.) On RDS for Oracle the option's default cipher suite may not be negotiable by the client library in use (the handshake fails with a TLS alert): set SQLNET.CIPHER_SUITE in the option's settings to a suite both sides support (for example an ECDHE-RSA AES-GCM suite) and keep the same value in the client's sqlnet.ora. Either route below ends the same way: a TCPS port, conventionally 2484, that presents a certificate. Your applications keep using 1521; only DBSheriff's connections use the TLS port.

Route A: Oracle's own TCPS endpoint, on a full Oracle installation (needs orapki). As the Oracle software owner on the database server, make a wallet with a self-signed certificate and export it:

orapki wallet create -wallet /opt/oracle/wallet/dbsheriff -auto_login -pwd '<wallet password>'
orapki wallet add    -wallet /opt/oracle/wallet/dbsheriff -dn "CN=db1.acme.internal" -keysize 2048 \
                     -self_signed -validity 3650 -pwd '<wallet password>'
orapki wallet export -wallet /opt/oracle/wallet/dbsheriff -dn "CN=db1.acme.internal" -cert /tmp/db1-cert.pem

(With a certificate from your own CA instead: orapki wallet add without -self_signed makes the request, you sign it, then import the CA with -trusted_cert -cert ca.pem and the signed certificate with -user_cert -cert db1-signed.pem.) Add the endpoint and the wallet to the server's listener.ora and sqlnet.ora:

# listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1.acme.internal)(PORT = 1521)))
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = db1.acme.internal)(PORT = 2484))))
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /opt/oracle/wallet/dbsheriff)))

# sqlnet.ora
SSL_CLIENT_AUTHENTICATION = FALSE
WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /opt/oracle/wallet/dbsheriff)))

then, as SYS in the CDB, make the instance register with both endpoints and restart the listener (the database and its sessions are untouched; new connections fail for a few seconds):

ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=db1.acme.internal)(PORT=1521))(ADDRESS=(PROTOCOL=TCPS)(HOST=db1.acme.internal)(PORT=2484)))' SCOPE=BOTH;

lsnrctl stop && lsnrctl start

The option names vary a little between Oracle releases; Oracle's Database Security Guide (the chapter on TLS) has the form for yours. The container images above ship without the PKI classes orapki runs on (Could not find or load main class oracle.security.pki.textui.OraclePKITextUI, in the slim and the full image), so use route B there.

Route B: a TLS terminator in front of the listener (stunnel). It needs nothing from Oracle: stunnel accepts TLS on 2484 and forwards the plain stream to the listener on the same host. This is the setup DBSheriff's platform test ran against (Oracle Free in Docker on an Azure VM), with verify-ca and verify-full checked against the exported certificate:

sudo apt-get install -y stunnel4
sudo mkdir -p /etc/stunnel/tls
sudo openssl req -x509 -newkey rsa:2048 -nodes -days 3650 \
     -keyout /etc/stunnel/tls/server.key -out /etc/stunnel/tls/server.crt \
     -subj "/CN=db1.acme.internal" -addext "subjectAltName=DNS:db1.acme.internal,IP:203.0.113.10"
sudo chmod 600 /etc/stunnel/tls/server.key

# /etc/stunnel/oracle-tls.conf
foreground = no
pid = /run/stunnel-oracle.pid
[oracle-tcps]
accept  = 0.0.0.0:2484
connect = 127.0.0.1:1521
cert    = /etc/stunnel/tls/server.crt
key     = /etc/stunnel/tls/server.key
sslVersion = TLSv1.2

Start it with sudo stunnel4 /etc/stunnel/oracle-tls.conf (or put the file in /etc/stunnel/ and enable the stunnel4 service), and let port 2484 through the host's firewall or security group for the DBSheriff host only. Oracle sees every session that comes through the terminator as coming from the terminator's address, so send only DBSheriff through it: an application connected that way would lose its real client address in V$SESSION and the audit trail.

What ORACLE_SSL_CA must hold. One PEM file on the DBSheriff host, mounted into the containers (ORACLE_SSL_CA=/certs/oracle-ca.pem and ORACLE_SSL_CA_HOST_PATH=/etc/dbsheriff/oracle-ca.pem in the stack's .env, then bring the stack up again): the certificate of the CA that signed the listener's certificate, or, for a self-signed one, the listener's own certificate (/tmp/db1-cert.pem from route A, server.crt from route B). The file can have any name. Certificates of several servers or CAs may be concatenated in it; there is one bundle for all Oracle servers of the installation. A directory that holds an ewallet.pem is accepted as well. A certificate that a public CA signed needs no file: it is checked against the operating system's trust store. Without the file, a server in a verifying mode fails its connection with a message that names ORACLE_SSL_CA.

Which ssl_mode.

The same three words are the workstation scripts' --ssl-mode, with --ca-file <the same PEM> and --port 2484; onboard_target.py also takes --registration-ssl-mode for the mode DBSheriff registers the server with. To check the endpoint before registering:

openssl s_client -connect db1.acme.internal:2484 </dev/null 2>/dev/null | openssl x509 -noout -subject -issuer -dates
scripts/install_prereqs.py oracle db1.acme.internal --port 2484 --check --ssl-mode verify-ca --ca-file db1-cert.pem

Put the exported certificate (db1-cert.pem) in the directory you run the tool from. With stack.sh tool, a file you pass (--ca-file, --credentials-file) must be in the current directory: that is the only host directory the tool can see.

What each account can do if its password leaks

DBSheriff keeps these credentials encrypted in its registry (SERVER_CREDENTIALS_KEY); whoever can read both the registry and that key holds every account above. Create only the accounts you will use (--roles).