View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|17123||Development||Other||public||2021-02-23 15:13||2021-06-15 10:11|
|Summary||17123: 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, owner or admins with permission.
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
|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.|
> You can change encryption settings between survey activation.
|« You can change encryption settings at all time» :D|
> « You can change encryption settings at all time» :D
No no, only when survey is not activated.
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.
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`
> 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)
|NB: Who should have permission to deactive survey, disable encryption, then activate survey and importing old response table, now saved as clear text?|
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.
> 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 ?
> 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).
> 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.
> If not : maybe we need an option ?
We certainly do - but not right now.
> 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 ?
|Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=31682|
LimeSurvey: master a326ac06
2021-04-19 10:34:03Details Diff
|Fixed issue 17123: Store missing state of encrypted fields for archived tables||
|mod - application/config/version.php||Diff File|
|mod - application/controllers/SurveyAdministrationController.php||Diff File|
|mod - application/controllers/admin/checkintegrity.php||Diff File|
|mod - application/controllers/admin/dataentry.php||Diff File|
|mod - application/controllers/admin/tokens.php||Diff File|
|mod - application/helpers/update/updatedb_helper.php||Diff File|
|add - application/models/ArchivedTableSettings.php||Diff File|
|mod - application/views/admin/dataentry/import.php||Diff File|
|mod - application/views/admin/token/tokenwarning.php||Diff File|
|mod - installer/create-database.php||Diff File|
|mod - psalm.xml||Diff File|
|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|
|2021-04-12 16:31||c_schmitz||Description Updated||View Revisions|
|2021-05-06 17:00||p_teichmann||Changeset attached||=> LimeSurvey master a326ac06|
|2021-05-06 17:00||p_teichmann||Note Added: 64276|
|2021-05-06 17:00||p_teichmann||Assigned To||=> p_teichmann|
|2021-05-06 17:00||p_teichmann||Resolution||open => fixed|
|2021-06-15 10:11||ollehar||Status||new => resolved|