Documentation

# Oracle Privilege Model

TuneVault never requires SYSDBA. The `tunevault_reader` role gives read-only access to everything needed for 200+ health checks, ADDM analysis, and SQL Tuning Advisor — with zero write permissions.

## Two Privilege Tiers

Capability | Standard — `tunevault_reader` Recommended | Advanced — OS Auth / SYSDBA
--- | --- | ---
All 200+ health checks | ✅ Yes | ✅ Yes
ADDM analysis | ✅ Yes | ✅ Yes
SQL Tuning Advisor | ✅ Yes | ✅ Yes
Wait event analysis | ✅ Yes | ✅ Yes
Read-only access only | ✅ Enforced — QUOTA 0, no DML | ❌ Full write access
Kill session | ❌ Not available | ✅ Requires ALTER SYSTEM
RMAN backup / recovery | ❌ Not available | ✅ Yes
Listener bounce | ❌ Not available | ✅ Via agent SSH tunnel
ADOP phases (EBS) | ❌ Not available | ✅ Yes
EBS health checks | ⚠️ With EBS APPS grants | ✅ Yes
Enterprise security approval | ✅ Standard security review | ⚠️ DBA-tier sign-off required

For most organizations: use Standard. It covers everything you need for monitoring and optimization. Advanced is for teams that also want TuneVault to execute remediation actions (kill sessions, bounce listeners, run ADOP).

## What Each Grant Does

Every grant in `tunevault_reader` is mapped to a specific capability. Nothing extraneous was added.

CREATE SESSION
Allows the user to connect to the database. Without this, nothing else works.
Oracle Docs ↗
SELECT_CATALOG_ROLE
Read access to all DBA_* and ALL_* data dictionary views: tablespace usage, segment sizes, object counts, undo stats, redo log status, backup status, object invalids, constraint status, and 80+ other health check queries.
Oracle Docs ↗
SELECT ANY DICTIONARY
Read access to V$ dynamic performance views: V$SESSION (active sessions, wait events, blocking locks), V$SQL / V$SQLAREA (top SQL by CPU/IO), V$WAIT_CLASS (system-level wait analysis), GV$ views for RAC environments.
Oracle Docs ↗
EXECUTE ON DBMS_WORKLOAD_REPOSITORY
Required to read AWR snapshots (DBA_HIST_* views) and snap IDs for ADDM analysis windows. Covered by Oracle Diagnostics Pack license on Enterprise Edition.
Oracle Docs ↗
EXECUTE ON DBMS_ADDM
Allows TuneVault to create and run ADDM analysis tasks and retrieve findings. The ADDM task itself is read-only — it analyzes AWR data in memory, no schema changes.
Oracle Docs ↗
EXECUTE ON DBMS_SQLTUNE
SQL Tuning Advisor — analyzes specific SQL statements and returns index/hint/rewrite recommendations. Task execution is transient; no persistent schema objects created under `tunevault`.
Oracle Docs ↗
EXECUTE ON DBMS_STATS
Read optimizer statistics (table row counts, column histograms, index stats). TuneVault uses read-only procedures — no stats are gathered or deleted.
Oracle Docs ↗
EXECUTE ON DBMS_SQL_MONITOR
Real-Time SQL Monitoring — reads execution plans, elapsed time, I/O for currently running SQL. Used for the top-SQL and wait-event panels.
Oracle Docs ↗
ADVISOR
System privilege required to create advisor tasks (ADDM and SQL Tuning). Without this, DBMS_ADDM and DBMS_SQLTUNE calls that create tasks fail with ORA-13605. Tasks created by `tunevault` are owned by `tunevault` and do not affect other users' advisor tasks.
Oracle Docs ↗
SELECT ON V_$SESSION, V_$SQL, V_$SQL_PLAN, V_$SQLAREA
Explicit grants on individual V$ synonyms for databases where SELECT ANY DICTIONARY does not automatically expose V$ views (common on 11g and some 12c configurations). Belt-and-suspenders approach to ensure session + SQL data is accessible.
Oracle Docs ↗

## Setup SQL Script

