#### 檢查修改
``` sql
show parameter audit

alter system set audit_trail='TRUE' scope=spfile sid='*';
alter system set audit_trail=db,extended scope=spfile sid='*';

col repeat_interval for a55
set lines 200
set pages 999
col window_name for a30
col duration for a30
select a.window_name,a.repeat_interval,a.duration from dba_scheduler_windows a,dba_scheduler_wingroup_members b where a.window_name=b.window_name and b.window_group_name='MAINTENANCE_WINDOW_GROUP';

--而不管誰正在進行更新，則每次該動作發生時，都可以使用如下所示的audit命令
audit session;
noaudit session;
audit delete any table by session;
audit update any table by session;
audit delete any table by access whenever successful;
audit update any table by access whenever successful;
AUDIT DELETE ANY TABLE;  
audit update any table;
select action_name,count(*) from dba_audit_trail group by action_name;
 
 
--audit update,delete on dwzy.t_tx_znyc_pdbyq by access; whenever successful;
audit update,delete on DWZY.T_TX_ZNYC_PDBYQ by access;
--audit update,delete on DWZY.T_TX_YXSB_JLX by access;
--audit update,delete on DWZY.T_TX_YXSB_JLKF by access;
--audit update,delete on DWZY.T_TX_ZNYC_PDBYQ by access;
--audit update,delete on  firsoul.t1 by access;
--查詢審計的項是否跟設定的相同
set lines 150
select * from DBA_STMT_AUDIT_OPTS;
select * from DBA_priv_audit_opts;

--審計日誌查詢 DBA_AUDIT_STATEMENT
select * from DBA_AUDIT_TRAIL where rownum<10;

set lines 150
set pages 999
col OS_USERNAME for a15
col USERNAME for a15
col USERHOST for a20
col OWNER for a10
col SQL_TEXT for a40
col AUDIT_OPTION for a15
col COMMENT_TEXT for a40
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (select OS_USERNAME,USERNAME,USERHOST,OWNER,TIMESTAMP,COMMENT_TEXT,SQL_TEXT,AUDIT_OPTION from DBA_AUDIT_TRAIL  order by TIMESTAMP desc) where rownum<=10;

select * from (select OS_USERNAME,USERNAME,USERHOST,OWNER,TIMESTAMP,COMMENT_TEXT,SQL_TEXT,AUDIT_OPTION from DBA_AUDIT_TRAIL where ACTION_NAME='SELECT' order by TIMESTAMP desc) where rownum<=10;
```
#### 細粒度審計
``` sql
--細粒度審計
exec dbms_fga.add_policy(object_schema=>'FIRSOUL',object_name=>'T',policy_name=>'audit_t',statement_types=>'update,delete');

--檢視細粒度審計
set lines 150
set pages 999
col DB_USER for a10
col OS_USER for a10
col USERHOST for a20
col OBJECT_NAME for a10
col SQL_TEXT for a60
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
select * from (select TIMESTAMP,DB_USER,OS_USER,USERHOST,OBJECT_NAME,SQL_TEXT from dba_fga_audit_trail order by TIMESTAMP desc) where rownum<=10;

--檢視客戶端相關主機
set lines 150
col USERHOST for a30
select distinct USERHOST from dba_fga_audit_trail;

exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_DEV_DICT',policy_name=>'audit_T_DEV_DICT',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_LOGIC_DEV_DICT',policy_name=>'audit_T_LOGIC_DEV_DICT',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_PHY_DEV_DICT',policy_name=>'audit_T_PHY_DEV_DICT',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WORKFLOW',policy_name=>'audit_T_WORKFLOW',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WF_NODE',policy_name=>'audit_T_WF_NODE',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WF_EDGE',policy_name=>'audit_T_WF_EDGE',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WF_VAR',policy_name=>'audit_T_WF_VAR',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST',policy_name=>'audit_T_WFINST',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_CANDIDATE',policy_name=>'audit_T_WFINST_CANDIDATE',statement_types=>'update,delete');
--exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_CANDIDATE_BAK ',policy_name=>'audit_T_WFINST_CANDIDATE_BAK ',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_EDGE',policy_name=>'audit_T_WFINST_EDGE',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_LOG',policy_name=>'audit_T_WFINST_LOG',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_NODE',policy_name=>'audit_T_WFINST_NODE',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_STEP',policy_name=>'audit_T_WFINST_STEP',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_USER',policy_name=>'audit_T_WFINST_USER',statement_types=>'update,delete');
exec dbms_fga.add_policy(object_schema=>'PMSWF',object_name=>'T_WFINST_VAR',policy_name=>'audit_T_WFINST_VAR',statement_types=>'update,delete');
```

【AUDIT]Oracle審計配置及常用sql

相關文章