Hi Martin,
the idea is really good. Some time ago we had this already in OpenCA. If you check OpenCA::DBI in 0.9.0 or 0.9.1 then you can find a table called log. I removed it because we don't find a way to transparently support this with DBM files too.
So I would ask you to make an exact description what we want to do and wait until october. I know that time is usually the thing which nobody has but the problem is that if we make the decision that we no longer support DBM then we can really easily integrate this stuff in OpenCA::DBI.
Another solution of this timing problem is that we wait until next week (Max is back at 2004-Aug-28) and then start a discussion over the DBM support. I think I will create the branch openca_0_9_2 at monday. So after this timestamp we can start breaking the CVS :)
General objective:
Whenever an object is modified/created in the database as a result of a user operation, a separate audit entry should be generated in the database stating at least - user - timestamp - action/operation performed
I agree.
Design proposal:
* Define a way to uniquely identify each object in the database. This could be a table identifier contatenated with its primary key value, e. g.: - crl_20040802162939 - req_288 - crr_257 A better way might be to have tuple (objecttype, key), both being primary keys on the db, to identify the object. Use this identifier to refer to the affected object from the audit table.
A clean database design is always the best way. So never mix fields and use two rows for objecttype and key (be warned keys can have non numerical serials). We need some sequence generators too. This was implemented some time ago and must be re-introduced for this stuff.
* Introduce a new table (e. g. 'audittrail'): objectid: varchar (OR objecttype: varchar; objectkey: varchar), see above userid: varchar (role: varchar - ) action: varchar timestamp: date
1. objecttype: varchar; objectkey: varchar
2. role must be present because this is the only chance to detect failures in an external authentication program if it returns a wrong role.
* On each insert/update in the database add a row to the 'audittrail' table. The entry should state timestamp, logged in user ID for the session (if available, it might also be DN of a signature's creator) and action. It might be possible to extend the DBI/DB interface to add this entry automatically (problem: pass userid/session information and action to the DBI methods; sometimes the DBI class may be able to deduce the 'action' information from the information passed)
... and this is the problem.
* Objects displayed by OpenCA (such as Request or Cert overview) could include either a complete list of audit trail events or a link to a summary page listing the audit trail events:
SELECT timestamp, userid, action FROM audittrail WHERE objectid=? ORDER BY timestamp
In case of CSRs and CRRs this would it make easier for RA admins to identify the applicant. It would also make the workflow much more transparent - and auditing happy.
The problem with this SQL like stuff is that we have problems to implement it with DBM. Therefore we should start next week a discussion about DBM. Nevertheless DBM will be supported in 0.9.2.
Michael -- ------------------------------------------------------------------- Michael Bell Email: [EMAIL PROTECTED] ZE Computer- und Medienservice Tel.: +49 (0)30-2093 2482 (Computing Centre) Fax: +49 (0)30-2093 2704 Humboldt-University of Berlin Unter den Linden 6 10099 Berlin Email (private): [EMAIL PROTECTED] Germany http://www.openca.org
------------------------------------------------------- SF.Net email is sponsored by Shop4tech.com-Lowest price on Blank Media 100pk Sonic DVD-R 4x for only $29 -100pk Sonic DVD+R for only $33 Save 50% off Retail on Ink & Toner - Free Shipping and Free Gift. http://www.shop4tech.com/z/Inkjet_Cartridges/9_108_r285 _______________________________________________ OpenCA-Devel mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/openca-devel
