-- tunevault_reader.sql
-- Canonical least-privilege role for TuneVault monitoring.
--
-- Run this as SYSTEM (or any DBA) on the target Oracle database.
-- TuneVault never asks for SYSDBA unless you explicitly choose the Advanced privilege model.
--
-- What this grants:
--   SELECT_CATALOG_ROLE  — read all DBA_*/ALL_* dictionary views (health checks, space, stats)
--   SELECT ANY DICTIONARY — V$SESSION, V$SQL, V$SQLAREA, V$WAIT_CLASS, GV$ views (RAC)
--   DBMS_WORKLOAD_REPOSITORY EXECUTE — AWR snapshot reads for ADDM
--   DBMS_ADDM EXECUTE — run and read ADDM analysis tasks
--   DBMS_SQLTUNE EXECUTE — SQL Tuning Advisor recommendations
--   DBMS_STATS EXECUTE — read optimizer statistics (no writes needed)
--   DBMS_SQL_MONITOR EXECUTE — Real-Time SQL Monitoring
--   ADVISOR — required to create and read advisor tasks (ADDM, SQL Tuning)
--   V$SESSION/SQL/SQL_PLAN/SQLAREA SELECT — runtime session + SQL inspection
--
-- What this does NOT grant:
--   ALTER SYSTEM, ALTER DATABASE, CREATE ANY TABLE, DROP ANY OBJECT — no writes
--   SYSDBA, SYSOPER — no elevated OS-level access
--   Kill session, RMAN, listener bounce — use the Advanced (OS Auth) model for those
--
-- EBS-specific grants are gated behind the EBS toggle in TuneVault settings.
-- Run the EBS block only if this database hosts Oracle E-Business Suite.

-- ── Step 1: Create the role ──────────────────────────────────────────────────
CREATE ROLE tunevault_reader;

-- ── Step 2: Core grants ───────────────────────────────────────────────────────
GRANT CREATE SESSION TO tunevault_reader;
GRANT SELECT_CATALOG_ROLE TO tunevault_reader;
GRANT SELECT ANY DICTIONARY TO tunevault_reader;

-- AWR + advisor packages (Diagnostics Pack license required for AWR/ADDM on EE)
GRANT EXECUTE ON DBMS_WORKLOAD_REPOSITORY TO tunevault_reader;
GRANT EXECUTE ON DBMS_ADDM TO tunevault_reader;
GRANT EXECUTE ON DBMS_SQLTUNE TO tunevault_reader;
GRANT EXECUTE ON DBMS_STATS TO tunevault_reader;
GRANT EXECUTE ON DBMS_SQL_MONITOR TO tunevault_reader;
GRANT ADVISOR TO tunevault_reader;

-- Dynamic performance views — explicitly grant for non-CDB / older 11g installs
GRANT SELECT ON V_$SESSION TO tunevault_reader;
GRANT SELECT ON V_$SQL TO tunevault_reader;
GRANT SELECT ON V_$SQL_PLAN TO tunevault_reader;
GRANT SELECT ON V_$SQLAREA TO tunevault_reader;

-- The six DBA_HIST_* views that need NO Diagnostics Pack licence.
--
-- Oracle's Database Licensing Information User Manual gates the DBA_HIST_* prefix
-- behind the Diagnostics Pack, then names exactly six exceptions:
--
--   "The only exception are the views: DBA_HIST_SNAPSHOT, DBA_HIST_DATABASE_INSTANCE,
--    DBA_HIST_SNAP_ERROR, DBA_HIST_SEG_STAT, DBA_HIST_SEG_STAT_OBJ, and
--    DBA_HIST_UNDOSTAT. They can be used without the Oracle Diagnostics Pack license."
--
-- Granting these is therefore safe on an UNLICENSED database. They are explicit
-- rather than strictly necessary: an installation that also grants
-- SELECT_CATALOG_ROLE can already read them. These lines make the six readable on
-- a least-privilege setup that deliberately withholds SELECT_CATALOG_ROLE, and
-- document WHICH DBA_HIST_* views are licence-free, which is the part people get
-- wrong.
--
-- NOTE for multitenant: on a CDB, undo is a container-level construct and
-- DBA_HIST_UNDOSTAT populates in CDB$ROOT. Queried from inside a PDB it is empty
-- even with the grant and a valid licence — verified on a 19c EBS CDB where root
-- held 250 rows and the PDB held none. A PDB-scoped connection will not see undo
-- history no matter what is granted here.
--
-- Deliberately NOT granted: every other DBA_HIST_* view. Reading those on an
-- unlicensed database is pack usage, and Oracle counts "direct access to the
-- underlying data" as usage just as much as Enterprise Manager.
--
-- Skip any ORA-00942 — DBA_HIST_* views do not exist on Standard Edition.
GRANT SELECT ON DBA_HIST_UNDOSTAT TO tunevault_reader;
GRANT SELECT ON DBA_HIST_SNAPSHOT TO tunevault_reader;
GRANT SELECT ON DBA_HIST_DATABASE_INSTANCE TO tunevault_reader;
GRANT SELECT ON DBA_HIST_SNAP_ERROR TO tunevault_reader;
GRANT SELECT ON DBA_HIST_SEG_STAT TO tunevault_reader;
GRANT SELECT ON DBA_HIST_SEG_STAT_OBJ TO tunevault_reader;

