The Oracle (tm) Users' Co-Operative FAQ

How can I track structural changes to my database ?


Author's name: Norman Dunbar

Author's Email: Oracle@bountifulsolutions.co.uk

Date written: 10/09/2002

Oracle version(s): 8.1.7.4

If someone makes any structural changes to a database, how can I track which changes were made, and who made the changes ?

Back to index of questions


If you are worried about people doing things to your database and you not knowing, and you are running at 8i then you can use a DDL trigger and a logging table to keep a log of all DDL changes, who made them and when. The DDL trigger can be amended so that it logs only certain DDL changes - you needn't worry about logging everything.

You will need a user, created somewhere so that no-one else has access - unless the DBA wishes them to have it, and a trigger owned by SYS which does the logging into a table in your user. The following shows how it all hangs together. This was taken from a session on an Oracle 8.1.7.4 database.

SQL> connect system@cmdb
Enter password: ******
Connected.

SQL> create tablespace logging
  2  datafile '/data2/CMDB/logging.dbf' size 201m
  3  extent management local
  4  uniform size 64k;
Tablespace created.

SQL> create user logging identified by something_daft
  2  default tablespace logging
  3  temporary tablespace temp
  4  quota 0 on system
  5  quota 200m on logging;
User created.

You will notice that I have not given the logging user the ability to connect to the database. This is not a problem as I'm simply using this user to keep my objects out of the SYS user and the SYSTEM tablespace. There is nothing stopping you from creating the logging table so that SYS owns it, but you will not be able to export it if you do. Having a separate user at least gives you that option.

SQL> create table logging.ddl_log
  2  ( user_name        varchar2(30),
  3     ddl_date         date,
  4     ddl_type         varchar2(30),
  5     object_type      varchar2(18),
  6     owner            varchar2(30),
  7     object_name      varchar2(128)
  8  ) tablespace logging;
Table created.

Next we need to switch over to the SYS user to create our trigger. The trigger will write all its data into the logging user's ddl_log table.

SQL> connect sys@cmdb
Enter password: ********
Connected.

SQL> create or replace trigger DDLTrigger
  2  after DDL on database
  3  begin
  4    insert into logging.ddl_log 
  5    values (ora_login_user,
  6            sysdate,
  7            ora_sysevent,
  8            ora_dict_obj_type,
  9            ora_dict_obj_owner,
 10            ora_dict_obj_name);
 11  exception
 12    when others then
 13      NULL;
 14  end;
 15  /
Trigger created.

And that is all there is to it. The following is an example where some DDL changes have been made in this test database, and the results of checking the contents of the logging.ddl_log table :

SQL> connect cmdb@cmdb
Enter password: ****
Connected.

SQL> alter table employee nologging;
Table altered.

SQL> grant select on location to public;
Grant succeeded.

SQL> alter user cmdb identified by new_password;
User altered.

SQL> create table test (a number);
Table created.

SQL> alter table test add constraint pk_test primary key (a);
Table altered.

SQL> insert into  test (a) values (1);
1 row created.

SQL> commit;
Commit complete.

SQL> truncate table test drop storage;
Table truncated.

SQL> drop table test cascade constraints;
Table dropped.


SQL> connect system@cmdb
Enter password: ******
Connected.

SQL> alter user cmdb identified by cmdb;
User altered.
  
SQL> column user_name format a10;
SQL> column ddl_date format a20;
SQL> column owner format a10
SQL> column object_name format a20
SQL> column object_type format a20
SQL> column ddl_type format a20
SQL> set lines 150
SQL> set pages 50
SQL>   
SQL> select * from logging.ddl_log;

USER_NAME  DDL_DATE             DDL_TYPE             OBJECT_TYPE          OWNER      OBJECT_NAME
---------- -------------------- -------------------- -------------------- ---------- ---------------
CMDB       10/09/2002 01:13:57  ALTER                TABLE                CMDB       EMPLOYEE
CMDB       10/09/2002 01:14:30  GRANT                OBJECT PRIVILEGE     CMDB       LOCATION
CMDB       10/09/2002 01:15:37  ALTER                USER                            CMDB
CMDB       10/09/2002 01:16:08  CREATE               TABLE                CMDB       TEST
CMDB       10/09/2002 01:16:23  CREATE               INDEX                CMDB       PK_TEST
CMDB       10/09/2002 01:16:23  ALTER                TABLE                CMDB       TEST
CMDB       10/09/2002 01:17:16  TRUNCATE             TABLE                CMDB       TEST
CMDB       10/09/2002 01:17:33  DROP                 TABLE                CMDB       TEST
SYSTEM     10/09/2002 01:20:54  ALTER                USER                            CMDB

