lab5_DBA.pdf

❑ What is Database Audit? Database auditing is the process of monitoring and recording database activities. It involves tracking user actions, system events, and data modifications to ensure data integrity, security, and compliance with regulations ❖ For MySQL, auditing helps administrators det...

❑ What is Database Audit? Database auditing is the process of monitoring and recording database activities. It involves tracking user actions, system events, and data modifications to ensure data integrity, security, and compliance with regulations ❖ For MySQL, auditing helps administrators detect unauthorized access, track changes, and maintain an audit trail for forensic examination. ❑ Auditing database activities is an important part of strengthening database security. This involves identifying vulnerabilities, such as default or weak login credentials, excessive user and group privileges, and unpatched databases. Attackers exploit such vulnerabilities to achieve their own goals, like privilege escalation, SQL injection, and DoS attacks. As a result, there is an increased need to audit important database activities for security and compliance reasons. ❑ server_audit plugin in MySQL is a powerful tool for auditing various activities such as logins, queries, and schema changes. ✓ Install the server_audit Plugin : Place the file server_audit.dll in the plugin directory. You can run SHOW VARIABLES LIKE 'plugin_dir'; to find the correct path. Activate the plugin by adding plugin_load_add = server_audit to my.cnf or by running INSTALL PLUGIN server_audit SONAME ‘server_audit.dll’; once. ✓ Check if the Plugin is Already Installed : Use this command “SHOW PLUGINS;” ❑ Enable the Plugin: Enable the plugin by setting the server_audit_logging variable to 'ON’: Use this command SET GLOBAL server_audit_logging = 'ON’; ❑ Specify What to Audit: You can configure which events to audit, such as connections, queries, and table access. The options are CONNECT, QUERY, TABLE, and ERROR. Here’s how to enable auditing for connections and queries: Use this command SET GLOBAL server_audit_events = 'CONNECT,QUERY’; ❑ Set the Log File Path: Specify the path where the audit logs should be stored: Use this command SET GLOBAL server_audit_file_path = '/var/log/mysql/server_audit.log'; ❑ Filter by Users: You can limit the audit to specific users. For example, to audit only activities by the admin user: Use this command SET GLOBAL server_audit_incl_users = 'admin’; Alternatively, you can exclude specific users from being audited: Use this command SET GLOBAL server_audit_excl_users = 'guest’; ❑ Set the Audit Log Format: You can choose between the traditional or JSON format for the audit logs. JSON format is easier to parse and analyze programmatically: Use this command SET GLOBAL server_audit_output_type = file'; 1) Audit Logging ❑ MySQL provides built-in audit logging capabilities through its audit plugin. This feature allows you to capture various types of events, including: ❖ User logins and logouts , SQL queries executed , Schema changes , Data modifications ❑ To enable audit logging, you need to install and configure the audit plugin. Here’s an example of how to enable it: INSTALL PLUGIN audit_log SONAME 'audit_log.so’; SET GLOBAL audit_log_file = '/var/log/mysql/audit.log’; SET GLOBAL audit_log_policy = 'ALL’; After executing these commands, MySQL will start logging audit events to the specified file. 2) Event Filtering ❑ Not all database activities require auditing. MySQL allows you to filter events based on various criteria, such as: ❖ User accounts , Database objects , Event types Here’s an example of how to set up event filtering: SET GLOBAL audit_log_include_accounts = 'user1@localhost,user2@localhost’; SET GLOBAL audit_log_exclude_databases = 'test,temporary’; This configuration will audit activities for user1 and user2, excluding events in the ‘test’ and ‘temporary’ databases. 3) Log Analysis Once you’ve collected audit logs, analyzing them is crucial. MySQL provides tools and techniques for efficiently analyzing logs: ❖ mysqlbinlog: A utility for processing binary log files ❖ MySQL Enterprise Monitor: A comprehensive monitoring solution ❖ Custom scripts using programming languages like Python or Perl For instance, you can use the following command to view the contents of a binary log file: mysqlbinlog /var/lib/mysql/mysql-bin.000001 | less This command displays the contents of the binary log, allowing you to review database changes and events.  Steps to Enable Auditing in SQL Server : 1. Create a Server Audit This step involves creating a server audit object that defines where the audit data will be stored. USE master; GO CREATE SERVER AUDIT ServerAudit TO FILE (FILEPATH = 'C:\Audit\’); GO -- Enable the audit ALTER SERVER AUDIT ServerAudit WITH (STATE = ON); GO 2. Create a Server Audit Specification This specifies what actions to audit, such as login attempts. USE master; GO -- Create a server audit specification for login events CREATE SERVER AUDIT SPECIFICATION AuditLogins FOR SERVER AUDIT ServerAudit ADD (FAILED_LOGIN_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP); GO -- Enable the audit specification ALTER SERVER AUDIT SPECIFICATION AuditLogins WITH (STATE = ON); GO 3. Create a Database Audit Specification This allows you to audit specific actions on a particular table. USE YourDatabase; GO -- Create a database audit specification for data modifications CREATE DATABASE AUDIT SPECIFICATION AuditDataModifications FOR SERVER AUDIT ServerAudit ADD (INSERT, UPDATE, DELETE ON dbo.YourTable BY PUBLIC); GO -- Enable the audit specification ALTER DATABASE AUDIT SPECIFICATION AuditDataModifications WITH (STATE = ON); GO 4. Review the Audit Logs Once auditing is enabled, SQL Server will log the specified activities in the audit log file. You can review these logs using the following query: SELECT *FROM sys.fn_get_audit_file ('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT);GO 1. Enable auditing for specific tables or actions (logins, data modifications) in mysql. 2. Analyze audit logs to identify potential security breaches in mysql. 3. Set up alerts for suspicious events in mysql