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.
Solved! See The Solution
Is there any way to give them access only following table . ?
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.
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).
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)