# MySQL

> Octelium documentation. Canonical page: <https://octelium.com/docs/octelium/latest/management/core/service/mysql>.

Setting the *Service* mode to `MYSQL` enables it to operate in application-layer aware MySQL mode. This mode enables you to provide secretless access for *Users* to the protected upstream MySQL-based server without having to share the upstream's password. This mode also provides you with clear application-layer aware visibility where your MySQL sessions and queries are logged and audited in real-time.

> **Note:**
>
> Read more about access to `MYSQL` from a *User* perspective [here](https://octelium.com/docs/octelium/latest/user/cli/access.md#mysql). You can also read more detailed guides [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/mysql-sso-zero-trust.md) for generic databases behind NAT and [here](https://octelium.com/docs/octelium/latest/management/guide/service/databases/planetscale.md) for SaaS PlanetScale MySQL-based databases.

## Secretless Access

Secretless access enables you to provide secretless access for authorized *Users* to `MYSQL`-based *Service* by automatically injecting passwords to authenticate to the upstream MySQL database, and force the *User* to connect to the upstream as a specific user, possibly with a certain role corresponding to specific permissions, to a specific database.

First, you need to create a *Secret* to store the password of your upstream SSH server (read more [here](https://octelium.com/docs/octelium/latest/management/core/secret.md#creating-a-secret)) as follows:

```bash
octeliumctl create secret mariadb-password
# OR via a --value flag
octeliumctl create secret --value <PASSWORD> mariadb-password
# OR via a --file flag
octeliumctl create secret --file /PATH/TO/PASSWORD mariadb-password
```

Now, you define your *Service* as follows:

```yaml
kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    # !mark(1:6)
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password
```

Now, the above configuration forces the *User* to authenticate as `root` with the password value set in the *Secret* `mariadb-password` and connect to the database `mysql` regardless of the information supplied by the downstream.

> **Note:**
>
> For now, authorized *Users* have to use an **empty** password value to access the *Service*.

> **Note:**
>
> For internal/private MySQL upstreams behind NAT, you need to remotely serve them via a connected `octelium` client or container as discussed [here](https://octelium.com/docs/octelium/latest/management/core/service/overview.md#remotely-via-a-connected-user).

## TLS

If the upstream database is serving over TLS, you have to use the `isTLS` boolean field as follows:

```yaml
kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password
      # !mark
      isTLS: true
```

## Access Control

By default, the *Service* enforces authorization only at the beginning of the connection. You can, however, enforce authorization for every MySQL command (e.g. queries) by setting the `authorization.mode` field to `ALL`. In such case, MySQL-specific information of each command is stored in `ctx.request.mysql` which can be used by your *Policies*. Here is a detailed example of an inline *Policy* that denies `DROP` and `TRUNCATE` statements:

```yaml
kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password
      # !mark(1:2)
      authorization:
        mode: ALL
  authorization:
    inlinePolicies:
      - spec:
          rules:
            - effect: ALLOW
              condition:
                matchAny: true
            - effect: DENY
              condition:
                # !mark
                match: 'ctx.request.mysql.query.query.matches("(?i)^\\s*(drop|truncate)\\s")'
```

The following MySQL commands are authorized:

- Query commands whose SQL statement is stored in `ctx.request.mysql.query.query`.
- Prepare statement commands whose SQL statement is stored in `ctx.request.mysql.prepareStatement.query`.
- Commands that change the default database (i.e. `COM_INIT_DB`) whose database name is stored in `ctx.request.mysql.initDB.database`.

A denied command is not proxied to the upstream and the downstream receives an access denied MySQL error instead while the connection itself remains open.

## Dynamic Configuration

You can use dynamic configuration (read more about dynamic configuration [here](https://octelium.com/docs/octelium/latest/management/core/service/dynamic-config.md)) to, for example, route to different upstreams or different database users with different privileges and roles (e.g. more privileged *Users* can automatically delete tables or databases while other *Users* cannot do so) based on identity and/or context. Here is an example:

```yaml
kind: Service
metadata:
  name: example-svc
spec:
  mode: MYSQL
  dynamicConfig:
    configs:
    - name: prod
      upstream:
        url: mysql://address
      mysql:
        user: prod
        database: prod-db
        auth:
          password:
            fromSecret: prod-password
    - name: dev
      upstream:
        url: mysql://address
      mysql:
        user: dev
        database: dev-db
        auth:
          password:
            fromSecret: dev-password
    rules:
    - condition:
        match: '"prod" in ctx.user.spec.groups'
      configName: prod
    - condition:
        matchAny: true
      configName: dev
```

## Visibility

The *Service* emits access logs in real time to the audit collector. Each log provides MySQL application-layer aware information about the request such as the command type, the query details, etc.... Here is an example:

```json
{
  "apiVersion": "core/v1",
  "kind": "Log",
  "metadata": {
    // Omitted for the sake of brevity of the example
  },
  "entry": {
    "service": {
      "info": {
        "common": {
          "status": "ALLOWED"
          // Omitted for the sake of brevity of the example
        },
        "mysql": {
          "query": {
            "query": "CREATE DATABASE db01;"
          },
          "type": "QUERY"
        }
      }
      // Omitted for the sake of brevity of the example
    }
  }
}
```

As you can see in the above example, the type of this `MYSQL` access *Log* is a `QUERY`. The `MYSQL` mode currently has 14 *Log* types, including `SESSION_START`, `SESSION_END`, `QUERY`, `INIT_DB`, `CREATE_DB`, `DROP_DB`, `PREPARE_STATEMENT` and `EXECUTE_STATEMENT`, and some of these types include different detailed information according to their type. You can read more in the API reference.

You can also disable recording the statement text of the query and prepare statement commands in the access logs via the `visibility.disableQuery` field. The access logs themselves are still recorded without the statement text. This is recommended for databases whose statements routinely embed credentials or other sensitive values. Here is an example:

```yaml
kind: Service
metadata:
  name: mariadb
spec:
  mode: MYSQL
  port: 3306
  config:
    upstream:
      url: mysql://address-to-db
    mysql:
      user: root
      database: mysql
      auth:
        password:
          fromSecret: mariadb-password
      # !mark(1:2)
      visibility:
        disableQuery: true
```