Hi Thomas,
I am attaching a trigger-based solution which I think gives you airtight
protection for
inserts and updates of createdBy
inserts and updates of updatedBy
updates of createdOn
The solution is not airtight, but might be good enough for
inserts of createdOn
inserts and updates of updatedOn
The solution consists of two vetting and logging procedures (found in
zz.java). A sample script for declaring and testing the solution can be
found in zz.sql.
Hope this helps,
-Rick
Thomas wrote:
use case
- use of database side programming to log data maintenance activities
- tech columns CreatedBy/CreatedOn, LastUpdatedBy/LastUpdatedOn
to store username, timestamp information on when row was
inserted / last updated defined on every application table
- no possibility for users/applications to tamper data stored in these columns
- avoid implementation of extensive protection mechanisms (e.g. by allowing
table data to be maintained only via stored procedures)
approaches investigated
1) using default column values to populate CreatedBy/CreatedOn on insert,
i.e. "CreatedBy" varchar(64) NOT NULL DEFAULT CURRENT_USER
-> column values not secured
-> works for insert statements only (can not be used for populating
columns LastUpdatedBy/LastUpdatedOn in case of updates)
2) using a generated column spec with a value expression
i.e. "CreatedBy" varchar(64) GENERATED ALWAYS AS CURRENT_USER;
-> not possible as CURRENT_USER, CURRENT_TIMESTAMP as non-determinstic
functions can not be used as value expressions
3) using INSERT triggers (with SQL statements or calling java procedures)
i.e. CREATE TRIGGER xy AFTER INSERT FOR EACH ROW UPDATE "CreatedBy"...
-> might work for insert statements, but triggering an update statement
with the insert would prevent me from defining an after update
trigger on the table which I would need to log update activity
(and which would create an infinte loop)
Can't think of a way that would work which is unfortunate as for example
PostgreSQL would allow such an implementation easily as transition values
can be referenced and updated with the system funtions associate with a
trigger(i.e. all needed is a one line statement NEW.CreatedBy = CURRENT_USER
within the trigger function).
Any suggestions? Is there really no way this can be achieved in Derby?
(other than maybe putting the tech cols on a separate table and joining the
two base tables in a view so that at least in read operations it would look
like the tech cols are held on the same table?)
Thanks
Thomas
import java.sql.*;
public class zz
{
private static final long TIMESTAMP_TOLERANCE = 1000L;
public static void vetAndLogInsert
(
String expectedCreationUserName,
Timestamp expectedCreationTimestamp,
String actualCreationUserName,
String actualUpdateUserName,
Timestamp actualCreationTimestamp,
Timestamp actualUpdateTimestamp
)
throws SQLException
{
long expectedTime = expectedCreationTimestamp.getTime();
long actualTime = actualCreationTimestamp.getTime();
long difference = expectedTime - actualTime;
if ( ( difference < 0 ) || ( difference > TIMESTAMP_TOLERANCE ) ) {
throw new SQLException( "Creation timestamp overridden." ); }
if ( !expectedCreationUserName.equals( actualCreationUserName ) ) {
throw new SQLException( "Creation user overridden." ); }
if ( !expectedCreationUserName.equals( actualUpdateUserName ) ) { throw
new SQLException( "Update user overridden." ); }
if ( !actualCreationTimestamp.equals( actualUpdateTimestamp ) ) { throw
new SQLException( "Update timestamp is not creation timestamp." ); }
logMe();
}
public static void vetAndLogUpdate
(
String expectedCreationUserName,
Timestamp expectedCreationTimestamp,
String expectedUpdateUserName,
Timestamp expectedUpdateTimestamp,
String actualCreationUserName,
String actualUpdateUserName,
Timestamp actualCreationTimestamp,
Timestamp actualUpdateTimestamp
)
throws SQLException
{
long expectedTime = expectedUpdateTimestamp.getTime();
long actualTime = actualUpdateTimestamp.getTime();
long difference = expectedTime - actualTime;
if ( ( difference < 0 ) || ( difference > TIMESTAMP_TOLERANCE ) ) {
throw new SQLException( "Update timestamp overridden." ); }
if ( !expectedCreationUserName.equals( actualCreationUserName ) ) {
throw new SQLException( "Creation user overridden." ); }
if ( !expectedCreationTimestamp.equals( actualCreationTimestamp ) ) {
throw new SQLException( "Creation timestamp overridden." ); }
if ( !expectedUpdateUserName.equals( actualUpdateUserName ) ) { throw
new SQLException( "Update user overridden." ); }
logMe();
}
private static void logMe() { System.out.println( "Logging a row." ); }
}
connect 'jdbc:derby:memory:dummy;create=true';
create table t
(
payload int,
createdBy varchar( 128 ) default current_user,
createdOn timestamp default current_timestamp,
updatedBy varchar( 128 ) default current_user,
updatedOn timestamp default current_timestamp
);
create procedure vetAndLogInsert
(
in expectedCreationUserName varchar( 128 ),
in expectedCreationTimestamp timestamp,
in actualCreationUserName varchar( 128 ),
in actualUpdateUserName varchar( 128 ),
in actualCreationTimestamp timestamp,
in actualUpdateTimestamp timestamp
)
language java parameter style java modifies sql data
external name 'zz.vetAndLogInsert';
create procedure vetAndLogUpdate
(
in expectedCreationUserName varchar( 128 ),
in expectedCreationTimestamp timestamp,
in expectedUpdateUserName varchar( 128 ),
in expectedUpdateTimestamp timestamp,
in actualCreationUserName varchar( 128 ),
in actualUpdateUserName varchar( 128 ),
in actualCreationTimestamp timestamp,
in actualUpdateTimestamp timestamp
)
language java parameter style java modifies sql data
external name 'zz.vetAndLogUpdate';
create trigger vetAndLogInsert
after insert on t
referencing new as insertedRow
for each row
call vetAndLogInsert
(
current_user,
current_timestamp,
insertedRow.createdBy,
insertedRow.updatedBy,
insertedRow.createdOn,
insertedRow.updatedOn
);
create trigger vetAndLogUpdate
after update on t
referencing old as old new as new
for each row
call vetAndLogUpdate
(
old.createdBy,
old.createdOn,
current_user,
current_timestamp,
new.createdBy,
new.updatedBy,
new.createdOn,
new.updatedOn
);
-- succeeds
insert into t( payload ) values ( 1 );
update t set payload = 2;
-- fails
insert into t( createdBy, payload ) values ( 'foo', 2 );
insert into t( createdOn, payload ) values( timestamp('1962-09-23
03:23:34.234'), 3 );
update t set createdBy = 'foo';
update t set updatedBy = 'foo';
update t set createdOn = timestamp('1962-09-23 03:23:34.234');
update t set updatedOn = timestamp('1962-09-23 03:23:34.234');
