Connecting With a Mapped Proxy User

To connect to a DB system using a mapped proxy user, first map MySQL proxied users to IAM groups, and then use the mapped proxy user to connect to the DB system.

  1. Map the MySQL proxied user to an IAM group to obtain the identity and privileges of the MySQL user. See Mapping a MySQL Proxied User to an IAM Group .
  2. Connect to the DB system with the mapped MySQL proxy user. See Connecting With a Mapped Proxy User.

Mapping a MySQL Proxied User to an IAM Group

Map a MySQL proxied user to an IAM group to grant 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 map MySQL proxied users to IAM groups.

This task requires the following:
  • The OCIDs of the IAM groups and the tenancy in which the groups are 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 create and map MySQL proxied users, <pUser1>, <pUser2>, and <pUserN> to IAM groups, <IAMGroup1OCID>, <IAMGroup2OCID>, and <IAMGroupNOCID> respectively:
Note

You can create and map any number of MySQL proxied users to IAM groups.
  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;
  2. Grant required privileges (such as database privileges, table privileges, and column privileges) to the MySQL proxied users using the GRANT statement. See GRANT Statement.
    MySQL proxied users, <pUser1>, <pUser2>, and <pUserN> with required privileges are created.
  3. 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>.
    • CREATE USER: Create an anonymous user that can connect from the <Hostname> with the correct credentials. The host can be restricted to a specific host or group of hosts. See CREATE USER.
    • <Hostname>: Specify the hostname that the user connects from.

      The username-hostname combination must be unique for each tenancy. If you map ''@'hostname1' to a tenancy in one group mapping request, you cannot reuse this username-hostname combination for another request to another tenancy. To perform multiple mappings to different tenancies, use different username-hostname combinations, such as ''@'hostname2'.

    • authentication_oci: Specify the name of the authentication plugin on the MySQL Server.
    • tenancy: Specify the OCID of the tenancy in which the user and DB system reside. You can specify one tenancy per CREATE USER statement.
    • group_mapping: Specify a list of group OCID to proxy user names. The group OCIDs must belong to the tenancy defined in the tenancy parameter. To map groups from a different tenancy, run the CREATE USER command again, with the required tenancy, groups, and users.
  4. 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>, <pUser1> 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 With a Mapped Proxy User

Connect to a DB system with a mapped proxy user. The mapped proxy user assumes the identity and privileges of a MySQL proxied 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 with a mapped proxy user.

This task assumes you have 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.
  • The user OCID or user ID from identity provider domain of the mapped proxy user.
  • 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 proxy user:
  1. From MySQL client: Run the following command:
    mysql -h <DBSystemEndpointIPAddress> --port <PortNumber> --oci-config-file=<PathToOciConfig> 
    --authentication-oci-client-config-profile=<ConfigProfile> -u <UserOCIDOrUserIDFromIdp>
    • 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: Depending on whether you are connecting with a local, federated, or provisioned user, specify the OCID (Oracle Cloud Identifier) or user ID from identity provider domain:
      • Local user: Specify the IAM user OCID of the local user.
      • Federated user: Specify the user ID from identity provider domain.
      • Provisioned user with API keys: Specify the IAM user OCID of the local user.
      • Provisioned user with IAM security token: Specify the user ID from identity provider domain.
  2. From MySQL Shell 8.0: Run the following command:
    mysqlsh <UserOCIDOrUserIDFromIdp>@<Hostname> --auth-method=authentication_oci_client
    • <UserOCIDOrUserIDFromIdp>: Depending on whether you are connecting with a local, federated, or provisioned user, specify the OCID (Oracle Cloud Identifier) or user ID from identity provider domain:
      • Local user: Specify the IAM user OCID of the local user.
      • Federated user: Specify the user ID from identity provider domain.
      • Provisioned user with API keys: Specify the IAM user OCID of the local user.
      • Provisioned user with IAM security token: Specify the user ID from identity provider domain.
    • <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 only.
      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 <UserOCIDOrUserIDFromIdp>@<Hostname> --oci-config-file=<PathToOciConfig> 
    --authentication-oci-client-config-profile=<ConfigProfile> --auth-method=authentication_oci_client
    • <UserOCIDOrUserIDFromIdp>: Depending on whether you are connecting with a local, federated, or provisioned user, specify the OCID (Oracle Cloud Identifier) or user ID from identity provider domain:
      • Local user: Specify the IAM user OCID of the local user.
      • Federated user: Specify the user ID from identity provider domain.
      • Provisioned user with API keys: Specify the IAM user OCID of the local user.
      • Provisioned user with IAM security token: Specify the user ID from identity provider domain.
    • <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 mapped proxy user, <UserOCIDOrUserIDFromIdp>, is connected to the DB system.