Sunday, November 3, 2013

How to audit ALTER TABLE statements on a specific table


Enable auditing on specific table:
AUDIT ALTER ON SCOTT.T1;
Verify that the object is being audited:
SELECT OBJECT_NAME,OBJECT_TYPE,ALT
FROM  DBA_OBJ_AUDIT_OPTS
WHERE  OWNER ='SCOTT'
ORDER BY 1 ASC; 
Result:
|------------------------------------|
|object_name  | object_type  | alt   |
|-------------|--------------|-------|
|T1           | TABLE        | S/S   |
|------------------------------------|
Object is indeed being audited.

Make a change to the table:
ALTER TABLE SCOTT.T1
 ADD (age  number(2));
Check that the change is captured in the audit-trail:
SELECT TIMESTAMP, OS_USERNAME, OBJ_NAME,USERHOST, SESSIONID, USERNAME,ACTION_NAME, RETURNCODE
FROM   DBA_AUDIT_TRAIL
WHERE  OBJ_NAME = 'T1'
AND    OWNER = 'SCOTT'
ORDER  BY TIMESTAMP DESC;

|-----------------------------------------------------------------------------------------------------------|
|TIMESTAMP     | OS_USERNAME | OBJ_NAME  | USERHOST| SESSIONID | USERNAME | ACTION_NAME  | RETURNCODE |
|-----------------------------------------------------------------------------------------------------------|
|03.11.2013 08:32:28 | SCOTT | T1    | scottpc  | 172154    | SCOTT   | ALTER TABLE  | 0         |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 1430      |
|03.11.2013 08:32:18 | SCOTT | T1    | scottpc  | 172154 | SCOTT   | ALTER TABLE  | 911       |
|01.11.2013 16:14:17 | SCOTT | T1    | scottpc  | 234766 | SCOTT   | ALTER TABLE  | 0         |
|-----------------------------------------------------------------------------------------------------------|
The return codes can be checked by using the oerr utility that comes with the oracle installed binaries, for example:
oerr ora 1430
01430, 00000, "column being added already exists in table"
// *Cause:
// *Action:

Note that other DDL statements, too, are captured in the audit trail for the table, such as DROP and TRUNCATE.
If you drop the table without purging the recyclebin, the auditing will still be in place, and if you query the DBA_OBJ_AUDIT_OPTS after a DROP without purge, it will show you the recyclebase name in the view.

1 comment:

  1. Awesome Description with exact details and practical enough

    ReplyDelete