System Views
1.8.1.7 CREATE AUDIT POLICY
Syntax
CREATE AUDIT POLICY <policy_name> AUDITING <audit_status_clause>
<audit_actions> LEVEL <audit_level>
Syntax Elements
<policy_name> ::= <identifier>
The name of the audit policy to be created.
<audit_status_clause> ::= SUCCESSFUL | UNSUCCESSFUL | ALL
Defines whether successful, unsuccessful or all executions of the specified audit actions will be audited.
<audit_actions> ::= <actions_for_user_clause>
| <audit_action_list_clause>
| <target_audit_action_list_clause>
The audit actions for the audit policy.
<actions_for_user_clause> ::= ACTIONS FOR <user_name>[{, <user_name>}...]
Audits actions for a user or a set of users.
<user_name> ::= <identifier>
The username of the user to be audited by the audit policy.
<audit_action_list_clause> ::= <audit_action_list> [FOR <user_name>[{,
<user_name>}...]
<audit_action_list> ::= <audit_action_name>[{, <audit_action_name>}...]
<audit_action_name> ::= GRANT PRIVILEGE | REVOKE PRIVILEGE
Audits specific system actions, optionally limited to a user or a set of users. For more information on audit actions see Audit Action Table.
<target_audit_action_list> ::= <target_audit_action_entry> [FOR <user_name>[{,
<user_name>}...]
<target_audit_action_entry> ::= <target_audit_action_name>[{,
<target_audit_action_name>}...] ON <object_name>[{, <object_name>}...]
<target_audit_action_name> ::= INSERT | UPDATE | DELETE | SELECT | EXECUTE
Audits actions on a database object or set of objects. Optionally this auditing can be limited to a user or a set of users. Only objects of type table, view, and procedure can be specified in the <target_audit_action_entry>.
Synonyms and sequences cannot be selected as objects for audit policies. Furthermore only these
<target_audit_action_name>s can be combined with an object. The following table shows an overview of auditable actions on objects.
Action Table View Procedure
Action Table View Procedure
DELETE YES YES
---INSERT YES YES
---SELECT YES YES
---UPDATE YES YES
---EXECUTE --- --- YES
<object_name> ::= <table_name> | <view_name> | <procedure_name>
<table_name> ::= [<schema_name>.]<identifier>
<view_name> ::= [<schema_name>.]<identifier>
<procedure_name> ::= [<schema_name>.]<identifier>
<schema_name> ::= <identifier>
Specifies a database object for the target audit action.
<audit_level> ::= EMERGENCY | ALERT | CRITICAL | WARNING | INFO
Assigns an audit policy to an audit level. Possible levels, in decreasing order of importance, are:
● EMERGENCY.
● ALERT.
● CRITICAL.
● WARNING.
● INFO.
Description
The CREATE AUDIT POLICY statement creates a new audit policy. This audit policy can then be enabled and will cause the auditing of the specified audit actions to occur.
Only database users having the system privilege AUDIT ADMIN are allowed to create an audit policy.
The specified audit policy name must be unique not match the name of an existing audit policy.
An audit policy defines which audit actions will be audited. Audit policies need to be enabled for auditing to occur.
One audit policy can contain one of the following:
● non-restricted auditing for n (>=1) users
● auditing for actions not restricted to objects
● auditing for actions which are restricted to objects.
For the last two alternatives listed, an optional restriction for user(s) is available.
For auditing to occur, audit policies have to be created and enabled. Also the configuration parameter global_auditing_state (see Configuration Parameters) has to be set to true.
Audit Actions
The table below contains the available audit actions. Each of the audit actions are in a specific group, audit actions in the same group can be combined into one audit policy.
Audit Action Name Group Number Audit Operation Comment
Audit Action Name Group Number Audit Operation Comment
CONNECT 1 creation of a user
connection to the database
SYSTEM
CONFIGURATION CHANGE
1 changes to the system
configuration (e.g.
INIFILE)
DISCONNECT SESSION 1 audits disconnects of
sessions
CANCEL SESSION 1 audits cancelation of
sessions
GRANT PRIVILEGE 2 granting of privileges to
users or roles
REVOKE PRIVILEGE 2 revoking of privileges from
users or roles
Audit Action Name Group Number Audit Operation Comment GRANT APPLICATION
PRIVILEGE
2 granting of application
privileges to users or roles REVOKE APPLICATION
PRIVILEGE
2 revoking of application
privileges from users or roles
GRANT ROLE 2 granting of roles to users
or roles
REVOKE ROLE 2 revoking of roles from
users or roles
GRANT ANY 2 granting of privileges,
structured privileges or roles to users or roles
REVOKE ANY 2 revoking of privileges,
structured privileges or roles from users or roles
INSERT 3 use of insert/replace/
upsert statements on tables and views
allows specification of target objects
UPDATE 3 use of update/replace/
upsert statements on tables and views
allows specification of target objects
DELETE 3 deletion of rows from
tables/views and truncation of tables
allows specification of target objects
SELECT 3 use of select statements
on tables and views
allows specification of target objects
EXECUTE 3 procedure calls allows specification of
target objects
CREATE USER 4 creation of users
DROP USER 4 dropping of users
ALTER USER 4 altering of users
CREATE ROLE 4 creation of roles
DROP ROLE 4 dropping of roles
Audit Action Name Group Number Audit Operation Comment
ENABLE AUDIT POLICY 5 activation of audit policies
DISABLE AUDIT POLICY 5 deactivation of audit
policies
SET SYSTEM LICENSE 8 installation of a system
license
UNSET SYSTEM LICENSE 9 deletion of licenses
ACTIVATE REPOSITORY CONTENT
10 activation of repository
design time objects EXPORT REPOSITORY
CONTENT
10 export of repository
design time objects IMPORT REPOSITORY
CONTENT
10 import of repository
design time objects
DROP TABLE 11 deletion of database
tables BACKUP CATALOG
DELETE
13 audits deletion of entries
in the backup catalog
ALL 12 all actions above used for specific users
Configuration Parameters
Currently the configuration parameter for auditing are stored in global.ini configuration file, in the auditing configuration section.
global_auditing_state ( 'true' / 'false' )
Activates / deactivates auditing globally, regardless of the enabled state of the audit policies. The default is false, meaning: no auditing will occur.
default_audit_trail_type ( 'SYSLOGPROTOCOL' / 'CSVTEXTFILE' ) Specifies how to store the auditing results. SYSLOGPROTOCOL is the default.
CSVTEXTFILE should be used only for testing purposes.
default_audit_trail_path
Specifies the audit file storage location for the CSVTEXTFILE audit trail type.
As for all configuration parameters, these parameters can be selected in view M_INIFILE_CONTENTS, assuming that the current user has the required privileges.
Note
These parameters can only be seen if they have been explicitly set.
System Views
AUDIT_POLICIES : All audit policies and their states.
M_INIFILE_CONTENTS : Configuration parameter concerning auditing.
Only database users with system privilege CATALOG READ, DATA ADMIN or INIFILE ADMIN can view information in the M_INIFILE_CONTENTS view. For other database users this view will be empty.
Example
Your create a new audit policy named priv_audit that will audit successful granting and revoking of privileges and roles. The audit policy has the medium audit level CRITICAL.
This policy has to be enabled explicity (see ALTER AUDIT POLICY) to make the auditing of the audit policy occur.
CREATE AUDIT POLICY priv_audit AUDITING SUCCESSFUL GRANT PRIVILEGE, REVOKE PRIVILEGE, GRANT ROLE, REVOKE ROLE LEVEL CRITICAL;
You create a new audit policy named object_audit that will audit the inserts into the existing table
MY_SCHEMA.MY_TABLE. This policy has to be enabled explicity (see ALTER AUDIT POLICY) to make the auditing of the audit policy occur. This policy is restricted to user FRED and uses the audit level INFO.
CREATE USER FRED PASSWORD Initial_1;
CREATE SCHEMA MY_SCHEMA OWNED BY system;
CREATE TABLE MY_SCHEMA.MY_TABLE (first_col int);
GRANT INSERT ON MY_SCHEMA.MY_TABLE to FRED;
CREATE AUDIT POLICY OBJECT_AUDIT AUDITING SUCCESSFUL INSERT ON MY_SCHEMA.MY_TABLE FOR FRED LEVEL INFO