（52-26）第44次：2016-02-02星期二

botang · 发表于 2016-2-3 11:03:22

本帖最后由 botang 于 2016-2-3 11:06 编辑

（52-26）上完1Z0-052第11章

2016-02-02fga.sql：

select * from dba_audit_trail t
where t.username not in ('SYSMAN','DBSNMP')
order by t.timestamp desc ;
select * from dba_obj_audit_opts;
select * from dba_priv_audit_opts p order by p.privilege;
select * from dba_stmt_audit_opts;
audit select any table by hr ;
grant select any table to hr ;
alter user sh identified by oracle_4U account unlock;
grant select any table to sh ;
noaudit select any table by hr;
audit select any table by access;
noaudit select any table;
noaudit drop any table;
audit drop any table by hr by access;
create table hr.t05211_hr( a number ) ;
insert into hr.t05211_hr values (1) ;
commit;
create table sh.t05211_sh( a number ) ;
insert into sh.t05211_sh values(1);
commit;
grant drop any table to hr;
---
select * from dba_stmt_audit_opts
minus
select * from dba_priv_audit_opts ;
---
audit table by hr ;
----
select * from dba_policies; FGAC
select * from dba_audit_policies ;FGA
---
create table tfga( a varchar2(200)) ;
alter table tfga modify ( a varchar2(2000));
create or replace procedure procfga(p_schema varchar2,
p_table varchar2,
p_policy varchar2)
is
begin
insert into tfga values('The user '||sys_context('userenv', 'session_user')||' (OS username is '||
sys_context('userenv', 'os_user')||' )'||sys_context('userenv', 'authentication_type')||
' at time '||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
' at ip address '||sys_context('userenv', 'ip_address')||' had bad operation on '||p_schema||'.'||
p_table||'.'||'Policy is '||p_policy||'.' );
end;
select * from user_errors;
select * from user_objects o where o.object_name='PROCFGA';
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',statement_types => 'SELECT,UPDATE',
audit_trail => dbms_fga.DB+dbms_fga.EXTENDED,
audit_column_opts =>dbms_fga.ALL_COLUMNS);
end;
---
select * from dba_fga_audit_trail t
order by t.timestamp desc ;
select * from tfga;

复制代码

2016-02-02value.sql：

create or replace trigger hr.trg1
after update of a on hr.t05211_check
referencing new as new old as old
for each row
begin
if :old.a >= :new.a
then
raise_application_error(-20901,'Must be bigger.');
end if;
end;
select * from dba_triggers t where t.owner='HR' and t.trigger_name='TRG1';
---
create table tvalue( a varchar2(2000)) ;
create or replace trigger trgvalue
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 tvalue values('The user '||sys_context('userenv', 'session_user')||' The current user '||
sys_context('userenv', 'current_user')||' (OS username is '||
sys_context('userenv', 'os_user')||' )'||sys_context('userenv', 'authentication_type')||
' at time '||to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')||
' at ip address '||sys_context('userenv', 'ip_address')||' had bad operation on '||:new.salary||'.'
||:old.salary );
end if;
end;
select * from dba_triggers t where t.owner='SYS' and t.trigger_name='TRGVALUE';
select * from tvalue;

复制代码

direct.sql：

create tablespace tbs05217 datafile '/home/oracle/tbs05217.dbf' size 6G;
alter table hr.tc move tablespace tbs05217;
select s.segment_name , s.bytes/1024/1024
from dba_segments s
where s.segment_name in ('TC','TD') ;

复制代码