topic Re: OCI Remote Connect To Data Warehouse MySQL Database in Active IQ Unified Manager Discussions

OCI Remote Connect To Data Warehouse MySQL Database

Don_Childers — Wed, 30 Nov 2016 21:47:56 GMT

We've had a request by our asset management department to provide remote connectivity to the OCI MySQL database with read-only rights. They want to pull some information as it relates to enterprise storage assets. They gave me a list of Windows Active Directory accounts that would need the read-only access to the MySQL database.

However, in searching the documentation I can only find reference to remote connections to MySQL within the Data Warehouse documentation. It states:

"When accessing MySQL, connect to the MySQL database on the machine where Data Warehouse is installed. The MySQL port is 3306 by default; however, you can change it during installation. The user name and password is dwhuser/netapp123."

Is dwhuser the only way to connect to the MySQL back end database?

Is it safe to give those credentials to another department to use?

Does dwhuser have any rights that could be potentially harmful to the database (like admin rights)?

Any way to add AD accounts for read-only access?

For reference, we are running OCI 7.2.2.

Thanks a lot for any info.

--Don Childers

Re: OCI Remote Connect To Data Warehouse MySQL Database

moechnig — Wed, 30 Nov 2016 22:31:49 GMT

The dwhuser account is intended for situations just like yours. It has read-only access.

Re: OCI Remote Connect To Data Warehouse MySQL Database

Don_Childers — Wed, 30 Nov 2016 22:33:31 GMT

Thank you.

Re: OCI Remote Connect To Data Warehouse MySQL Database

ninja — Wed, 30 Nov 2016 22:44:53 GMT

Can AD users get access or is it not supported?

Re: OCI Remote Connect To Data Warehouse MySQL Database

ostiguy — Thu, 01 Dec 2016 02:35:32 GMT

Hey Ninja,

What is the use case for AD users having access?

Is the goal to avoid a shared user account, which is an admitted current limitation?

No, this is currently not supported.

I have been looking at some MySQL features to contemplate what would be within the realm of possible (which implies nothing from an OCI support perspective, rather, I am curious if I see anything that would be adaptable for future OCI versions to use).

https://dev.mysql.com/doc/refman/5.5/en/windows-authentication-plugin.html

Doesn't seem very viable, because it requires each user account to be manually created in MySQL - i.e, you are mapping a Windows account to a MySQL user account, and it doesn't seem possible to do something like

"When username is a member of group X, create username with mysql permissions GRANT ____ on tables _____"

Instead, you are mapping a group to a specific mysql user, which I would tend to think wouldn't help much, as you couldn't seemingly understand within mysql who is doing what (any slow query run by a human would appear as the same username)

Matt

Re: OCI Remote Connect To Data Warehouse MySQL Database

ninja — Thu, 01 Dec 2016 19:03:24 GMT

Yep. The intent is to avoid those ugly frowns from the security teams for each shared account that we have 🙂

Thanks for the response.

Re: OCI Remote Connect To Data Warehouse MySQL Database

ameegama — Fri, 24 Mar 2017 17:17:47 GMT

Hi Don,

I have same situation,Capacity management team wanted to access OCI data from remote . Have you done that ?

Could you Please send more details how you setup ..

Thanks

Amanda Meegama

Re: OCI Remote Connect To Data Warehouse MySQL Database

moechnig — Fri, 24 Mar 2017 17:36:24 GMT

The credentials you need to set this up is in the manual, here: https://library.netapp.com/ecmdocs/ECMLP2572619/html/GUID-4AD1EDE4-A6B5-4B84-B07B-C682B79BE3AA.html

The MySQL TCP port is 3306.

Re: OCI Remote Connect To Data Warehouse MySQL Database

ameegama — Fri, 24 Mar 2017 18:13:14 GMT

Thanks.

Is there any way to give them access only following table . ?

Required TABLES
======================
dwh_capacity.storage_dimension
dwh_capacity.date_dimension
dwh_capacity.storage_and_storage_pool_capacity_fact

Re: OCI Remote Connect To Data Warehouse MySQL Database

moechnig — Fri, 24 Mar 2017 18:19:12 GMT

You could create a new user for this particular use case and modify the schema to grant your new users access exclusively to the tables in question.

You would probably need to re-implement this change after any backup/restore, including during upgrade.

Unless the restriction is very important, it may not be worth the effort.