Dangerous careless trigger declaration

Just few days before going to Christmas holidays, I have run into one pretty interesting and dangerous situation which was caused because careless trigger definition. The beauty of this example is that it is very hard to see real problem until you run in it. I had a luck that, regardless all happened on production database, involved table were part of source data for warehouse part, which is updated once a day in a batch job, so nothing luckily went wrong.

The problem arise when I have imported (impdp) one table from original schema to other one (just to see some structure and data). Database version as well as OS is totally unimportant for this issue-so I may say this danger is present in any database version across all platforms. Just to mention that original schema is TAB and table was imported to TAB_DWH schema. It is also important to mention that in original schema (TAB) the same table (OSO_ATOL) exists with a lot of data.

Here is log from that import:

F:\>imp '/ as sysdba' FILE='hcpro_4_cet.dmp' fromuser=tab touser=tab_dwh log=imp_oso_atol.log tables=('OSO_ATOL') RECORDLENGTH=65535 BUFFER=50000000

Import: Release 10.2.0.4.0 - Production on Pet Pro 16 15:57:03 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path
import done in EE8MSWIN1250 character set and AL16UTF16 NCHAR character set
. importing TAB's objects into TAB_DWH
. . importing table                     "OSO_ATOL"          0 rows imported
IMP-00041: Warning: object created with compilation warnings
 "CREATE TRIGGER "TAB_DWH".TGBI_OSO_ATOL_KRE"
 "BEFORE INSERT"
 "ON TAB.OSO_ATOL "
 "REFERENCING NEW AS NEW OLD AS OLD"
 "FOR EACH ROW"
 "begin"
 "  IF :NEW.UNIID IS NULL THEN "
 "    FOR c1 IN (SELECT OSO_ATOL_SEQ.NEXTVAL next_val FROM dual) LOOP"
 "      :NEW.UNIID :=  c1.next_val;"
 "      IF :new.korkre is NULL THEN"
 "        :new.korkre:=user;"
 "        :new.datkre:=sysdate;"
 "      END IF;"
 "    END LOOP;       "
 "  END IF;"
 "end;"
About to enable constraints...
Import terminated successfully with warnings.

As you see table had no rows and import was done with sys user and finished with some warnings in trigger creation. Let us focus on that warned trigger and it's code in original declaration:

CREATE OR REPLACE TRIGGER TGBI_OSO_ATOL_KRE
BEFORE INSERT
ON TAB.OSO_ATOL
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  IF :NEW.UNIID IS NULL THEN
    FOR c1 IN (SELECT OSO_ATOL_SEQ.NEXTVAL next_val FROM dual) LOOP
      :NEW.UNIID :=  c1.next_val;
      IF :new.korkre is NULL THEN
        :new.korkre:=user;
        :new.datkre:=sysdate;
      END IF;
    END LOOP;
  END IF;
END;

This trigger generate ID (PK) value with one sequence and some information when record is created ... what is in this context irrelevant, but just want to explain that there is nothing rocket science in it.

When I look in TAB_DWH.OSO_ATOL table definition, which were just imported, I saw that there is no trigger there!?? In first moment I was surprised, but returning mine focus on mentioned import log, I saw interesting part:

...
BEFORE INSERT
ON TAB.OSO_ATOL
REFERENCING NEW AS NEW OLD AS OLD
...

So this looks like trigger should be created in TAB schema ... original OSO_ATOL table ... but I was importing OSO_ATOL in TAB_DWH schema-WTH ... ?!?

After checking TAB.OSO_ATOL table here is what I saw (picture taken from Toad):

As picture shows, beside original three triggers (original TAB owner) additional three triggers were created, two with no errors and one which was invalid!!
So in this moment:

• OSO_ATOL was unable to get any new data because TGBI_OSO_ATOL_KRE trigger was invalid
• If update occur, the same thing will fire twice-once for TAB and once for TAB_DWH triggers, which were the same

Pretty nasty and unpleasant situation indeed!

The Solution

The solution is very easy-define triggers without table owner. Simple as that. Here is correct code of same trigger:

CREATE OR REPLACE TRIGGER TGBI_OSO_ATOL_KRE
BEFORE INSERT
ON OSO_ATOL
REFERENCING NEW AS NEW OLD AS OLD
BEGIN
  IF :NEW.UNIID IS NULL THEN
    FOR c1 IN (SELECT OSO_ATOL_SEQ.NEXTVAL next_val FROM dual) LOOP
      :NEW.UNIID :=  c1.next_val;
      IF :new.korkre is NULL THEN
        :new.korkre:=user;
        :new.datkre:=sysdate;
      END IF;
    END LOOP;
  END IF;
END;

As you see table owner is avoided!

The End

First let me be honest and tell that if no warnings in import were shown error will be raised when it will be too late. But I had "luck" because trigger was invalid because sequence has no declared PUBLIC synonym, so in TAB_DWH schema OSO_ATOL_SEQ was unknown object.

Regardless this problem may be avoided if impdp was done with TAB_DWH user (which doesn't have enough privileges to create anything in TAB schema), ask yourself which user you use for ordinary impdp-sys or system!?

I think that there are many developers and fewer DBAs who are not familiar with mentioned problem, so introducing people around you is something that might help in protecting production database.

Hope this helps someone.

Cheers!

P.S.

Happy New 2012 again. Hope it would not be worse then 2011!
:-)