Prepare for MySQL integration

Before ingesting MySQL metadata into Dawiso, create a dedicated MySQL user and grant it access to the metadata of the databases you want to ingest.

Dawiso reads metadata only from INFORMATION_SCHEMA. It doesn’t read the data stored in your tables and doesn’t change anything on your server. MySQL shows an object in INFORMATION_SCHEMA only to users who hold a privilege on that object, so the user needs the privileges below even though Dawiso never queries your data. Apart from SELECT on views, which MySQL requires to show view definitions, none of these privileges lets the user read data or change anything.

Dawiso Cloud currently supports the traditional authentication method using credentials.

Supported MySQL versions

8.0 and later, including 8.4 LTS

Connection prerequisites

You have the global CREATE USER privilege or the INSERT privilege for the mysql system schema.
You hold each privilege you grant below WITH GRANT OPTION. MySQL only lets you grant privileges you have yourself.

Create a new user

First, create a new dedicated user using the following statement:

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

Replace user_name and password with your values. The % host lets the user connect from any address. To restrict it, replace % with the IP address Dawiso connects from. Refer to the official MySQL documentation for more details.

Tip

To ingest metadata from your on-premises sources, make sure to whitelist Dawiso IP address for Dawiso to connect to your internal sources (pull method). The same IP should be whitelisted for Dawiso Integration Runtime (DIR) to send data from your on-premises infrastructure to Dawiso cloud (push method).

To obtain the Dawiso IP address, contact your key Account Manager at Dawiso.

Grant permissions to the user

In the following statements, replace user_name with the user created in the previous step.

Grant access to each database

Run the following statement for every database you list in the Dawiso data source. Replace database_name with the name of the database.

GRANT REFERENCES, SHOW VIEW ON `database_name`.* TO 'user_name'@'%';

REFERENCES makes the tables, views, columns, and foreign keys of the database visible to the user. SHOW VIEW is needed for view definitions, together with the grants in Grant access to view definitions.

Grant access to procedure and function code

Run the following statement once. It lets Dawiso read the code of stored procedures and functions. MySQL only allows this privilege server-wide. It shows the code but doesn’t let the user run procedures or functions.

GRANT SHOW_ROUTINE ON *.* TO 'user_name'@'%';

Warning

SHOW_ROUTINE is available from MySQL 8.0.20. Some managed services don’t let the admin user grant it. For example, on Amazon RDS for MySQL the master user doesn’t have it. If you can’t grant SHOW_ROUTINE, choose one of the following instead:

To ingest procedures, functions, and their parameters without their code, grant EXECUTE on each database. EXECUTE also lets the user run them.
To also ingest their code, grant SELECT on *.*. SELECT on *.* also lets the user read all data on the server.

Grant access to view definitions

MySQL shows the SQL definition of a view only to users who have both the SHOW VIEW and the SELECT privilege on that view. Without SELECT, Dawiso ingests the view and its columns, but the view’s Definition stays empty.

Grant SELECT on each view in the databases you ingest. To generate the statements for all views in a database, run the following query and then run the statements it returns. Replace database_name and user_name with your values.

SELECT CONCAT('GRANT SELECT ON `', REPLACE(TABLE_SCHEMA, '`', '``'), '`.`',
              REPLACE(TABLE_NAME, '`', '``'), '` TO ''user_name''@''%'';')
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'database_name';

SELECT on a view lets the user query the data the view returns, but not the tables behind it. Granting it per view keeps that access limited to views. Views created later need their own grant, so run the query again after you add views.

What each privilege is for

Privilege	Scope	What Dawiso ingests with it
`REFERENCES`	Each ingested database	Tables, views, their columns, and foreign keys.
`SHOW VIEW`	Each ingested database	View definitions, together with `SELECT` on the view.
`SELECT`	Each view	View definitions, together with `SHOW VIEW` on the database.
`SHOW_ROUTINE`	Server	Procedures, functions, their parameters, and their code.

Warning

If a privilege is missing, the ingestion still completes, but the objects that depend on it are missing in Dawiso or have an empty definition. If the user has no privilege at all on a database listed in the data source, the ingestion fails.

Sample script

You can copy and paste the following script and run it on your server. Replace my_database and my_view with your values.

CREATE USER 'DawisoIntegration'@'%' IDENTIFIED BY 'password';

-- Repeat for each database you list in the Dawiso data source
GRANT REFERENCES, SHOW VIEW ON `my_database`.* TO 'DawisoIntegration'@'%';

-- Procedure and function code (MySQL 8.0.20 and later)
GRANT SHOW_ROUTINE ON *.* TO 'DawisoIntegration'@'%';

-- View definitions: repeat for each view
GRANT SELECT ON `my_database`.`my_view` TO 'DawisoIntegration'@'%';

To generate the GRANT SELECT statements for all views, use the query in Grant access to view definitions.

Update an existing Dawiso user

If you created the Dawiso user following an earlier version of this guide, the user holds SELECT, SHOW VIEW, and EXECUTE on *.*. These privileges let it read all data on the server and run any procedure. To reduce them, revoke the server-wide privileges, then run the grants in Grant permissions to the user:

REVOKE SELECT, SHOW VIEW, EXECUTE ON *.* FROM 'user_name'@'%';

Dawiso continues to ingest the same objects, including view definitions.