View Issue Details

IDProjectCategoryView StatusLast Update
17123Development Otherpublic2021-02-24 18:00
Reporterollehar Assigned To 
PrioritynoneSeverityminor 
Status newResolutionopen 
Summary17123: Store state of encrypted fields for archived tables
Description

When archiving/restoring a response/participant table using encryption, we need to know which fields were encrypted.
Because if a user enables/disabled encryption for some fields and restores the data from an archived table, on restoration the data needs to encrypted/decrpyted according to the new setting(s).

Data will be stored in a new table with the following structure:;

id - autoincrement integer
survey_id- the survey ID (original survey ID this table belonged to)
user_id - the user ID of user which created the archived table
tbl_name - varchar(255) name of the archived table without prefix
tbl_type - string (10) - token / survey / timing
created - datetime - datetime when table was archived
properties - text - JSON which contains a copy of fieldmap ?

Things to pay attention:

  • When a table is archived an entry should be created
  • When an archived table is deleted the entry should be removed from this table and not kept around - it is not a log
  • The integrity check should check if there are entries with missing table and should remove them
  • All existing archive tables should be added to the table on update - user ID should be set to 1. sid, date and Type needs to be extracted from name
  • Restoration of/access to an archived table should only be allowed for superadmins and user_id
Tagsencryption

Activities

DenisChenu

DenisChenu

2021-02-23 15:18

developer   ~62463

Currently : when we export : we export decrypted,

We don't offer (on 4.4 lats time i look) a way to export crypted.

If we offer to export crypted : we need a real key management

ollehar

ollehar

2021-02-23 15:19

administrator   ~62464

This is not for export/import, but for active/deactive survey, and to remember which questions was encrypted. You can change encryption settings between survey activation. It's a bit messy, indeed.

DenisChenu

DenisChenu

2021-02-23 16:57

developer   ~62467

You can change encryption settings between survey activation.

Argl …

DenisChenu

DenisChenu

2021-02-23 16:57

developer   ~62468

« You can change encryption settings at all time» :D

ollehar

ollehar

2021-02-23 17:54

administrator   ~62472

« You can change encryption settings at all time» :D

No no, only when survey is not activated.

ollehar

ollehar

2021-02-23 17:58

administrator   ~62473

Column for sid would be nice. I also vote for a user_id (I just know that I will want to know that at some time or other (customer messes up entire survey, we can see exactly when it happened and who did it)).

tblname - Can we do tbl_name or table_name? We use underscore in the database generally.

DenisChenu

DenisChenu

2021-02-24 07:13

developer   ~62476

Last edited: 2021-02-24 07:13

View 2 revisions

tblname : why not only extraname part (the datetime) ? and add sid ?

Unsure about time (and currently not crypted) but 'lime_old_survey_'.$object->sid.'_'$object->extra and 'lime_old_token_'.$object->sid.'_'$object->extra

Then table name are always 'lime_old_'.$object->type.'_'.$object->sid.'_'object->extra or 'lime_old_'.$object->type.'_'.$object->sid.'_'object->whendatetime

DenisChenu

DenisChenu

2021-02-24 07:14

developer   ~62477

When an archived table is deleted the entry should be removed from this table and not kept around - it is not a log

And when survey is deleted too (this one is most easy)

ollehar

ollehar

2021-02-24 11:50

administrator   ~62485

NB: Who should have permission to deactive survey, disable encryption, then activate survey and importing old response table, now saved as clear text?

c_schmitz

c_schmitz

2021-02-24 16:30

administrator   ~62497

Last edited: 2021-02-24 16:32

View 2 revisions

Updated table schema and details

@shnoulle: No, don't delete archived tables on survey deletion. I might want to load the data into a new copy with the same survey id.

DenisChenu

DenisChenu

2021-02-24 16:57

developer   ~62500

I might want to load the data into a new copy with the same survey id.

We don't delete it currently ?

If not : maybe we need an option ?

DenisChenu

DenisChenu

2021-02-24 16:59

developer   ~62501

Restoration of/access to an archived table should only be allowed for superadmins and user_id

Oh, you broke previous system ? Currently : any admin with response (update/create?) can reload archived table (of current survey).

c_schmitz

c_schmitz

2021-02-24 17:58

administrator   ~62503

Oh, you broke previous system ? Currently : any admin with response (update/create?) can reload archived table (of current survey).

Not breaking, more like fixing. If a survey is deleted and I create a survey with the same response ID I could probably access the data from the old response table, even if that data does not belong to me.

c_schmitz

c_schmitz

2021-02-24 17:59

administrator   ~62504

If not : maybe we need an option ?

We certainly do - but not right now.

DenisChenu

DenisChenu

2021-02-24 18:00

developer   ~62505

Not breaking, more like fixing. If a survey is deleted and I create a survey with the same response ID

Ok, currently only VV is available.

I could probably access the data from the old response table, even if that data does not belong to me.

I really think it's hard since gid and qid are updated … how do you know this qid is the same than this other qid ?

Issue History

Date Modified Username Field Change
2021-02-23 15:13 ollehar New Issue
2021-02-23 15:13 ollehar Description Updated View Revisions
2021-02-23 15:16 ollehar Description Updated View Revisions
2021-02-23 15:18 DenisChenu Note Added: 62463
2021-02-23 15:19 ollehar Note Added: 62464
2021-02-23 15:24 c_schmitz Description Updated View Revisions
2021-02-23 15:25 c_schmitz Description Updated View Revisions
2021-02-23 15:26 c_schmitz Summary Store history of encrypted fields => Store state of encrypted fields for archived tables
2021-02-23 16:39 c_schmitz Description Updated View Revisions
2021-02-23 16:57 DenisChenu Note Added: 62467
2021-02-23 16:57 DenisChenu Note Added: 62468
2021-02-23 17:54 ollehar Note Added: 62472
2021-02-23 17:58 ollehar Note Added: 62473
2021-02-23 18:27 ollehar Tag Attached: encryption
2021-02-24 07:13 DenisChenu Note Added: 62476
2021-02-24 07:13 DenisChenu Note Edited: 62476 View Revisions
2021-02-24 07:14 DenisChenu Note Added: 62477
2021-02-24 11:50 ollehar Note Added: 62485
2021-02-24 16:28 c_schmitz Description Updated View Revisions
2021-02-24 16:30 c_schmitz Note Added: 62497
2021-02-24 16:31 c_schmitz Description Updated View Revisions
2021-02-24 16:32 c_schmitz Note Edited: 62497 View Revisions
2021-02-24 16:33 c_schmitz Description Updated View Revisions
2021-02-24 16:34 c_schmitz Description Updated View Revisions
2021-02-24 16:34 c_schmitz Description Updated View Revisions
2021-02-24 16:35 c_schmitz Description Updated View Revisions
2021-02-24 16:57 DenisChenu Note Added: 62500
2021-02-24 16:59 DenisChenu Note Added: 62501
2021-02-24 17:58 c_schmitz Note Added: 62503
2021-02-24 17:59 c_schmitz Note Added: 62504
2021-02-24 18:00 DenisChenu Note Added: 62505