MySQL Plugin for Exact Data Match

This document explains how to configure the MySQL EDM plugin v1.1.0 with the Exact Data Match module of the Netskope Cloud Exchange platform. Use this plugin to pull database records from a MySQL Database and generate Exact Data Match (EDM) hashes according to the defined plugin configurations.

Prerequisites

To complete the configuration, you need:

• A Netskope tenant (or multiple, for example, production and development/test instances).
• A Netskope Cloud Exchange tenant with the Tenant plugin and Exact Data Match plugin already configured.
• MySQL Server database user with read-only (Select) access to fetch the data.

MySQL EDM Plugin Support

This plugin fetches MySQL Database records from a MySQL Database via SQL query and generates Exact Data Match (EDM) hashes according to the defined plugin configurations. The plugin supports advanced data sanitization, normalization, and hash generation capabilities to ensure data quality and security compliance.

MySQL Database Permissions

Required Permissions

• Database user must have read permissions for the specified database.
• Network connectivity to the MySQL server on the configured port (default: 3306).
• Sufficient disk space on Cloud Exchange for temporary file processing.

from sqlalchemy import create_engine
eng = create_engine(
       connection_string, connect_args={"connect_timeout": CONNECTION_TIMEOUT}
)  # creates connection with database.

with eng.connect() as connection:
      # used to stop user from executing any database modification query.
      result = connection.execute(text("START TRANSACTION READ ONLY;"))
      query = text(config["query"])
      result = connection.execute(query)

# csv_path will be new csv file
# Fetch 100,000 rows per batch
while True:
    rows = result.fetchmany(BATCH_SIZE)
    if not rows:
        break  # No more rows to fetch
    #store rows into csv file
    self.store_data_to_csv(rows, csv_path)

Performance Matrix

Here is the performance reading conducted for fetching and sanitizing ~1M Rows (25 columns, per column ~50 characters long string,1.3 GB size, Avg Column Uniqueness: ~96%, Avg Row Uniqueness: ~96%) from Database table on a Large CE instance with these specifications:

Workflow

1. Configure MySQL Server.
2. Configure MySQL EDM Plugin.
3. Configure sharing between MySQL EDM Plugin and Tenant.
4. Validate the MySQL plugin.

Watch a Video

Click play to watch a video.

Configure MySQL Server

For a MySQL database, you must create a MySQL user account with read-only permissions granted to the specific database and tables from which the data will be pulled.

Follow the below steps to create the user on MySQL Database:

1. Enable MySQL server for remote connection.

2. Expose the Port on which MySQL server is hosted to ensure MySQL server is accessible on remote machines.

3. Create a read-only user on the MySQL server by following the below steps:

   • Login to MySQL server as an Administrator: mysql -u <root> -p

     • Enter the <root> user password

   • Create a new User: CREATE USER '<new_username>'@'%' IDENTIFIED BY '<secret>';

   • Grant Read Only Permissions for particular databases: GRANT SELECT ON <database_name>.* TO '<new_username>'@'%';

     • The above command grants the user read-only access to the database from the localhost only. If you know the hostname or IP address of the host that the collector will be installed on, type the following command:

       GRANT SELECT ON <database_name>.* TO '<new_username>'@'<host_name> or <ip_address>';

4. Save the changes

   FLUSH PRIVILEGES;
   SHOW GRANTS FOR '<new_username>'@'<host_name> or <ip_address>'

Configure MySQL EDM Plugin

1. In Cloud Exchange, go to Settings > Plugin Store.

2. Search for and select the MySQL v1.1.0 (EDM) plugin.

   

3. Enter the Basic Information:

   • Configuration Name: Enter a name appropriate for your plugin.

     The configuration name you provide will be used to give the name to the generated hash file to share with the Netskope tenant. Be aware that if you later delete this configuration and create a new one with the same name, the hash file may already exist in the Netskope tenant. In such cases, attempting to push the hash file to the tenant will result in an error, as the file with the same name already exists. Consider using a unique name to avoid this issue.

   • Sync Interval: Adjust Sync Interval as per your requirement. (Default: 12 hours)

   

4. Click Next and enter the Configuration Parameters:

   • Server IP/Hostname: Provide IP or hostname of My SQL Server.

   • Username: Provide MySQL database username to access the database and its table.

   • Password: Provide password associated with the username.

   • Database Name: Provide a database name from which you want to retrieve data.

   • Port: Provide TCP port on which your MySQL database is running.(Default Port: 3306)

   • Query: Provide a read-only query to fetch data from database.(Read-only means ‘Select’ Query)

   • Remove Quotes: Enable this if your data file encloses fields in double quotes, especially when values contain commas. Quoted fields will be parsed as single columns. Improper quote placement may cause rows to be skipped. Helpful for CSVs exported from databases.

     By default, quotes are treated as literal text. Enable Remove Quotes toggle if your CSV uses double quotes to encapsulate fields that contain commas (like "123 ABC Street, Suite 100"). This ensures the field is treated as a single column. Note that this mode requires strict CSV formatting. If a field starts with a quote, any character, including a space, following the closing quote but preceding the comma (like "Word",) will cause the row to be skipped.

   

