Database Change Management via Role-Based Access Control and Cloning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
In multi-user database environments, simultaneous modifications by multiple users can lead to data corruption and integrity issues, as existing methods lack effective mechanisms for preventing concurrent modifications and documenting changes, resulting in potential mistakes and complex rollback processes.
Innovation Solution
A system that manages database resource availability by creating security roles, assigning modification permissions, and using a Change Management System (CMS) to track and control user access, prevent simultaneous modifications, and automatically document changes through a cloning mechanism and DDL triggers.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If multiple users access and modify the database simultaneously, then productivity is improved, but data integrity and reliability deteriorate due to potential data corruption and conflicting modifications
Solution Approach 1:
The system performs preliminary actions by creating a snapshot of the database state before allowing modifications. This snapshot is taken in advance to establish a baseline for comparison, enabling the system to detect conflicts and prevent data corruption while allowing concurrent access.
Solution Approach 2:
The system creates a copy of the database state (snapshot) that can be modified without affecting the original. Users work on the copied version while the original remains intact, allowing simultaneous access and modifications without compromising data integrity. The snapshot mechanism enables multiple users to access different versions simultaneously.
2Loss of information
If manual documentation of database modifications is implemented, then tracking changes is improved, but ease of operation deteriorates due to tedious and complicated rollback processes
Solution Approach 1:
The system performs self-service by automatically documenting all database modifications through triggers and logging mechanisms. The rollback process is also automated, requiring minimal user intervention. The system manages its own change tracking and restoration without relying on manual user actions, thereby improving ease of operation while maintaining complete change tracking.
Solution Approach 2:
The system implements feedback mechanisms where triggers automatically detect modifications and log them to documentation tables. This automated feedback loop ensures that all changes are recorded without manual intervention, and the system can automatically restore previous states based on this logged information, simplifying the rollback process.
3Adaptability or versatility
If conversion code is saved in files and managed externally, then adaptability is improved, but device complexity increases due to additional migration tools and conversion processes
Solution Approach 1:
The system merges the code management functionality directly into the database management system. Instead of using external file-based version control, the solution integrates versioning, snapshot, and modification tracking capabilities within the database itself, eliminating the need for separate migration tools and reducing overall system complexity.
Solution Approach 2:
The system introduces an intermediary layer in the form of database triggers and stored procedures that automatically handle code versioning and migration. This intermediary mechanism mediates between the user's modification actions and the actual database state, providing adaptability while keeping the system architecture unified and manageable.
4Loss of information
If MS VSS is used for documentation, then change tracking is improved, but productivity deteriorates due to lack of locking mechanisms allowing parallel modifications that override each other
Solution Approach 1:
The system takes preliminary action by creating a snapshot of the database state before allowing modifications. This snapshot serves as a baseline for detecting conflicts. If parallel modifications are detected, the system can identify and resolve conflicts before they override each other, thereby maintaining both documentation accuracy and modification efficiency.
Solution Approach 2:
The system implements feedback through triggers that automatically detect when parallel modifications occur. The feedback mechanism compares the snapshot with current changes, identifies conflicts, and prevents overriding modifications. This automated conflict detection and resolution maintains productivity while ensuring accurate change tracking.
Data Source
AI summary
The present invention relates to a method for preventing the simultaneous modification of the same database object in a shared database by more than one user during the database development stage comprising the steps of: (a) creating security roles for said users, wherein each of the security roles has a modification permission set for denying or granting at least one modification permission to at least one said database object; (b) assigning each of said security roles to each of said users; (c) providing means for said users to request said modification permission to said database object; (d) receiving said request from a first user for said modification permission to said at least one database object; (e) determining that said security roles of said users, excluding the security role of first user, are set to deny said modification permissions to said object; (f) updating said security role of said first user to grant said modification permission to said object; (g) allowing said first user to modify said object; (h) receiving a termination notification from said first user for concluding said modification permission to said object; and (i) updating said security role of said first user to cease said modification permission to said object.