Enabling audit logging

Answer

1. With below command please drop all the query history configuration.

SYSTEM.ADMIN(ADMIN)=> drop history configuration ALL_HIST;
DROP HISTORY CONFIGURATION

2. Drop all history user, and owner

SYSTEM.ADMIN(ADMIN)=> drop user auditusr;
DROP USER

3. Create user, and owner with below privileges.............

SYSTEM.ADMIN(ADMIN)=> create user auditusr with password 'test1234';
CREATE USER
SYSTEM.ADMIN(ADMIN)=> create user auditowner with password 'test1234';
CREATE USER
SYSTEM.ADMIN(ADMIN)=> grant create database to auditowner;
GRANT
SYSTEM.ADMIN(ADMIN)=> grant list on user to auditowner;
GRANT

4. Run nzhistcreatedb script at linux prompt .........

[nz@p2-nz103 ~]$ nzhistcreatedb -d auditdb -t audit -u auditusr -o
auditowner -p 'test1234' -v 3
This operation may take a few minutes. Please wait...
Unable to display verbose progress bar.Creation of history database will
proceed silently.
History database auditdb created successfully !

5. Create audit history configuration with below command at nzsql
prompt..........

SYSTEM.ADMIN(ADMIN)=> create history configuration all_hist histtype
audit database auditdb user auditusr password 'test1234' collect
PLAN,QUERY,TABLE,COLUMN,SERVICE l
oadinterval 2 loadminthreshold 0 loadmaxthreshold 20 version 3
storagelimit 100000 loadretry 0;
CREATE HISTORY CONFIGURATION

6. Set this configuration...

SYSTEM.ADMIN(ADMIN)=> set history configuration all_hist;
SET HISTORY CONFIGURATION
SYSTEM.ADMIN(ADMIN)=> \q

7. Run stop/start to take effect of this setting.
[nz@p2-nz103 ~]$ nzstop
[nz@p2-nz103 ~]$ nzstart
************************************************************************
***********************************