Oracle audit and reporting in one hour or less
This presentation is the property of its rightful owner.
Sponsored Links
1 / 18

Oracle audit and reporting in one hour or less . PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

Oracle audit and reporting in one hour or less. UGF9157. Prepared by: Leon Rzhemovskiy Database Architect UnikaSolution.com. Learning Objectives.

Download Presentation

Oracle audit and reporting in one hour or less .

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Oracle audit and reporting in one hour or less

Oracle audit and reporting in one hour or less.

UGF9157

Prepared by:

Leon Rzhemovskiy

Database Architect

UnikaSolution.com


Learning objectives

Learning Objectives

  • Answer questions Who, What , When and Where about any database activity by setting up an Oracle audit. The infrastructure is free and available in every database edition.

  • Stay on top of any possible performance and storage issues by choosing appropriate audit parameters.

  • Build summary and detail reports to analyze audit events from multiple databases using APEX or SQL*Plus.

  • Setup a data retention period and cleanup audit records regularly.

  • Create honeypot to attract hacker’s attention.

  • Enable alerts and send email notifications using Oracle Enterprise Manager infrastructure.


Monitoring infrastructure

Monitoring Infrastructure


Enabling auditing

Enabling Auditing

Database auditing is enabled and disabled by the AUDIT_TRAIL parameter in the database initialization parameter file. Unfortunately, audit_trail is not a dynamic parameter and as a result, requires a database reboot. To enable the audit, execute the following SQL commands as sysdba:

  • ALTER SYSTEM SET audit_trail=db_extended SCOPE=SPFILE;

  • SHUTDOWN immediate;

  • STARTUP;

    Verify the audit value by executing

    SELECT VALUE FROM V$PARAMETER WHERE NAME='audit_trail';


Auditing options

Auditing options

Audit policy is controlled by setting up options based on the statement, privilege, object and network level.

AUDIT and NOAUDIT statements are used to set up auditing settings.

Following views could be used to display auditing settings:

  • DBA_PRIV_AUDIT_OPTS;

  • DBA_STMT_AUDIT_OPTS;

  • DBA_OBJ_AUDIT_OPTS;

    To remove all audit settings on the statement and privilege level execute

  • NOAUDIT ALL PRIVILEGES;

  • NOAUDIT ALL;


Auditing options setup

Auditing Options Setup

The auditing event’s frequency impacts database performance and storage. The number of auditing options varies between database versions.

You can setup auditing options by running oracle recommended RDBMS/admin/secconf.sql.

In my opinion, it is more beneficial to start from auditing everything available and reduce/modify auditing options if needed.

  • AUDIT ALL PRIVILEGES WHENEVER SUCCESSFUL;

  • AUDIT ALL WHENEVER SUCCESSFUL;

  • NOAUDIT CREATE SESSION;

  • AUDIT CREATE SESSION WHENEVER NOT SUCCESSFUL;

  • NOAUDIT SELECT ANY TABLE;

  • NOAUDIT SELECT ANY SEQUENCE;

  • NOAUDIT INSERT ANY TABLE;

  • NOAUDIT DELETE ANY TABLE;

  • NOAUDIT UPDATE ANY TABLE;

  • NOAUDIT EXECUTE ANY PROCEDURE;


Audit reporting

Audit Reporting

Audit entries are stored in SYS.AUD$ table and usually are accessed using DBA_AUDIT_TRAIL view. For every entry, the user name, timestamp, connection origin, action performed and even SQL statement is recorded.

You can use TOAD, SQLDeveloper to browse audit records. SQL*Plus could be used to generate html output. The following is a sample:

SET MARKUP HTML ON

spool summary.html

SELECT TRUNC(TIMESTAMP) "Date" ,COUNT(1) "Count Audit Entries"

FROM dba_audit_trail

WHERE TIMESTAMP BETWEEN TRUNC(SYSDATE,'MON') AND SYSDATE

GROUP BY TRUNC(TIMESTAMP)

ORDER BY TRUNC(TIMESTAMP) DESC;

exit;


Audit reporting using apex demo

Audit reporting using APEX demo


Audit reporting using apex demo1

Audit reporting using APEX demo


Audit reporting using apex demo2

Audit reporting using APEX demo


Audit reporting using apex demo3

Audit reporting using APEX demo


Audit reporting using apex demo4

Audit reporting using APEX demo


Audit reporting using apex demo5

Audit reporting using APEX demo


Audit cleanup

Audit Cleanup

The simplest (but not recommended by Oracle) option is

TRUNCATE TABLE sys.aud$;

If you are running 11g, then use the following solution:

-- init cleanup

BEGIN

DBMS_AUDIT_MGMT.INIT_CLEANUP(

AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

DEFAULT_CLEANUP_INTERVAL => 720 /*hours*/ );

END;

/

-- set timestamp to purge data 30+ days old

BEGIN

DBMS_AUDIT_MGMT.set_last_archive_timestamp(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

last_archive_time => SYSTIMESTAMP-30);

END;

/

-- cleanup job. Should be scheduled to execute daily

BEGIN

DBMS_AUDIT_MGMT.clean_audit_trail(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

use_last_arch_timestamp => TRUE);

END;

/


Setup alerts and email notifications

Setup alerts and email notifications

Login to Oracle Enterprise Manager Cloud Control 12c

Navigate to Enterprise-> Monitoring -> Metric Extensions -> Action -> Create


Setup alerts and email notifications1

Setup alerts and email notifications


Honeypot setup

Honeypot Setup

Create a table with an intriguing name and then monitor any attempt to select data from this table. Setup the Metric Extension and email notifications in Enterprise Manager to react on unauthorized actions immediately.

CREATE TABLE CUSTOMER_CREDIT_CARDS(

Customer_no number(16),

Credit_Card_No number(16),

Credit_Card_Exp varchar2(4));

GRANT SELECT ON CUSTOMER_CREDIT_CARDS TO PUBLIC;

CREATE PUBLIC SYNONYM CUSTOMER_CREDIT_CARDS FOR CUSTOMER_CREDIT_CARDS;

AUDIT SELECT ON CUSTOMER_CREDIT_CARDS;


Conclusion

Conclusion

There is no excuse to avoid setting up an Audit Trail. You will get an enormous value for expending very little effort. You can utilize APEX application or any SQL client to browse Oracle audit entries. You can react immediately on unauthorized activities by setting up Enterprise Manager metric extensions and email notifications.


  • Login