活动第42次（2018-07-21星期六下午）

botang

1. Mandatory auditing：
Trail在/u01/app/oracle/admin/orcl/adump（默认是文本形式）：

1. Audit file /u01/app/oracle/admin/orcl/adump/orcl_ora_5917_20180721112448670359143795.aud
   
2. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
   
3. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
   
4. ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_2
   
5. System name:    Linux
   
6. Node name:      station90.example.com
   
7. Release:        4.8.6-1.el6.elrepo.x86_64
   
8. Version:        #1 SMP Mon Oct 31 13:01:00 EDT 2016
   
9. Machine:        x86_64
   
10. Instance name: orcl
    
11. Redo thread mounted by this instance: 1
    
12. Oracle process number: 53
    
13. Unix process pid: 5917, image: oracle@station90.example.com (TNS V1-V3)
    
14. 
    
15. Sat Jul 21 11:24:48 2018 +08:00
    
16. LENGTH : '160'
    
17. ACTION :[7] 'CONNECT'
    
18. DATABASE USER:[1] '/'
    
19. PRIVILEGE :[6] 'SYSDBA'
    
20. CLIENT USER:[6] 'oracle'
    
21. CLIENT TERMINAL:[5] 'pts/2'
    
22. STATUS:[1] '0'
    
23. DBID:[10] '1508759397'
    
24. 
    
25. Sat Jul 21 11:24:48 2018 +08:00
    
26. LENGTH : '159'
    
27. ACTION :[6] 'COMMIT'
    
28. DATABASE USER:[1] '/'
    
29. PRIVILEGE :[6] 'SYSDBA'
    
30. CLIENT USER:[6] 'oracle'
    
31. CLIENT TERMINAL:[5] 'pts/2'
    
32. STATUS:[1] '0'
    
33. DBID:[10] '1508759397'
    
34. 
    
35. Sat Jul 21 11:24:48 2018 +08:00
    
36. LENGTH : '159'
    
37. ACTION :[6] 'COMMIT'
    
38. DATABASE USER:[1] '/'
    
39. PRIVILEGE :[6] 'SYSDBA'
    
40.                                                                                                                     <font size="3">
    
41. </font>

复制代码

2. 12c 默认打开了SYSDBA (and SYSOPER) auditing：

1. SQL> show parameter sys
   
2. 
   
3. NAME                                     TYPE         VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. audit_sys_operations                     boolean         TRUE
   

复制代码

3. Standard database auditing：
3.1 先观察一下审计的选项：
3.1.1 对象审计选项：

1. select  * from DBA_OBJ_AUDIT_OPTS;

复制代码

OWNER

OBJECT_NAME

OBJECT_TYPE

ALT

AUD

COM

DEL

GRA

IND

INS

LOC

REN

SEL

UPD

REF

EXE

CRE

REA

WRI

FBK

以上代表：所有者  对象                 对象类型                                     A/A（ 成功操作 A代表语句级 S代表会话级  /    未遂操作    A代表语句级 S代表会话级 ）



0. 建议做准备工作：

1. create tablespace tbsaudit  datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf'
   
2. size 20M autoextend on;
   
3. 
   
4. alter table aud$ move tablespace tbsaudit;

复制代码

1. Enable database auditing：

1. SQL> show parameter audit
   
2. NAME                                     TYPE         VALUE
   
3. ------------------------------------ ----------- ------------------------------
   
4. audit_file_dest                      string         /u01/app/oracle/admin/orcl/adu
   
5.                                                  mp
   
6. audit_sys_operations                     boolean         FALSE
   
7. audit_syslog_level                     string
   
8. audit_trail                             string         DB
   
9. unified_audit_sga_queue_size             integer         1048576
   

复制代码

建议为了记sql text把audit_trail 值改为'db','extended'

2. Specify audit options.
没法到达列级，非常粗:

1. audit update  on  hr.employees  by session whenever  successful ;
   
2. 
   
3. select  * from DBA_OBJ_AUDIT_OPTS  ao
   
4.   where ao.owner='HR' and ao.object_name='EMPLOYEES';

复制代码

OWNER

OBJECT_NAME

OBJECT_TYPE

ALT

AUD

COM

DEL

GRA

IND

INS

LOC

REN

SEL

UPD

REF

EXE

CRE

REA

WRI

FBK

1

HR

EMPLOYEES

TABLE

-/-

-/-

-/-

-/-

-/-

-/-

-/-

-/-

-/-

-/-

S/-

-/-

-/-

-/-

-/-

-/-

-/-

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> select salary from employees where employee_id=100;
   
4. 
   
5.     SALARY
   
6. ----------
   
7.      24000
   
8. 
   
9. SQL> update  employees  set salary=salary+1 where employee_id=100;
   
