============= 表ddl 审计==============
1、table信息
SQL> select * from test;
ID CUST_CREDIT_LIMIT TIME PRICE NAME
--- ----------------- ------------ ---------- ----------
23 12 19-NOV-20 ab
29 12 20-NOV-20 abc
10 12 20-NOV-20 abc
50 12 20-NOV-20 abc
40 12 20-NOV-20 abc
============= 方式一 开启全库审计 =============
优点：简单明了，审计信息详细
缺点：增加资源消耗，审计表数据增量大
需要定时对审计表做备份清理
alter system set audit_sys_operations=TRUE scope=spfile;
alter system set audit_trail=DB_EXTENDED scope=spfile; ---DB_EXTENDED sql语句记录
SQL> show parameter audit;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/app/oracle/admin/test/
adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB_EXTENDED
== 指定表审计
SQL> AUDIT UPDATE,DELETE,INSERT ON TEST.TEST by access;
== 表更改数据 ==
-->crt工具连接
SQL> insert into test values (10,12,'24-APR-21',120,'ab');
SQL> update test set ID=24 where id=23;
SQL> delete from test where id=24;
-->PLSQL连接
update test set id=24 where id=29;
==== 查询审计信息 ==
set lines 300
col EXTENDED_TIMESTAMP for a40
col SQL_TEXT for a60
col os_user for a18
col db_user for a12
col USERHOST for a18
set pagesize 1000
select to_char(EXTENDED_TIMESTAMP,'yyyy-mm-dd hh24:mi:ss'),SESSION_ID,os_user,DB_USER,USERHOST,SQL_TEXT
from DBA_COMMON_AUDIT_TRAIL ORDER BY EXTENDED_TIMESTAMP ASC;
--输出：
TO_CHAR(EXTENDED_TI SESSION_ID OS_USER DB_USER USERHOST SQL_TEXT
------------------- ---------- ------------------ ------------ ------------------ ------------------------------------------------------
2021-04-24 20:28:15 4460757 oracle TEST test insert into test values (10,12,'24-APR-21',120,'ab')
2021-04-24 20:32:04 4460757 oracle TEST test update test set ID=24 where id=23
2021-04-24 20:35:36 4460757 oracle TEST test delete from test where id=24
2021-04-24 20:44:29 4460759 Administrator TEST WorkGroup\LZ-PC
2021-04-24 20:44:29 4460760 Administrator TEST WorkGroup\LZ-PC
2021-04-24 20:44:33 4460760 Administrator TEST WorkGroup\LZ-PC
2021-04-24 20:44:51 4460761 Administrator TEST WorkGroup\LZ-PC
2021-04-24 20:45:45 4460761 Administrator TEST WorkGroup\LZ-PC update test set id=24 where id=29
================ 方式二、创建trigger审计 ============
tips：其他user创建trigger
优点：占用资源少，用户和空间可以自定义
缺点：没有全库审计记录的表审计信息详细
-->创建审计表
CREATE TABLE TEST.trig_sql
( "LT" DATE,
"SID" NUMBER,
"SERIAL#" NUMBER,
"USERNAME" VARCHAR2(30),
"OSUSER" VARCHAR2(64),
"MACHINE" VARCHAR2(32),
"TERMINAL" VARCHAR2(16),
"PROGRAM" VARCHAR2(64),
"SQLTEXT" VARCHAR2(2000),
"STATUS" VARCHAR2(30),
"CLIENT_IP" VARCHAR2(60)
);
-->sys创建审计trigger 可以创建审计用户，trigger创建在指定用户下
create or replace trigger pri_test
after insert or update or delete on test.test
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF inserting THEN
INSERT INTO TEST.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'INSERT',
sys_context('userenv','ip_address')
from v$sql q, v$session s
where s.audsid=(select userenv('SESSIONID') from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF deleting then
INSERT INTO TEST.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'DELETE',
sys_context('userenv','ip_address')
from v$sql q, v$session s
where s.audsid=(select userenv('SESSIONID') from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
ELSIF updating then
INSERT INTO TEST.trig_sql
select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER,
s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line,
'UPDATE',
sys_context('userenv','ip_address')
from v$sql q, v$session s
where s.audsid=(select userenv('SESSIONID') from dual)
and s.prev_sql_addr=q.address
AND s.PREV_HASH_VALUE = q.hash_value;
COMMIT;
END IF;
END;
/
-->表更改数据
CRT工具连接
SQL> insert into test values (66,22,'24-APR-21',666,'ab');
PLSQL连接
update test.test set id=24 where id=50;
-->查询审计表信息
col USERNAME for a12
col OSUSER for a14
col MACHINE for a20
col SQLTEXT for a70
col status for a12
col CLIENT_IP for a20
select SID,USERNAME,OSUSER,MACHINE,SQLTEXT,STATUS,CLIENT_IP from TEST.trig_sql;
SID USERNAME OSUSER MACHINE SQLTEXT STATUS CLIENT_IP
---- ------------ -------------- -------------------- ---------------------------------------------------------------------- ------------ --------------------
1 SYS oracle test select SID,USERNAME,OSUSER,MACHINE,SQLTEXT,STATUS from TEST.trig_sql UPDATE 192.168.154.1
38 SYS Administrator WorkGroup\LZ-PC begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.154.1
39 SYS Administrator WorkGroup\LZ-PC begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.154.1
40 SYS Administrator WorkGroup\LZ-PC select value from v$sesstat where sid = :sid order by statistic# UPDATE 192.168.154.1
40 SYS Administrator WorkGroup\LZ-PC select value from v$sesstat where sid = :sid order by statistic# UPDATE 192.168.154.1
41 SYS Administrator WorkGroup\LZ-PC begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.154.1
41 TEST Administrator WorkGroup\LZ-PC begin :id := sys.dbms_transaction.local_transaction_id; end; UPDATE 192.168.154.1
37 TEST oracle test SELECT DECODE('A','A','1','2') FROM DUAL INSERT