-- ============================================================================
-- Deynao - Dedicated supervision account (creation + read-only privileges)
-- ============================================================================
-- This script CREATES the account (if missing) then grants it CREATE SESSION
-- + read-only SELECT on system views. No write privilege, no DBA role, no access
-- to application data. Generic (no account/server/IP hardcoded).
-- Compatible with Oracle 11g / 12c / 19c / 21c.
--
-- HOW TO RUN: connect as SYS, then run this file. The script prompts for the
-- supervision account name + a password (masked), creates the account if needed
-- and grants the privileges. Safe to re-run.
--   - Windows : Command Prompt on the Oracle server, then
--       sqlplus / as sysdba @C:\path\deynao_grant_privileges_template.sql
--   - Linux   : shell (oracle user) on the Oracle server, then
--       sqlplus / as sysdba @/path/deynao_grant_privileges_template.sql
--   - Remotely (Oracle client installed):
--       sqlplus sys/<password>@<host>:<port>/<service> as sysdba @<file_path>
--   - GUI tool (SQL Developer, Toad...): open the file, run it as a SCRIPT (F5).
-- Then, in Deynao: create a connection with this account to supervise the database.
--
-- WHY SYS (and not SYSTEM): the V$ views are owned by SYS; a standard SYSTEM/DBA
-- account cannot grant privileges on them (ORA-01031).
--
-- MULTITENANT (12c+): for a PDB, connect to the target PDB (LOCAL account). At
-- the CDB$ROOT level, name the account with the C## prefix (otherwise ORA-65096).
-- ============================================================================

SET FEEDBACK OFF
SET VERIFY OFF
SET LINESIZE 120
SET PAGESIZE 100
SET SERVEROUTPUT ON
WHENEVER SQLERROR CONTINUE
WHENEVER OSERROR CONTINUE

PROMPT ============================================================================
PROMPT Deynao - Supervision account setup (read-only)
PROMPT ============================================================================
PROMPT

-- Fleet tip: use the SAME name on all your databases (e.g. DEYNAO_MON) so you can
-- recognise the supervision account everywhere. The account is LOCAL to each database.
ACCEPT oracle_user CHAR PROMPT 'Deynao supervision account name (e.g. DEYNAO_MON): '

-- Password: MASKED input, never echoed or spooled (block under SET ECHO OFF).
-- Ignored if the account already exists. Avoid & and quotes in the password.
SET ECHO OFF
ACCEPT oracle_pwd CHAR PROMPT 'Password for this account (masked): ' HIDE