Hand this to your DBA. Run it as SYSTEM (or any user with the DBA role). TuneVault never asks for SYSDBA unless you choose the Advanced privilege model.

Diagnostics Pack note: DBMS_WORKLOAD_REPOSITORY (AWR) and DBMS_ADDM require Oracle Diagnostics Pack license on Enterprise Edition. If you're on Standard Edition or don't have Diagnostics Pack, ADDM tab will be unavailable — all other health checks work without it.
SQL — run as SYSTEM
Loading…

The same script is available at /setup/role-script for programmatic download.

## Oracle E-Business Suite Additional Grants

EBS health checks query the `APPS` schema (FND tables, WF, AD). These grants are in the setup script as commented-out blocks — uncomment them only if this database hosts EBS.

The EBS grants cover read access only to FND and WF tables. No concurrent program submission, no user admin, no patching. TuneVault uses these to report on queue depth and patch levels — not to modify them.
APPS.FND_CONCURRENT_REQUESTS / _PROGRAMS / _PROGRAMS_TL
Concurrent Manager queue: running, pending, completed requests; program names and application ownership. Powers the CM queue depth and stuck-job checks.
APPS.WF_DEFERRED / WF_ERROR
Workflow background engine queue depth and error queue. TuneVault alerts when either queue exceeds thresholds — indicator of a stalled Workflow Mailer or stuck BG process.
APPS.AD_BUGS / AD_APPLIED_PATCHES
Applied Oracle patch history. Used to detect patch-level gaps and generate ADOP runbook context.
APPS.FND_NODES / FND_OAM_APP_SYS_STATUS
EBS node topology (app tier nodes) and OAM system status for the EBS deep-health panel.

## Verifying the Grant

After running the script, verify access by connecting as `tunevault` and running:

SQL — verification queries
-- Connect as tunevault user first -- sqlplus tunevault/<password>@<host>:<port>/<service> -- Should return a row count (any number) SELECT COUNT(*) FROM dba_segments; -- Should return active sessions count SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE'; -- Should return the role definition (confirms ADVISOR grant) SELECT grantee, granted_role FROM dba_role_privs WHERE grantee = 'TUNEVAULT'; -- Should return EXECUTE privs SELECT privilege, table_name FROM dba_tab_privs WHERE grantee IN ('TUNEVAULT', 'TUNEVAULT_READER') ORDER BY table_name;

## Frequently Asked Questions

### Why SELECT ANY DICTIONARY instead of individual V$ grants?

SELECT ANY DICTIONARY is the Oracle-recommended approach for monitoring tools. It grants read access to all data dictionary views without requiring individual grants for each new V$ view added across Oracle versions. The individual V$ grants in the script are belt-and-suspenders for 11g installs where this doesn't auto-propagate to V$ synonyms.

### Does QUOTA 0 prevent any health checks?

No. QUOTA 0 means the user has no space allocation — it cannot create tables or store data. All health checks are SELECT-only. ADDM and SQL Tuning tasks are transient (created in SGA, not permanent schema objects under the tunevault schema). DBMS_STATS reads are all SELECT.

### What happens if I use tunevault_reader with the ADDM tab?

ADDM works with tunevault_reader. The ADVISOR privilege + DBMS_ADDM EXECUTE grant covers task creation and retrieval. You need Oracle Diagnostics Pack license to access AWR data (DBA_HIST_* views) — that's an Oracle licensing requirement, not a TuneVault restriction.

### Can I restrict which databases the user can connect to?

Yes. Oracle connection-level security (sqlnet.ora) can restrict which hostnames and services a user can connect from. The tunevault user should only have network access to your Oracle listener — it doesn't need access to anything else. Use Oracle's Connection Manager (CMAN) or network ACLs to further restrict if needed.

### What about CDB/PDB environments?

For CDB root connections, the grants above apply to the root container. To monitor individual PDBs, connect TuneVault directly to the PDB service name and run the script inside that PDB. TuneVault auto-detects CDB vs non-CDB via `SELECT CDB FROM V$DATABASE`.

Questions? support@tunevault.app · Security page · Oracle setup guide · Add a connection →