-- ── Step 3: Create the monitoring user ───────────────────────────────────────
CREATE USER tunevault IDENTIFIED BY "<replace-with-strong-password>";
GRANT tunevault_reader TO tunevault;

-- Zero quota — TuneVault never writes to the database
ALTER USER tunevault DEFAULT TABLESPACE USERS QUOTA 0 ON USERS;

-- ── Step 4 (EBS only): Grant APPS schema read access ─────────────────────────
-- Run this block ONLY if this database hosts Oracle E-Business Suite.
-- Run as APPS or a DBA user with grant option.
-- Skip any ORA-00942 errors — some views vary by EBS version and configuration.
GRANT SELECT ON APPS.FND_CONCURRENT_QUEUES TO tunevault_reader;
GRANT SELECT ON APPS.FND_CONCURRENT_QUEUES_VL TO tunevault_reader;
GRANT SELECT ON APPS.FND_CONCURRENT_REQUESTS TO tunevault_reader;
GRANT SELECT ON APPS.FND_PRODUCT_GROUPS TO tunevault_reader;
GRANT SELECT ON APPS.AD_TRACKABLE_ENTITIES TO tunevault_reader;
GRANT SELECT ON APPS.FND_NODES TO tunevault_reader;
GRANT SELECT ON APPS.AD_ADOP_SESSIONS TO tunevault_reader;
GRANT SELECT ON APPS.AD_ADOP_SESSION_PATCHES TO tunevault_reader;
GRANT SELECT ON APPS.FND_PROFILE_OPTIONS TO tunevault_reader;
GRANT SELECT ON APPS.FND_PROFILE_OPTION_VALUES TO tunevault_reader;
GRANT SELECT ON APPS.FND_SVC_COMP_PARAM_VALS_V TO tunevault_reader;
GRANT SELECT ON APPS.FND_CONCURRENT_QUEUE_SIZE TO tunevault_reader;
GRANT SELECT ON APPS.FND_SVC_COMPONENTS TO tunevault_reader;
GRANT SELECT ON APPS.WF_ERROR TO tunevault_reader;
GRANT SELECT ON APPS.WF_NOTIFICATIONS TO tunevault_reader;
GRANT SELECT ON APPS.FND_OAM_METVAL TO tunevault_reader;

-- Added 2026-08-26 after auditing every object the product queries against what
-- this script actually grants. These 15 were queried by shipping checks and
-- granted nowhere, so those checks failed with ORA-00942 on every run and, until
-- the same day, rendered as empty panels rather than as errors.
--
-- Verified to exist on a live EBS 12.2.12 / 19c database. Most are APPS synonyms
-- onto APPLSYS tables, which is why this block must run as APPS.
--
-- The surface each one serves is named so a future reader can tell what breaks if
-- a site declines a particular grant.
GRANT SELECT ON APPS.DUAL TO tunevault_reader;                        -- EBS detection probe (gates all EBS checks)
GRANT SELECT ON APPS.FND_USER TO tunevault_reader;                    -- security: user/account checks
GRANT SELECT ON APPS.FND_RESPONSIBILITY_TL TO tunevault_reader;       -- security: responsibility assignment
GRANT SELECT ON APPS.FND_USER_RESP_GROUPS_DIRECT TO tunevault_reader; -- security: direct responsibility grants
GRANT SELECT ON APPS.ICX_SESSIONS TO tunevault_reader;                -- security: active EBS sessions
GRANT SELECT ON APPS.FND_CONCURRENT_PROGRAMS_VL TO tunevault_reader;  -- DB Ops: concurrent program lookup
GRANT SELECT ON APPS.AD_APPLIED_PATCHES TO tunevault_reader;          -- patch advisor
GRANT SELECT ON APPS.AD_BUGS TO tunevault_reader;                     -- patch advisor
GRANT SELECT ON APPS.AD_PATCH_RUN_BUGS TO tunevault_reader;           -- patch advisor
GRANT SELECT ON APPS.WF_ITEMS TO tunevault_reader;                    -- workflow: open/stuck items
GRANT SELECT ON APPS.WF_ITEM_ACTIVITY_STATUSES TO tunevault_reader;   -- workflow: errored activities
GRANT SELECT ON APPS.WF_AGENTS TO tunevault_reader;                   -- workflow: agent listener status
GRANT SELECT ON APPS.WF_DEFERRED TO tunevault_reader;                 -- workflow: deferred queue depth
GRANT SELECT ON APPLSYS.WF_NOTIFICATIONS TO tunevault_reader;         -- workflow: notification backlog
GRANT SELECT ON APPLSYS.WF_ITEM_ACTIVITY_STATUSES TO tunevault_reader;-- workflow: EBS Ops error drill-in
--
-- DELIBERATELY NOT GRANTED: APPLSYS.WF_DEFERRED_TABLE_M and
-- APPLSYS.WF_ERROR_TABLE_M. Those are AQ internal tables, not standard-grantable
-- on EBS 12.2, and the checks that used them were removed on 2026-06-24. Do not
-- add them back because an audit script lists them.
GRANT SELECT ON APPS.FND_PRODUCT_INSTALLATIONS TO tunevault_reader;

