MySQL Prerequisites and Base Integration Setup

Required prerequisites

There are prerequisite activities that are required to be performed on the MySQL server before the MySQL integration is configured on the ObserveID side. Below is a short overview of the prerequisites followed by the Permissions List defining levels of access.

Prerequisite

Description

MySQL version 8 and later

The MySQL integration supports MySQL version 8 and later. Earlier versions use a different privilege model that is not supported by the current implementation of the integration.

MySQL account

A database user dedicated to ObserveID.

Password

The password of the database user dedicated to ObserveID.

Privileges

Privileges that must be assigned to the database user dedicated to ObserveID. The available privilege assignment options are listed below in the Minimum Permissions List.

Port

Port number for the connection to MySQL. 3306 is the default port.

Minimum Permissions List

The table below outlines the scope of permissions required for integration operations. If the configured access is insufficient, data import and other integration processes may generate warning messages. These warnings can be suppressed by selecting the corresponding option in the integration configuration on the ObserveID side.

Permission

Description

GRANT ALL PRIVILEGES
ON sys.* TO 'username'@'%';
FLUSH PRIVILEGES;

Full read-write access to the given user on the sys. database.

GRANT ALL PRIVILEGES
ON mysql.* TO 'username'@'%';
FLUSH PRIVILEGES;

Full read-write access to the given user on the mysql. database.

GRANT CREATE USER ON *.* TO 'username'@'%';
FLUSH PRIVILEGES;

Ability to create users.

GRANT SELECT
ON mysql.user TO 'username'@'%';
FLUSH PRIVILEGES;
GRANT SELECT
ON performance_schema.* TO 'username'@'%';
FLUSH PRIVILEGES;

Ability to read users.

GRANT SELECT
ON mysql.role_edges TO 'username'@'%';
FLUSH PRIVILEGES;
GRANT SELECT, ALTER, CREATE ROUTINE, ALTER ROUTINE, 
EXECUTE, CREATE TEMPORARY TABLES
ON mysql.* TO 'username'@'%';
FLUSH PRIVILEGES;

Ability to read roles and privileges.

GRANT SELECT, ALTER, CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON sys.* TO 'username'@'%';
FLUSH PRIVILEGES;

Ability to read performance metrics.

GRANT SELECT, ALTER, CREATE ROUTINE, ALTER ROUTINE, EXECUTE
ON local_1.* TO 'username'@'%';
FLUSH PRIVILEGES;

Ability to read a specific database which is the local_1 database.

Connection parameters

The connection parameters specified below must be populated in ObserveID for the Universal Connector to connect to the MySQL server as a data source. The connection parameters are established in: ObserveID > Identity Automation > Integrations > {specific MySQL integration} > Details.

Connection parameter

Description

Environment Type

Environment the new integration pertains to. The Na option establishes no environment.

Integration Name

Automatically generated name for the new integration. The name is created by combining the Integration Type with what is established as Environment Type, Alternate Name, and Description for the new integration.

Alternate Name

Any preferred name for the new integration.

Description

Any valid text to differentiate one integration from another. This text is displayed in addition to the integration name in several UI elements, e.g., dropdown lists, in the system.

Host name or IP address

The host name or IP address of the MySQL server machine.

Port

Port number for connection over MySQL client–server protocol. The default port number is 3306.

MySQL instance name

Database name for the connection.

Login

Account name of the user dedicated for the integration.

Password

Password of the user dedicated for the integration.

Show warnings when importing

Enable the toggle to view warning messages received from the MySQL server during data import.

MySQL integration configurationMySQL integration configuration

Initial data load

After the connection parameters are established for MySQL in ObserveID, and the connection test is successfully completed, next is the first load of data. It allows the systems to set up an initial point starting from which it is possible to determine and synchronize deltas later. For what data is loaded, refer to the schema of MySQL integration data.

A Data Import task is used to perform the initial data load as well as all subsequent data imports.

The Data Import task for the MySQL integration is created automatically when the integration is saved. If required, the task can also be created manually. To execute the Data Import task, navigate to:

  • ObserveID > Identity Automation > Workflows > Tasks

The Data Import task is considered successfully completed when the integration data (including accounts, entitlements, resources, and associated properties) is imported and becomes available within the MySQL integration in ObserveID.