5. Click Next and enter the Hash Generation and Sanitization Parameters:

   • Select the appropriate options for sanitization and hash generation operations:

     • Sanitization (Name Column): Sanitize the content by checking the Name Column checkbox. (Default: Unchecked). The Sanitization Process performs the following actions:
       • One character: The cell will be marked as invalid if it contains only one character.
       • Digits: Cells containing digits will be marked as invalid.
       • Stopwords: Cells that match a stopword from the list will be marked as invalid (This works only if the Remove stopwords checkbox is checked).
       • Non-alphanumeric characters: Remove all special characters to validate the cell.
     • Hash Generation is divided into two parts:
       • Normalization: Select the value from dropdown to normalize the data value. (Default: None)
       • Create Dictionary: Select value from dropdown to create dictionary of unique values for selected field that can be used in DLP rule in netskope tenant. (Default: None)

   • Remove Stopwords: Mark as checked if you want to remove certain stopwords as part of the sanitization process. (Default:Unchecked) Ensure that Name Column is checked for the applicable field to reflect the changes.

   Note

   • Use Normalization parameter to normalize the fetched results. For example, a number such as 123-45-6789 or 123 45 6789 will be treated as 123456789. Number normalizations ignore characters such as dots, dashes and spaces. A string normalization ignores the case sensitivity of the letters.
   • Use the Create Dictionary option only when necessary, as this operation is resource-intensive and may impact system performance. Choose this option thoughtfully for optimal efficiency.

   

6. Click Next. Preview the sanitization sample output by clicking Preview Good File or Preview Bad File.

   

   If you are using the plugin with sanitization ON then the performance may be differ due to extra processing.

7. Proceed without sanitization: Uncheck this option to proceed with sanitization. (Default: Checked)

   All the data will be under consideration for hash generation if this option is Unchecked; otherwise, only the Good File content will be part of the hash generation.

8. Click Save. You will be redirected to Exact Data Match > Plugins page where you can see your configured plugin.

   

Configure an EDM Sharing Configuration for MySQL

A sharing configuration is used to share the generated EDM hashes with the destination platform. To share EDM hashes with your Netskope tenant, create a sharing configuration using these steps:

1. Go to Exact Data Match > Sharing and click Add Sharing Configuration.

   

2. Configure the sharing parameters:

   • Source Configuration: Select the configured MySQL EDM plugin.
   • Destination Configuration: Select a destination where EDM Hash will be shared.
   • Target: The value is automatically set according to the selected Destination Configuration.

   

3. Click Save.

Validate the MySQL EDM Plugin

Validate the Pull

To validate the pulling of the configured plugin in Cloud Exchange, go to Settings > Logging and search for the MySQL EDM plugin logs.

You can verify the plugin operation from the logs available at Logging in Cloud Exchange:

The status values are as follows:

• Scheduled: Indicates that the sharing has been configured, and the pull and push operations are waiting in the queue for processing.
• Generating Hash: Indicates that the hash generation process has started. This stage includes fetching > validating > sanitization (if enabled) > generating hash.
• Uploading Hash: Indicates that uploading the hash to the destination configuration has started.
• Upload Completed: Indicates that hashes are uploaded to the destination configuration.
• Checking Apply Status: Checking the apply status of hashes to the destination configuration.
• Apply In Progress: The hash process has started and is in progress on the destination.
• Completed: Indicates that the hash file has been pushed successfully to the destination configuration.
• Failed: Indicates that the final result of the action has failed to execute. The actions are Generating Hash/Uploading Hash/Checking Apply Status.

Shared EDM hashes can be verified from the logs available at Logging in Cloud Exchange:

Validate the Push to your Netskope Tenant

To ensure the push of EDM hashes on the Netskope Tenant from the cloud exchange:

1. Log in to your Netskope Tenant, go to Policies > DLP.

   

2. Click Edit Rules and select Data Loss Prevention.

   

3. On the Exact Match tab, a list of files is shown.

   

Troubleshooting the MySQL Plugin

Unable to configure the MySQL plugin

If you are unable to configure the MySQL EDM plugin, it could be due to one of the following reasons:

• Incorrect credentials provided.
• The user doesn’t have required permissions.
• Incorrect hostname/server IP address.
• Incorrect Database Name.

What to do:

1. Make sure to provide the correct credentials.
2. Make sure that the user has the required permissions for the database and table.
3. Make sure that the correct hostname/server IP.
4. Make sure that the database name is correct.

CSV file not found or access denied

If you receive errors about file not found or access denied:

What to do:

1. Verify the CSV file path is correct and the file exists.
2. Ensure the SSH user has read permissions for the file.
3. Check if the file is not locked by another process.

Hash generation fails or takes too long

If hash generation fails or performance is poor:

What to do:

1. Check available disk space on Cloud Exchange (ensure at least 2x CSV file size).
2. Reduce CSV file size or split into smaller files.
3. Disable sanitization if not required to improve performance.
4. Monitor system resources during processing.

Known Behaviors

• Sanitization can reduce processing speed by approximately 65% but improves data quality.
• Dictionary creation is resource-intensive and should only be used when necessary.
• The plugin creates temporary files during processing that are automatically cleaned up.
• SSH connection timeouts may occur with very large files; consider increasing timeout values.
• Memory usage scales with CSV file size; monitor system resources during large file processing.

Limitations

Each Netskope tenant has a limit of handling up to 5 staging files. If this maximum limit is reached, you may encounter the following error while sharing hashes:

EDM Netskope Exact Data Match [EDM Netskope] Received exit code 400, Error occurred while uploading edm hashes of configuration Linux EDM to the configuration EDM Netskope. 

To resolve this error, you have to delete the existing files from staging.