10. 
    
11. 1 row updated.
    
12. 
    
13. SQL> rollback;
    
14. 
    
15. Rollback complete.
    
16. 
    
17. SQL>
    

复制代码

查看审计轨迹：

1. select  * from dba_audit_trail order by 5 desc;

复制代码

1. 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)
   
2. 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, '', 'update  employees  set salary=salary+1 where employee_id=100', '', 1508759397);

复制代码

1. select  * from dba_audit_trail order by 5 desc;
   
2. 
   
3. select  * from dba_common_audit_trail  order by 6 desc;

复制代码

3.1.2 权限审计选项：
11g这里有默认一大堆，而且都不是focus，应该去掉：

1. select * from DBA_PRIV_AUDIT_OPTS;

复制代码

USER_NAME

PROXY_NAME

PRIVILEGE

SUCCESS

FAILURE

以上代表：用户                      权限              成功行使/未遂行使                  

2. Specify audit options.

1. audit select  any table by hr by session whenever successful;
   
2. 
   
3. audit drop any table by hr by session whenever successful;

复制代码

1. audit drop any table by hr by access whenever successful;

复制代码

1. select * from DBA_PRIV_AUDIT_OPTS;

复制代码

	USER_NAME	PROXY_NAME	PRIVILEGE	SUCCESS	FAILURE
1	HR		SELECT ANY TABLE	BY SESSION	NOT SET
2	HR		DROP ANY TABLE	BY ACCESS	NOT SET

另外权限审计同时一定也是语句级的审计选项：

1. select  * from DBA_STMT_AUDIT_OPTS;

复制代码

	USER_NAME	PROXY_NAME	AUDIT_OPTION	SUCCESS	FAILURE
1	HR		SELECT ANY TABLE	BY SESSION	NOT SET
2	HR		DROP ANY TABLE	BY ACCESS	NOT SET

1. SQL> conn / as sysdba
   
2. Connected.
   
3. SQL> grant select any table to hr;
   
4. 
   
5. Grant succeeded.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  salary  from employees where employee_id=100;
   
10. 
    
11.     SALARY
    
12. ----------
    
13.      24000
    
14. 
    
15. SQL> select  count(*) from sh.sales ;
    
16. 
    
17.   COUNT(*)
    
18. ----------
    
19.     918843
    
20. 
    
21. SQL>
    

复制代码

查看审计轨迹：

1. select  * from dba_common_audit_trail  order by 6 desc;

复制代码

1. 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)
   
2. 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, '', 'select  count(*) from sh.sales ', '', 1508759397);

复制代码

3.1.3 语句审计选项：
都必须是by access的：

1. audit table by hr by session whenever successful;

复制代码

语句审计，要从新的会话里，进行测试：

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. 
   
4. SQL> create table t05211_a ( a  number ) ;
   
5. 
   
6. Table created.
   
7. 
   
8. SQL>
   

复制代码

查看审计轨迹：

1. select  * from dba_common_audit_trail  order by 6 desc;

复制代码

1. 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)
   
2. 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 ( a  number ) ', '', '', '', 'NONE', '', 'CREATE TABLE', '', null, null, null, null, '', null, '', 1508759397);

复制代码

1. SQL> drop table t05211_a;
   
2. 
   
3. Table dropped.
   
4. 
   
5. SQL>
   

复制代码

1. 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)
   
2. 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：

1. SQL> alter system set audit_trail=none scope=spfile;
   
2. 
   
3. System altered.
   
4. 
   
5. SQL> alter system set audit_trail='xml','EXTENDED' scope=spfile;
   
6. 
   
7. System altered.
   
8. 
   
9. SQL>
   

复制代码

----------------------------------------------------------------------------------------
细粒度审计是对占标准审计1/3的对象审计的补充：
先查看一下有没有细粒度审计策略：

1. select  * from dba_audit_policies;

复制代码

与多租户的统一审计策略相比较：
select * from audit_unified_policies  order by 1;
select  * from  audit_unified_enabled_policies;
统一审计轨迹（unified_audit_trail），每个插件数据库看自己的，根容器看全部的cdb_unified_audit_trail：


在介绍细粒度审计的时候，先准备handler存储过程（可选）：

1. create table t05211_fga ( a varchar2(200))  tablespace tbsaudit;
   
2. 
   
3. CREATE OR REPLACE PROCEDURE proc05211  ( object_schema VARCHAR2,
   
4.                                                                                   object_name VARCHAR2,
   
5.                                                                                   policy_name VARCHAR2 )
   
6. AS
   
7. 
   
8. begin
   
