Overview

OCI MySQL Heatwave allows customers to use OCI user accounts to authenticate using authentication_oci plugin. Up to MySQL 8.0.32, this required an API Key pair. Starting with MySQL 8.0.33, the plugin also supports the IAM Security token generated using the OCI CLI.

Since only local and provisioned users have access to the API Key-Pair, federated users can greatly benefit from our new feature, because credentials used to generate the IAM Security Token can use integration with external identity provider. This enables DBAs to leverage the Identities and Group Memberships of any OCI user (Local, Federated or Provisioned) for authentication into MySQL Instances.

Using this feature, a MySQL instance can validate a connecting user via the IAM security token which is generated by the user through OCI CLI using the same credentials that they use to sign in to the OCI Console.

 

MySQL HeatWave Authenticate using Ephemeral key-pair

 

Prerequisites

To use authentication_oci plugin for IAM security token based authentication, you require following:

  • A Local, Federated or Provisioned user account which allows you to login to OCI console either through Direct Sign-In or Single Sign-On.
  • A correctly configured OCI CLI.
  • The following policy statement defined in each tenancy you intend to connect:
ALLOW service mysql_dp_auth TO {AUTHENTICATION_INSPECT, GROUP_MEMBERSHIP_INSPECT, DYNAMIC_GROUP_INSPECT} IN TENANCY
  • A running DB system.
  • A correctly configured VCN granting command line access to the DB system either from a compute instance or a local machine.
  • A correctly configured mysql command-line client.

 

Database Administrator’s Task

A Database Admin can create mappings which can allow a single OCI user or a group of OCI users to login to MySQL using IAM Security Tokens.

Mapping a MySQL user to a single OCI user

This mapping provides the OCI user the identity and privileges of a MySQL user.

Mapping a MySQL user to a Local user

This requires the OCID of the local user and the tenancy in which the user is defined.

Connect to the DB system as the administrator and run the following command to create and map the MySQL user, <MySQLUser> to the local user with OCID, <UserOCID>.

CREATE USER ‘<MySQLUser>’@’%’ IDENTIFIED WITH ‘authentication_oci’ AS ‘{“tenancy” : “<TenancyOCID>”, “user” : “<UserOCID>”}’;
 
Mapping a MySQL user to a Federated/Provisioned user

This requires the User ID of the federated/provisioned user from the identity provider domain and the tenancy OCID.

Connect to the DB system as the administrator and run the following command to create and map the MySQL user, <MySQLUser> to the federated/provisioned user with User ID, <UserID>.

CREATE USER ‘<MySQLUser>’@’%’ IDENTIFIED WITH ‘authentication_oci’ AS ‘{“tenancy” : “<TenancyOCID>”, “user” : “<UserID>”}’;

Notice the difference in terms of using <UserOCID> for local user v/s <UserID> for federated/provisioned user.

 

Mapping a MySQL Proxied User to an IAM Group

This mapping provides all the users of the IAM group the identity and privileges of the MySQL proxied user.

Connect to the DB system as the administrator and do the following to create and map MySQL proxied users <pUser1>, <pUser2> and <pUserN> to IAM groups <IAMGroup1OCID>, <IAMGroup2OCID> and <IAMGroupNOCID> respectively:

  1. Create MySQL users <pUser1>, <pUser2> and <pUserN> to proxy:
CREATE USER <pUser1> IDENTIFIED BY <password> ACCOUNT LOCK;
CREATE USER <pUser2> IDENTIFIED BY <password> ACCOUNT LOCK;
CREATE USER <pUserN> IDENTIFIED BY <password> ACCOUNT LOCK;
  1. Grant required privileges (such as database privileges, table privileges, and column privileges) to the MySQL proxied users using the GRANT Statement.
  2. Map the MySQL proxied users to IAM groups:
CREATE USER ”@'<Hostname>’ IDENTIFIED WITH ‘authentication_oci’ AS ‘{“tenancy” : “<TenancyOCID>”, “group_mapping” : {“<IAMGroup1OCID>” : “<pUser1>”, “<IAMGroup2OCID>” : “<pUser2>”, “<IAMGroupNOCID>” : “<pUserN>” }}’;

If an IAM user is a part of more than one IAM group, and if you map these IAM groups to different MySQL proxied users, then the IAM user is mapped to the first MySQL proxied user (that corresponds to the IAM group of which the IAM user is a part) defined in the group_mapping field. For example, if an IAM user is part of both <IAMGroup2OCID> and <IAMGroupNOCID>, then it is mapped to the first MySQL proxied user, <pUser2>, that corresponds to the first IAM group of which the IAM user is a part, <IAMGroup2OCID>.

  1. Grant proxy privileges to the anonymous user created in step 3:
GRANT PROXY ON ‘<pUser1>’ TO ”@'<Hostname>’;
GRANT PROXY ON ‘<pUser2>’ TO ”@'<Hostname>’;
GRANT PROXY ON ‘<pUserN>’ TO ”@'<Hostname>’;

MySQL proxied users <pUser1>, <pUser2> and <pUserN> are mapped to the IAM groups, <IAMGroup1OCID>, <IAMGroup2OCID> and <IAMGroupNOCID> respectively, enabling the IAM group members to access the MySQL Server with all the identity and privileges assigned to the MySQL proxied users.

 

Connecting to the DB System

Generating IAM Security Token

Use the Oracle Cloud Infrastructure command-line interface to generate an IAM Security Token.

  1. Run the following command in Oracle Cloud Infrastructure command-line interface:
oci session authenticate –profile-name <ProfileName> –region <region>

A web browser is launched.

  1. In the browser, enter your user credentials and login to OCI console.

The IAM security token is generated along with a session (or ephemeral) key pair. The authentication information is saved to the .config file. By default, the IAM security token expires in one hour.

  1. To refresh the IAM security token (within the validity period), run the following command:
oci session refresh –profile <ProfileName>

You can refresh the token up to 24 hours.

 

Connecting with a Mapped MySQL User

From MySQL client, run the following command to connect to a DB system as a MySQL user who is mapped to an OCI user:

mysql -h <DBSystemEndpointIPAddress> –port <PortNumber> –oci-config-file <PathToOciConfig> –authentication-oci-client-config-profile <ProfileName> -u <MySQLUser>

 

Connecting with a Mapped Proxy User

From MySQL client, run the following command to connect to a DB system as a MySQL proxied user who is mapped to an IAM group:

mysql -h <DBSystemEndpointIPAddress> –port <PortNumber> –oci-config-file <PathToOciConfig> –authentication-oci-client-config-profile <ProfileName> -u <UserIdentifier>

Here, <UserIdentifier> is the User OCID for a Local user and User ID from Identity Provider’s domain for a federated or provisioned user.

 

Conclusion

MySQL Heatwave allows users managed in OCI or an external Identity Provider to login an MySQL instance using IAM Security Tokens. This offers improved security and ease in user management.

 

Additional References