-- Create the account IF missing (otherwise skipped, no error). On failure
-- (password policy, C## prefix required in CDB$ROOT...), a clear message is
-- printed, then the error stops creation: the following GRANTs will not be
-- applied to a non-existent account (errors visible, no ambiguity).
DECLARE
  v_exists NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_exists FROM dba_users WHERE username = UPPER('&oracle_user');
  IF v_exists = 0 THEN
    EXECUTE IMMEDIATE 'CREATE USER "' || UPPER('&oracle_user') || '" IDENTIFIED BY "&oracle_pwd"';
    DBMS_OUTPUT.PUT_LINE('Account ' || UPPER('&oracle_user') || ' created.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Account ' || UPPER('&oracle_user') || ' already exists: creation skipped.');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('FAILED to create account: ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('Hints: password policy (profile), or C## prefix required in CDB$ROOT.');
    RAISE;
END;
/

-- The password must no longer remain in the SQL*Plus session memory.
UNDEFINE oracle_pwd
SET ECHO OFF

PROMPT Granting read privileges (any errors will be shown below)...

-- ============================================================================
-- [MANDATORY] Connection (without CREATE SESSION, the account cannot connect)
-- ============================================================================
GRANT CREATE SESSION TO &oracle_user;

-- ============================================================================
-- [CRITICAL] Views required for core features
-- ============================================================================

-- Overview + Status
GRANT SELECT ON v_$instance TO &oracle_user;
GRANT SELECT ON v_$database TO &oracle_user;
GRANT SELECT ON v_$version TO &oracle_user;

-- Performance
GRANT SELECT ON v_$sga TO &oracle_user;
GRANT SELECT ON v_$sgainfo TO &oracle_user;
GRANT SELECT ON v_$sgastat TO &oracle_user;
GRANT SELECT ON v_$pgastat TO &oracle_user;
GRANT SELECT ON v_$system_event TO &oracle_user;
GRANT SELECT ON v_$sysstat TO &oracle_user;
GRANT SELECT ON v_$sysmetric TO &oracle_user;
GRANT SELECT ON v_$sys_time_model TO &oracle_user;
GRANT SELECT ON v_$system_wait_class TO &oracle_user;
GRANT SELECT ON v_$segment_statistics TO &oracle_user;
GRANT SELECT ON v_$buffer_pool_statistics TO &oracle_user;

-- Capacity
GRANT SELECT ON dba_tablespaces TO &oracle_user;
GRANT SELECT ON dba_data_files TO &oracle_user;
GRANT SELECT ON dba_temp_files TO &oracle_user;
GRANT SELECT ON dba_free_space TO &oracle_user;
GRANT SELECT ON dba_segments TO &oracle_user;
GRANT SELECT ON v_$temp_space_header TO &oracle_user;

-- Processes and Sessions
GRANT SELECT ON v_$session TO &oracle_user;
GRANT SELECT ON v_$process TO &oracle_user;
GRANT SELECT ON v_$sesstat TO &oracle_user;
GRANT SELECT ON v_$statname TO &oracle_user;
GRANT SELECT ON v_$asm_disk TO &oracle_user;

-- Locks and Waits
GRANT SELECT ON v_$lock TO &oracle_user;
GRANT SELECT ON v_$session_wait TO &oracle_user;
GRANT SELECT ON v_$locked_object TO &oracle_user;

-- Objects
GRANT SELECT ON dba_objects TO &oracle_user;
GRANT SELECT ON dba_tables TO &oracle_user;
GRANT SELECT ON dba_indexes TO &oracle_user;
GRANT SELECT ON dba_views TO &oracle_user;
GRANT SELECT ON dba_synonyms TO &oracle_user;
GRANT SELECT ON dba_source TO &oracle_user;

-- Oracle alerts (alert.log)
GRANT SELECT ON v_$diag_alert_ext TO &oracle_user;
GRANT SELECT ON v_$diag_info TO &oracle_user;

-- Security and Audit
GRANT SELECT ON dba_users TO &oracle_user;
GRANT SELECT ON dba_profiles TO &oracle_user;
GRANT SELECT ON dba_roles TO &oracle_user;
GRANT SELECT ON dba_role_privs TO &oracle_user;
GRANT SELECT ON dba_users_with_defpwd TO &oracle_user;
GRANT SELECT ON v_$pwfile_users TO &oracle_user;

-- ============================================================================
-- [IMPORTANT] Strongly recommended
-- ============================================================================

-- Logging and Archivelog
GRANT SELECT ON v_$log TO &oracle_user;
GRANT SELECT ON v_$logfile TO &oracle_user;
GRANT SELECT ON v_$log_history TO &oracle_user;
GRANT SELECT ON v_$archived_log TO &oracle_user;
GRANT SELECT ON v_$archive_dest TO &oracle_user;
GRANT SELECT ON v_$archive_dest_status TO &oracle_user;

-- Oracle files
GRANT SELECT ON v_$controlfile TO &oracle_user;
GRANT SELECT ON v_$datafile TO &oracle_user;
GRANT SELECT ON v_$recover_file TO &oracle_user;

-- UNDO and Rollback
GRANT SELECT ON dba_undo_extents TO &oracle_user;
GRANT SELECT ON v_$undostat TO &oracle_user;

-- Oracle parameters
GRANT SELECT ON v_$parameter TO &oracle_user;

-- ============================================================================
-- [OPTIONAL] Advanced features
-- ============================================================================

-- RMAN backups and recovery strategy
GRANT SELECT ON v_$rman_backup_job_details TO &oracle_user;
GRANT SELECT ON v_$rman_configuration TO &oracle_user;
GRANT SELECT ON v_$rman_output TO &oracle_user;
GRANT SELECT ON v_$backup_set TO &oracle_user;
GRANT SELECT ON v_$backup_piece TO &oracle_user;
GRANT SELECT ON v_$backup_redolog TO &oracle_user;
GRANT SELECT ON v_$recovery_file_dest TO &oracle_user;
GRANT SELECT ON v_$recovery_area_usage TO &oracle_user;
GRANT SELECT ON v_$flash_recovery_area_usage TO &oracle_user;
GRANT SELECT ON v_$dataguard_config TO &oracle_user;

-- Scheduled jobs (DBMS_SCHEDULER)
GRANT SELECT ON dba_scheduler_jobs TO &oracle_user;
GRANT SELECT ON dba_scheduler_job_run_details TO &oracle_user;
GRANT SELECT ON dba_scheduler_programs TO &oracle_user;

-- Advanced security (full audit)
-- (unified_audit_trail = 12c+; dba_audit_trail = classic audit; a view missing
-- on your version raises an error with no impact on the other privileges)
GRANT SELECT ON dba_sys_privs TO &oracle_user;
GRANT SELECT ON dba_tab_privs TO &oracle_user;
GRANT SELECT ON dba_stmt_audit_opts TO &oracle_user;
GRANT SELECT ON dba_audit_trail TO &oracle_user;
GRANT SELECT ON unified_audit_trail TO &oracle_user;

-- Advanced performance
GRANT SELECT ON v_$sql TO &oracle_user;

-- Licensing (vigilance index: technical detection of options/packs, READ-ONLY.
-- DBA_FEATURE_USAGE_STATISTICS is used to AUDIT feature usage (Oracle-recommended);
-- no AWR / Diagnostic Pack view here.)
GRANT SELECT ON v_$option TO &oracle_user;
GRANT SELECT ON v_$license TO &oracle_user;
GRANT SELECT ON v_$osstat TO &oracle_user;
GRANT SELECT ON dba_registry TO &oracle_user;
GRANT SELECT ON dba_feature_usage_statistics TO &oracle_user;

-- ============================================================================
-- [DASHBOARDS] Additional detailed views (per domain)
-- Required by the DETAILED dashboard views (internal helpers/services).
-- All standard, READ-ONLY, NO AWR / Diagnostic Pack view.
-- ============================================================================

-- Performance (memory / PGA / latches / library cache / I-O / long ops details)
GRANT SELECT ON v_$sga_dynamic_components TO &oracle_user;
GRANT SELECT ON v_$pga_target_advice TO &oracle_user;
GRANT SELECT ON v_$sql_workarea_histogram TO &oracle_user;
GRANT SELECT ON v_$latch TO &oracle_user;
GRANT SELECT ON v_$librarycache TO &oracle_user;
GRANT SELECT ON v_$session_longops TO &oracle_user;
GRANT SELECT ON v_$filestat TO &oracle_user;
GRANT SELECT ON v_$iostat_file TO &oracle_user;

-- Capacity (quotas / resource limits)
GRANT SELECT ON dba_tablespace_usage_metrics TO &oracle_user;
GRANT SELECT ON dba_ts_quotas TO &oracle_user;
GRANT SELECT ON v_$resource_limit TO &oracle_user;

-- Objects (constraints / columns / triggers / compilation errors)
GRANT SELECT ON dba_constraints TO &oracle_user;
GRANT SELECT ON dba_cons_columns TO &oracle_user;
GRANT SELECT ON dba_tab_columns TO &oracle_user;
GRANT SELECT ON dba_triggers TO &oracle_user;
GRANT SELECT ON dba_errors TO &oracle_user;

-- Locks and Waits (enqueues / transactions / pending 2-phase-commit)
GRANT SELECT ON v_$enqueue_stat TO &oracle_user;
GRANT SELECT ON v_$transaction TO &oracle_user;
GRANT SELECT ON dba_2pc_pending TO &oracle_user;

-- Journaling and Files (redo threads / block corruption / traces / listener)
GRANT SELECT ON v_$thread TO &oracle_user;
GRANT SELECT ON v_$database_block_corruption TO &oracle_user;
GRANT SELECT ON v_$diag_trace_file TO &oracle_user;
GRANT SELECT ON v_$listener_network TO &oracle_user;

-- Sessions and Multitenant (services / PDB 12c+)
GRANT SELECT ON v_$services TO &oracle_user;
GRANT SELECT ON v_$pdbs TO &oracle_user;

-- Security and Server alerts (audit sessions / server-generated alerts)
GRANT SELECT ON dba_audit_session TO &oracle_user;
GRANT SELECT ON dba_outstanding_alerts TO &oracle_user;

-- Jobs and DataPump (autotask / DataPump jobs and sessions / directories)
GRANT SELECT ON dba_autotask_client TO &oracle_user;
GRANT SELECT ON dba_datapump_jobs TO &oracle_user;
GRANT SELECT ON dba_datapump_sessions TO &oracle_user;
GRANT SELECT ON dba_directories TO &oracle_user;

-- ============================================================================
-- ALTERNATIVE (pre-prod / lab): instead of the individual grants above,
-- GRANT SELECT_CATALOG_ROLE + SELECT ANY DICTIONARY cover all views.
-- Not recommended in PRODUCTION (grants access to the whole dictionary).
--   GRANT SELECT_CATALOG_ROLE TO &oracle_user;
--   GRANT SELECT ANY DICTIONARY TO &oracle_user;
-- ============================================================================

PROMPT
PROMPT ============================================================================
PROMPT Summary
PROMPT ============================================================================
SET HEADING OFF
SET FEEDBACK OFF
SELECT CASE WHEN COUNT(*) > 0 THEN 'CREATE SESSION : OK'
            ELSE 'CREATE SESSION : MISSING' END
FROM dba_sys_privs
WHERE grantee = UPPER('&oracle_user')
  AND privilege = 'CREATE SESSION';

SELECT 'Read views granted : ' || COUNT(*)
FROM dba_tab_privs
WHERE grantee = UPPER('&oracle_user')
  AND privilege = 'SELECT';
SET HEADING ON

PROMPT
PROMPT Done - account &oracle_user ready for Deynao supervision.
PROMPT An ORA-xxxxx error above = view absent on this Oracle version (no impact
PROMPT on the other privileges).
PROMPT ============================================================================

SET VERIFY ON
EXIT;