Database Masking with Mapping Tables for Referential Integrity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional data masking in databases often compromises referential integrity and is resource-intensive, particularly when masking keys, which can disrupt testing and development processes by destroying links between tables and requiring excessive processor cycles.
Innovation Solution
A method that maintains referential integrity by masking data within the database using SQL, creating a mapping table to manage primary and foreign key relationships, and recreating tables while preserving constraints, ensuring that masked values are consistent across dependent columns.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If conventional masking is applied to sensitive data in databases, then data security is improved, but referential integrity between tables is compromised
Solution Approach 1:
The system performs preliminary actions by creating a comprehensive mapping table before masking data. This mapping table stores the correspondence between original and masked values, enabling referential integrity to be maintained through lookups. The mapping table is created in advance to prevent referential integrity issues rather than fixing them after masking occurs.
Solution Approach 2:
The mapping table serves as an intermediary between the original sensitive data and the masked data. When referential integrity needs to be maintained, the system uses the mapping table as a mediator to translate between masked values in one table and their corresponding original values that reference other tables. This intermediary layer preserves relationships without exposing sensitive data.
2Reliability
If conventional masking is applied to all data, then data security is improved, but processing efficiency deteriorates due to excessive processor cycles
Solution Approach 1:
The system applies local quality by selectively masking data based on its sensitivity and usage context. Not all data is masked uniformly - the system identifies specific columns and rows that require masking while leaving other data unmasked. This localized approach maintains security where needed while preserving processing efficiency for data that doesn't require masking.
Solution Approach 2:
The system performs partial masking rather than complete masking of all data. By masking only the necessary portions of data (partial action), the system achieves adequate security protection while minimizing the processing overhead associated with masking entire datasets. This selective approach reduces processor cycle consumption while maintaining acceptable security levels.
3Reliability
If keys are masked to protect sensitive information, then data security is improved, but table links and relationships are destroyed
Solution Approach 1:
The system creates a copy of the key relationships in the mapping table. Instead of directly masking keys in place, the system copies the correspondence between original and masked key values into a separate mapping structure. This copying approach allows the original key relationships to be preserved while enabling the use of masked keys in test environments through the copied mapping data.
Solution Approach 2:
The mapping table acts as an intermediary that preserves table relationships even when keys are masked. By storing the correspondence between original and masked key values, the mapping table enables foreign key relationships to be maintained through lookup operations. This intermediary structure allows test systems to use masked keys while still maintaining valid relationships between tables.
4Reliability
If complete data masking is performed, then data security is improved, but testing accuracy deteriorates due to loss of live data characteristics
Solution Approach 1:
The system applies different masking qualities to different data elements based on their testing requirements. Critical fields that need to maintain live data characteristics for accurate testing are either left unmasked or use reversible masking techniques, while other sensitive fields use complete masking. This localized differentiation of masking quality enables both security and testing accuracy to be maintained.
Solution Approach 2:
The system performs preliminary identification of which data elements require masking and which should remain accessible for testing. By analyzing data sensitivity and testing requirements in advance, the system prepares an optimized masking strategy that preserves testing accuracy for critical fields while maintaining security for sensitive fields. This preliminary analysis prevents over-masking that would degrade testing accuracy.
Data Source
AI summary
Systems, methods, and other embodiments associated with maintaining referential integrity while masking database columns are described. One example method includes determining a transitive closure for dependency relationships involving a column to be masked and other columns related to the column to be masked through a dependency relationship. The example method may also include identifying a root column for a column in the transitive closure and creating a mapping table that stores original values and masking values for the root column. The method includes masking the root column and related child columns based on the mapping table in a manner that maintains referential integrity between the root column and the child column. Integrity may be maintained by masking corresponding values in the root column and a child column(s) with identical mask values from the mapping table.