Embedded Files
While Logging and Auditing are often driven by the need for compliance, my approach transcends this requirement. I view these processes as critical tools for gaining insights into our database's usage patterns, identifying potential areas for optimization, and reinforcing security posture. They are not just mechanisms for record-keeping but instruments for continuous improvement and strategic decision-making.
At the heart of the Logging and Auditing architecture are a series of triggers and stored procedures, each playing a specific role in capturing and recording database events. Key components include:
log_company_insert
log_company_insert
This trigger is essential for keeping a record of data insertions in the company table. It ensures that any addition to the table is tracked, providing valuable information about when the data was added, what was added, and the context of the addition. This kind of logging is critical for data integrity, security, and auditability in database management.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_company_insert`
AFTER INSERT ON `company`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('INSERT', 'Company', NEW.name, CONCAT('New row added with ID: ', NEW.id));
END
log_company_delete
log_company_delete
The trigger is designed to create a log entry whenever a company record is deleted from the company table. This trigger plays a crucial role in maintaining a history of database changes, particularly deletions in the company table. It ensures that there's a transparent and traceable record of what data was removed, by whom (as inferred from the activity_log_auto procedure), and when, which is vital for database auditing and tracking changes over time.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_company_delete`
AFTER DELETE ON `company`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('DELETE', 'Company', OLD.name, CONCAT('Deleted Row with ID: ', OLD.id));
END
log_employee_insert
log_employee_insert
This trigger is particularly useful for automatically documenting the addition of new employees in the employee table. By recording each insertion, it provides valuable information for auditing purposes and helps maintain transparency and integrity in the database's employee management.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_employee_insert`
AFTER INSERT ON `employee`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('INSERT', 'Employee', NEW.email_address, CONCAT('New row added with ID: ', NEW.id));
END
log_employee_delete
log_employee_delete
This trigger is particularly useful for keeping a record of deletions in the employee table. It ensures that any removal of employee data is tracked, providing valuable information about when the data was removed, what specific data was removed, and the context of the removal. This kind of logging is critical for data integrity, security, and auditability in database management.
CREATE DEFINER=`root`@`localhost`
TRIGGER `log_employee_delete`
AFTER DELETE ON `employee`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('DELETE', 'Employee', OLD.email_address, CONCAT('Deleted Row with id: ', OLD.id));
END
log_film_insert
log_film_insert
This trigger is particularly valuable for automatically documenting the addition of new film records in the film table. It assists in maintaining a comprehensive history of all additions made to the film data, which is crucial for auditing purposes and for managing the integrity and transparency of the database.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_film_insert`
AFTER INSERT ON `film`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('INSERT', 'Film', NEW.title, CONCAT('New row added with movie title: ', NEW.title));
END
log_film_delete
log_film_delete
This trigger automates the process of logging film deletions, ensuring a consistent and reliable record of all film data removals from the database. It plays a crucial role in ensuring that all deletions are recorded, providing a transparent and traceable history of changes made to the film records.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_film_delete`
AFTER DELETE ON `film`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('DELETE', 'Film', OLD.title, CONCAT('Record of movie,', OLD.title, ', has been deleted'));
END
log_grant_insert
log_grant_insert
The automatic logging facilitated by this trigger is crucial for audit purposes, as it ensures that every addition to the grant_request table is tracked. This can be invaluable for reviewing historical data, ensuring compliance with data governance standards, providing insights into database usage patterns, and enhancing data transparency and accountability.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_grant_insert`
AFTER INSERT ON `grant_request`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('INSERT', 'Grant Request', NEW.title, CONCAT('New row added with ID: ', NEW.id));
END
log_grant_delete
log_grant_delete
This trigger is crucial for maintaining an accurate and complete record of all data deletion activities within the grant_request table. They also provide insights into the patterns of data deletion and help maintain transparency in the database's operation.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_grant_delete`
AFTER DELETE ON `grant_request`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('DELETE', 'Grant Request', OLD.title, CONCAT('Deleted Row with id: ', OLD.id));
END
log_shareholder_insert
log_shareholder_insert
The log_shareholder_insert trigger is instrumental in maintaining a detailed and transparent record of shareholder additions. It enhances the database's auditing capabilities by ensuring that every new entry into the shareholder table is systematically tracked.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_shareholder_insert`
AFTER INSERT ON `shareholder`
FOR EACH ROW
BEGIN
CALL `log_insert_procedure`('INSERT', 'Shareholder', NEW.national_insurance_number,
CONCAT('New row added with ID: ', NEW.id));
END
log_shareholder_delete
log_shareholder_delete
This trigger is essential for maintaining an accurate and complete record of all data deletion activities within the shareholder table which provides a reliable mechanism for maintaining a detailed and accurate audit trail. They provide insights into the patterns of shareholder data deletion and help maintain transparency and accountability in database operations.
CREATE DEFINER=`root`@`localhost` TRIGGER `log_shareholder_delete`
AFTER DELETE ON `shareholder`
FOR EACH ROW
BEGIN
CALL `activity_log_auto`('DELETE', 'Shareholder', OLD.national_insurance_number,
CONCAT('Deleted Row with id: ', OLD.id));
END
log_salary_insert
log_salary_insert
The log_salary_insert trigger is a pivotal component of the database's auditing and logging mechanism, serving two critical functions:
Enhanced Auditability: By automatically logging each payroll record insertion with detailed information, it significantly enhances the audit trail's granularity, allowing for better tracking and verification of database changes related to staff compensation.
Data Integrity and Security: The automated logging of payroll changes contributes to data integrity by providing a reliable record of all salary adjustments, which is essential for financial transparency and compliance.
CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`log_salary_insert`
AFTER INSERT ON `staff_salary` FOR EACH ROW
BEGIN
DECLARE email VARCHAR(115);
SELECT email_address INTO email FROM employee e, staff_salary s
WHERE e.id = s.staff_id AND e.id = NEW.staff_id;
CALL `activity_log_auto`('INSERT', 'Staff_Salary', NEW.staff_id, CONCAT('Added new payroll record with staff email: ', email));
END
log_salary_delete
log_salary_delete
By systematically recording deletions of salary data, the trigger aids in safeguarding data integrity and supports adherence to regulatory standards and internal policies regarding financial and personnel data management. Automatically logging each deletion of payroll records with detailed specifics, significantly improving the audit trail's comprehensiveness and facilitating effective tracking and verification of crucial database transactions.
CREATE DEFINER = CURRENT_USER TRIGGER `movie_production_companies`.`log_salary_delete`
AFTER DELETE ON `staff_salary`
FOR EACH ROW
BEGIN
DECLARE email VARCHAR(115);
SELECT email_address INTO email FROM employee e, staff_salary s
WHERE e.id = s.staff_id AND e.id = OLD.staff_id;
CALL `activity_log_auto`('DELETE', 'Staff_Salary', OLD.staff_id, CONCAT('Deleted payroll record with staff email: ', email));
END
The Root of it all
The activity_log_auto Stored Procedure
The activity_log_auto stored procedure is designed to automatically log different types of activities (like insertions, deletions, and updates) that occur within the movie_production_companies database. It serves as a universal logging mechanism that can be invoked from various triggers (as the ones seen above) or other database operations to record significant events.
Parameters:
log_type (VARCHAR(50)): Represents the type of log entry, such as 'INSERT', 'DELETE', or 'UPDATE'.
entity (VARCHAR(50)): Specifies the database entity (table or view) where the activity occurred.
key_column (VARCHAR(114)): Identifies a key attribute or column associated with the log entry, typically a primary key or unique identifier.
log_description (VARCHAR(200)): Provides a detailed description of the activity being logged.
Functionality
On invocation, the procedure inserts a new record into the activity_log table.
The record includes the type of event, the name of the entity involved, a key attribute for reference, a description of the event, the user who executed the action (obtained using CURRENT_USER()) and the time the event as executed.
CREATE DEFINER=`root`@`localhost` PROCEDURE `activity_log_auto`(
IN log_type VARCHAR(50),
IN entity VARCHAR(50),
IN key_column VARCHAR(114),
IN log_description VARCHAR(200)
)
BEGIN
INSERT INTO activity_log (event_type, table_name, key_attribute, event_description, user)
VALUES (log_type, entity, key_column, log_description, CURRENT_USER());
END
Importance in Auditing
The logs generated by activity_log_auto are invaluable for auditing purposes, providing a clear and traceable record of all significant activities and changes in the database.
They assist in understanding the sequence of events, identifying potential issues, and ensuring accountability.
Conclusion
Conclusion
The activity_log_auto stored procedure is a cornerstone of the movie_production_companies database's logging and auditing strategy. Its consistent use across triggers and other database operations ensures that all significant data manipulations are recorded, thereby upholding the integrity and transparency of the database system.
Page updated
Google Sites
Report abuse