SQL connexion to multiple databases

gg

When I set up the connection for a given mysql database, and then click on "Import tables to datasets", I have access to all the tables from all the databases of the host. It is not prefixed with the name of the database.

For info, this is the Connection URL :

`jdbc:mysql://localhost:3306/alfa_survey_test?serverTimezone=UTC`

the targeted database is alfa_survey_test and the listed tables come from 5 other databases...

I have duplicates database for test / prod..., the tables are called the same. How am I supposed to know from which database the table comes from? How can I restrict the table list so that I can only see the one from the required database? And finally, how comes that the connection gives access to all database, and not just the one required? Bug or feature?

Thanks!

Liev

Hi gg,

I've been able to reproduce this behavior. There're a couple of comments on the general design pattern on your infrastructure.

- You're seeing tables from both dev and prod databases, but they're on the same server. This is potentially an issue, since hosting both on the same server might lead to corruption or data loss.

- The DSS Mysql connection uses a specific user to access the database. In Mysql, a user might be able to access several databases, depending on permissions granted. Hence, should you need to scope your user to a relevant database, please do so and assign permissions accordingly. A snippet below

CREATE database development;
CREATE USER 'dev_user' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON development.* TO 'dev_user';

CREATE database production;
CREATE USER 'prod_user' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON production.* TO 'prod_user';

- When you're ready to move your project to production, your DSS production environment should be using the production user credentials and you will get the opportunity to remap connections when importing or updating your project.

I hope this helps!