- 论坛徽章:
- 0
|
How to check objects changed/accessed?
Sometimes we need to track objects, determine if objects be changed.
Timestamp is not always a good track to identify objects changed.
@>create or replace function f_test(p_str varchar2)
2 return varchar2
3 as
4 v_str varchar2(32767);
5 begin
6 v_str:=upper(p_str);
7 return v_str;
8 end;
9 /
Function created.
@>select CREATED,LAST_DDL_TIME from dba_objects where OBJECT_NAME='F_TEST';
CREATED LAST_DDL_TIME
------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:43:38
@>select CTIME,MTIME,STIME from obj$ where NAME='F_TEST';
CTIME MTIME STIME
------------------- ------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:43:38 2007-12-21 23:43:38
@>alter function f_test compile;
Function altered.
@>select CREATED,LAST_DDL_TIME from dba_objects where OBJECT_NAME='F_TEST';
CREATED LAST_DDL_TIME
------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:48:19
@>select CTIME,MTIME,STIME from obj$ where NAME='F_TEST';
CTIME MTIME STIME
------------------- ------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:48:19 2007-12-21 23:43:38
@>create or replace function f_test(p_str varchar2)
2 return varchar2
3 as
4 v_str varchar2(32767);
5 begin
6 v_str:=upper(p_str);
7 return v_str;
8 end;
9 /
Function created.
@>
@>select CREATED,LAST_DDL_TIME from dba_objects where OBJECT_NAME='F_TEST';
CREATED LAST_DDL_TIME
------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:49:53
@>select CTIME,MTIME,STIME from obj$ where NAME='F_TEST';
CTIME MTIME STIME
------------------- ------------------- -------------------
2007-12-21 23:43:38 2007-12-21 23:49:53 2007-12-21 23:49:53
DBA_OBJECTS.LAST_DDL_TIME refers to obj$.MTIME.
Even though this function contents doesn’t change, but the timestamp all changed.
@>select dbms_utility.get_hash_value(dbms_metadata.get_ddl('FUNCTION','F_TEST',user),3,1073741824) from dual;
DBMS_UTILITY.GET_HASH_VALUE(DBMS_METADATA.GET_DDL('FUNCTION','F_TEST',USER),3,1073741824)
-----------------------------------------------------------------------------------------
142768459
@>create or replace function f_test(p_str varchar2)
2 return varchar2
3 as
4 v_str varchar2(32767);
5 begin
6 v_str:=upper(p_str);
7 return v_str;
8 end;
9 /
Function created.
@>select dbms_utility.get_hash_value(dbms_metadata.get_ddl('FUNCTION','F_TEST',user),3,1073741824) from dual;
DBMS_UTILITY.GET_HASH_VALUE(DBMS_METADATA.GET_DDL('FUNCTION','F_TEST',USER),3,1073741824)
-----------------------------------------------------------------------------------------
142768459
@>
@>create or replace function f_test(P_str varchar2)
2 return varchar2
3 as
4 v_str varchar2(32767);
5 begin
6 v_str:=upper(p_str);
7 return v_str;
8 end;
9 /
Function created.
@>select dbms_utility.get_hash_value(dbms_metadata.get_ddl('FUNCTION','F_TEST',user),3,1073741824) from dual;
DBMS_UTILITY.GET_HASH_VALUE(DBMS_METADATA.GET_DDL('FUNCTION','F_TEST',USER),3,1073741824)
-----------------------------------------------------------------------------------------
629865228
If use hash value to determine objects changed, we must store hash value firstly, then we have base value to compare. We can also use dbms_obfuscation_toolkit to compute hash value.
Audit is important to track objects changed or accessed. Since Oracle 10g R2, if you set audit_trail=DB_EXTENDED, triggered SQL statement be recorded in aud$.SQLTEXT, but before Oracle 10g, aud$ table doesn’t record the SQL statements, for ddl operation, you can use trigger to accomplish it (see Appendix A), for select statements, you can use fine grained auditing (DBMS_FGA).
10g
sys@TEST>@getvv audit
NUM NAME ORD VALUE ISDEFAULT
---------- -------------------- -------------- -------------------- --------------------
938 audit_trail 1 DB FALSE
audit_trail 2 OS FALSE
audit_trail 3 NONE FALSE
audit_trail 4 TRUE FALSE
audit_trail 5 FALSE FALSE
audit_trail 6 DB_EXTENDED FALSE
audit_trail 7 XML FALSE
audit_trail 8 EXTENDED FALSE
Appendix A
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- refer to Application Developer’s Guide - Fundamentals
-- logon as sys
whenever sqlerror exit failure;
create table audit_trail
(USERNAME VARCHAR2(30),
MACHINE VARCHAR2(64),
CLIENT_IP VARCHAR2(15),
OS_USER VARCHAR2(30),
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_TYPE VARCHAR2(1 ,
ACTION VARCHAR2(30),
SQL_TEXT VARCHAR2(4000),
TIME DATE);
create or replace trigger audit_ddl_trigger
after ddl on database
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
for i in 1..n loop
stmt := stmt || sql_text(i);
end loop;
insert into audit_trail(username,machine,client_ip,os_user,owner,object_name,object_type,action,sql_text,time)
values(ora_login_user,SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','IP_ADDRESS'),
SYS_CONTEXT('USERENV','OS_USER'),ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,
ora_sysevent,stmt,sysdate);
exception
when others then
NULL;
end;
/ |
|