F Transaction Log Audit Data Collection for MySQL

This chapter explains how to configure Oracle GoldenGate for MySQL databases and how to create transaction log audit trails in the Oracle Database Security Central console.

Introduction to the Transaction Audit Log Trail Using Oracle GoldenGate for MySQL

Change Data Capture (CDC) in MySQL records the insert, update, and delete operations that are performed on the data in the tables of the MySQL server.

The Transaction Log collector takes advantage of Oracle GoldenGate’s Extract process (CDC Capture) to pull CDC table data into XML files. Oracle Oracle Database Security Central’s Transaction Log collector for MySQL collects transaction log records from generated XML files. These logs are forwarded to the Audit Vault Server to show the before and after values that are changed in the Data Modification Before-After Values report. The DML changes are available in the Data Modification Before-After Values report.

Starting with Oracle Oracle Database Security Central 20.10, the Data Modification Before-After Values report has additional information about key columns. GoldenGate, by default, uses the primary key columns of the table as key columns. If no primary keys are defined for the table, or if you want to use some other columns as key columns, then GoldenGate provides an option to specify key columns in the parameter file.

Figure ?: Figure F-1 Transaction Log Collection Process

Description of the illustration transaction_log_collector_process.png

Note: Oracle GoldenGate for MySQL does not capture certain details, such as the program name, database username, OS username, OS terminal, client host name, client ID, process ID, and proxy session ID for transactions committed in the database. As a result, this limitation will cause the Oracle Oracle Database Security Central’s Data Modification Before-After Values report to display empty values for these fields.

Sizing Guidelines

Follow these sizing guidelines to configuring Oracle GoldenGate for MySQL.

Prerequisites

Follow the system and sizing requirements in What is Required? in the Oracle GoldenGate documentation.

General Sizing Guidelines

• For memory and CPU, start with 32 GB of memory and 2 CPUs per Extract, because it’s a multithreaded process and uses a large amount of memory when processing large transactions. Depending on the transaction volume and pattern, scale up the resources appropriately following the guidelines in the Oracle GoldenGate documentation.

• For disk space, start with 2 TB, and vary it based on the volume of data that the Extract captures from the source databases. The Extract uses storage for trail files and temporary disk space for cache files if there’s a big transaction to buffer for processing.

Temporary disk space requirements due to large transactions may fill up the cache and spill over to the transaction-cached data or temporary files. Configure an archive policy and define the retention period of the files so they can be recycled accordingly.

Maintain enough physical memory to handle large transactions. According to the guidelines, have at least 32 GB of memory available for the Extract to use. For a more accurate estimation, collect the statistics from the database server history run and check for the size of the biggest transaction. Oracle GoldenGate provides the send <extract> cachemgr, cachestats command that displays the statistics of the transaction, which is helpful to determine the baseline for estimation.

In general, the sizing, storage, and memory for the Oracle GoldenGate Extract process is highly dependent on the transaction volume and transaction pattern. Collect these statistics from every single database server to estimate, because there’s no standard value. The number of databases that can be supported by a single GoldenGate instance or Extract process depends on the system resources that support multiple Extracts. Configure one Extract for every database.

See Also:

• Other Disk Space Considerations

• Temporary Disk Requirements

Restricted Use License for Oracle GoldenGate

A restricted-use license for Oracle GoldenGate is included with Oracle DBSecCentral.

This license permits you to install Oracle GoldenGate and use the Extract process to capture transactional changes in database systems that are monitored by Oracle DBSecCentral. The extracted data from Oracle GoldenGate is consumed only by Oracle DBSecCentral. Deploy Oracle GoldenGate Microservices Architecture on a separate server other than the server on which the Oracle DBSecCentral appliance is deployed. Then configure the Oracle GoldenGate Extract feature. Oracle Database Security Central supports Oracle GoldenGate Microservices Architecture version 23.4.2.24.06 and later for MySQL version 8.4.

Installing Oracle GoldenGate for MySQL Database

Follow these instructions to install Oracle GoldenGate on MySQL Server.

For MySQL-compatible databases on Linux platform

1. Follow the instructions for Installing Oracle GoldenGate in the Oracle GoldenGate MicroServices Documentation.

2. Verify the requirements to install Oracle GoldenGate for MySQL in the Oracle GoldenGate MicroServices Documentation.

3. Download and install Oracle GoldenGate Microservices architecture version 23.4.2.24.06 or later for MySQL-compatible Databases on Linux x86-64 from Oracle Software Delivery Cloud.

Capturing Transaction Log Data from MySQL Server

To configure the database and CDC extract process on Oracle GoldenGate for a MySQL database, follow the below steps.

