Audit

New mandatory unified audit policy on 19.26

This entry was posted in Audit Oracle Oracle 19c Oracle 23ai on by

This feature was just backported from Oracle 23ai. The new ORA$MANDATORY audit policy was added with the Oracle 19.26 RU. This policy is not visible at UNIFIED_AUDIT_POLICIES or AUDIT_UNIFIED_ENABLED_POLICIES.

After patching the database to 19.26, then you see entries on UNIFIED_AUDIT_TRAIL:

SYS@CDB2.CDB$ROOT> select EVENT_TIMESTMAP, SYSTEM_PRIVILEGE_USED, ACTION_NAME 
from UNIFIED_AUDIT_TRAIL 
where UNIFIED_AUDIT_POLICIES='ORA$MANDATORY' 
order by EVENT_TIMESTMAP;

                  EVENT_TIMESTAMP     SYSTEM_PRIVILEGE_USED       ACTION_NAME
_________________________________ _________________________ _________________
02-FEB-2025 21:54:56.192982000    SYSDBA                    LOGON
02-FEB-2025 21:54:56.216549000    SYSDBA                    SELECT
02-FEB-2025 21:55:00.381577000    SYSDBA, ALTER DATABASE    ALTER DATABASE
02-FEB-2025 21:55:00.393882000    SYSDBA                    LOGOFF
...

The actions that are audited by ORA$MANDATORY policy are described on Oracle 23ai documentation.

What I find interesting, is that the “ALTER DATABASE MOUNT” during startup is audited, so we can have a good history of database startups.

(more…)

Get history of Switchover and Failover of an Oracle database

This entry was posted in Audit Oracle on by

For physical standbys, there is no DBA_* view that I know which tracks the history of switchover and failover. For Logical standby there is DBA_LOGSTDBY_HISTORY.

Ways to find the information are the Dataguard Broker log – when Broker is in use -, or using the database alert log and the following grep command:

$ grep -EB1 '^ALTER DATABASE (SWITCHOVER|FAILOVER) TO' alert_*.log

2023-03-06T14:12:38.905705+01:00
ALTER DATABASE SWITCHOVER TO 'mydb2'
--
2023-03-06T14:37:43.209653+01:00
ALTER DATABASE FAILOVER TO MYDB1
--
2023-03-06T14:38:03.352141+01:00
ALTER DATABASE SWITCHOVER TO PRIMARY (MYDB1)
--
2023-03-11T16:00:22.224218+01:00
ALTER DATABASE SWITCHOVER TO 'mydb2'

Check Advanced Compression option usage on Oracle database

This entry was posted in Audit Oracle Oracle 11g Oracle 12c on by

To check if the “advanced compression” Oracle database option was used on your environment is not so easy, as there are several activities that can activate this option.

For example for a mix Oracle 11.2.0.4 and 12.1.0.2 environment we can use the following query:

select * from DBA_FEATURE_USAGE_STATISTICS
where ((name like '%Compress%' and name not in ('Backup BASIC Compression','Backup BZIP2 Compression','SecureFile Compression (system)')) 
  and detected_usages>0)
or (name='Data Guard' and FEATURE_INFO like '%Compression used: TRUE%')
or (name in ('Oracle Utility Metadata API','Oracle Utility Datapump (Export)','Oracle Utility Datapump (Import)') 
  and version like '11.2.%' and feature_info not like '%compression used: 0 times%')
or (name in ('Oracle Utility Metadata API','Oracle Utility Datapump (Import)') 
  and version like '12.%' and feature_info like '%compression%')
or (name in ('Oracle Utility Datapump (Export)') 
  and version like '12.%' and feature_info not like '%compression used: 0 times%');

Why do we need so many filters? Because the text that shows if compression was used changes depending on the utility and Oracle version!
Below 3 different examples when compression was not used:

Version Utilities FEATURE_INFO text
12.1 Oracle Utility Datapump (Export) invoked: 1 times, compression used: 0 times (BASIC algorithm used: 0 times, LOW algorithm used: 0 times, MEDIUM algorithm used: 0 times, HIGH algorithm used: 0 times), encryption used: 0 times (AES128 algorithm used: 0 times, AES192 algorithm used: 0 times, AES256 algorithm used: 0 times, PASSWORD mode used: 0 times, DUAL mode used: 0 times, TRANSPARENT mode used: 0 times), parallel used: 0 times, full transportable used: 0 times
12.1 Oracle Utility Datapump (Import)

Oracle Utility Metadata API

 invoked: 1 times, parallel used: 0 times, full transportable used: 0 times
11.2 Oracle Utility Metadata API invoked: 1 times, compression used: 0 times, encryption used: 0 times

Also, seems that for “Data Guard” utility the text has capital letters “%Compression used: TRUE%”.

More information about which features enable the usage of Advanced Compression option can be find here.

And how to avoid the usage of the Advanced Compression option by error on Mathias Zarick blog.

Oracle RDA modules, profiles and security filter

This entry was posted in Audit Oracle RDA on by

Running the Remote Diagnostics Assistant from Oracle is always a pain with so many questions.

Today I took a bit more time reading the Readme and I found you can avoid most of the questions by using predefined profiles.

 Today I was running on a 10g RAC, so what I did for the setup was (instead of ./rda.pl -S)

./rda.pl -S -p Rac_Assessment

With this I had only questions about the SID, way to connect, if there is OCFS, ASM… but no questions about the 300 other Oracle products…

More information on Metlink note: Remote Diagnostic Agent (RDA) 4 – Profile Manual Pages [ID 391983.1]

Something interesting also, it that you can remove more sensitive data from the RDA report. The readme says:

RDA allows you to remove sensitive data from RDA reports. The security profile can be used to turn on filtering and can be combined with other profiles. For example:
    -S -p DB10g-Security 
This will do the RDA setup for the DB10g profile and turn on filtering through the Security profile. If you want to enable the filtering for an existing setup:
    -X Filter enable

Oracle auditing – answer to auditors

This entry was posted in Audit Oracle Oracle 10g on by
Queries for audit information in Oracle 10gR2. The auditors are there…

They want to know that audit exists and what is audited:

select * from v$spparameter where name like ‘%audit%’; — equivalent to SQL> show parameter audit

select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from dba_STMT_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_OBJ_AUDIT_OPTS a;
select sys_context(‘USERENV’, ‘DB_NAME’) DBNAME , a.* from DBA_PRIV_AUDIT_OPTS a;
And they want to know who has access to what:
Roles per user:
set pages 1000
break on grantee;
select grantee, granted_role 
  from dba_role_privs, dba_users 
  where grantee=username
  order by 1,2; 
User per role:
set pages 0
break on granted_role
select granted_role,grantee username 
  from dba_role_privs, dba_users
  where grantee=username
  order by 1,2; 
Table privileges per role:
set lines 150
set pages 0
break on privilege on role_name
col role_name for a30
col privilege for a20
col table_name for a60
select grantee role_name,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b, dba_roles c
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’)
    and a.table_name=b.table_name
    and a.grantee=c.role
  order by 1,2,3;
Privileges given directly to users:
set pages 0
set lines 150
break on username
col username for a30
col privilege for a20
col table_name for a60
select grantee username,privilege, a.owner||’.’||a.table_name table_name
  from dba_tab_privs a, dba_tables b
  where a.owner not in (‘ORDSYS’,’EXFSYS’,’MDSYS’,’SYSMAN’,’OUTLN’,’SYS’,’SYSTEM’,’DBSNMP’,’WMSYS’)
    and a.table_name=b.table_name
    and a.grantee not in (select role from dba_roles)

  order by 1,2,3;