课程第17次

secoug · 发表于 2019-10-30 20:21:02

select * from dba_obj_audit_opts;
select sao.user_name, sao.audit_option, sao.success,sao.failure
from dba_stmt_audit_opts sao
minus
select pao.user_name, pao.privilege, pao.success, pao.failure
from dba_priv_audit_opts pao;

复制代码

select * from dba_audit_trail at
order by at.timestamp desc;
noaudit create session;
select * from dba_views v
where v.owner='SYS' and v.view_name='DBA_AUDIT_TRAIL';
select * from dba_tables t
where t.owner='SYS' and t.table_name='AUD
;
create tablespace tbsaudit datafile size 20M autoextend on;
alter table aud$ move tablespace tbsaudit;
truncate table aud$;
audit create session by ops$oracle by access whenever successful;
select pao.user_name, pao.privilege, pao.success, pao.failure
from dba_priv_audit_opts pao;

复制代码

grant sysdba to hr;
select u.password
from user$ u where u.name='HR';
----
select p."SPID"
from v$process p , v$session s
where p."ADDR"=s."PADDR" and
s."TERMINAL"='pts/1';

复制代码

----

select sao.user_name, sao.audit_option, sao.success,sao.failure
from dba_stmt_audit_opts sao
minus
select pao.user_name, pao.privilege, pao.success, pao.failure
from dba_priv_audit_opts pao;
audit table by hr by access;
-------------------
select * from dba_audit_trail at
order by at.timestamp desc;
select * from dba_common_audit_trail cat
order by cat.extended_timestamp desc;
alter system set audit_trail='db','extended' scope=spfile;
grant select any table to hr;
audit select any table by hr by session;
select * from v$xml_audit_trail;
alter system set audit_trail='xml','extended' scope=spfile;
----
grant update (email) on hr.employees to ops$oracle;
select * from dba_col_privs;

复制代码

create or replace directory fgalog as '/home/oracle/FGALOG';
-----
CREATE OR REPLACE PROCEDURE PROC_FGA ( object_schema VARCHAR2, object_name VARCHAR2, policy_name
VARCHAR2 ) AS
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen(location => 'FGALOG',filename => 'FGA.log',open_mode => 'w');
utl_file.put_line(file => v_filehandle,buffer =>object_schema );
utl_file.put_line(file => v_filehandle,buffer =>object_name);
utl_file.put_line(file => v_filehandle,buffer =>policy_name);
utl_file.put_line(file => v_filehandle,buffer =>sys_context('userenv','ip_address'));
utl_file.put_line(file => v_filehandle,buffer => sys_context('userenv','session_user'));
utl_file.put_line(file => v_filehandle,buffer =>sys_context('userenv','current_user'));
utl_file.put_line(file => v_filehandle,buffer =>to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS'));
utl_file.put_line(file => v_filehandle,buffer =>sys_context('userenv','authentication_type'));
utl_file.new_line( v_filehandle);
utl_file.fclose( v_filehandle);
end;
begin
PROC_FGA ('HR','EMPLOYEES', 'POLICY2');
end;
----
select * from dba_audit_policies;
-----
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 => 'PROC_FGA',
statement_types => 'SELECT,UPDATE',
audit_trail => dbms_fga.XML+dbms_fga.EXTENDED,
audit_column_opts => dbms_fga.ANY_COLUMNS);
end;
--------------
select * from dba_common_audit_trail cat
order by cat.extended_timestamp desc;

复制代码

课程第17次

浏览过的版块