9 rows selected.

From the logging.ddl_log table we can see that user CMDB made a change to the EMPLOYEE table with ALTER TABLE. CMDB then granted some privileges on the LOCATION object - which could be a view, a table, a trigger etc, we cannot tell from the logging details unfortunately, nor can we tell who was granted the privileges - whatever ones thay may have been. And so on.

Notice that when CMDB created a primary key constraint using ALTER TABLE, this was logged first as a CREATE INDEX PK_TEST followed by an ALTER TABLE TEST. This is because the index was created in the background to support the constraint. Had there been an existing index which was suitable, the CREATE INDEX PK_TEST would not be seen.

Things to remember

You cannot, unfortunately, get the OSUSER of the person who made the changes to an object in the database. This is a problem if you have everybody logging in as user 'application' for example, you will only see that user 'application' made the change - so it will still be difficult to trace it back to a specific user. There is an option to obtain the IP address of the client PC, but that field is not available in the DDL Trigger unfortunately.

The logging user has only 200 Mb of space to play with - if you are logging all changes on a fast changing database, you'll need to clear out the dross regularly to stop the tablespace filling up. You can use something like the following command to remove unwanted data :

SQL> delete from logging.ddl_log where ddl_date
commit;
Commit complete.

In my example, I simply used today's date as I don't have old data in there, you would use something like 'sysdate - 30' to delete the details of chnages made prior to 30 days ago.

What will happen if the tablespace is full up, and someone tries to make some DDL changes ?

SQL> alter user  logging quota 0 on logging;
User altered.

At this point, the logging table has not used up all of it's first allocated extent, so I'll run a few 'insert into .. select * from ...' to fill the extent up. First though, I'll keep the date and time for now - I'll need it later.

SQL> select sysdate from dual;

SYSDATE
-------------------
10/09/2002 14:05:04


SQL> insert into logging.ddl_log select * from logging.ddl_log;
insert into logging.ddl_log

( repeat until the following error occurs )

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LOGGING'

Then a few more DDL changes ...

SQL> alter user logging identified by logging;
User altered.

SQL> r
  1* alter user logging identified by logging
User altered.

SQL> r
  1* alter user logging identified by logging
User altered.

Then find out what rows have been added to the logging table after the date & time when I filled up the table :

SQL> select * from logging.ddl_log
  2  where ddl_date > to_date('10/09/2002 14:05:04','DD/MM/YYYY HH24:MI:SS');
no rows selected

So, when the tablespace fills up, no DDL changes are logged to the logging table, but they do get carried out. However, if I change the trigger to remove the exception handling, the following will happen :

SQL> connect sys@cmdb
Enter password: ********
Connected.

SQL> create or replace trigger DDLTrigger
  2  after DDL on database
  3  begin
  4    insert into logging.ddl_log 
  5    values (ora_login_user,
  6            sysdate,
  7            ora_sysevent,
  8            ora_dict_obj_type,
  9            ora_dict_obj_owner,
 10            ora_dict_obj_name);
 11  end;
 12  /
Trigger created.

SQL> alter user logging identified by another_password;
alter user logging identified by another_password
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'LOGGING'
ORA-06512: at line 2

SQL> grant create session to logging;
grant create session to logging
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01536: space quota exceeded for tablespace 'LOGGING'
ORA-06512: at line 2

SQL> connect logging/another_password@cmdb
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

So, without the exception handling, the users get to see an error message and the DDL is not carried out. Always make sure you have exception handling on your triggers to avoid causing your users problems.


Further reading:

The article that first pointed me in the direction of DDL triggers can be found here at DBAZine.com.

More information, including a table of what triggers and events are possible, what parameters can be used for each type of trigger, and other useful information can be found in the Oracle 8i Application Developers Guide, chapter 13 - Working with system events. More importantly, it also has information on which DDL commands do not get logged.


Back to top

Back to index of questions