$2^{nd}$ Project Title: Database Audit Trail Implementation for

Almaraee Dairy Product Company$($DUE DATE $15$ May$)$

Note:use TOAD if you feel convenient $($optional$)$

Project Description:

In this project, you will design and implement an audit trail feature using Oracle $19$c DB and SQL$*$Plus for the dairy product company "Almaraee" in Saudi Arabia. The audit trail will help monitor and track user activities, executed statements, accessed objects, and database connections to enhance database security.

Requirements:

First of all change the SPFILE server parameter to have Arabic interface by add the NLS$\_$Lang $=$ ARABIC$\_$UNITED ARAB EMIRATES.

AR$8$MSWIN$1256$

Enable Audit Trail:

Configure the necessary parameters in the SPFILE $($Server Parameter File$)$ to enable auditing.$(\text{'}$DB OR OS OR XML$)$

Set the audit trail mode to capture user, statement, object, and connection audit information.

User Auditing:

Audit user activities, such as logins$,$ logouts$,$ and privilege changes.

Track user creation, modification, and deletion events.

Capture failed login attempts and unauthorized access.

Statement Auditing:

Audit executed SQL statements, including DML $($Data Manipulation Language$)$INSERT$,$UPDATE,SELECT,DELETE and DDL $($Data Definition Language$)$ CREATE AND DROP operations.

Monitor critical database operations, such as data modifications and schema changes.$($integrity related issues$)$

Capture statements issued by privileged users.

Object Auditing:

Audit access to important database objects, such as tables, views, and procedures.

Track modifications to sensitive data or critical database objects.

Monitor object$-$level activities by both privileged and nonprivileged users.

Connection Auditing:

Audit database connections, including successful and failed connection attempts.

Track connection sources, such as IP addresses and client applications.

Capture details about connected users and their activities.

Reporting and Analysis:

Develop SQL queries to generate audit trail reports.

Analyze the audit data to identify security incidents or suspicious activities.

Provide recommendations for enhancing database security based on the audit findings.

Deliverables:

SPFILE Configuration: Document the necessary parameters and their values in the SPFILE to enable audit trail functionality.

SQLPlus Scripts: Write SQLPlus scripts to enable and configure audit trail settings, as well as generate audit reports based on specific criteria $($e$.$g$.,$ user, statement, object, connection$).$

Documentation: Provide a comprehensive documentation explaining the design choices, implementation steps, and usage of SQL$*$Plus scripts. Include examples of sample audit reports and any recommendations for improving database security.

Note: Ensure that the audit trail implementation adheres to relevant security standards and best practices. Consider the privacy and confidentiality of the collected audit data and implement appropriate access controls.