Preventing accidental large deletes.

Instructions for Developers on Using the safe_delete Stored Procedure

To enhance safety and auditability of delete operations within our databases, we have implemented a controlled deletion process using a stored procedure named safe_delete. This procedure relies on a temporary table (temp_delete_table) that lists complete records intended for deletion, not just their IDs. This approach helps prevent accidental deletions and provides a traceable audit log of delete actions.

Why We Are Doing This

• Controlled Deletions: Centralizing delete operations through a stored procedure reduces the risk of erroneous or unauthorized deletions.
• Auditability: Using a temporary table to store complete records before deletion allows for an in-depth review and verification process, enhancing our ability to confirm and audit delete operations accurately.
• Security: Restricting direct delete permissions and channeling deletions through a specific procedure aligns with the principle of least privilege, reducing potential unauthorized data manipulations.

How We Are Doing This

The safe_delete stored procedure is designed to manage deletions securely and transparently:

CREATE PROCEDURE safe_delete(IN table_name VARCHAR(255))
BEGIN
    SET @s = CONCAT('DELETE FROM ', table_name, ' WHERE id IN (SELECT id FROM temp_delete_table)');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- Note: The temporary table `temp_delete_table` is not dropped here to allow for auditing.
END
    

Explanation of the Stored Procedure: The procedure constructs a SQL command as a string that deletes records from the specified table where their IDs match those listed in temp_delete_table. This dynamic SQL is prepared and executed within the procedure, ensuring that deletions are based strictly on verified IDs from the temporary table. By not dropping temp_delete_table immediately, we maintain an audit trail that allows developers and auditors to review which records were affected post-operation.

How to Use the safe_delete Functionality

1. Prepare the Temporary Table: Start by creating and populating the temp_delete_table with the IDs of the records you intend to delete.

   CREATE TEMPORARY TABLE IF NOT EXISTS temp_delete_table AS SELECT * FROM [target_table] WHERE [condition];

   Example condition could be status = 'inactive' or any other logical condition that fits the criteria for deletion.
2. Verify the Contents of the Temporary Table: Before proceeding with the deletion, verify the contents of temp_delete_table to ensure it contains exactly the records you intend to delete.

   SELECT * FROM temp_delete_table;

   Thoroughly review the results. This step is critical to avoid the accidental deletion of unintended records.
3. Execute the safe_delete Procedure: Once you confirm the temporary table contains the correct data, execute the stored procedure to perform the deletion.

   CALL safe_delete('target_table_name');

   Replace 'target_table_name' with the actual name of the table from which records are to be deleted.

Importance of Not Immediately Deleting the Temporary Table

Keeping the temporary table after the delete operation allows for immediate and subsequent reviews. This is crucial if issues arise or if detailed audits are needed. Temporary tables exist only during the database session. They are automatically dropped when the session ends, typically removing the need for manual cleanup. Maintaining the temporary table post-operation ensures that detailed data is available for auditing and validating the deletion process.

Best Practices and Reminders

• Session Management: Remember that losing the database session will drop the temporary table. Plan your deletions and reviews accordingly.
• Security Practices: Always use the safe_delete process for deletions to ensure compliance with our data handling and security policies.
• Documentation: Always document the reasoning and specifics of the deletion operation, maintaining clear records for operational and audit purposes.