Forum OpenACS Development: adding history to package parameter changes

Hi,

ecommerce logs (some) parameter changes to table ec_admin_settings_audit, including new value, timestamp, user and ip address.

Could this feature be added to parameter::set_value as a standard feature for system auditing??

If so, does anyone have any recommendations on how to implement (in case it gets added)?

Collapse
Posted by Torben Brosten on

I hope I've recovered well enough from a few dizzy spells from looking at kernel code to be coherent...

It seems that when ad_conn has info for ip_address and user, that parameter::set_value should pass the ip_address and user_id to apm__set_value, which would then perform acs_object__update_last_modified just prior to PERFORM apm__set_value(int4,int4,varchar)

..and then, have a trigger/table setup like this:

create table apm_parameter_values_audit (
       value_id integer,
       package_id integer,
       parameter_id integer,	 
       --
       --  This column holds the value for the instance parameter.
       -- 
       attr_value_was	 text,
       last_modified         timestamptz default current_timestamp not null,
-- get following 2 values from acs_objects table
       modifying_user     integer,
       modifying_ip     varchar(50)
);

create or replace function apm_parameter_values_audit_tr () 
returns opaque as '

   DECLARE 
    v_attr_value text;
    v_parameter_name varchar(100);
    v_last_modified   timestamptz;
    v_creation_date   timestamptz;
    v_last_modifying_user integer;
    v_modified_ip_address varchar(50);

   BEGIN

   select into v_last_modified last_modified from acs_objects where object_id = old.value_id;
   select into v_last_modifying_user modifying_user from acs_objects where object_id = old.value_id;
   select into v_modified_ip_address modifying_ip from acs_objects where object_id = old.value_id;
   select into v_parameter_name parameter_name from apm_parameters where parameter_id = old.parameter_id;

   v_attr_value := old.attr_value;

  IF (v_parameter_name  = ''froogle_password'' ) THEN
      v_attr_value := ''value expunged'';
  END IF;

-- check if modified from system instead of UI
IF ( v_last_modifying_user IS NULL ) THEN
  IF (v_last_modified IS NULL ) THEN
       -- logging original parameter setting
       select into v_creation_date creation_date from acs_objects where object_id = old.value_id;
        insert into apm_parameter_values_audit (
       value_id, package_id, parameter_id, attr_value_was, creation_date
        ) values (
        old.value_id, old.package_id, old.parameter_id, v_attr_value, v_creation_date 
        );
   ELSE
        insert into apm_parameter_values_audit (
       value_id, package_id, parameter_id, attr_value_was, last_modified
        ) values (
        old.value_id, old.package_id, old.parameter_id, v_attr_value, v_last_modified 
        );
  END IF;
ELSE
        insert into apm_parameter_values_audit (
       value_id, package_id, parameter_id, attr_value_was, last_modified,modifying_user, modifying_ip
        ) values (
        old.value_id, old.package_id, old.parameter_id, v_attr_value, v_last_modified, v_last_modifying_user, v_modified_ip_address 
        );

END IF;

        return new;
end;' language 'plpgsql';

create trigger apm_parameter_values_audit_tr
before update or delete on apm_parameter_values
for each row execute procedure apm_parameter_values_audit_tr ();

Is there a simpler way to log parameter changes to a table (assuming that this way will even work)?

Please let me know if I'm going in the right direction, as logging of parameter changes will be added to the kernel in one for or another soon.

Thanks in advance,
 Torben

Collapse
Posted by Malte Sussdorff on
This looks good. I was wondering if there should be a general audit mechanism for OpenACS for non cr items. Something like

util::audit -object_id -modifing_user -creation_ip -attributes {list of attribute key/value pairs} -table_name -table_primary_key -table_primary_key_value

This will store the values in a general audit table. If we have an object_id, use the reference to acs_objects, for all other tables use tablename and primary_key_name and value:

create table audit (
audit_id primary key,
object_id references acs_objects,
table_name references pg_(whatever the view for user tables is),
table_primary_key,
table_primary_key_value,
last_modified timestamptz default current_timestamp not null default now(),
-- get following 2 values from acs_objects table
modifying_user integer,
modifying_ip varchar(50)
);

create table audit_value {
audit_id references audit,
key,
value
};

and then obviously something like

ad_proc util::audit::get_changes {
{-object_id ""}
{-modifing_user ""}
{-table_name ""}
{-table_primary_key ""}
{-table_primary_key_value ""}
} {
Retrieve audit log
@return list of list of changes matching the given combination of object_id, user or table definition with one row per change ordered by date.
@return the list will look like {$object_id $modifing_user $modifing_ip $table_name $table_primary_key $table_primary_key_value $modification_date {[list {key new_value old_value}]}}
}

What do you think? This should also take care of Torben's request.

Collapse
Posted by Torben Brosten on
Malte, great idea!

Since acs-messaging is being phased out, would this work to add optional message logging in acs-mail-lite?

ecommerce has a logging feature for outbound messages that will need to be integrated.

Your suggestion would fill the ecommerce[1] audit features/requirements and could easily be extended to whatever needs audited within an implementation.

1. https://openacs.org/doc/ecommerce/audit

Collapse
Posted by Malte Sussdorff on
acs-mail-lite has message logging with the mail-tracking package, which works out of the box and has some special features that you would not want in a general audit package like links to the persons who recieved the mail, display of mails, forwarding of mails. But otherwise I agree and great that you like the idea.
Collapse
Posted by Torben Brosten on
I'm following up on a related discussion on irc yesterday evening, where others have expressed interest in and implemented local versions of this functionality.

Table journal_entries from acs-kernel/sql/postgresql/jounral-create.sql[1] was suggested.

journal_entries requires all entries relate to acs_objects. Many packages, such as AMS (and the accounting packages) do not use acs_objects in fine granularity. What approach should be made to add Malte's general version to acs-kernel head? a new table? modify this existing one?

Would adding it to head require a TIP?

1. http://cvs.openacs.org/cvs/openacs-4/packages/acs-kernel/sql/postgresql/journal-create.sql