Audit


Check Advanced Compression option usage on Oracle database

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

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

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;