Prepare for MariaDB integration

Before ingesting MariaDB metadata into Dawiso, create a dedicated MariaDB 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. MariaDB 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.

Dawiso Cloud currently supports the traditional authentication method using credentials.

Supported MariaDB versions

• MariaDB 10.5 and later. Dawiso has been verified on MariaDB 10.5, 10.6, 10.11, 11.4, 11.8, and 12.3.

The privilege for reading procedure and function code differs between MariaDB 11.2 and earlier and MariaDB 11.3 and later. The steps below show both.

Connection prerequisites

• You have the global CREATE USER privilege.
• You hold each privilege you grant below WITH GRANT OPTION. MariaDB 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 MariaDB documentation for more details.

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, sequences, and foreign keys of the database visible to the user. It doesn’t let the user read or change data. SHOW VIEW is needed for view definitions, together with the grants in Grant access to view definitions.

Grant access to procedure and function code

The privilege that lets Dawiso read the code of stored procedures and functions depends on your MariaDB version. Neither privilege lets the user run procedures or functions.

GRANT SHOW CREATE ROUTINE ON `database_name`.* TO 'user_name'@'%';

GRANT SELECT ON mysql.proc TO 'user_name'@'%';

Without one of these privileges, Dawiso doesn’t ingest procedures, functions, or their parameters.

Grant access to view definitions

MariaDB 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 (SQL) 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.

Grant access to triggers and events

MariaDB lists a trigger only to users with the TRIGGER privilege, and an event only to users with the EVENT privilege. To ingest triggers and events, run the following statement for every database you list in the Dawiso data source:

GRANT TRIGGER, EVENT ON `database_name`.* TO 'user_name'@'%';

What each privilege is for

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'@'%';
GRANT SHOW CREATE ROUTINE ON `my_database`.* TO 'DawisoIntegration'@'%';
GRANT TRIGGER, EVENT ON `my_database`.* TO 'DawisoIntegration'@'%';

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

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'@'%';
GRANT TRIGGER, EVENT ON `my_database`.* TO 'DawisoIntegration'@'%';

-- Procedure and function code, once for the server
GRANT SELECT ON mysql.proc 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. To leave out triggers and events, remove the GRANT TRIGGER, EVENT line.

Verify permissions

Connect as the new user and run the following queries. Replace my_database with your value. Compare the results with the same queries run by an administrator: the numbers must match.

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'my_database';
SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'my_database' AND ROUTINE_DEFINITION IS NOT NULL;
SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'my_database' AND VIEW_DEFINITION <> '';
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'my_database';
SELECT COUNT(*) FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA = 'my_database';