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 do not increase the size of data only minimal. The highest amount of disk space will be used, if sign every single Attribute of all records.
- 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 Signing an Attribute
- 2 Learning Task
- 3 See also
- 4 References
Signing an Attribute
In the following table the attribute meeting_time is signed.
|Attribute: name||Attribute: meeting_time||Digital Signature|
|unsigned||signed as attribute||Validation: (meeting_time) valid|
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.
Assume some changed the altered the attribute meeting_time in the database from 11:30 to 10:00. The digital signature become not valid.
|Attribute: name||Attribute: meeting-time||Digital Signature|
|unsigned||signed as attribute||Validation: (meeting_time) 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.
Valid Signature with undetected alterations
So the following digital signature is valid, because the value of the unsigned attribute name was changed to "Anna".
|Attribute: name||Attribute: meeting_time||Digital Signature|
|unsigned||signed attribute||Validation: (meeting_time) valid|
Valid Signature with signed record
Assume the record consist of just two attributes. Digital signature of the record is equivalent to the generation of ONE digital signature for ALL attributes. The signature prefix determines the order and selection of attribute, that were signed. The prefix for signing the whole record at once would look like this:
|Attribute: name||Attribute: meeting-time||Digital Signature for Record|
|signed record||signed record||(name,meeting_time) not valid|
Sign a database
A database is signed just like any digital media, a digital signature is generated for the file or submitted stream.
Data Model for Digital Signature
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
- 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).
In general keep in mind that the extension of your current data model must incorporate two information:
- 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.
This Attribute Sequence for Digital Signature (AS4DS) is a combined pair treated as a unit in the data model.
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.
- 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).
- Design a Relational Database
- Collaborative Mapping and the use of digital signature to implement selective validation of database records of even geolocations that specific mapped elements really exist.
- JSON2Schema - Transformation of a JSON record into a Schema for JSON editor by Jeremy Dorn (2017/06/05) Wiki comment by Engelbert Niehaus (accessed 2017/08/17) - https://github.com/jdorn/json-editor/wiki/JSON2Schema