IBM Netezza Database Activity History: Auditing and Security Insights

IBM Netezza provides robust capabilities for tracking database activity through its native audit features. This guide explores how to utilize IBM Netezza Database Activity History using SQL, connect to the database, set up and configure auditing, and view results. Additionally, we will briefly cover DataSunrise as an advanced solution for managing IBM Netezza DataBase Activity History.

Native IBM Netezza DataBase Activity History Overview

IBM Netezza’s native database activity history enables organizations to monitor and audit sensitive data access. This feature helps ensure compliance with security policies, detect unauthorized activities, and improve database performance by analyzing usage patterns.

Key Features of Native Database Activity History

• Logs query execution, column access, and failed authentications.
• Provides customizable configurations for granular activity tracking.
• Outputs data into specialized audit tables within a designated history database.

Setting Up Native IBM Netezza Database Activity History

Before enabling database activity history, specific users and databases must be configured.

Step 1: Create an Audit User

The audit user will manage activity history collection.

CREATE USER audituser WITH PASSWORD 'password';
GRANT CREATE DATABASE TO audituser;

Step 2: Create a History Database

Run the following command in the terminal to create a history database:

nzhistcreatedb -d auditDB -t query -v 1 -u audituser -o audituser -p password

Step 3: Log into the Database

Use the terminal or DBeaver to log into the system database and verify that the history database was created:

nzsql -host localhost -port 5480 -u admin -pw password -d system

Check for the new database:

\l

Step 4: Verify Tables in the History Database

List the tables in the history database. These will populate based on your audit configuration:

\c auditDB
\dt

Step 5: Create and Enable History Configuration

Define what activities to log by creating a history configuration:

CREATE HISTORY CONFIGURATION all_hist
HISTTYPE QUERY
DATABASE auditDB
USER audituser
PASSWORD 'password'
COLLECT QUERY, COLUMN
LOADINTERVAL 5
LOADMINTHRESHOLD 4
LOADMAXTHRESHOLD 20
STORAGELIMIT 40
LOADRETRY 0
VERSION 1;
SET HISTORY CONFIGURATION all_hist_on;

Restart the system to apply changes:

su - nz
nzstop
nzstart

Step 6: Query Audit Tables

To view collected data, query the relevant history tables:

Example: Table Access History

SELECT SESSIONID, DBNAME, TABLENAME
FROM AUDITDB.AUDITUSER."$hist_table_access_1";

Example: Failed Authentication Logs

SELECT NPSID, NPSINSTANCEID, LOGENTRYID, CLIENTIP, SESSIONUSERNAME, "TIME", FAILURETYPE, FAILURE
FROM AUDITDB.AUDITUSER."$hist_failed_authentication_1";

Managing Exclusions

To exclude specific users or databases from auditing, configure with COLLECT HISTORY OFF:

CREATE USER user WITH PASSWORD 'password' COLLECT HISTORY OFF;

Using DataSunrise for IBM Netezza Database Activity History

DataSunrise provides a centralized platform for managing IBM Netezza’s database activity history. It enhances native auditing features by adding:

• Advanced auditing: Configurable rules and detailed tracking.
• Security features: Masking, data discovery, and real-time monitoring.
• Unified control: Centralized management for multiple databases.

Configuring DataSunrise for IBM Netezza

1. Create a DataSunrise Instance Assuming DataSunrise is installed, create a new instance for your IBM Netezza database. This setup allows DataSunrise to monitor activities effectively.
   
   
2. Set Up Audit Rules Define specific auditing rules, such as tracking sensitive data access, monitoring specific users, or overseeing changes to critical tables. Additionally, configure security settings like masking to protect sensitive information, and use data discovery to identify unprotected sensitive data.
   
   
3. View Activity History Use the DataSunrise interface to generate detailed audit reports, track sensitive data interactions, and monitor database activity in real time.
   
   

Benefits of DataSunrise

DataSunrise enhances auditing capabilities with features like:

• Data Masking: Safeguard sensitive information as an additional layer of protection during audits.

• Security Rules: Define and enforce robust access controls.

• Centralized Audit Management: Streamline audit oversight for distributed systems.

• Data Discovery: Identify sensitive data across Cassandra clusters.

These features simplify compliance processes and elevate database security, making DataSunrise an essential tool for administrators.

Conclusion

IBM Netezza’s native database activity history offers a powerful solution for monitoring and auditing database usage. By leveraging SQL configurations, organizations can customize audit parameters to meet specific needs. Additionally, DataSunrise enhances these capabilities with advanced security and centralized control features.

Explore DataSunrise’s robust tools for database security and auditing by visiting our website for an online demonstration.