Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0005542
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] C. Securitymajoralways2008-10-15 18:522008-12-31 12:13
ReporternataliaperuView Statuspublic 
Assigned Toiciordia 
PriorityhighResolutionno change requiredFixed in Version
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0005542: Foreign keys must be included in CREATEDBY and UPDATEDBY fields

DescriptionCREATEDBY and UPDATEDBY columns within all Openbravo ERP tables are used for auditing purposes. These columns are filled with the ID of the logged user that creates or updates the record. This user has to be recorded in AD_USER table. The problem is that in many (IF NOT ALL) tables do not have as foreign key the AD_USER_ID column of the AD_USER table for CREATEDBY and UPDATEDBY columns. As a result of this, one can delete one record from AD_USER table even if this user has created or updated records. It is known that this has caused problems with the upgrader from Openbravo ERP MP4 to MP5 where some users were deleted (those who had AD_CLIENT_ID = 0). Since no foreign key exists in CREATEDBY and UPDATEDBY fields, for example in C_INVOICE table, there are records in C_INVOICE table within users that do not exist in the database.
Steps To Reproduce1) Logged as Client Admin, create a new User and assign him a Role.
2) Log out and log in with this user and create a new transaction, for instance, an invoice.
3) Log out and log in again as Client Admin and deleted the previously created User.

You are able to do it since CREATEDBY and UPDATEDBY columns have not AD_USER_ID as a foreign key.
Proposed SolutionThe solution for this issue would be add the following lines in all the XML tables withing /src-db/database/model/tables (changing _CINVOICE by the corresponging table name):
<foreign-key foreignTable="AD_USER" name="ADUSER_CBY_CINVOICE">
  <reference local="CREATEDBY" foreign="AD_USER_ID"/>
</foreign-key>
<foreign-key foreignTable="AD_USER" name="ADUSER_UBY_CINVOICE">
  <reference local="UPDATEDBY" foreign="AD_USER_ID"/>
</foreign-key>

Nevertheless, a decision has to be taken since this could give a lot of errors during upgrades if any current Openbravo live implementation has inserted records in any table with an user that does not exist anymore in AD_USER table.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on backport 0005584 closediciordia Foreign keys must be included in CREATEDBY and UPDATEDBY fields 
depends on backport 0005585 closediciordia Foreign keys must be included in CREATEDBY and UPDATEDBY fields 

-  Notes
(0010721)
rafaroda (developer)
2008-11-26 16:50
edited on: 2008-11-26 17:07

 The following SELECT in Oracle shows the tables that miss from this foreign key.

select *
  from user_tables u
  where not exists (select 1 from user_constraints t, user_cons_columns cl
          where t.table_name = u.table_name
          and r_constraint_name = 'AD_USER_KEY'
          and cl.constraint_name = t.constraint_name
          and cl.column_name ='UPDATEDBY')
    or not exists (select 1 from user_constraints t, user_cons_columns cl
          where t.table_name = u.table_name
          and r_constraint_name = 'AD_USER_KEY'
          and cl.constraint_name = t.constraint_name
          and cl.column_name ='CREATEDBY');
(0011783)
iciordia (manager)
2008-12-31 12:09

 Natalia and Rafa,

foreign keys in audit info result in a heavy overload and bad performance. Since the user is not allowed to edit this information (the system is in charge of that) these foreign keys are not needed. If the upgrader performs an action that creates any data inconsitency then the fix needs to be applied to the upgrader, but these constraints MUST NOT be created.

Ismael
(0011784)
iciordia (manager)
2008-12-31 12:13

 Foreign keys in audit info result in a heavy overload and bad performance. Since the user is not allowed to edit this information (the system is in charge of that) these foreign keys are not needed. If the upgrader performs an action that creates any data inconsitency then the fix needs to be applied to the upgrader, but these constraints MUST NOT be created.

- Issue History
Date Modified Username Field Change
2008-10-15 18:52 nataliaperu New Issue
2008-10-15 18:52 nataliaperu Assigned To => cromero
2008-10-15 18:52 nataliaperu sf_bug_id 0 => 2168926
2008-10-20 11:28 psarobe Priority normal => high
2008-10-20 11:29 psarobe Status new => scheduled
2008-10-20 11:29 psarobe fix_in_branch => trunk
2008-11-05 14:15 cromero Assigned To cromero => rafaroda
2008-11-13 22:07 rafaroda Assigned To rafaroda => Dowid
2008-11-26 16:42 rafaroda Assigned To Dowid => rafaroda
2008-11-26 16:50 rafaroda Note Added: 0010721
2008-11-26 16:50 rafaroda fix_in_branch trunk =>
2008-11-26 16:50 rafaroda Description Updated
2008-11-26 16:50 rafaroda Proposed Solution updated
2008-11-26 16:50 rafaroda Steps to Reproduce Updated
2008-11-26 17:07 rafaroda Note Edited: 0010721
2008-11-26 17:07 rafaroda Description Updated
2008-12-31 12:09 iciordia Note Added: 0011783
2008-12-31 12:12 iciordia Assigned To rafaroda => iciordia
2008-12-31 12:13 iciordia Regression testing => No
2008-12-31 12:13 iciordia Status scheduled => closed
2008-12-31 12:13 iciordia Note Added: 0011784
2008-12-31 12:13 iciordia Resolution open => no change required

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker