Connecting to a DB System

Note

If you are connecting to your DB System using a VPN connection, as described in VPN Connections, or a Bastion Session, you can connect directly from your local network to your DB System using the utilities described in this chapter. You do not need to configure a Compute instance to connect from.

Connecting to the MySQL DB System with SSH and MySQL Shell

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use MySQL Shell to connect to your MySQL DB System's Endpoint.

This task requires the following:
  • A running VCN with Internet Connectivity.
  • A running MySQL DB System attached to the VCN. Make note of the IP address of the MySQL Endpoint in the DB System, and the administration user and password. To find the IP Address, see Endpoints
  • A running Compute instance (Oracle Linux is used in the following example) attached to a public subnet on the same VCN as the MySQL DB System. Make note of the public IP address of the compute instance . The SSH port, 22, is added to the public subnet by default. For more information, see Network Setup. It is strongly recommended you add an Ingress Rule for port 33060, which is the default port for MySQL Shell. If that port is not available, MySQL Shell connects to port 3306. If you have configured alternative ports, you must include the port number in your connection string.
  1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.
    ssh opc@computeInstancePublicIP
  2. Install MySQL Shell on the Compute instance using the following command:
    shell> sudo yum install mysql-shell
  3. Start MySQL Shell and connect to the MySQL DB System's endpoint using the following command:
    
    shell> mysqlsh Username@IPAddressOfMySQLDBSystemEndpoint 
    Please provide the password for 'Username@IPAddressOfMySQLDBSystemEndpoint':
    Save password for 'Username@IPAddressOfMySQLDBSystemEndpoint'? 
    [Y]es/[N]o/Ne[v]er (default No): 
    
    This command starts a global session. MySQL Shell attempts to connect to port 33060 by default and, if that port is not available, falls back to port 3306.
The connection is made and the following message displayed:
MySQL Shell 8.0.21-commercial

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'Username@IPAddressOfMySQLDBSystemEndpoint'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 15
Server version: 8.0.21-u2-cloud MySQL Enterprise - Cloud
No default schema selected; type \use <schema> to set one.
MySQL  IPAddressOfMySQLDBSystemEndpoint:PortNumber ssl  JS > 

Connecting to the MySQL DB System with SSH and the MySQL Client

This topic describes how to ssh into a Linux Compute instance, on the same VCN as your DB System, install and use the mysql command line client to connect to your MySQL DB System's Endpoint.

This task requires the following:
  • A running VCN with Internet Connectivity.
  • A running MySQL DB System attached to the VCN. Make note of the IP address of the MySQL Endpoint in the DB System, and the administration user and password. To find the IP Address, see Endpoints
  • A running Compute instance (Oracle Linux is used in the following example) attached to a public subnet on the same VCN as the MySQL DB System. Make note of the public IP address of the compute instance . The SSH port, 22, is added to the public subnet by default. For more information, see Network Setup.
  1. SSH into the compute instance from your local machine using the opc user and the public IP address of the compute instance.
    ssh opc@computeInstancePublicIP
  2. Install the MySQL client on the Compute instance using the following command:
    shell> sudo yum install mysql
  3. Connect to your DB System using the MySQL client.
    shell> mysql --host IPAddressOfMySQLDBSystemEndpoint -u Username -p 

Connecting from MySQL Workbench via Compute

This topic describes how to connect to MySQL Database Service using MySQL Workbench and a Compute Instance.

This task assumes the following:
  • You have created a Linux Compute Instance to connect to. It is not possible to connect directly from a remote IP to the MySQL DB System endpoint. You must connect to a Compute Instance, and from the Compute Instance to the MySQL DB System.
  • An SSH key-pair.
    Note

    MySQL Workbench does not support PuTTy-generated keys. If you used PuTTy to generate your SSH key pair, you must use PuTTygen's Conversion utility to convert your private key to the OpenSSH format for use with MySQL Workbench.
  • You have configured your network as described in Configuring the Network
For information on MySQL Workbench, see MySQL Workbench User Guide
To connect to MySQL using Workbench:
  1. See TCP/IP over SSH Connection.
  2. Enter the following:
    • SSH Hostname: the public IP address of the Compute Instance.
    • SSH Username: opc
    • SSH Keyfile: path to your SSH private key.
    • MySQL Hostname: the IP address of the MySQL Endpoint.
    • MySQL Server Port: the port the MySQL Endpoint is listening on.
    • Username/Password: credentials you defined when creating the DB System.
  3. Click Test Connection to confirm the connection details are valid.

MySQL authentication_oci plugin

MySQL IAM Authentication

MySQL authentication_oci plugin enables you to create users in your MySQL DB System and map them to existing users and groups defined in the IAM service. This enables you to log in to your DB System's MySQL server from the command line, or one of the MySQL Connectors (version 8.0.27 or higher), using a public-private key pair for authentication.

Prerequisites

What is required to use this functionality.

To use the authentication plugin, you must ensure the following:
  • Generate a key-pair, if you have not already done so, and generate an API fingerprint. See Required Keys and OCIDs.This must be done for every user you map. Individual users and mapped group members. It is not possible for a user to connect to a DB System using the authentication_oci plugin without a public-private key pair properly registered in IAM.
  • Create the configuration file. See SDK and CLI configuration file.
  • The following policy statement is defined in each tenancy you intend to connect to:
    ALLOW service mysql_dp_auth TO {AUTHENTICATION_INSPECT, GROUP_MEMBERSHIP_INSPECT, DYNAMIC_GROUP_INSPECT} IN TENANCY

    For more information on Policies, see Managing Policies. For more information on service-specific policies, see Policy Details for MySQL Database Service.

Limitations

Limitations

The following are the limitations of this functionality or client applications which use it:
  • Client application limitations:
    • It is not possible for MySQL Shell or the MySQL client application to use specific profiles in the OCI config file. They can only use the [DEFAULT] configuration profile.
    • MySQL Shell: it is not possible to configure an alternate location for the OCI config file. It must be present in the default location for your platform.
  • Server limitations:
    • It is only possible to assign multiple groups from a single tenancy in a CREATE USER statement. To assign groups from other tenancies, you must define a CREATE USER statement for each tenancy. See Mapping a proxied user to a group for more information.

User types

Describes the user types,

The following are the user types:
  • IAM user: a user defined on a tenancy. For more information on IAM users, see Managing Users
  • MySQL user: a user on the DB System's MySQL server. For more information on MySQL users , see MySQL Access Control and Account Management
  • MySQL proxy user: a user treated as a different user for privilege-checking purposes. This enables an external user to be a proxy for the second user; that is, to assume the privileges of the second user. The proxied user can be mapped to an IAM group, thereby granting the members of the mapped IAM group specific privileges on the DB System's MySQL server. Members of the IAM group can connect to the MySQL server, from the command line, using their OCID as the username. For more information, see MySQL Proxy Users. The members of the IAM groups are the proxy users, the MySQL users mapped to the groups are the proxied users.
  • Mapped user: a MySQL user which has been mapped to an IAM user or group, granting access to a DB System's MySQL Server.

Mapping a MySQL user to an IAM user

How to create a MySQL user and map it to an IAM user.

This task assumes the following:
  • You have the OCIDs of the IAM user and the tenancy in which the user is defined.
  • A running DB System.
  • A correctly configured VCN granting command line access to the DB System either from a Compute instance or local machine.
  • Correctly configured commandline client.
To map a MySQL user to an IAM user, do the following:
  1. Connect to your DB System as the administrator user using your command line client.
  2. Run the following command to map your MySQL user, User001, to the required IAM user.
    CREATE USER 'User001'@'%' IDENTIFIED WITH 'authentication_oci' AS 
    '{"tenancy" : "ocid1.tenancy.oc1..aaaaaaaabbbbbcccc",
      "user" : "ocid1.user.oc1..aaaaaaaabbbbbcccc"}';
    • CREATE USER: creates a user which can connect from any host, with the correct credentials. The host can be restricted to a specific host or group of hosts. For more information, see MySQL CREATE USER statement.
    • authentication_oci is the name of the authentication plugin on the MySQL server.
    • tenancy: is the OCID of the tenancy in which the user was created.
    • user: is the OCID of the IAM user to which you are mapping the MySQL user, User001.
User001 is created and mapped to the defined IAM user.

Mapping a proxied user to a group

This task maps two IAM groups to two proxied users, granting proxy access to the DB System's resources to the members of those groups.

This task assumes the following:
  • You have the OCIDs of the IAM groups and the tenancy in which the groups are defined. In this task, the groups are:
    • ocid1.group.oc1..alphanumericStringabc
    • ocid1.group.oc1..alphanumericStringxyz
  • A running DB System.
  • A correctly configured VCN granting command line access to the DB System either from a Compute instance or local machine.
  • Existing users to proxy. In this task, the users are pUser1 and pUser2.
  • Correctly configured command line client.
