KB: How to create the monitoring account for the Oracle monitoring

In some situations, an account used for Oracle monitoring should be locked down due to security restrictions. The table below shows which “Oracle tables” the account needs to have at least read access rights on.

Create the Monitoring user

First we start with creating the Oracle user that we are going to use for the monitoring.

-------------------------------------------
-- Script to use for creating the oracle user
-- account that is used for the monitoring
-- Notice: For MP versions 1.3.17.x or higher.
-------------------------------------------

------------------------------
-- create the Monitoring user
------------------------------
CREATE USER opslogix 
IDENTIFIED BY opslogix -- Assign password
DEFAULT TABLESPACE users -- Assign space for table and index segments
TEMPORARY TABLESPACE temp -- Assign sort space 
QUOTA UNLIMITED ON users -- Assign unlimited quota 

After you have created the new user you will have 2 options to assign the privileges to this account. Best when looking at security is to use option 2 but for testing purposes option 1 is the quickest.

Option 1: Max read only privileges

------------------------------
-- Use the grant below to just give select privs to all tables.
-- Best practice is to use the minimal privileges (option 2) as the example below this one.
------------------------------
GRANT CREATE SESSION, SELECT ANY DICTIONARY,SELECT ANY TABLE TO opslogix -- Assign privileges

Option 2: Minimal Privileges

------------------------------
-- If you would like to run the Oracle monitoring account with minimal privileges, the table below shows which tables/views the Oracle monitoring account 
-- needs to be able to read instead of selecting any table or view:
------------------------------
grant SELECT on sys.v_$database to opslogix;
grant SELECT on sys.gv_$instance to opslogix;
grant SELECT on sys.v_$spparameter to opslogix;
grant SELECT on sys.v_$parameter to opslogix;
grant SELECT on sys.v_$system_event to opslogix;
grant SELECT on sys.v_$filestat to opslogix;
grant SELECT on sys.dba_temp_files to opslogix;
grant SELECT on sys.gv_$sqlstats to opslogix;
grant SELECT on sys.v_$log to opslogix;
grant SELECT on sys.v_$logfile to opslogix;
grant SELECT on sys.v_$controlfile to opslogix;
grant SELECT on sys.dba_free_space to opslogix;
grant SELECT on sys.v_$datafile to opslogix;
grant SELECT on sys.v_$rman_status to opslogix;
grant SELECT on sys.v_$archived_log to opslogix;
grant SELECT on sys.gv_$sgastat to opslogix;
grant SELECT on sys.gv_$pgastat to opslogix;
grant SELECT on sys.gv_$session to opslogix;
grant SELECT on sys.dba_extents to opslogix;
grant SELECT on sys.dba_tablespaces to opslogix;
grant SELECT on sys.dba_data_files to opslogix;
grant SELECT on sys.v_$log_history to opslogix;
grant SELECT on sys.v_$sysstat to opslogix;
grant SELECT on sys.gv_$rowcache to opslogix;
grant SELECT on sys.gv_$librarycache to opslogix;
grant SELECT on sys.gv_$session_longops to opslogix;
grant SELECT on sys.dba_objects to opslogix;
grant SELECT on sys.dba_temp_free_space to opslogix;
grant SELECT on sys.dba_indexes to opslogix;
grant SELECT on sys.v_$tempfile to opslogix;
grant SELECT on sys.v_$sort_segment to opslogix;
grant SELECT on sys.v_$dataguard_config to opslogix; 
grant SELECT on sys.v_$dataguard_stats to opslogix; 
grant SELECT on sys.gv_$archive_dest to opslogix;
grant SELECT on sys.gv_$standby_log to opslogix;
grant SELECT on sys.gv_$managed_standby to opslogix;
grant SELECT on sys.v_$flash_recovery_area_usage to opslogix;
grant SELECT on sys.dba_cpu_usage_statistics to opslogix;
grant SELECT on sys.v_$controlfile_record_section to opslogix;
grant SELECT on sys.dba_tablespace_usage_metrics to opslogix;
grant SELECT on sys.v_$resource_limit to opslogix;
grant SELECT on sys.gv_$containers to opslogix;
grant SELECT on sys.dba_segments to opslogix;
grant SELECT on sys.dba_segments to opslogix;