设为首页收藏本站

Botang唐波's Oracle Station

查看: 281|回复: 0

活动第42次(2018-07-21星期六下午)

[复制链接]

745

主题

1118

帖子

8120

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8120
发表于 2018-7-21 11:25:34 | 显示全部楼层 |阅读模式
1. Mandatory auditing:
Trail在/u01/app/oracle/admin/orcl/adump(默认是文本形式):
a.png

  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. Sat Jul 21 11:24:48 2018 +08:00
  15. LENGTH : '160'
  16. ACTION :[7] 'CONNECT'
  17. DATABASE USER:[1] '/'
  18. PRIVILEGE :[6] 'SYSDBA'
  19. CLIENT USER:[6] 'oracle'
  20. CLIENT TERMINAL:[5] 'pts/2'
  21. STATUS:[1] '0'
  22. DBID:[10] '1508759397'

  23. Sat Jul 21 11:24:48 2018 +08:00
  24. LENGTH : '159'
  25. ACTION :[6] 'COMMIT'
  26. DATABASE USER:[1] '/'
  27. PRIVILEGE :[6] 'SYSDBA'
  28. CLIENT USER:[6] 'oracle'
  29. CLIENT TERMINAL:[5] 'pts/2'
  30. STATUS:[1] '0'
  31. DBID:[10] '1508759397'

  32. Sat Jul 21 11:24:48 2018 +08:00
  33. LENGTH : '159'
  34. ACTION :[6] 'COMMIT'
  35. DATABASE USER:[1] '/'
  36. PRIVILEGE :[6] 'SYSDBA'
  37.                                                                                                                     <font size="3">
  38. </font>
复制代码
2. 12c 默认打开了SYSDBA (and SYSOPER) auditing:
b.png

  1. SQL> show parameter sys

  2. NAME                                     TYPE         VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. audit_sys_operations                     boolean         TRUE
复制代码

3. Standard database auditing:
3.1 先观察一下审计的选项:
3.1.1 对象审计选项:
  1. select  * from DBA_OBJ_AUDIT_OPTS;
复制代码
  OWNEROBJECT_NAMEOBJECT_TYPEALTAUDCOMDELGRAINDINSLOCRENSELUPDREFEXECREREAWRIFBK
以上代表:所有者  对象                 对象类型                                     A/A( 成功操作 A代表语句级 S代表会话级  /    未遂操作    A代表语句级 S代表会话级 )

d.png

0. 建议做准备工作:
  1. create tablespace tbsaudit  datafile '/u01/app/oracle/oradata/orcl/tbsaudit.dbf'
  2. size 20M autoextend on;

  3. 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.
没法到达列级,非常粗:
f.png

  1. audit update  on  hr.employees  by session whenever  successful ;

  2. select  * from DBA_OBJ_AUDIT_OPTS  ao
  3.   where ao.owner='HR' and ao.object_name='EMPLOYEES';
复制代码
   OWNEROBJECT_NAMEOBJECT_TYPEALTAUDCOMDELGRAINDINSLOCRENSELUPDREFEXECREREAWRIFBK
1HREMPLOYEESTABLE-/--/--/--/--/--/--/--/--/--/-S/--/--/--/--/--/--/-
a.png

  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> select salary from employees where employee_id=100;

  4.     SALARY
  5. ----------
  6.      24000

  7. SQL> update  employees  set salary=salary+1 where employee_id=100;

  8. 1 row updated.

  9. SQL> rollback;

  10. Rollback complete.

  11. 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. select  * from dba_common_audit_trail  order by 6 desc;
复制代码
3.1.2 权限审计选项:
11g这里有默认一大堆,而且都不是focus,应该去掉:
  1. select * from DBA_PRIV_AUDIT_OPTS;
复制代码
   USER_NAMEPROXY_NAMEPRIVILEGESUCCESSFAILURE
以上代表:用户                      权限              成功行使/未遂行使                  

