View Issue Details

IDProjectCategoryView StatusLast Update
17123Development Otherpublic2021-06-15 10:11
Reporterollehar Assigned Top_teichmann  
PrioritynoneSeverityminor 
Status resolvedResolutionfixed 
Summary17123: Store state of encrypted fields for archived tables
DescriptionWhen 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.
Tagsencryption

Users monitoring this issue

User List kjnerhus

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

Last edited: 2021-04-12 16:22

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

Last edited: 2021-04-12 16:22

> You can change encryption settings between survey activation.

Argl …
DenisChenu

DenisChenu

2021-02-23 16:57

developer   ~62468

Last edited: 2021-04-12 16:22

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

ollehar

2021-02-23 17:54

administrator   ~62472

Last edited: 2021-04-12 16:22

> « 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

Last edited: 2021-04-12 16:22

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-04-12 16:22

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

Last edited: 2021-04-12 16:22

> 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

Last edited: 2021-04-12 16:22

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-04-12 16:22

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

Last edited: 2021-04-12 16:22

> 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

Last edited: 2021-04-12 16:22

> 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

Last edited: 2021-04-12 16:22

> 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

Last edited: 2021-04-12 16:22

> If not : maybe we need an option ?

We certainly do - but not right now.
DenisChenu

DenisChenu

2021-02-24 18:00

developer   ~62505

Last edited: 2021-04-12 16:22

> 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 ?
p_teichmann

p_teichmann

2021-05-06 17:00

administrator   ~64276

Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=31682

Related Changesets

LimeSurvey: master a326ac06

2021-04-19 10:34:03

p_teichmann

Details Diff
Fixed issue 17123: Store missing state of encrypted fields for archived tables Affected Issues
17123
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

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
2021-04-08 19:58 kjnerhus Issue Monitored: kjnerhus
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