1. Creating Users and Privileges

2. Preparing Database Connection, System, Parameter, and Transaction Log Settings

3. Configuring the Database for Oracle GoldenGate

4. Creating the GoldenGate CDC Extract

Creating Users and Privileges

Oracle GoldenGate recommends having a separate user for it. This can be the same user for all Oracle GoldenGate processes that must connect to a database. For more information, see Prepare Database Users and Privileges in the Oracle GoldenGate Microservices Documentation.

Preparing Database Connection, System, Parameter, and Transaction Log Settings

To configure the database and its connection, see Prepare Database Connection, System, and Parameter Settings in Oracle GoldenGate Microservices Documentation.

To configure the transaction log settings, see Transaction Log Settings and Requirements in Oracle GoldenGate Microservices Documentation.

Configuring the Database for Oracle GoldenGate

Following the installation of Oracle GoldenGate, you will need to configure the MySQL database.

1. Open the console page of the Administration Service.

2. Select Application Navigation menu.

3. Select Configuration.

4. Select the Database option.

5. Select Add Credential (plus icon) to add the database credentials.

6. Enter the credential domain.

7. Enter any credential aliases.

8. Enter the database server address.

9. Enter the database port number.

10. Enter the database name.

11. Enter the user ID.

12. Enter the password.

13. Select Submit.

14. After creating the credential, select Log in database icon. This ensures that the newly created credential is able to connect to the target database.

Creating the GoldenGate CDC Extract

Create and run the CDC capture for MySQL.

1. Open the Console page of Administration Service.

2. Select Administration Service.

3. Select Add Extracts (plus icon).

4. Select the extract type as Change Data Capture Extract

5. Select Next.

6. Enter the process name in the Process Name field.

7. Select Unidirectional in the Intent field.

8. If the extract is going to do remote capture, select Remote.

9. Choose the appropriate Credential Domain from the drop-down list.

10. Choose the appropriate Credential Alias created in Configuring the Database for Oracle GoldenGate from the drop-down list.

11. Select Now from the drop-down list in the Begin field.

12. Enter the Trail Name. It can be a combination of any two alphabetic characters.

13. Enter the Trail Subdirectory if customization of the Trail Subdirectory is needed.

    The trail subdirectory can be the full path of any directory. This directory must already exist in the file system.

14. Set the Trail Size (in MB).

    Note: In case the record generation rate of GoldenGate is low (less than 50 records per second), then it is recommended to set the Trail Size to lower values. For example, 100MB.

15. Select Next.

16. In the Parameter File subsection, enter the below parameters:

    EXTRACT <extract name>
    SOURCEDB <Database Name@Database Server:port> USERIDALIAS <useralias>, DOMAIN <Domain name>
    OUTFORMAT XML _AUDIT_VAULT
    TRANLOGOPTIONS ALTLOGDEST REMOTE
    EXTTRAIL <subdirectory>/<trail name>
    TABLE <schema>.<table name>,GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL);

    For example:

    EXTRACT exta
    SOURCEDB HR@10.245.102.35:3306 USERIDALIAS  mysql, DOMAIN OracleGoldenGate
    OUTPUTFORMAT XML _AUDIT_VAULT
    TRANLOGOPTIONS ALTLOGDEST REMOTE
    EXTTRAIL xy
    TABLE HR.*,KEYCOLS(id,gid),GETBEFORECOLS (ON UPDATE ALL, ON DELETE ALL);

    Note the following parameter guidelines:

    • There is space between XML and _AUDIT_VAULT in the OUTPUTFORMAT parameter.

    • The OUTPUTFORMAT parameter must be mentioned before the exttrail parameter in the parameter file. Otherwise, the XML files are not generated.

    • Ensure the TABLE command always ends with a semicolon (;).

    • Ensure the sequence of all the parameters is in the exact order as mentioned above.

    • The TABLE command is used to specify the tables for which DML changes need to be captured.

    • The REMOTE keyword should be used only in case of remote capture, where Oracle GoldenGate and MySQL are installed on different machines.

    • To get more information about Oracle GoldenGate parameters, see Oracle GoldenGate Parameters in the Reference for Oracle GoldenGate guide.

17. Select Create and Run to start the CDC Extract process.

    The newly created CDC Extract appears in the Extracts section on the Administration Service page.

18. To view the status of the CDC Extract:

    1. Select Actions for the extract.

    2. Select Details.

    3. Select Report to view the diagnostic messages.

       If the extract process fails, this report displays the relevant errors.

Sample Oracle GoldenGate CDC Extract Parameter Files

Use these Oracle GoldenGate CDC Extract parameter files as samples.

Audit DML for a table and set the columns to be used as key columns

