活动第42次（2018-07-21星期六下午）
1. Mandatory auditing：Trail在/u01/app/oracle/admin/orcl/adump（默认是文本形式）：
Audit file /u01/app/oracle/admin/orcl/adump/orcl_ora_5917_20180721112448670359143795.aud
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_2
System name: Linux
Node name: station90.example.com
Release: 4.8.6-1.el6.elrepo.x86_64
Version: #1 SMP Mon Oct 31 13:01:00 EDT 2016
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 5917, image: oracle@station90.example.com (TNS V1-V3)
Sat Jul 21 11:24:48 2018 +08:00
LENGTH : '160'
ACTION : 'CONNECT'
DATABASE USER: '/'
PRIVILEGE : 'SYSDBA'
CLIENT USER: 'oracle'
CLIENT TERMINAL: 'pts/2'
STATUS: '0'
DBID: '1508759397'
Sat Jul 21 11:24:48 2018 +08:00
LENGTH : '159'
ACTION : 'COMMIT'
DATABASE USER: '/'
PRIVILEGE : 'SYSDBA'
CLIENT USER: 'oracle'
CLIENT TERMINAL: 'pts/2'
STATUS: '0'
DBID: '1508759397'
Sat Jul 21 11:24:48 2018 +08:00
LENGTH : '159'
ACTION : 'COMMIT'
DATABASE USER: '/'
PRIVILEGE : 'SYSDBA'
<font size="3">
</font>2. 12c 默认打开了SYSDBA (and SYSOPER) auditing：
SQL> show parameter sys
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_sys_operations boolean TRUE
3. Standard database auditing：
3.1 先观察一下审计的选项：
3.1.1 对象审计选项：
select* from DBA_OBJ_AUDIT_OPTS;
OWNEROBJECT_NAMEOBJECT_TYPEALTAUDCOMDELGRAINDINSLOCRENSELUPDREFEXECREREAWRIFBK
以上代表：所有者对象 对象类型 A/A（ 成功操作 A代表语句级 S代表会话级/ 未遂操作 A代表语句级 S代表会话级 ）
0. 建议做准备工作：
create tablespace tbsauditdatafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf'
size 20M autoextend on;
alter table aud$ move tablespace tbsaudit;
1. Enable database auditing：
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
建议为了记sql text把audit_trail 值改为'db','extended'
2. Specify audit options.
没法到达列级，非常粗:
audit updateonhr.employeesby session wheneversuccessful ;
select* from DBA_OBJ_AUDIT_OPTSao
where ao.owner='HR' and ao.object_name='EMPLOYEES';
OWNEROBJECT_NAMEOBJECT_TYPEALTAUDCOMDELGRAINDINSLOCRENSELUPDREFEXECREREAWRIFBK
1HREMPLOYEESTABLE-/--/--/--/--/--/--/--/--/--/-S/--/--/--/--/--/--/-
SQL> conn hr/oracle_4U
Connected.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> updateemployeesset salary=salary+1 where employee_id=100;
1 row updated.
SQL> rollback;
Rollback complete.
SQL>
查看审计轨迹：
select* from dba_audit_trail order by 5 desc;
insert into dba_audit_trail (OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, OWNER, OBJ_NAME, ACTION, ACTION_NAME, NEW_OWNER, NEW_NAME, OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, GRANTEE, AUDIT_OPTION, SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID, RETURNCODE, PRIV_USED, CLIENT_ID, ECONTEXT_ID, SESSION_CPU, EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER, OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT, OBJ_EDITION_NAME, DBID)
values ('oracle', 'HR', 'station90.example.com', 'pts/2', to_date('21-07-2018 14:40:25', 'dd-mm-yyyy hh24:mi:ss'), 'HR', 'EMPLOYEES', 103, 'SESSION REC', '', '', '', '', '', '', '', '----------S-----', null, null, null, null, '', '', 1080320, 1, 11, 0, '', '', '', null, '21-JUL-18 02.40.25.212335 PM +08:00', null, '', 0, '20526', '0A000200AD050000', 2796807, '', 'updateemployeesset salary=salary+1 where employee_id=100', '', 1508759397);
select* from dba_audit_trail order by 5 desc;
select* from dba_common_audit_trailorder by 6 desc;3.1.2 权限审计选项：
11g这里有默认一大堆，而且都不是focus，应该去掉：
select * from DBA_PRIV_AUDIT_OPTS;
USER_NAMEPROXY_NAMEPRIVILEGESUCCESSFAILURE
以上代表：用户 权限 成功行使/未遂行使
2. Specify audit options.
audit selectany table by hr by session whenever successful;
audit drop any table by hr by session whenever successful;
audit drop any table by hr by access whenever successful;
select * from DBA_PRIV_AUDIT_OPTS;
USER_NAMEPROXY_NAMEPRIVILEGESUCCESSFAILURE
1HR SELECT ANY TABLEBY SESSIONNOT SET
2HR DROP ANY TABLEBY ACCESSNOT SET
另外权限审计同时一定也是语句级的审计选项：
select* from DBA_STMT_AUDIT_OPTS;
USER_NAMEPROXY_NAMEAUDIT_OPTIONSUCCESSFAILURE
1HR SELECT ANY TABLEBY SESSIONNOT SET
2HR DROP ANY TABLEBY ACCESSNOT SET
SQL> conn / as sysdba
Connected.
SQL> grant select any table to hr;
Grant succeeded.
SQL> conn hr/oracle_4U
Connected.
SQL> selectsalaryfrom employees where employee_id=100;
SALARY
----------
24000
SQL> selectcount(*) from sh.sales ;
COUNT(*)
----------
918843
SQL>
查看审计轨迹：
select* from dba_common_audit_trailorder by 6 desc;
insert into dba_audit_trail (OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, OWNER, OBJ_NAME, ACTION, ACTION_NAME, NEW_OWNER, NEW_NAME, OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, GRANTEE, AUDIT_OPTION, SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK, COMMENT_TEXT, SESSIONID, ENTRYID, STATEMENTID, RETURNCODE, PRIV_USED, CLIENT_ID, ECONTEXT_ID, SESSION_CPU, EXTENDED_TIMESTAMP, PROXY_SESSIONID, GLOBAL_UID, INSTANCE_NUMBER, OS_PROCESS, TRANSACTIONID, SCN, SQL_BIND, SQL_TEXT, OBJ_EDITION_NAME, DBID)
values ('oracle', 'HR', 'station90.example.com', 'pts/2', to_date('21-07-2018 15:00:53', 'dd-mm-yyyy hh24:mi:ss'), 'SH', 'SALES', 103, 'SESSION REC', '', '', '', '', '', '', '', '---------S------', null, null, null, null, '', '', 1080363, 1, 11, 0, 'SELECT ANY TABLE', '', '', null, '21-JUL-18 03.00.53.520010 PM +08:00', null, '', 0, '28161', '', 2797513, '', 'selectcount(*) from sh.sales ', '', 1508759397);3.1.3 语句审计选项：
都必须是by access的：
audit table by hr by session whenever successful;
语句审计，要从新的会话里，进行测试：
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05211_a ( anumber ) ;
Table created.
SQL>
查看审计轨迹：
select* from dba_common_audit_trailorder by 6 desc;
insert into dba_common_audit_trail (AUDIT_TYPE, SESSION_ID, PROXY_SESSIONID, STATEMENTID, ENTRYID, EXTENDED_TIMESTAMP, GLOBAL_UID, DB_USER, CLIENT_ID, ECONTEXT_ID, EXT_NAME, OS_USER, USERHOST, OS_PROCESS, TERMINAL, INSTANCE_NUMBER, OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, NEW_OWNER, NEW_NAME, ACTION, STATEMENT_TYPE, AUDIT_OPTION, TRANSACTIONID, RETURNCODE, SCN, COMMENT_TEXT, SQL_BIND, SQL_TEXT, OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, OS_PRIVILEGE, GRANTEE, PRIV_USED, SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK, SESSION_CPU, OBJ_EDITION_NAME, DBID)
values ('Standard Audit', 1080375, null, 11, 1, '21-JUL-18 03.08.48.468039 PM +08:00', '', 'HR', '', '', '', 'oracle', 'station90.example.com', '31251', 'pts/2', 0, 'HR', 'T05211_A', '', '', '', 1, 'CREATE TABLE', '', '0100100087060000', 0, null, '', '', 'create table t05211_a ( anumber ) ', '', '', '', 'NONE', '', 'CREATE TABLE', '', null, null, null, null, '', null, '', 1508759397);
SQL> drop table t05211_a;
Table dropped.
SQL>
insert into dba_common_audit_trail (AUDIT_TYPE, SESSION_ID, PROXY_SESSIONID, STATEMENTID, ENTRYID, EXTENDED_TIMESTAMP, GLOBAL_UID, DB_USER, CLIENT_ID, ECONTEXT_ID, EXT_NAME, OS_USER, USERHOST, OS_PROCESS, TERMINAL, INSTANCE_NUMBER, OBJECT_SCHEMA, OBJECT_NAME, POLICY_NAME, NEW_OWNER, NEW_NAME, ACTION, STATEMENT_TYPE, AUDIT_OPTION, TRANSACTIONID, RETURNCODE, SCN, COMMENT_TEXT, SQL_BIND, SQL_TEXT, OBJ_PRIVILEGE, SYS_PRIVILEGE, ADMIN_OPTION, OS_PRIVILEGE, GRANTEE, PRIV_USED, SES_ACTIONS, LOGOFF_TIME, LOGOFF_LREAD, LOGOFF_PREAD, LOGOFF_LWRITE, LOGOFF_DLOCK, SESSION_CPU, OBJ_EDITION_NAME, DBID)
values ('Standard Audit', 1080375, null, 12, 2, '21-JUL-18 03.11.21.454748 PM +08:00', '', 'HR', '', '', '', 'oracle', 'station90.example.com', '31251', 'pts/2', 0, 'HR', 'T05211_A', '', '', '', 12, 'DROP TABLE', '', '040010000F060000', 0, 2798369, '', '', 'drop table t05211_a', '', '', '', 'NONE', '', '', '', null, null, null, null, '', null, '', 1508759397);关掉标准审计：1. 先查出选项；2. 拼凑命令关掉，比如：noaudit select any table;
标准审计还可以关掉，或者产出为xml格式的操作系统文件（在adump目录之下），xml的审计轨迹可以仍然用dba_common_audit_trail视图查看，或者查看v$xml_audit_trail或者直接查看adump/*.xml：
SQL> alter system set audit_trail=none scope=spfile;
System altered.
SQL> alter system set audit_trail='xml','EXTENDED' scope=spfile;
System altered.
SQL>
----------------------------------------------------------------------------------------
细粒度审计是对占标准审计1/3的对象审计的补充：
先查看一下有没有细粒度审计策略：
select* from dba_audit_policies;与多租户的统一审计策略相比较：
select * from audit_unified_policiesorder by 1;
select* fromaudit_unified_enabled_policies;
统一审计轨迹（unified_audit_trail），每个插件数据库看自己的，根容器看全部的cdb_unified_audit_trail：
在介绍细粒度审计的时候，先准备handler存储过程（可选）：
create table t05211_fga ( a varchar2(200))tablespace tbsaudit;
CREATE OR REPLACE PROCEDURE proc05211( object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 )
AS
begin
insert into t05211_fgavalues( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
sys_context('userenv','ip_address')||' '||
sys_context('userenv','session_user')||' '||
sys_context('userenv','authentication_type')||' '||
sys_context('userenv','current_user')||' '||
object_schema||' '||
object_name||' '||
policy_name);
end;定细粒度访问的策略：
begin
dbms_fga.add_policy(object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'POLICY1',
audit_condition => 'department_id=20',
audit_column => 'salary,commission_pct',
handler_schema => 'SYS',
handler_module => 'PROC05211',
enable => true,
statement_types => 'SELECT',
audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
audit_column_opts => dbms_fga.ANY_COLUMNS
);
end;查看策略:
select* from dba_audit_policies;
下面进行测试：
SQL> conn hr/oracle_4U
Connected.
SQL> selectsalary, commission_pct from employees where department_id=20;
SALARY COMMISSION_PCT
---------- --------------
13000
6000
SQL>
select* from t05211_fga;
A
12018-07-21:16:13:42HR DATABASE SYS HR EMPLOYEES POLICY1
有IP的再试验一次：
SQL> conn hr/oracle_4U@orcl
Connected.
SQL> selectsalary, commission_pct from employees where department_id=20;
SALARY COMMISSION_PCT
---------- --------------
13000
6000
select * from t05211_fga;
A
12018-07-21:16:16:05 192.168.0.90 HR DATABASE SYS HR EMPLOYEES POLICY1
22018-07-21:16:13:42HR DATABASE SYS HR EMPLOYEES POLICY1
$ cd adump/
$ ls
orcl_ora_10721_20180721113424109886143795.audorcl_ora_19375_20180721115828725590143795.aud
orcl_ora_10734_20180721113431819824143795.audorcl_ora_19434_20180721161342086781289225.xml
orcl_ora_10736_20180721113432966666143795.audorcl_ora_20417_20180721161605749408491053.xml
orcl_ora_10743_20180721113436383368143795.audorcl_ora_28002_20180721145944647551143795.aud
orcl_ora_16798_20180721115055133320143795.audorcl_ora_3073_20180721151947185275143795.aud
orcl_ora_16798_20180721115103218292143795.audorcl_ora_5917_20180721112448670359143795.aud
orcl_ora_16879_20180721115103268719143795.audorcl_ora_8900_20180721112949359247143795.aud
orcl_ora_16900_20180721115107958856143795.audorcl_ora_8900_20180721112959200792143795.aud
orcl_ora_18763_20180721143343274728143795.audorcl_ora_8992_20180721112959254020143795.aud
orcl_ora_19253_20180721115756821268143795.audorcl_ora_9113_20180721113003961264143795.aud
orcl_ora_19266_20180721115758137202143795.audorcl_ora_9481_20180721113032771187143795.aud
$
----------------------------------------------------------基于值的审计：
create table t05211_value ( a varchar2(200) ) tablespace tbsaudit;
create or replace trigger trg05211
after update of salary on hr.employees
referencing new as new old as old
for each row
begin
if :old.salary != :new.salary
then
insert into t05211_valuevalues( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
sys_context('userenv','ip_address')||' '||
sys_context('userenv','session_user')||' '||
sys_context('userenv','authentication_type')||' '||
sys_context('userenv','current_user')||' '||
:old.salary||' '||
:new.salary);
end if;
end;
辅助FGA，还记住了新值和旧值：
SQL> show user
USER is "HR"
SQL> update employees set salary=salary*2 where employee_id=101;
1 row updated.
SQL> commit;
Commit complete.
SQL>
页:
[1]