2. Specify audit options.
b.png

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

  2. audit drop any table by hr by session whenever successful;
复制代码
c.png
  1. audit drop any table by hr by access whenever successful;
复制代码
  1. select * from DBA_PRIV_AUDIT_OPTS;
复制代码
   USER_NAMEPROXY_NAMEPRIVILEGESUCCESSFAILURE
1HR SELECT ANY TABLEBY SESSIONNOT SET
2HR DROP ANY TABLEBY ACCESSNOT SET

另外权限审计同时一定也是语句级的审计选项:
  1. select  * from DBA_STMT_AUDIT_OPTS;
复制代码
   USER_NAMEPROXY_NAMEAUDIT_OPTIONSUCCESSFAILURE
1HR SELECT ANY TABLEBY SESSIONNOT SET
2HR DROP ANY TABLEBY ACCESSNOT SET

  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> grant select any table to hr;

  4. Grant succeeded.

  5. SQL> conn hr/oracle_4U
  6. Connected.
  7. SQL> select  salary  from employees where employee_id=100;

  8.     SALARY
  9. ----------
  10.      24000

  11. SQL> select  count(*) from sh.sales ;

  12.   COUNT(*)
  13. ----------
  14.     918843

  15. 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;
复制代码
d.png

e.png

语句审计,要从新的会话里,进行测试:
  1. SQL> conn hr/oracle_4U
  2. Connected.

  3. SQL> create table t05211_a ( a  number ) ;

  4. Table created.

  5. 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. Table dropped.

  3. 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. System altered.

  3. SQL> alter system set audit_trail='xml','EXTENDED' scope=spfile;

  4. System altered.

  5. 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:
g.png

在介绍细粒度审计的时候,先准备handler存储过程(可选):
  1. create table t05211_fga ( a varchar2(200))  tablespace tbsaudit;

  2. CREATE OR REPLACE PROCEDURE proc05211  ( object_schema VARCHAR2,
  3.                                                                                   object_name VARCHAR2,
  4.                                                                                   policy_name VARCHAR2 )
  5. AS

  6. begin
  7. insert into t05211_fga  values( to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||' '||
  8.                                                   sys_context('userenv','ip_address')||' '||
  9.                                                   sys_context('userenv','session_user')||' '||
  10.                                                   sys_context('userenv','authentication_type')||' '||
  11.                                                   sys_context('userenv','current_user')||' '||
  12.                                                   object_schema||' '||
  13.                                                   object_name||' '||
  14.                                                   policy_name);
  15. 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;
复制代码
a.png

下面进行测试:
  1. SQL> conn hr/oracle_4U
  2. Connected.
  3. SQL> select  salary, commission_pct from employees where department_id=20;

  4.     SALARY COMMISSION_PCT
  5. ---------- --------------
  6.      13000
  7.       6000

  8. SQL>
复制代码
  1. select  * from t05211_fga;
复制代码
   A
12018-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.     SALARY COMMISSION_PCT
  5. ---------- --------------
  6.      13000
  7.       6000

复制代码
  1. 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:42  HR DATABASE SYS HR EMPLOYEES POLICY1


c.png

[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. create or replace trigger trg05211
  3. after update of salary on hr.employees
  4. referencing new as new old as old
  5. for each row
  6. begin
  7.   if :old.salary != :new.salary
  8.   then
  9.       insert into t05211_value  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.                                                   :old.salary||' '||
  15.                                                   :new.salary);
  16.     end if;
  17. end;      
复制代码

辅助FGA,还记住了新值和旧值:
  1. SQL> show user
  2. USER is "HR"
  3. SQL> update employees set salary=salary*2 where employee_id=101;

  4. 1 row updated.

  5. SQL> commit;

  6. Commit complete.

  7. SQL>
复制代码

d.png










回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-12-19 12:05 , Processed in 0.195842 second(s), 27 queries .

快速回复 返回顶部 返回列表