Digital Signature/Database

Digital Signature allows to sign digital media. A data model. The question is:"Shall I sign just the whole Database, all Records seperately or even every single attribute of all records?" The answer depends on the use-case and intended way the database will be used:
 * PRO (just whole Database): Only one signature is necessary and we increase the size of data by ONE digital signature. The highest amount of disk space will be used, if the approachs signs every single attribute of all records independently without.
 * CON (just whole Database): If someone altered just one record, then you can still trust the rest of the records. But the digital signature, does not tell you, which record was altered. The digital signature is invalid for the whole database and hence the digital support for trust is gone for all records.

Conclusions: Granularity of digital signature is dependent on dynamic of alteration of database. If the database should only in the original version (see Version Control) then the one signature for whole database is sufficient. When alteration will be allowed and digital support of trust should be supported, then a more granular/more detailled digital signature should be applied. Especially if some attributes in the database contain highly relevant information for Spatial Decision Support Systems (e.g. data from WHO), then these attributes should be signed on the attribute level.

(1) Valid Signature
In the following table the attribute meeting_time is signed.

The private key of author is used by the author for signing the attribute meeting_time i.e. create the digital signature. The public key of the author is available for everyone, especially for the user of the database. The user of the message is able to validate just the intregrity of the time with public key. Because only that attribute was signed.

(2) Invalid Signature
Assume some changed the altered the attribute meeting_time in the database from 11:30 to 10:00. The digital signature become not valid.

Alteration in unsign attributes cannot recognize any alteration. See prefix (meeting_time) indicates which attributes were signed. Digital signature was applied by the owner of the public key on the concatenation of all values of the attribute.

(3) Valid Signature with undetected alterations
In comparison to valid signature (1) the meeting time was not altered, but the name was changed in the record. So the following digital signature is still valid, because a value of an unsigned attribute name was changed from "Bert" to "Anna".

(4) Sign a subset of attributes
Assume the record consist of just two attributes. Only the meeting time was signed before with a Digital signature. The signature signs only a one attribute and not all. The full list of attributes is equivalent to the generation of ONE digital signature for the record. The signature prefix (attribute list) determines the order and selection of attribute, that were signed.

(5) Invalid Signature by alteration of list of signed Attributes
In comparison to the valid record (1) the prefix for signing shows that only the meeting time was signed. the whole record at once would look like this: (meeting_time) Alteration of the attribute list e.g. to all attributes leads to the following attribute list as signature prefix: (name,meeting_time) This means that the attribute name and meeting_time are signed with digital signature. Due to the fact that the digital signature was not changed in comparison to the valid record (1), the alteration of the attribute list leads to an invalid signature.

If the signature remains the same it become invalid, due to the fact that the previous digital signature signed just the attribute meeting_time. This can be solved by multiple signatures for single attributes or a new signature that signs both attribute name and meeting_time.

Multiple Signatures for one Record
Different agencies or institutions can sign different attributes or sign the same set of attributes of one record, e.g. could sign independently a healthcare facility as existing and in operation (applicable e.g. for healthsites.io).
 * WHO and
 * Médecins_Sans_Frontières

Sign a database
A database is signed just like any digital media, a digital signature is generated for the file or submitted stream.

Extension of existing database
When an institution or organisation decides to introduce Public Key Infrastructure the major question is: Do we need a new database management system or replace major parts of our IT infrastructure? The answer is no, because you can regard the introduction of digital signature as In general keep in mind that the extension of your current data model must incorporate two information: This Attribute Sequence for Digital Signature (AS4DS) is a combined pair treated as a unit in the data model.
 * appending a columns in database table, if you use e.g. relational database
 * extension of a JSON schema, and extend a JSON object with attributes, that store the digital signature, which preserves the current data model and software finds and access all previously existing attributes in the JSON object in the same way (not alteration of libraries necessary).
 * sequence of attributes, that define the concatenation of values in the order of the sequence.
 * digital signature, that signs the concatenation of values in the order of the sequence of attributes.

Interagency collaboration
In an interagency collaboration (e.g. WHO and ESA) there might be more than one digital signature for a single record (e.g. one staff member from WHO validates number of repored injuries and a staff member of Disaster Management unit validates the number of destroyed health care facilities due to an earth quake in a the same region. This lead to the management of more AS4DS units for one database record.
 * Relational Database: Create a table with AS4DS pairs (Attribute Sequence, Digital Signature) and a secondary DB key pointing at record, that is signed. Then you can have more than one AS4DS pairs for the same record, that sign different sets of attributes.
 * JSON Schema: Add an array of AS4DS pairs, that sign different parts of JSON object. Signing an array can be treated as the digital signature of the concatenation of all value of its components. The digital signature of a JSON object is defined by the JSON schema. Please keep in mind that a single JSON object with data could contain more or less attributes, than defined in JSON schema. Undefined values in the JSON object are removed from the AS and additional attributes are excluded by default from the AS (attribute sequence) unless the user explicitely wants to sign them. If the user wants to sign additional attributes it is recommended to add an additional AS4DS pair to AD4DS array, because algorithm might expect signature for parts of the JSON schema only. The order of definitions in the JSON schema define the order of attributes in th AS4DS pair.

Pleaes keep in mind, that changing the order in the AS creates in invalid signature in general, even if the values and digital signature itself in the database records are not changed.

Learning Task

 * Explore the basic principle of digital signature with an e-mail client, e.g. OpenSource tools
 * GnuPG,
 * Thunderbird and
 * Enigmail.
 * Explain how this concept can be transfered to databases, records and even single attributes.


 * Learn about a Citizen Science approach and assume people report additional accidents and road blocks in a crowd sourcing database. The these information can be regarded as relevant from a regional regional traffic management to plan for annoucements in radio for the population, so alteration of the database is allowed, but trust in reported road blocks at that geolocation might not low. So if the other information provide by police might create more trust. Describe to what granularity would you sign the road block and accident database.
 * Assume WHO provides a spatial decision support layer for health risk, that use by Humanitarian Open Street Map in the project. Assume that the health risk is exchanged between people offline from mobile device to mobile device, because internet connectivity is not available. Do you know how to validate a spatial decision support layer, when internet connectivity is not available in very remote areas? Discover an explain the workflow! All users need a WHO public key for validation. Can this public key be shared with the database (apply logic)?
 * Explore the concept of signing a public key. Do you I create a chain of trust?
 * Apply Digital Signature to Spatial Decision Support Layers!
 * Analyse the concept of validation at HealthSites.io explain the role of digital signature data for validation e.g. of health care facilities and its specification by WHO or by governnmentatl institutions. Compare different data sources
 * patients report that a rural health care facility does not provide any service anymore (damage report of infrastructure) or limitation of capacity because the waiting for treatment caused severe negative impact due to limitation of capacity
 * in comparision to comparision to official governmental mapping of regional and local availability of health care services,
 * a combination of those data sources could lead to regional and local guidance to health care facilities that are in operation or have at least capacity to support the patient (Health Care Management).