To create proxied users and map them to IAM groups, do the following:
  1. To map the proxied users, pUser1 and pUser2, run the following command on your DB System's MySQL server:
    CREATE USER ''@'host_name' IDENTIFIED WITH 'authentication_oci'
    AS '{"tenancy": ocid.tenancy.oc1..alphanumericString},
    "groupMapping": 
    {"ocid1.group.oc1..alphanumericStringabc": "pUser1"
    "ocid1.group.oc1..alphanumericStringxyz": "pUser2" }
    • CREATE USER: creates an anonymous user which can connect from the named host, with the correct credentials. The host can be restricted to a specific host or group of hosts. For more information, see MySQL CREATE USER statement.
    • host_name: the host the user connects from.
      Note

      The username-hostname combination must be unique for each tenancy defined. If you map ''@'hostname1' to a tenancy in one group mapping request, you cannot reuse that username-hostname combination for another request to another tenancy. To perform multiple mappings to different tenancies, you must use different username-hostname combinations, such as ''@'hostname2', for example.
    • authentication_oci the name of the authentication plugin on the MySQL server.
    • tenancy: the OCID of the tenancy in which the user and DB System reside. It is only possible to specify one tenancy per CREATE USER statement.
    • groupMapping: unlimited list of group OCID to proxy user names. The group OCIDs must all belong to the tenancy defined in the tenancy parameter. To map groups from a different tenancy, you must run CREATE USER again, with the required tenancy, groups, and users.
The users pUser1 and pUser2 are mapped to the IAM groups, enabling the group members to access the MySQL server with all the rights and privileges assigned to those proxied users.

Command-line clients

Using MySQL client and MySQL Shell to connect using the authentication_oci plugin

This section describes the connection strings for the following command-line clients:
  • MySQL client uses the following syntax:
    mysql -h hostName --port portNumber -u userName
    • mysql: the MySQL command-line client.
    • -h hostName: the IP address you use to connect to your DB System. -h 100.0.2.3 for example.
    • --port portNumber: (optional) the default port is 3306. If you changed the port in your DB System's definition, include this parameter with the new port number.
    • --oci-config-file=path/to/ociConfig: (optional) required if your CLI configuration file is not in the default location for your platform.
    • -u userName: username used to connect to the DB System.

    For more information on MySQL connection options, see Connection Options

  • MySQL Shell uses the following syntax:
    mysqlsh username@hostname --auth-method=authentication_oci_client
    • mysqlsh: the MySQL Shell command-line client.
    • username: username used to connect to the DB System.
    • hostName: the IP address you use to connect to your DB System.
    • --auth-method=authentication_oci_client: the name of the client plugin used to authenticate the request.

    MySQL Shell uses the default CLI configuration, only.

MySQL Connectors

You can also use MySQL Connectors to connect using the authentication_oci plugin.

The following connectors (version 8.0.27, or higher) support authentication_oci:

Connecting to a DB System using a mapped user

How to connect to your DB System from the command line using a mapped user account.

This task assumes you have the following:
  • A running DB System.
  • A correctly defined CLI config file. For more information, see SDK and CLI configuration file
  • A correctly configured VCN granting command line access to the DB System either from a Compute instance or local machine.
To connect to your DB System using a mapped user and command line client, do the following:
  1. To connect to your DB System using the MySQL client, run the following command:
    mysql -h DBSystemEndpoint --port 3306 -u User001
    • -h DBSystemEndpoint: -h defines the host, DBSystemEndpoint is the IP address of the DB System.
    • port: the port the DB System is listening on. By default this is 3306.
    • oci-config-file: (optional) the location of the configuration file. This is required only if you have an existing configuration file, with multiple profiles or a different default from the tenancy of the user you want to connect with. If your existing default profile is the correct one, you do not need to define this option.
    • -u User001: -u defines the user, User001 is the username of the mapped user.
The MySQL client connects to the DB System using the credentials provided.

Connecting to a DB System using a mapped proxy user

How to connect to a DB System using a mapped proxy user.

This task assumes you have the following:
  • A running DB System.
  • A correctly defined CLI config file. For more information, see SDK and CLI configuration file
  • A correctly configured VCN granting command line access to the DB System either from a Compute instance or local machine.
  • The OCID of an IAM user which is a member of a group mapped to a MySQL proxied user.
To connect to a DB System using a mapped user, do the following:
  1. Enter the text of the first step here.
    mysql -h DBSystemEndpoint --port portNumber -u OCIDofUser
    • -h DBSystemEndpoint: -h defines the host, DBSystemEndpoint is the IP address of the DB System.
    • port: the port the DB System is listening on. By default this is 3306.
    • oci-config-file: (optional) the location of the configuration file. This is required only if you have an existing configuration file, with multiple profiles or a different default from the tenancy of the user you want to connect with. If your existing default profile is the correct one, you do not need to define this option.
    • -u OCIDofUser: -u defines the user, the OCID is the unique identifier of the user, who must be a member of a mapped group.
The connection is established.