|
1Z0-052第11章
1Z0-052共19章(上完16章),1Z0-053共21章(上完16章)和1Z0-063多租户部分共9章(上完0章)
总共上完全部49章中的32章
对象审计不能到列,不focus并且对SYS不审计。11g开始by session 和 by access只有审计轨迹上的"session record"微小差别:
- select * from Dba_Audit_Trail
- order by timestamp desc;
- select * from dba_obj_audit_opts
- where owner='HR';
-
- audit update on hr.employees ;
- audit alter on hr.employees whenever successful;
- audit insert on hr.employees whenever not successful;
- noaudit select on hr.employees ;
- audit select on hr.employees by session ;
- audit alter on hr.employees by session whenever not successful;
复制代码
关于xml格式的审计:
- select * from v$xml_audit_trail
- where db_user='HR'
- order by extended_timestamp desc;
- select * from Dba_Audit_Trail
- order by timestamp desc;
- select * from dba_common_audit_trail
- where db_user='HR'
- order by extended_timestamp desc ;
复制代码
- select * from dba_sys_privs p
- where p.GRANTEE='USER4';
-
- select * from dba_tab_privs p
- where p.GRANTEE='USER4';
-
- grant select any table to user4;
-
- grant create any table to user4;
-
- audit select any table by user4 by session;
-
- --error
- audit create any table by user4 by access;
-
-
- select * from dba_priv_audit_opts;
-
-
-
- select * from dba_common_audit_trail
- where db_user not in ('SYS','/')
- order by extended_timestamp desc ;
复制代码 DDL 性质的权限审计只能by access不能by session:
语句级审计都不能by session:
- select s.audit_option from dba_stmt_audit_opts s
- minus
- select p.privilege from dba_priv_audit_opts p;
- audit table by hr ;
复制代码
细粒度审计:
- create table audit1( a varchar2(2000)) tablespace tbsaudit;
- select * from dba_audit_policies;
- select * from dba_fga_audit_trail
- order by timestamp desc;
- select * from dba_common_audit_trail
- where db_user not in ('SYS','/')
- order by extended_timestamp desc ;
- create or replace procedure procfga (p_owner varchar2,
- p_table varchar2,
- p_policy varchar2)
- is
- begin
- insert into audit1
- values ( sys_context('userenv','os_user')||
- ' '||user||
- ' '||sys_context('userenv','session_user')||
- ' '||sys_context('userenv','current_user')||
- ' '||sysdate||
- ' '||sys_context('userenv','ip_address')||
- ' '||p_owner||
- ' '||p_table||
- ' '||p_policy );
- end;
- select * from audit1;
- 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=>'procfga',
- ENABLE=>true,
- STATEMENT_TYPES=>'SELECT,UPDATE',
- AUDIT_TRAIL=>dbms_fga.XML+dbms_fga.EXTENDED,
- AUDIT_COLUMN_OPTS=>dbms_fga.ANY_COLUMNS);
- end;
-
- select * from dba_audit_policies;
-
- select * from audit1;
-
复制代码
- select * from audit1;
- begin
- dbms_fga.drop_policy('HR','EMPLOYEES','POLICY1');
- 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=>'procfga',
- ENABLE=>true,
- STATEMENT_TYPES=>'SELECT,UPDATE',
- AUDIT_TRAIL=>dbms_fga.XML+dbms_fga.EXTENDED,
- AUDIT_COLUMN_OPTS=>dbms_fga.ALL_COLUMNS);
- end;
-
-
-
复制代码
基于值的审计, trigger是同一个事务,如果用户rollback,审计记录也会rollback:
- create table audit2( a varchar2(2000)) tablespace tbsaudit;
复制代码- create or replace trigger trigvalue
- 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 audit2
- values ( sys_context('userenv','os_user')||' '||user||' '||sysdate||' modified '||:new.salary||' '||:old.salary||' '||sys_context('userenv','ip_address') );
- end if;
- end;
复制代码
直接路径加载的对比实验:
- select tablespace_name from dba_tables t
- where t.TABLE_NAME like 'T_BIG%';
-
- select s.segment_name, bytes/1024/1024 from dba_segments s
- where s.SEGMENT_NAME like 'T_BIG%';
-
- alter table hr.t_big allocate extent ( size 2G );
- alter table hr.t_big2 allocate extent ( size 2G ) ;
- alter system flush buffer_cache;
- select n.name, se.value/1024/1024
- from v$session s, v$sesstat se ,v$statname n
- where s.sid=se.sid and se.statistic#=n.statistic#
- and ( n.name='redo size' or n.name like 'undo%' ) and s.terminal='pts/1';
复制代码
直接路径加载,最后检查索引的唯一性,导致索引不可用:
- sqlldr hr/oracle_4U control=ulcase4.ctl direct=y
复制代码
传统路径加载,只有7行,但是索引可用:
- sqlldr hr/oracle_4U control=ulcase4.ctl
复制代码- select i.INDEX_NAME, i.STATUS
- from dba_indexes i
- where i.TABLE_OWNER='HR' and i.TABLE_NAME='EMP';
复制代码 控制文件里有这样的语句,就只能用直接路径加载:
--
load data
infile 'ulcase6.dat'
replace
into table emp
sorted indexes (empix)
(empno position(1:4),
ename position(6:15),
job position(17:25),
mgr position(27:30) nullif mgr=blanks,
sal position(32:39) nullif sal=blanks,
comm position(41:48) nullif comm=blanks,
deptno position(50:51) nullif empno=blanks)
- [oracle@station26 demo]$ sqlldr hr/oracle_4U control=ulcase6.ctl
- SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 8 17:34:10 2017
- Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
- SQL*Loader-256: SORTED INDEXES option allowed only for direct path
复制代码 日志中会出现:
SQL*Loader: Release 12.1.0.2.0 - Production on Fri Dec 8 17:34:34 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: ulcase6.ctl
Data File: ulcase6.dat
Bad File: ulcase6.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table EMP, loaded from every logical record.
Insert option in effect for this table: REPLACE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO 1:4 4 CHARACTER
ENAME 6:15 10 CHARACTER
JOB 17:25 9 CHARACTER
MGR 27:30 4 CHARACTER
NULL if MGR = BLANKS
SAL 32:39 8 CHARACTER
NULL if SAL = BLANKS
COMM 41:48 8 CHARACTER
NULL if COMM = BLANKS
DEPTNO 50:51 2 CHARACTER
NULL if EMPNO = BLANKS
The following index(es) on table EMP were processed:
index HR.EMPIX loaded successfully with 7 keys
Table EMP:
1,0-1 Top
银行对账单的例子,第一步建表:
- create table hr.t_bank_invoice (
- account_number number ,
- sub_number number ,
- transac_date date ,
- transac_address varchar2(600),
- transac_comment varchar2(600),
- currency varchar2(100),
- remit varchar2(100),
- in_amount number(20,2) ,
- out_amount number(20,2),
- balance number(20,2),
- opposite_number number ,
- opposite_username varchar2(100),
- all_comment varchar2(2000)) ;
复制代码
工行:
- LOAD DATA
- characterset ZHS16GBK
- INFILE 'icbc.dat'
- DISCARDFILE 'icbc.dis'
- append
- INTO table t_bank_invoice
- when (5) ='^'
- FIELDS TERMINATED BY '^'
- TRAILING NULLCOLS
- (
- account_number "1402029101000999999",
- sub_number "00000",
- transac_date position(6:15) DATE(10) "YYYY-MM-DD" TERMINATED BY WHITESPACE,
- transac_address TERMINATED BY WHITESPACE
- "substr(:transac_address,2)",
- transac_comment TERMINATED BY WHITESPACE
- "substr(:transac_comment,2)",
- currency TERMINATED BY WHITESPACE
- "substr(:currency,2)",
- remit TERMINATED BY WHITESPACE
- "substr(:remit,2)",
- in_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:in_amount,2),'999,999,999,999.00')",
- out_amount TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:out_amount,2),'999,999,999,999.00')",
- balance TERMINATED BY WHITESPACE
- "TO_NUMBER(substr(:balance,2),'999,999,999,999.00')",
- all_comment TERMINATED BY WHITESPACE
- "substr(:all_comment,2)"
- )
复制代码
建行:
- LOAD DATA
- characterset ZHS16GBK
- INFILE 'ccb.dat'
- DISCARDFILE 'ccb.dis'
- append
- INTO table t_bank_invoice
- TRAILING NULLCOLS
- (
- account_number "4367421823250099999",
- sub_number "00000",
- transac_date position(1:8) DATE(8) "YYYYMMDD" TERMINATED BY ',',
- transac_address position(10) char TERMINATED BY ',',
- out_amount decimal external TERMINATED BY ',' nullif out_amount=blanks,
- in_amount decimal external TERMINATED BY ',' nullif in_amount=blanks ,
- balance decimal external TERMINATED BY ',' nullif balance=blanks,
- opposite_number char TERMINATED BY ',',
- opposite_username char TERMINATED BY ',',
- currency char TERMINATED BY ',',
- transac_comment char TERMINATED BY whitespace
- )
复制代码
|
|