Db2 – Applying audit policy on multiple auditing subject or object

auditdb2

I have created a policy on execute category and I would like to know if this policy can be applied on multiple auditing subject or object. Since the execute category will generate many audit entries I would like to limit the results by applying this execute audit policy on specific user/table combination.

Is it possible?

Best Answer

Whilst the audit policy created by CREATE AUDIT POLICY is database-wide, it is applied to individual objects or roles by the AUDIT statement. For example, to audit statement executions (successful or otherwise) against a particular table:

CREATE AUDIT POLICY allexec CATEGORIES EXECUTE STATUS BOTH;
COMMIT;
AUDIT TABLE mytable USING POLICY allexec;
COMMIT;

To audit statements executed by a particular role:

AUDIT ROLE somerole USING POLICY allexec;
COMMIT;

The reason for all the COMMITs is that, as the docs note, "[a]n AUDIT-exclusive SQL statement must be followed by a COMMIT or ROLLBACK statement...". See audit policies for more information.

Related Solutions

MySQL Authentication – How to Audit Logins on MySQL Database

You would probably want to use the general query log.

The general query log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.

One important thing with logging for security is that an attacker cannot access the log to erase traces of their presence, so consider append-only files.

FWIW in Oracle we can send logs automatically to a remote syslog, but I don't believe MySQL has this feature yet. Perhaps you could fake it with SNMP but I have not tried it.

Audit select queries against specified tables by specified users

I have found a way to prevent duplicate rows in FGA audit trail with parallel query

First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)

create or replace function is_pg_slave return integer as
begin
  if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
    return 1;
  else 
    return 0;
  end if;
end;
/

Then add policy to table

begin
      dbms_fga.add_policy(
        object_name       => 'table_name',
        policy_name       => 'noaudit_pq_slave',
        audit_condition   => 'is_pg_slave = 0',
        statement_types   => 'SELECT');
end;
/

Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.

I have not noticed any performance issues with this approach compared to normal auditing or no auditing.

Best Answer

Related Solutions

MySQL Authentication – How to Audit Logins on MySQL Database

Audit select queries against specified tables by specified users

Related Question