The following parameter file configures CDC Extract to do the following:

• Capture DML operations on the emp table in the dbo schema.

• Set empno and ename as key columns.

EXTRACT <extract name>
SOURCEDB <Database Name@Database Server:port> USERIDALIAS <useralias>, DOMAIN <Domain name>
OUTFORMAT XML _AUDIT_VAULT
EXTTRAIL <subdirectory>/<trail name>
TABLE dbo.emp, KEYCOLS (empno, ename);

Audit DML in table

The following parameter file audits DML operations on the required tables:

• The parameter file provided is for a single table.

• Additional table names can be added by the user.

EXTRACT <extract_name>
SOURCEDB <Database Name@Database Server:port> USERIDALIAS <useralias>, DOMAIN <Domain name>
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL <subdirectory> {Any combination of two alphabets indicating prefix of trail file e.g. ab, bc, ea, sn.....etc}
TABLE owner.table_name;

Example: The following parameter file audits DML operations on the dbo.employee table. The audit data will be stored in the \dirdat\ea location:

EXTRACT exta
SOURCEDB HR@10.245.102.35:3306 USERIDALIAS mysql, DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL \dirdat\ea
TABLE dbo.employee;

• exta is the name of the CDC Extract.

• In dbo.employee, dbo is the schema name that owns the employee table.

• HR is the name of the database.

• 10.245.102.35 is the IP of the host on which database is installed.

• 3306 is the port number of the MySQL database.

• mysql is the USERIDALIAS

• OracleGoldenGate is the DOMAIN.

Audit DML with GETBEFORECOLS option

The following parameter file configures the Extract process to capture DML operations on a specific table with the GETBEFORECOLS option enabled. This option ensures that key columns appear in the before image of the audit file generated by Oracle GoldenGate, which is essential for displaying key columns in reports for update and delete operations.

EXTRACT exta
SOURCEDB HR@10.245.102.35:3306 USERIDALIAS mysql, DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL ea
TABLE dbo.employee, GETBEFORECOLS(ON UPDATE ALL, ON DELETE ALL);

Use GETBEFORECOLS to specify the columns to be captured and written to the before image of the trail. In the above example, the ALL keyword indicated that all columns should be included in the before image for update and delete operations.

Audit DML with KEYCOLS option

The following parameter file configures the Extract process to capture DML operations on a specific table using the KEYCOLS option. This option is used to define a substitute primary key when a primary key or an appropriate unique index is not available for the table.

EXTRACT exta
SOURCEDB HR@10.245.102.35:3306 USERIDALIAS mysql, DOMAIN OracleGoldenGate
OUTPUTFORMAT XML _AUDIT_VAULT
EXTTRAIL ea
TABLE dbo.emp3, KEYCOLS(id.name), GETBEFORECOLS(ON DELETE ALL);

The above parameter file audits DML operations on the dbo.employee table. The KEYCOLS option is used to treat the id and name columns together as a unique primary key or substitute index (KEYCOLS(id, name). The audit data will be stored in the ea trail. The format for KEYCOLS is KEYCOLS(column1, column1, ...).

Related Topics

• Adding Audit Trails with Agent-Based Collection

Guidelines for Creating Audit Trails in the Oracle Database Security Central Console

Follow these guidelines for creating transaction log audit trails for MySQL database targets in the Oracle Database Security Central console.

Recommendations

1. Before creating the audit trails, Oracle recommends that you set the AV.COLLECTOR.TIMEZONEOFFSET attribute for the MySQL database target in the Oracle Database Security Central console, because the transaction log audit trail gets the timezones of audit records from the target. Set AV.COLLECTOR.TIMEZONEOFFSET to the timezone offset of the MySQL database. For example: +03:00 for positive offset and -03:00 for negative offset.

   See Registering Targets for more information.

2. Oracle also recommends, that you set the AV.COLLECTOR.securedTargetVersion attribute for the MySQL database target in the Oracle Database Security Central console. This attribute specifies the version of the MySQL database. If this attribute is not set, by default MySQL will be treated as version 8.0.

Guidelines

Use the following guidelines when you create audit trails according to the steps in Adding Audit Trails with Agent-Based Collection:

• For Trail Type, select TRANSACTION LOG.

• For Trail Location, enter the full path of the directory that contains the CDC Extract XML files.

• Ensure that the Audit Vault Agent is running on the host machine that has access to the trail location.

• Ensure that the Audit Vault Agent user has read permission for the trail location.

Cleaning Up Audit Trails

Audit trail cleanup involves deleting the files that are read by the Audit Vault Agent.

See Cleaning up Oracle GoldenGate Extracts.