One of our clients has been experiencing a recurring problem that has re-awoken something that I've been mulling over for a while.

The view permissions on the home page keep changing so that only admins can see it.  This means that non-admin users get redirected to a login page - not terribly friendly for a large church!

We've been round the loop of checking that only the appropriate staff have admin rights etc and they've all promise me that they haven't done anything silly.  The problem seemed to have disappeared but today it happened again and they called me as soon as they spotted it.  The obvious thing for me to do was for me to check the last modified date and time on the mp_Pages table, but to my surprise although this table has a LastModifiedUTC column it doesn't have a column saying who modified it!

I will put a post in the bugs forum just in case anyone else has spotted a similar issue, but it raises a bigger issue - the lack of an audit trail.  

Frustratingly mojoPortal already has much of what's needed to create a trigger-based audit trailing system, and I would gladly put some development work in to get it going, but before it can work correctly we need to standardize on certain column names.

Many of the Mojo tables already collect information about when a table row was last modified and who by but it's not consistent.  If you run the following query you'll get back a list of all the modification related columns:

SELECT o.name as 'table',c.name as 'column'
FROM sys.all_columns c
left join sys.all_objects o on c.object_id=o.object_id
WHERE c.name like '%Mod%'and o.type='U'
and c.name not like '%Module%' and c.name not like '%mode%'
order by o.name

Here's a selection:

Table                    Column
mp_Pages           LastModifiedUTC
mp_RssFeeds    LastModUserGuid
mp_RssFeeds    LastModUtc
mp_SavedQuery LastModUtc
mp_SavedQuery LastModBy
mp_Tag                ModifiedBy
mp_Tag                ModifiedUtc

If we could standardize on the name of the columns - e.g. "LastModUTC" and "LastModBy" it would be fairly straightforward to write a trigger that could -

• Check in an audit trail config table whether this table has auditing enabled, and which column values are to be stored.
• Write the before (and possibly after) values of the selected columns into a separate audit trail table.

So, please could we have a "LastModBy" column added to mp_Pages, and then have a process of standardizing on the name of the "modifiedby" columns as the next version of Mojo are released and new features are rolled-out?

Thanks

Thanks Joe - that's excellent news.

Ian

That's excellent news - I had no expectation of if happening so soon - thanks Joe!

I suppose I'll have to think about coming good on my promise of a trigger based audit trail now!

Thanks again - I'll let me client know - I'm sure they'll be very pleased.

Ian