9. insert into t05211_fga  values( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
   
10.                                                   sys_context('userenv','ip_address')||' '||
    
11.                                                   sys_context('userenv','session_user')||' '||
    
12.                                                   sys_context('userenv','authentication_type')||' '||
    
13.                                                   sys_context('userenv','current_user')||' '||
    
14.                                                   object_schema||' '||
    
15.                                                   object_name||' '||
    
16.                                                   policy_name);
    
17. end;

复制代码

定细粒度访问的策略：

1. begin
   
2.   dbms_fga.add_policy(object_schema => 'HR',
   
3.                                        object_name => 'EMPLOYEES',
   
4.                                        policy_name => 'POLICY1',
   
5.                                        audit_condition => 'department_id=20',
   
6.                                        audit_column => 'salary,commission_pct',
   
7.                                        handler_schema => 'SYS',
   
8.                                        handler_module => 'PROC05211',
   
9.                                        enable => true,
   
10.                                        statement_types => 'SELECT',
    
11.                                        audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
    
12.                                        audit_column_opts => dbms_fga.ANY_COLUMNS
    
13.                                       );
    
14. end;

复制代码

查看策略:

1. select  * from dba_audit_policies;

复制代码

下面进行测试：

1. SQL> conn hr/oracle_4U
   
2. Connected.
   
3. SQL> select  salary, commission_pct from employees where department_id=20;
   
4. 
   
5.     SALARY COMMISSION_PCT
   
6. ---------- --------------
   
7.      13000
   
8.       6000
   
9. 
   
10. SQL>
    

复制代码

1. select  * from t05211_fga;

复制代码

	A
1	2018-07-21:16:13:42  HR DATABASE SYS HR EMPLOYEES POLICY1

有IP的再试验一次：

1. SQL> conn hr/oracle_4U@orcl
   
2. Connected.
   
3. SQL> select  salary, commission_pct from employees where department_id=20;
   
4. 
   
5.     SALARY COMMISSION_PCT
   
6. ---------- --------------
   
7.      13000
   
8.       6000
   
9. 
   

复制代码

1. select * from t05211_fga;

复制代码

	A
1	2018-07-21:16:16:05 192.168.0.90 HR DATABASE SYS HR EMPLOYEES POLICY1
2	2018-07-21:16:13:42  HR DATABASE SYS HR EMPLOYEES POLICY1

[oracle@station90 orcl]$ cd adump/
[oracle@station90 adump]$ ls
orcl_ora_10721_20180721113424109886143795.aud  orcl_ora_19375_20180721115828725590143795.aud
orcl_ora_10734_20180721113431819824143795.aud  orcl_ora_19434_20180721161342086781289225.xml
orcl_ora_10736_20180721113432966666143795.aud  orcl_ora_20417_20180721161605749408491053.xml
orcl_ora_10743_20180721113436383368143795.aud  orcl_ora_28002_20180721145944647551143795.aud
orcl_ora_16798_20180721115055133320143795.aud  orcl_ora_3073_20180721151947185275143795.aud
orcl_ora_16798_20180721115103218292143795.aud  orcl_ora_5917_20180721112448670359143795.aud
orcl_ora_16879_20180721115103268719143795.aud  orcl_ora_8900_20180721112949359247143795.aud
orcl_ora_16900_20180721115107958856143795.aud  orcl_ora_8900_20180721112959200792143795.aud
orcl_ora_18763_20180721143343274728143795.aud  orcl_ora_8992_20180721112959254020143795.aud
orcl_ora_19253_20180721115756821268143795.aud  orcl_ora_9113_20180721113003961264143795.aud
orcl_ora_19266_20180721115758137202143795.aud  orcl_ora_9481_20180721113032771187143795.aud
[oracle@station90 adump]$

----------------------------------------------------------基于值的审计：

1. create table t05211_value ( a varchar2(200) ) tablespace tbsaudit;
   
2. 
   
3. 
   
4. create or replace trigger trg05211
   
5. after update of salary on hr.employees
   
6. referencing new as new old as old
   
7. for each row
   
8. begin
   
9.   if :old.salary != :new.salary
   
10.   then
    
11.       insert into t05211_value  values( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
    
12.                                                   sys_context('userenv','ip_address')||' '||
    
13.                                                   sys_context('userenv','session_user')||' '||
    
14.                                                   sys_context('userenv','authentication_type')||' '||
    
15.                                                   sys_context('userenv','current_user')||' '||
    
16.                                                   :old.salary||' '||
    
17.                                                   :new.salary);
    
18.     end if;
    
19. end;      

复制代码

辅助FGA，还记住了新值和旧值：

1. SQL> show user
   
2. USER is "HR"
   
3. SQL> update employees set salary=salary*2 where employee_id=101;
   
4. 
   
5. 1 row updated.
   
6. 
   
7. SQL> commit;
   
8. 
   
9. Commit complete.
   
10. 
    
11. SQL>
    

复制代码