Yaping's Weblog

September 2, 2008

How to check objects changed/accessed?

Filed under: Oracle — Yaping @ 2:28 am
Tags:

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(18),
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;
/

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: