Give third-party access to AWS hosted MySQL

Question

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?

Answer 1

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'

Answer 2

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.