-- 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;

-- ── 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;
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';