oracle 开启审计

--修改参数，启动 sys 审核 和 sql
sqlplus / as sysdba
show parameter audit
alter system set audit_sys_operations = true scope=spfile;
alter system set audit_trail = db,extended scope = spfile;
startup force

创建表空间
create tablespace "audit" datafile '/u01/app/oracle/oradata/orcl/audit01.dbf' size 1024m autoextend on;
--创建用户：
create user c##aud identified by aud default tablespace "audit" ;
grant dba to c##aud;
--移动表空间
alter table sys.aud$ move tablespace "audit";
alter table sys.aud$ move tablespace "audit" lob (SQLBIND,SQLTEXT) store as( tablespace "audit"); 
alter user sys quota unlimited on "audit";

--查看表的所属表空间
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

开启审计：
audit all by C##AUD by access; 
audit select table, update table, insert table, delete table by C##AUD by access; 
audit execute procedure by C##AUD by access; 

audit all by C##HR65 by access; 
audit select table, update table, insert table, delete table by C##HR65 by access; 
audit execute procedure by C##HR65 by access;

audit all by C##MERIT by access;
audit select table, update table, insert table, delete table by C##MERIT by access;
audit execute procedure by C##MERIT by access;

audit all by C##MERITNC65 by access;
audit select table, update table, insert table, delete table by C##MERITNC65 by access;
audit execute procedure by C##MERITNC65 by access;

audit all by C##NC651 by access;
audit select table, update table, insert table, delete table by C##NC651 by access;
audit execute procedure by C##NC651 by access;

--制定脚本：
cat aud.sh 
#/bin/bash
export ORACLE_BASE="/u01/app/oracle"
export ORACLE_HOME="/u01/app/oracle/product/12.2.0/db_1"
export ORACLE_SID="orcl"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
/u01/app/oracle/product/12.2.0/db_1/bin/sqlplus / as sysdba << EOF
spool /home/oracle/auser.log
begin
execute immediate 'create table c##aud.aud_'||to_char(sysdate, 'yyyymmddhh24miss')||' as select * from aud$';
end;
/
truncate table aud$;
spool
EOF
exit

chmod +x aud.sh 

00 */1 * * * /home/oracle/aud.sh >> /home/oracle/logs/aud_71_$(date +\%Y\%m\%d_\%H\%M\%S).log 2>&1

 

--查看开启的审计
select object_name,object_type,alt,del,ins,upd,sel from dba_obj_audit_opts; 
select count(*) from dba_audit_trail;
select count(*) from aud$;
select * from aud$ WHERE sqltext IS NOT NULL AND DBMS_LOB.GETLENGTH(sqltext) <> 0 ;    --slqtext 不为空判断
select segment_name,bytes/1024/1024 from dba_segments where segment_name in('AUD$');    --查看表的大小
SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;    --查看这2个表的默认表空间
select userhost from aud$ group  by userhost;

select sessionid,entryid,statement,userid,userhost,terminal,action#,returncode,obj$creator,
obj$name,spare1,ntimestamp#,instance#,process#,xid,scn,dbid,sqlbind,sqltext,rls$info,current_user 
from aud$ WHERE sqltext IS NOT NULL AND DBMS_LOB.GETLENGTH(sqltext) <> 0 and userhost = 'WORKGROUP\MERITIT';

 

--查看表空间使用率：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名", 
D.TOT_GROOTTE_MB "表空间大小(M)", 
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比", 
F.TOTAL_BYTES "空闲空间(M)", 
F.MAX_BYTES "最大块(M)" 
FROM (SELECT TABLESPACE_NAME, 
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 
FROM SYS.DBA_FREE_SPACE 
GROUP BY TABLESPACE_NAME) F, 
(SELECT DD.TABLESPACE_NAME, 
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
FROM SYS.DBA_DATA_FILES DD 
GROUP BY DD.TABLESPACE_NAME) D 
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 
ORDER BY 4 DESC; 

--查看是否自动扩展：
SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS 
FROM DBA_TABLESPACES T,DBA_DATA_FILES D 
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME 
ORDER BY TABLESPACE_NAME,FILE_NAME; 

 

--扩展：
--以审核的用户查询
select * from aud$;

select userid from aud$ group by userid

select to_char(ntimestamp# + 8 / 24, 'yyyy-mm-dd hh24:mi:ss') as dt,  --ntimestamp# 加 8 小时为我们的时间
       sqltext as st,
       sessionid as sd,
       entryid as ed,
       statement as sm,
       userid as ud,
       userhost as uh,
       terminal as tm,
       action# as ac,
       returncode as rt,
       obj$creator as oc,
       obj$name as oa,
       spare1 as sa,  
       instance# as it,
       process#  as po,
       xid       as xd,
       scn       as sn,
       dbid      as di,
       sqlbind   as sb,     
       rls$info     as ri,
       current_user as cu
  from aud$
 WHERE sqltext IS NOT NULL
   AND DBMS_LOB.GETLENGTH(sqltext) <> 0          --查询 clob 不为 0 的行
   AND dbms_lob.instr(sqltext, 'SELECT') > 0     --查询 clob 中包含 SELECT 的字符
   AND dbms_lob.instr(sqltext, 'so_saleorder') > 0
   and userid in ('用户名')
 order by dt desc;

 

--以客户端计算机名查询
select * from aud$;

select userhost from aud$ group  by userhost;
select sessionid,entryid,statement,userid,userhost,terminal,action#,returncode,obj$creator,
obj$name,spare1,ntimestamp#,instance#,process#,xid,scn,dbid,sqlbind,sqltext,rls$info,current_user 
from aud$ 
WHERE sqltext IS NOT NULL AND DBMS_LOB.GETLENGTH(sqltext) <> 0 
and userhost = '客户端名';