Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0034833Openbravo ERPA. Platformpublic2016-12-30 11:582017-03-15 20:19
inigosanchez 
inigosanchez 
highminorhave not tried
closedfixed 
5
 
3.0PR17Q2 
alostale
Core
No
0034833: audit triggers can have naming clashes
Triggers created for audit trail can have clashes in naming, causing incorrect auditing in these cases.

This occurs because of the way name is generated [1], which doesn't validate there is no function nor trigger with the same name.

[1] https://code.openbravo.com/erp/devel/pi/file/2a03b4d16b5d/src-db/database/model/postscript-PostgreSql.sql#l421 [^]
1. Log in as System Admin in an activated instance.
2. Open Table window and mark as audited OBKMO_Widget_Class_Menu and OBKMO_Widget_Class_Menu_Trl tables
3. Execute Update Audit Trail Infrastructure process
4. Check two triggers are created based on same function:


# select * from user_triggers where trigger_name like 'au_%';
         table_name | tablespace_name | trigger_name | status
-----------------------------+-----------------+--------------------------------+---------
 OBKMO_WIDGET_CLASS_MENU | PUBLIC | au_obkmo_widget_class_menu_trg | ENABLED
 OBKMO_WIDGET_CLASS_MENU_TRL | PUBLIC | au_obkmo_widget_class_menu_trg | ENABLED
(2 rows)


  ERROR: this is incorrect, each trigger should have its own function
5. Execute Update Audit Trail Infrastructure process again
  ERROR: cannot drop function au_obkmo_widget_class_menu_trg() because other objects depend on it
  This occurs because function is tried to be dropped after dropping 1st trigger, but keeping 2nd one.
After generating trigger name, validate it doesn't exist before creating, if it exists generate a different name till it doesn't exist.
No tags attached.
png errorAuditTrail.png (206,939) 2016-12-30 11:58
https://issues.openbravo.com/file_download.php?file_id=10228&type=bug
png
Issue History
2016-12-30 11:58inigosanchezNew Issue
2016-12-30 11:58inigosanchezAssigned To => platform
2016-12-30 11:58inigosanchezFile Added: errorAuditTrail.png
2016-12-30 11:58inigosanchezModules => Core
2016-12-30 11:58inigosanchezTriggers an Emergency Pack => No
2016-12-30 12:18inigosanchezSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=14137#r14137
2017-01-10 11:33alostaleSummaryUpdate Audit Trail Infrastructure process doesn't works properly under some circumstances => audit trigger can have naming clashes
2017-01-10 11:33alostaleDescription Updatedbug_revision_view_page.php?rev_id=14230#r14230
2017-01-10 11:33alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=14231#r14231
2017-01-10 11:33alostaleProposed Solution updated
2017-01-10 11:33alostaleSummaryaudit trigger can have naming clashes => audit triggers can have naming clashes
2017-01-10 11:34alostaleStatusnew => acknowledged
2017-01-10 11:43alostalePrioritylow => high
2017-02-02 15:03inigosanchezStatusacknowledged => scheduled
2017-02-02 15:03inigosanchezAssigned Toplatform => inigosanchez
2017-02-06 12:52inigosanchezReview Assigned To => alostale
2017-02-06 12:57hgbotCheckin
2017-02-06 12:57hgbotNote Added: 0094080
2017-02-06 12:57hgbotStatusscheduled => resolved
2017-02-06 12:57hgbotResolutionopen => fixed
2017-02-06 12:57hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/f730600d5079cc23e1e5e8254ec5de3949a8f6a4 [^]
2017-02-10 09:02alostaleNote Added: 0094211
2017-02-10 09:02alostaleStatusresolved => closed
2017-02-10 09:02alostaleFixed in Version => 3.0PR17Q2
2017-03-15 20:19hudsonbotCheckin
2017-03-15 20:19hudsonbotNote Added: 0095161

Notes
(0094080)
hgbot   
2017-02-06 12:57   
Repository: erp/devel/pi
Changeset: f730600d5079cc23e1e5e8254ec5de3949a8f6a4
Author: Inigo Sanchez <inigo.sanchez <at> openbravo.com>
Date: Mon Feb 06 12:56:44 2017 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/f730600d5079cc23e1e5e8254ec5de3949a8f6a4 [^]

Fixed issue 34833: audit triggers can have naming clashes

The problem occurs because of the way audit trigger name is generated,
which doesn't validate there is no function nor trigger with the same name.

In order to fix this problem a validation is added. Now, this problem has
been managed properly.

---
M src-db/database/model/postscript-Oracle.sql
M src-db/database/model/postscript-PostgreSql.sql
---
(0094211)
alostale   
2017-02-10 09:02   
code reviewed

tested ora + pg
(0095161)
hudsonbot   
2017-03-15 20:19   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/54e102bef53e [^]
Maturity status: Test