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 ? |
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.
