Oracle Cloud Infrastructure Documentation
Try Free Tier

Connecting With a Mapped MySQL User

To connect to a DB system with a mapped MySQL user, first map a MySQL user to a local, federated, or provisioned user, and then connect to the DB system using the mapped MySQL user .

  1. Map the MySQL user to a local, federated or provisioned user to obtain the identity and privileges of the MySQL user.
  2. Connect to the DB system with the MySQL user mapped to a local, federated, or provisioned user. See Connecting With a MySQL User Mapped to a Local, Federated, or Provisioned User.

Mapping a MySQL User to a Local User

Map a MySQL user to a local user to grant the local user access to the MySQL Server of the DB system.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to create a MySQL user and map it to a local user.

This task requires the following:
  • The OCID of the local 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 a local machine.
  • A correctly configured command-line client.
  • For the required setup to use the authentication_oci plugin, see Prerequisites.
Do the following to map a MySQL user to a local user using a command line client:
  1. Connect to the DB system as the administrator.
  2. Run the following command to create and map the MySQL user, <MySQLUser>, to the local user, <LocalUserOCID>.
    CREATE USER '<MySQLUser>'@'%' IDENTIFIED WITH 'authentication_oci' AS 
'{"tenancy" : "<TenancyOCID>",
  "user" : "<LocalUserOCID>"}';
    • CREATE USER: Create a user, <MySQLUser>, that can connect from any host with the correct credentials. The host can be restricted to a specific host or group of hosts. See CREATE USER.
    • authentication_oci: Specify the name of the authentication plugin on the MySQL Server.
    • tenancy: Specify the OCID of the tenancy in which the local user, <LocalUserOCID>, was created.
    • user: Specify the OCID of the local user, <LocalUserOCID>, to which you are mapping the MySQL user, <MySQLUser>.
MySQL user, <MySQLUser>, is created and mapped to the local user OCID, <LocalUserOCID>.

Mapping a MySQL User to a Federated User

Map a MySQL user to a federated user to grant the federated user access to the MySQL Server of the DB system.

A federated user is a user that belongs to an Identity Provider and logs into Oracle Cloud Infrastructure services through federation.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to create a MySQL user and map it to a federated user.

This task requires the following:
  • The user ID of the federated user and the tenancy to log into Oracle Cloud Infrastructure services.
  • 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 command-line client.
  • For the required setup to use the authentication_oci plugin, see Prerequisites.
Do the following to create and map a MySQL user <MySQLUser>, to a federated user, <UserIdFromIdpDomain>, using a command line client:
  1. Connect to the DB system as the administrator.
  2. Run the following command to create and map the MySQL user to the federated user:
    CREATE USER '<MySQLUser>'@'%' IDENTIFIED WITH 'authentication_oci' AS 
'{"tenancy" : "<TenancyOCID>",
  "user" : "<UserIdFromIdpDomain>"}';
    • CREATE USER: Create a user, <MySQLUser>, that can connect from any host with the correct credentials. The host can be restricted to a specific host or group of hosts. See CREATE USER.
    • authentication_oci: Specify the name of the authentication plugin on the MySQL Server.
    • tenancy: Specify the OCID of the tenancy that is federated to the identity provider in which the federated user, <UserIdFromIdpDomain>, was created..
    • user: Specify the user ID of the federated user to which you are mapping the MySQL user, <MySQLUser>. It is the same user id used for Single Sign-on.
MySQL user, <MySQLUser>, is created and mapped to the federated user , <UserIdFromIdpDomain>.

Mapping a MySQL User to a Provisioned User

Map a MySQL user to a provisioned user to grant the provisioned user access to the MySQL Server of the DB system.

A provisioned user is systematically provisioned by the identity provider in Oracle Cloud Infrastructure. Provisioned users can have Oracle Cloud Infrastructure credentials, but not Console passwords.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to create a MySQL user and map it to a provisioned user.

This task requires the following:
  • The user ID or the user OCID of the provisioned user and the tenancy to log into Oracle Cloud Infrastructure services.
  • 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 command-line client.
  • For the required setup to use the authentication_oci plugin, see Prerequisites.
Do the following to map a MySQL user to a provisioned user using a command line client:
  1. Connect to the DB system as the administrator.
  2. For API key-based authentication, run the following command to create and map the MySQL user to the provisioned user:
    CREATE USER '<MySQLUser>'@'%' IDENTIFIED WITH 'authentication_oci' AS 