-- ── Step 5 (Security Posture scanner): Views used by /vault Security Posture ──
-- All 10 check groups use read-only SELECTs. On 12c+ with SELECT_CATALOG_ROLE
-- + SELECT ANY DICTIONARY (Step 2), NO additional grants are needed.
-- Listed here for DBAs who prefer explicit grants, and to document coverage.
--
-- Group                   Views queried
-- ──────────────────────────────────────────────────────────────────────────────
-- sp_default_passwords    DBA_USERS_WITH_DEFPWD, DBA_USERS
-- sp_priv_escalation      DBA_SYS_PRIVS, DBA_USERS (oracle_maintained),
--                         DBA_ROLE_PRIVS, V$PWFILE_USERS, DBA_TAB_PRIVS
-- sp_public_grants        DBA_SYS_PRIVS
-- sp_init_params          V$PARAMETER
-- sp_password_policy      DBA_PROFILES
-- sp_audit_trail          V$PARAMETER, AUDIT_UNIFIED_POLICIES (12c+, optional)
-- sp_encryption           V$ENCRYPTION_WALLET, DBA_TABLESPACES, DBA_ENCRYPTED_COLUMNS
-- sp_account_hygiene      DBA_USERS, DBA_SYS_PRIVS
-- sp_db_links             DBA_DB_LINKS
-- sp_patch_currency       V$INSTANCE, V$VERSION, DBA_REGISTRY_SQLPATCH
--
-- SELECT_CATALOG_ROLE covers: DBA_USERS_WITH_DEFPWD, DBA_SYS_PRIVS, DBA_USERS,
--   DBA_PROFILES, DBA_ROLE_PRIVS, DBA_TAB_PRIVS, DBA_TABLESPACES,
--   DBA_ENCRYPTED_COLUMNS, DBA_DB_LINKS, DBA_REGISTRY_SQLPATCH, AUDIT_UNIFIED_POLICIES.
-- SELECT ANY DICTIONARY covers: V$PARAMETER, V$PWFILE_USERS, V$ENCRYPTION_WALLET,
--   V$INSTANCE, V$VERSION (via V_$ synonyms).
--
-- On 11g without SELECT ANY DICTIONARY, add explicit V$ grants:
--
-- GRANT SELECT ON V_$PARAMETER           TO tunevault_reader;
-- GRANT SELECT ON V_$PWFILE_USERS        TO tunevault_reader;
-- GRANT SELECT ON V_$ENCRYPTION_WALLET   TO tunevault_reader;
-- GRANT SELECT ON V_$INSTANCE            TO tunevault_reader;
-- GRANT SELECT ON V_$VERSION             TO tunevault_reader;
--
-- Note: V$PWFILE_USERS lists SYSDBA/SYSOPER accounts from the password file.
--   On 12c+, SELECT ANY DICTIONARY covers it. On 11g, explicit grant required.
-- Note: V$ENCRYPTION_WALLET requires TDE to be configured; query returns 0 rows
--   if TDE is not enabled (scanner handles this gracefully — no error thrown).

-- ── Verification ──────────────────────────────────────────────────────────────
-- Run as tunevault to verify access:
--   SELECT COUNT(*) FROM dba_segments;
--   SELECT COUNT(*) FROM v$session;
--   SELECT dbms_metadata.get_ddl('ROLE','TUNEVAULT_READER') FROM dual;
--   -- Security posture checks:
--   SELECT COUNT(*) FROM DBA_USERS_WITH_DEFPWD WHERE ACCOUNT_STATUS='OPEN';
--   SELECT COUNT(*) FROM DBA_SYS_PRIVS WHERE GRANTEE='PUBLIC';
--   SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';
--   SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail';

-- Optional CEMLI inventory metadata (commercial intelligence).
-- Customer approval is required before applying these SELECT-only grants.
-- Existing installations remain unchanged; unavailable categories return UNKNOWN.
GRANT SELECT ON APPS.FND_CONCURRENT_PROGRAMS TO tunevault_reader;
GRANT SELECT ON APPS.FND_FORM TO tunevault_reader;
GRANT SELECT ON APPS.FND_MENUS TO tunevault_reader;
GRANT SELECT ON APPS.FND_RESPONSIBILITY TO tunevault_reader;
GRANT SELECT ON APPS.FND_FLEX_VALUE_SETS TO tunevault_reader;
GRANT SELECT ON APPS.FND_LOOKUP_TYPES TO tunevault_reader;