I have a production MySQL database hosted on AWS for an eCommerce store I work with. Employees in the firm are requesting access to our live SQL database to use with BI tools like Tableau/Qlikview. How can I grant these users read-only permissions to analyze data from the live database and not the ability to write data?

  • If this is RDS then give 'em access to a read replica. They not only can't modify your database, they also can't impair its performance with badly written queries, which is always a risk. Commented Mar 21, 2016 at 2:28

2 Answers 2

GRANT SELECT ON My_Database.* TO bi_user;

It's explained in detail here (sample code below).

Don't forget though that people with SELECT privileges can cripple a database as surely as if they had DELETE privileges by running complex reports.

Code from website:

If you want give a user read-only access to a database, you can grant to him/her only the "SELECT" privilege, so that he/she can not run any DDL statements, and any INSERT, UPDATE, or DELETE statements. The tutorial exercise gives you a good example:
>cd \mysql\bin
>mysql -u root -ppassword

mysql> USE fyi;
Database changed

mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));
Query OK, 0 rows affected (0.14 sec)

mysql> INSERT INTO links VALUES (1, 'dba.fyicenter.com');
Query OK, 1 row affected (0.05 sec)

mysql> CREATE USER guest IDENTIFIED BY 'other_password';
Query OK, 0 rows affected (0.24 sec)

mysql> GRANT SELECT ON fyi.* TO guest;
Query OK, 0 rows affected (0.00 sec)

mysql> QUIT;

>mysql -u guest -pother_password

mysql> use fyi;
Database changed

mysql> SELECT * FROM links;
| id   | name              |
|    1 | dba.fyicenter.com |
1 row in set (0.04 sec)

mysql> INSERT INTO links VALUES (2, 'dev.fyicenter.com');
ERROR 1142 (42000): INSERT command denied to user 
'guest'@'localhost' for table 'links'

In your AWS Management Console. Identity Access Management (IAM) create a Policy, Group and Users for the firm. Grant them only the necessary privileges.

Create Passwords for each, and send them the server, database, port and sign on credentials for them.

If they are already using tableau they will know how to connect with the credentials you gave them. How Tableau and AWS Work Together

They should then be able to connect to the database just as any other data source in Tableau.

  • Apart from it being absolutely unnecessary for the task at hand, you most certainly don't want to create IAM records in your account for third-party users.
    – mustaccio
    Commented Mar 25, 2022 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.