OracleToSQL: Database Audit


Description

  • Auditing facilitates database activity monitoring. It’s the recording of selected user database actions. Monitoring statements, privileges, or objects
  • Security policies can trigger auditing when specified elements in an Oracle database are accessed or altered
  • AUDIT_SYS_OPERATIONS initialization parameter- Enables or disables the auditing of top-level operations directly issued by user SYS, and users connecting with SYSDBA or SYSOPER privilege. This parameter should be enabled on ALL production databases
  • Oracle Database writes the audit records to the audit trail of the operating system. The database audit trail consists of a single table named SYS.AUD$. Audit trail records contain different types of info, depending on the events audited and the auditing options set
  • Oracle Database allows audit trail records to be directed to an operating system audit trail if the operating system makes such an audit trail available to Oracle DB. If not, then audit records are written to a file outside the database. the database will write a trace file of the session actions (for sys or sysdba) to the dump directory location specified by AUDIT_FILE_DEST
  • If you set the AUDIT_TRAIL initialization parameter to XML or XML, EXTENDED, it writes the audit records in XML format. AUDIT_TRAIL enables or disables database auditing

How to Find Feature Enablement?

show parameter audit_sys_operations;
show parameter audit_trail;
select * from dba_stmt_audit_opts union select * from dba_priv_audit_opts;

— if a non-container database

conn / as sysdba

— connect to each PDB in turn and run the following queries

show parameter audit

SELECT MAX(logoff$time)

FROM sys.aud$;

SELECT MAX(timestamp#), MAX(ntimestamp#)

FROM sys.fga_log$;

SELECT table_name, tablespace_name, num_rows

FROM dba_tables

WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’)

ORDER BY 1;

The audit trail is stored in the SYS.AUD$ table. It’s contents can be viewed directly or via the following views.

  • DBA_AUDIT_EXISTS
  • DBA_AUDIT_OBJECT
  • DBA_AUDIT_SESSION
  • DBA_AUDIT_STATEMENT
  • DBA_AUDIT_TRAIL
  • DBA_OBJ_AUDIT_OPTS
  • DBA_PRIV_AUDIT_OPTS
  • DBA_STMT_AUDIT_OPTS

Recommendation

Feature Description
DDL triggers and notifications can aid in auditing
SQL Server server-level auditing is resilient, available in all editions, and provides T-SQL call stack frame info
SQL Server supports user-defined audit groups and audit filtering

Can use T-SQL to enable audit by creating the audit specification for specific database and specific access group.
The Audit action items can be individual actions such as SELECT operations on a Table, or a group of actions such as SERVER_PERMISSION_CHANGE_GROUP.
SQL Audit Events track the following three categories of Events:

  • Server Level: These actions include server operations, such as management changes, and logon and logoff operations.
  • Database Level: These actions include data manipulation languages (DML) and Data Definition Language (DDL).
  • Audit Level: These actions include actions in the auditing process.

You could implement an audit trail quickly in SQL Server by creating shadow table for each table in database and triggers to log every time when a record is inserted, updated or deleted in the table. see last link in the list for Audit Trail Generator Script.

The SQL Server Audit feature is built on top of Extended Events to leverage the performance benefits and provide both asynchronous and synchronous write capabilities (by default, SQL Server Audit uses the asynchronous event model). You could use SQL Profiler to see Workload Performance impact of Auditing and turn on audit on specific objects and specific logins. All editions of SQL Server support server level audits. Database level auditing is limited to Enterprise, Developer, and Evaluation editions.

Feature Comparison

Similar to Oracle Audit Vault for DDL and DML statements. All actions (DDL and DML) are auditable in SQL Server.

Migration Approach

SSMA does not support migrating Auditing configurations.

In SQL server, use T-SQL to enable audit by creating the audit specification for specific database and specific access group.

T-SQL to create a server audit
USE master ;
GO
— Create the server audit.
CREATE SERVER AUDIT Payrole_Security_Audit
TO FILE ( FILEPATH =
‘C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA’ ) ;
GO
— Enable the server audit.
ALTER SERVER AUDIT Payrole_Security_Audit
WITH (STATE = ON) ;
T-SQL to create a database-level audit specification
(Following example creates a database audit specification called Audit_Pay_Tables that audits SELECT and INSERT statements by the dbo user, for the HumanResources.EmployeePayHistory table based on the server audit defined above.)

USE AdventureWorks2012 ;
GO
— Create the database audit specification.
CREATE DATABASE AUDIT SPECIFICATION Audit_Pay_Tables
FOR SERVER AUDIT Payrole_Security_Audit
ADD (SELECT , INSERT
ON HumanResources.EmployeePayHistory BY dbo )
WITH (STATE = ON) ;
GO

References


Comments (0)

Skip to main content