基本语法：
CREATE AUDIT POLICY <policy_name> AUDITING <audit_status_clause>
<audit_actions> LEVEL <audit_level>语法元素：
<policy_name> ::= <identifier>
<audit_status_clause> ::= SUCCESSFUL | UNSUCCESSFUL | ALL
<audit_actions> ::= ACTIONS FOR <user_name>[, <user_name>]
| <audit_action_list> [FOR <user_name>[, <user_name>]]
| <target_audit_action_list> [FOR <user_name>[, <user_name>]...]
<user_name> ::= <simple_identifier>
<audit_action_list> ::= <audit_action_name>[, <audit_action_name>]...
<target_audit_action_list> ::= <target_audit_action_name>[, <target_audit_action_name] ON <object_name>[, <object_name>]
<audit_action_name> ::= GRANT PRIVILEGE | REVOKE PRIVILEGE
| GRANT STRUCTURED PRIVILEGE | REVOKE STRUCTURED PRIVILEGE
| GRANT APPLICATION PRIVILEGE | REVOKE APPLICATION PRIVILEGE
| GRANT ROLE | REVOKE ROLE
| GRANT ANY | REVOKE ANY
| CREATE USER | DROP USER
| CREATE ROLE | DROP ROLE
| ENABLE AUDIT POLICY | DISABLE AUDIT POLICY
| CREATE STRUCTURED PRIVILEGE | DROP STRUCTURED PRIVILEGE
| ALTER STRUCTURED PRIVILEGE | CONNECT
| SYSTEM CONFIGURATION CHANGE | SET SYSTEM LICENSE
| UNSET SYSTEM LICENSE | ALTER USER
| REPOSITORY_ACTIVATE | DROP TABLE
<target_audit_action_name> ::= INSERT | UPDATE | DELETE | SELECT | EXECUTE
<audit_level> ::= EMERGENCY | ALERT | CRITICAL | WARNING | INFO
<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>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 happen.
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.
The <audit_status_clause> defines if successful, unsuccessful or all executions of the specified audit actions are audited.
The table below contains the available audit actions. They are grouped in several groups. Audit actions in the same group can be combined into one audit policy. Audit actions of different groups can not be combined into the same audit policy.
| Audit Action Name | Group Number | Audit Operation | Comment |
|---|---|---|---|
| GRANT PRIVILEGE | 1 | granting of privileges to users or roles | |
| REVOKE PRIVILEGE | 1 | revoking of privileges from users or roles | |
| GRANT STRUCTURED PRIVILEGE | 1 | granting of structured/analytical privileges to users or roles | |
| REVOKE STRUCTURED PRIVILEGE | 1 | revoking of structured/analytical privileges from users or roles | |
| GRANT APPLICATION PRIVILEGE | 1 | granting of application privileges to users or roles | |
| REVOKE APPLICATION PRIVILEGE | 1 | revoking of application privileges from users or roles | |
| GRANT ROLE | 1 | granting of roles to users or roles | |
| REVOKE ROLE | 1 | revoking of roles from users or roles | |
| GRANT ANY | 1 | granting of privileges, structured privileges or roles to users or roles | |
| REVOKE ANY | 1 | revoking of privileges, structured privileges or roles from users or roles | |
| CREATE USER | 2 | creation of users | |
| DROP USER | 2 | dropping of users | |
| ALTER USER | 2 | altering of users | |
| CREATE ROLE | 2 | creation of roles | |
| DROP ROLE | 2 | dropping of roles | |
| CONNECT | 3 | creation of a user connection to the database | |
| SYSTEM CONFIGURATION CHANGE | 4 | changes to the system configuration (e.g. INIFILE) | |
| ENABLE AUDIT POLICY | 5 | activation of audit policies | |
| DISABLE AUDIT POLICY | 5 | deactivation of audit policies | |
| CREATE STRUCTURED PRIVILEGE | 6 | creation of structured/analytical privileges | |
| DROP STRUCTURED PRIVILEGE | 6 | destruction of structured/analytical privilege | |
| ALTER STRUCTURED PRIVILEGE | 6 | change of structured/analytical privilege | |
| SET SYSTEM LICENSE | 7 | installation of a system license | |
| UNSET SYSTEM LICENSE | 7 | deletion of licenses | |
| DROP TABLE | 7 | deletion of database tables | |
| REPOSITORY ACTIVATE | 7 | activation of repository design time objects | |
| INSERT | 7 | use of insert/replace/upsert statements on tables and views | allows specification of target objects |
| UPDATE | 7 | use of update/replace/upsert statements on tables and views | allows specification of target objects |
| DELETE | 7 | deletion of rows from tables/views and truncation of tables | allows specification of target objects |
| SELECT | 7 | use of select statements on tables and views | allows specification of target objects |
| EXECUTE | 7 | procedure calls | allows specification of target objects |
| ALL | 7 | all actions above | typically used for specific users |
Only objects of type table, view, and procedure can be specified in the <target_audit_action_list>. Synonyms and sequences cannot be selected as objects for audit policies. Furthermore only those <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 |
|---|---|---|---|
| DELETE | YES | YES | --- |
| INSERT | YES | YES | --- |
| SELECT | YES | YES | --- |
| UPDATE | YES | YES | --- |
| EXECUTE | --- | --- | YES |
Each audit policy is assigned to an audit level. The possible levels, in decreasing order of importance, are: EMERGENCY, ALERT, CRITICAL, WARNING, INFO.
To make auditing occur, audit policies have to be created and enabled. Also the configuration parameter global_auditing_state (see below) has to be set to true.
Configuration Parameter
Currently the configuration parameter for auditing are stored in global.ini, in the auditing configuration section and are the following:
global_auditing_state ( 'true' / 'false' ) to activate / deactivate auditing globally, no matter how many audit policies are available and enabled. The default is false, meaning: no auditing will occur.
default_audit_trail_type ( 'SYSLOGPROTOCOL' / 'CSVTEXTFILE' ) to specify, how to store the auditing results. SYSLOGPROTOCOL is the default.
CSVTEXTFILE should be used only for testing purposes.
default_audit_trail_path to specify where to store the audit file, in the case that CSVTEXTFILE has been selected.
As for all configuration parameters, these parameters can be selected in view M_INIFILE_CONTENTS, if the current user has the required privilege to do so. These parameters will only be seen in case they have been explicitly set.
System and Monitoring Views
AUDIT_POLICY: shows all audit policies and their states
M_INIFILE_CONTENTS: shows the 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 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 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
其他例子
-- create audit policy
CREATE AUDIT POLICY policyAdministratePrincipals AUDITING ALL
CREATE ROLE, DROP ROLE, CREATE USER, DROP USER LEVEL Critical;
--disable audit policy
ALTER AUDIT POLICY policyAdministratePrincipals disable;
--enable audit policy
ALTER AUDIT POLICY policyAdministratePrincipals enable;
--query audit policy
select * from "PUBLIC"."AUDIT_POLICIES"