Troubleshooting
Problem
This document refers to Netezza's Advanced Security features. The following is an example of a typical question that may arise during the setup of audit functionality. Question: "I would like to test audit functionality, but I have some problems - I have set up the auditing using the steps outlined below but the tables in audit database remain empty. I probably haven't set it up properly. What am I missing? How can I troubleshoot the issue?"
Environment
Database to be audited: data
Audit database: dataaudit
User to be audited: datausr
User to write audit data: dataauditusr
Audit database was created with the following command:
/nz/kit/bin/adm/nzhistcreatedb -d audittest -t q -o auditusr -u auditusr -p auditusr -v 1
Executed sql commands (under user admin):
--------------------------------------------------
CREATE HISTORY CONFIGURATION all_hist HISTTYPE QUERY DATABASE auditdata USER auditdatausr PASSWORD 'auditdatausr' COLLECT PLAN,COLUMN LOADINTERVAL 5 LOADMINTHRESHOLD 4 LOADMAXTHRESHOLD 20 VERSION 1 STORAGELIMIT 100000 LOADRETRY 0;
SET HISTORY CONFIGURATION all_hist;
ALTER DATABASE DATA COLLECT HISTORY ON;
ALTER USER DATAUSR COLLECT HISTORY ON;
--------------------------------------------------
Resolving The Problem
The first step in troubleshooting audit issues is to check for errors in the following two log files:
/nz/kit/log/alcloader
/nz/kit/log/postgres
If no related errors exist in the above log files, then it could be that the setup is correct but the feature is turned off. In this case, it would be a good idea to review the query history logging configuration settings by running the following command:
| SHOW HISTORY CONFIGURATION ALL_HIST; |
The command output would be similar to the following:
SYSTEM(ADMIN)=> SHOW HISTORY CONFIGURATION ALL_HIST;
CONFIG_NAME | CONFIG_DBNAME | CONFIG_DBTYPE | CONFIG_TARGETTYPE | CONFIG_LEVEL | CONFIG_HOSTNAME | CONFIG_USER | CONFIG_PASSWORD | CONFIG_LOADINTERVAL | CONFIG_LOADMINTHRESHOLD | CONFIG_LOADMAXTHRESHOLD | CONFIG_DISKFULLTHRESHOLD | CONFIG_STORAGELIMIT | CONFIG_LOADRETRY | CONFIG_ENABLEHIST | CONFIG_ENABLESYSTEM | CONFIG_NEXT | CONFIG_CURRENT | CONFIG_VERSION | CONFIG_COLLECTFILTER | CONFIG_KEYSTORE_ID | CONFIG_KEY_ID | KEYSTORE_NAME | KEY_ALIAS | CONFIG_NAME_DELIMITED | CONFIG_DBNAME_DELIMITED | CONFIG_USER_DELIMITED<br>-------------+---------------+---------------+-------------------+--------------+-----------------+--------------+---------------------------------------+---------------------+-------------------------+-------------------------+--------------------------+---------------------+------------------+-------------------+---------------------+-------------+----------------+----------------+----------------------+--------------------+---------------+---------------+-----------+-----------------------+-------------------------+-----------------------
ALL_HIST | AUDITDATA | 1 | 1 | 20 | localhost | AUDITDATAUSR | PWFaRHxNMmzn$OOUDG94OnWY84tLr8fpeog== | 5 | 4 | 20 | 0 | 100000 | 0 | f | f | f | t | 1 | 1 | 0 | 0 | | | f | f | f
(1 row)
In this result, the CONFIG_ENABLEHIST value is 'f' for FALSE, which means history is not being collected and that may be the reason why the tables are empty.
You can amend the setting using the following nzsql command:
| ALTER HISTORY CONFIGURATION ALL_HIST ENABLEHIST ON; |
To confirm that the value is set correctly, run the following command again:
| SHOW HISTORY CONFIGURATION ALL_HIST; |
Historical Number
NZ627188
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21572659