Can Power BI change data in the database?

Question

A colleague approached me with a request to install Power BI for him. As I understand it, this software creates various graphs and reports based on data from the database. In this regard, I have a question. The database to which the user asks to give access (MySQL) is very sensitive to changes, I cannot allow him to change anything in the database. In this regard, I have a question - how exactly does PowerBi communicate with the database? Does it only execute SELECT requests or can it write data to the database itself? I understand that all this can be limited using different accounts, I am more interested in how the Power BI communicates with the database

So - do I need to do a separate dump of the database purely for this user or can I give him access to the production database?

Answer 1

The right way to approach this would be to create a separate authorisation ID (user) for this person and grant only minimally necessary privileges to it. This way the person will not be able to do anything outside the scope of their privileges, whether they connect to the database using Power BI or any other client software.

If there is a risk that BI queries can negatively affect the production database performance, then you should maintain a separate, periodically updated instance of the production database specifically for BI purposes. This doesn't negate the requirement to restrict access to that database as noted earlier.

Answer 2

Perhaps sufficient to protect from writes:

CREATE USER 'powerbi'@'...' IDENTIFIED BY ...;
GRANT SELECT ON dw.* TO 'powerbi'@'...';

And then require that Power BI login with that.

You may need a few things more than SELECT. Example: EXECUTE in the case of it needing stored routines. At that point, the protection is delegated to the SECURITY clause plus other aspects of the routines.

Answer 3

No need to create separate database Power bi Only allow to view data not any type of update or delete.