'{"tenancy" : "<TenancyOCID>",
  "user" : "<ProvisionedUserOCID>"}';
    • CREATE USER: Create a user, <MySQLUser>, that can connect from any host with the correct credentials. The host can be restricted to a specific host or group of hosts. See CREATE USER.
    • authentication_oci: Specify the name of the authentication plugin on the MySQL Server.
    • tenancy: Specify the OCID of the tenancy in which the provisioned user, <ProvisionedUserOCID>, was created.
    • user: Specify the OCID of the provisioned user, <ProvisionedUserOCID>, to which you are mapping the MySQL user, <MySQLUser>.
  3. For IAM security token based authentication, run the following command to create and map the MySQL user to the provisioned user:
    CREATE USER '<MySQLUser>'@'%' IDENTIFIED WITH 'authentication_oci' AS 
'{"tenancy" : "<TenancyOCID>",
  "user" : "<UserIdFromIdpDomain>"}';
    • CREATE USER: Create a user, <MySQLUser>, that can connect from any host with the correct credentials. The host can be restricted to a specific host or group of hosts. See CREATE USER.
    • authentication_oci: Specify the name of the authentication plugin on the MySQL Server.
    • tenancy: Specify the OCID of the tenancy in which the provisioned user, <UserIdFromIdpDomain>, was created.
    • user: Specify the user ID of the provisioned user to which you are mapping the MySQL user, <MySQLUser>. It is the same user id used for Single Sign-on.
MySQL user is created and mapped to the provisioned user.

Connecting With a MySQL User Mapped to a Local, Federated, or Provisioned User

Connect to a DB system with a MySQL user mapped to a local, federated, or provisioned user. A mapped MySQL user provides the local, federated, or provisioned user the identity and privileges of a MySQL user. Local, federated and provisioned users can use an IAM security token to connect to a DB system. However, only local and provisioned users can use an API key-pair to connect to a DB system.

Using a Command-Line Client

Use a command-line client such as MySQL Client or MySQL Shell to connect to a DB system using a MySQL user who is mapped to a local, federated or provisioned user.

This task requires the following:
  • A running DB system.
  • A configuration file. See SDK and CLI configuration file.
  • A correctly configured VCN granting command line access to the DB system either from a compute instance or a local machine.
  • For the required setup to use the authentication_oci plugin, see Prerequisites.
Do either of the following steps to connect to the DB system using a mapped user, <MySQLUser>:
  1. From MySQL client: Run the following command:
    mysql -h <DBSystemEndpointIPAddress> --port <PortNumber> --oci-config-file=<PathToOciConfig> 
--authentication-oci-client-config-profile=<ConfigProfile> -u <MySQLUser>
    • h: Specify the IP address of the endpoint of the DB system.
    • port: Specify the port the DB system is listening on. The default value is 3306.
    • oci-config-file: (Optional) Specify the location of the configuration file. If you do not specify this option, the MySQL client reads the configuration from the default location, %HOMEDRIVE%%HOMEPATH%\.oci\config for Windows systems, and ~/.oci/config, for all other operating systems.
    • authentication-oci-client-config-profile: (Optional) Specify the name of the configuration profile, which you specify in the configuration file, whose configuration options MySQL client should use for authentication. If you do not specify this option, the MySQL client reads the configuration options mentioned in the DEFAULT profile.
    • u: Specify the username of the mapped MySQL user, <MySQLUser>.
  2. From MySQL Shell 8.0: Run the following command:
    mysqlsh <MySQLUser>@<Hostname> --auth-method=authentication_oci_client
    • <MySQLUser>: Specify the username of the mapped MySQL user.
    • <Hostname>: Specify the IP address of the endpoint of the DB system.
    • auth-method=authentication_oci_client: Specify the name of the client plugin used to authenticate the request. MySQL Shell uses the default CLI configuration.
      Note

      This authentication method supports classic MySQL protocol only.
    Note

    MySQL Shell 8.0.33 or later allows the location of the configuration file to be configured with the oci.configFile option.
  3. From MySQL Shell 8.1 or later: Run the following command:
    mysqlsh <MySQLUser>@<Hostname> --oci-config-file=<PathToOciConfig> 
--authentication-oci-client-config-profile=<ConfigProfile> --auth-method=authentication_oci_client
    • <MySQLUser>: Specify the username of the mapped MySQL user.
    • <Hostname>: Specify the IP address of the endpoint of the DB system.
    • oci-config-file: (Optional) Specify the location of the configuration file. If you do not specify this option, the MySQL Shell reads the configuration from the default location, %HOMEDRIVE%%HOMEPATH%\.oci\config for Windows systems, and ~/.oci/config, for all other operating systems.
    • authentication-oci-client-config-profile: (Optional) Specify the name of the configuration profile, which you specify in the configuration file, whose configuration options MySQL Shell should use for authentication. If you do not specify this option, the MySQL Shell reads the configuration options mentioned in the DEFAULT profile.
    • auth-method=authentication_oci_client: Specify the name of the client plugin used to authenticate the request.
      Note

      This authentication method supports classic MySQL protocol only.
The MySQL client or MySQL Shell connects to the DB system using the credentials provided.

Was this article helpful?