SF.net SVN: ledger-smb:[6314] trunk/sql

[..hidden..]

Revision: 6314
          http://sourceforge.net/p/ledger-smb/code/6314
Author:   einhverfr
Date:     2013-12-08 02:23:41 +0000 (Sun, 08 Dec 2013)
Log Message:
-----------
Undoing Herman's audit trail changes, which would have enforced a primary key against person(id), not against entity(id).  Currently the trigger requires a database user account, as well as an entry in the users table.    This should be quite safe (if even overly restricted) regarding preventing unauthorized access.

Additionally the trigger had a bug in it, so this has been corrected.

I am going to propose instead that we rename the person_id field in 1.5 to entity_id.  This would allow addons for managing automated processes as entities as well (perhaps through a different join table).

Modified Paths:
--------------
    trunk/sql/Pg-database.sql
    trunk/sql/modules/Fixes.sql

Modified: trunk/sql/Pg-database.sql
===================================================================
--- trunk/sql/Pg-database.sql	2013-12-07 15:49:52 UTC (rev 6313)
+++ trunk/sql/Pg-database.sql	2013-12-08 02:23:41 UTC (rev 6314)
@@ -2191,8 +2191,7 @@
   formname text,
   action text,
   transdate timestamp default current_timestamp,
-  --person_id integer references person(entity_id) not null,
-  person_id integer references person(id) not null,
+  person_id integer references person(entity_id) not null,
   entry_id BIGSERIAL PRIMARY KEY
 );
 
@@ -2415,7 +2414,6 @@
 DECLARE
    t_reference text;
    t_row RECORD;
-   t_user_id int;
 BEGIN
 
 IF TG_OP = 'INSERT' then
@@ -2430,12 +2428,9 @@
     t_reference := t_row.reference;
 END IF;
 
-SELECT id into t_user_id from users where username = SESSION_USER;
+INSERT INTO audittrail (trans_id,tablename,reference, action, person__get_my_entity_id())
+values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
 
-INSERT INTO audittrail (trans_id,tablename,reference, action, person_id)
---values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
-values (t_row.id,TG_RELNAME,t_reference, TG_OP, t_user_id);
-
 return null; -- AFTER TRIGGER ONLY, SAFE
 END;
 $$ language plpgsql security definer;

Modified: trunk/sql/modules/Fixes.sql
===================================================================
--- trunk/sql/modules/Fixes.sql	2013-12-07 15:49:52 UTC (rev 6313)
+++ trunk/sql/modules/Fixes.sql	2013-12-08 02:23:41 UTC (rev 6314)
@@ -258,9 +258,8 @@
 COMMIT;
 
 BEGIN;
-update audittrail set person_id=(select id from person where last_name='Admin') where person_id not in (select id from person) ;
 ALTER TABLE audittrail DROP CONSTRAINT IF EXISTS "audittrail_person_id_fkey";
-ALTER TABLE audittrail ADD CONSTRAINT "audittrail_person_id_fkey" FOREIGN KEY(person_id) REFERENCES person(id);
+ALTER TABLE audittrail ADD CONSTRAINT "audittrail_person_id_fkey" FOREIGN KEY(person_id) REFERENCES entity(id);
 
 CREATE OR REPLACE FUNCTION gl_audit_trail_append()
 RETURNS TRIGGER AS
@@ -268,7 +267,6 @@
 DECLARE
    t_reference text;
    t_row RECORD;
-   t_user_id int;
 BEGIN
 
 IF TG_OP = 'INSERT' then
@@ -283,17 +281,15 @@
     t_reference := t_row.reference;
 END IF;
 
-SELECT id into t_user_id from users where username = SESSION_USER;
-
 INSERT INTO audittrail (trans_id,tablename,reference, action, person_id)
---values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
-values (t_row.id,TG_RELNAME,t_reference, TG_OP, t_user_id);
+values (t_row.id,TG_RELNAME,t_reference, TG_OP, person__get_my_entity_id());
 
 return null; -- AFTER TRIGGER ONLY, SAFE
 END;
 $$ language plpgsql security definer;
 
 COMMIT;
+
 BEGIN;
 ALTER TABLE ar ADD COLUMN crdate date;
 ALTER TABLE ap ADD COLUMN crdate date;

This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.


------------------------------------------------------------------------------
Sponsored by Intel(R) XDK 
Develop, test and display web and hybrid apps with a single code base.
Download it for free now!
http://pubads.g.doubleclick.net/gampad/clk?id=111408631&iu=/4140/ostg.clktrk
_______________________________________________
Ledger-smb-commits mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-commits