OCI Remote Connect To Data Warehouse MySQL Database

[Don_Childers]

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

[moechnig · Accepted solution]

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

[Don_Childers]

Thank you.

[ninja]

Can AD users get access or is it not supported?

[ostiguy]

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

[ninja]

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

Thanks for the response.

[ameegama]

 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

[moechnig]

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. 

[ameegama]

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

[moechnig]

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.