MySQL Automatic User Provisioning

Teleport can automatically create an account in your MySQL database when a Teleport user connects to the database, removing the need for creating individual user accounts in advance or using the same set of shared database accounts for all users. This guide shows you how to set up automatic user provisioning for MySQL.

How it works

Before enrolling a MySQL database with Teleport, you set up an admin user on the database with permissions to manage users. You then enroll the database in your Teleport cluster with a configuration field that includes the name of the admin user.

When a Teleport user connects to the database with a role that enables automatic user provisioning, the Teleport Database Service first connects to the database as the admin user, creates a temporary user in the database, then initiates a database session for the Teleport user as the temporary database user.

When the Teleport user closes the session, the Teleport Database Service prevents the temporary database user from accessing the database by, depending on the configuration, either removing the database user or disabling it and stripping its privileges.

Prerequisites

• Teleport cluster v14.1 or higher with a configured self-hosted MySQL or RDS MySQL database.
• Ability to connect to and create user accounts in the target database.
• Automatic user provisioning is not compatible with MySQL versions lower than 8.0.
• Automatic user provisioning is not compatible with RDS Aurora reader endpoints.

Step 1/3. Configure database admin

Teleport should be able to connect to the database as a user that can create other users and assign them roles. We recommend creating a separate user designated specifically for Teleport automatic user provisioning. Let's call it teleport-admin.

Teleport uses the same authentication mechanism when connecting as an admin user as for regular user connections: X.509 for self-hosted databases and AWS IAM for RDS.

The admin user must have privileges within the database to create users and grant them privileges. The admin user must also have privileges to monitor user processes and role assignments.

In addition, a schema is required for the admin user to log into by default. Stored procedures are also created and executed from this schema.

CREATE USER 'teleport-admin' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT SELECT ON mysql.role_edges TO 'teleport-admin' ;
GRANT PROCESS, ROLE_ADMIN, CREATE USER ON *.* TO 'teleport-admin' ;

CREATE DATABASE IF NOT EXISTS `teleport`;
GRANT ALTER ROUTINE, CREATE ROUTINE, EXECUTE ON `teleport`.* TO 'teleport-admin' ;

CREATE USER "teleport-admin" REQUIRE SUBJECT "/CN=teleport-admin";
GRANT SELECT ON mysql.role_edges TO 'teleport-admin' ;
GRANT PROCESS, ROLE_ADMIN, CREATE USER ON *.* TO 'teleport-admin' ;

CREATE DATABASE IF NOT EXISTS `teleport`;
GRANT ALTER ROUTINE, CREATE ROUTINE, EXECUTE ON `teleport`.* TO 'teleport-admin' ;

Users created by Teleport will be assigned the teleport-auto-user role in the database, which will be created automatically if it doesn't exist.

Next, configure the database admin user in the Teleport database configuration:

kind: db
version: v3
metadata:
  name: example
spec:
  protocol: "mysql"
  uri: "localhost:3306"
  admin_user:
    name: "teleport-admin"

This example assumes that you have configured the database as a dynamic resource. If you have configured your database using a static Teleport Database Service configuration, edit the entry in your db_service.databases configuration.

For auto-discovered cloud databases, the name of the admin user is taken from the teleport.dev/db-admin label.

Step 2/3. Configure a Teleport role

To specify the database roles a user should be assigned within the database, use the db_roles role option:

kind: role
version: v7
metadata:
  name: auto-db-users
spec:
  options:
    # create_db_user_mode enables automatic user provisioning for matching databases
    create_db_user_mode: keep
  allow:
    db_labels:
      "*": "*"
    db_names:
    - "*"
    # db_roles is a list of roles the database user will be assigned
    db_roles:
    - reader
    - "{{internal.db_roles}}"
    - "{{external.db_roles}}"

With automatic user provisioning, users always connect to the database with their Teleport username so the db_users role field is ignored for roles that have database user provisioning enabled.

The available provisioning modes are:

• off: Disables user provisioning.

• keep: Enables user provisioning and disables users at session end. The user will be stripped of all roles and the user account will be locked.

• best_effort_drop: Enables user provisioning and, when the session ends, drops the user if no resources depend on it. In cases where any resource depends on the user, it falls back to disabling the user, mirroring the behavior of keep mode.

Users created within the database will:

• Be assigned the teleport-auto-user role.
• Be assigned all roles from the Teleport user's role set that match the database. The role names must be valid and exist in the database.

MySQL limits usernames to 32 characters. When the Teleport username is within this limit, the user created within the database will have the same name as the Teleport username. When the Teleport username is over the 32 character limit, the user created within the database will have the name in the format of tp-<base64-sha1-teleport-username>.

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE CONCAT(USER, '@', HOST) = current_user();

SELECT * FROM INFORMATION_SCHEMA.USER_ATTRIBUTES WHERE ATTRIBUTE->"$.user" = "teleport-user-name";

Note that in case of a name conflict where a user with the same name already exists in the database and is not managed by Teleport (i.e. not assigned the teleport-auto-user role), the connection will be aborted.

Step 3/3. Connect to the database

Now, log into your Teleport cluster and connect to the database:

tsh login --proxy=teleport.example.com
tsh db connect --db-name <database> example

To view the list of database roles that are allowed for each database, you can use the command tsh db ls -v. By default, all database roles will be assigned to your auto-provisioned database user. You can optionally select a subset of the database roles with --db-roles:

tsh db connect --db-name <database> --db-roles reader example

Troubleshooting

Access denied to database error

By default, the newly created users won't have permissions to access any particular database. These permissions should be granted through database-specific roles, such as reader.

Otherwise you may see errors like the following:

tsh db connect --db-name <database> example
ERROR 1105 (HY000): ERROR 1044 (42000): Access denied for user '<your-teleport-username>'@'%' to database '<database>'

Table is read only error

You may encounter the following error when connecting to an Amazon RDS Aurora reader endpoint:

tsh db connect --db-name <database> example
ERROR 3501 (HY000): The ACL operation failed due to the following error from SE: errcode 165 - Table is read only

Database auto-user provisioning is not compatible with RDS Aurora reader endpoints. Please use auto-user provisioning on the primary endpoints.

Use your mapped remote username error

You may encounter the following error when connecting to a database in a remote cluster:

> tsh db connect --db-name <database> exampleERROR: please use your mapped remote username ("remote-<your-teleport-username>-<root-cluster-name>") to connect instead of "<database-user>"

When you access resources in a remote cluster, the remote cluster will receive the name remote-<your-teleport-username>-<root-cluster-name> from the local cluster. This is to prevent any naming collisions with users in the remote cluster. Please use the username from the error message as the database username for when connecting through tsh or GUI clients.

Next steps

• Connect using your GUI database client.
• Learn about role templating.
• Read automatic user provisioning RFD.