Creating and Authenticating Users

Before you can authenticate a user with SAML 2.0, two conditions must be met:

  1. The user must already exist in the MemSQL database and be configured to allow SAML 2.0 authentication instead of the default authentication method.
  2. Each MemSQL aggregator node’s memsql.cnf file must be configured for SAML 2.0 authentication. The configuration process is described in the the Configuring SAML Global Variables topic.

Creating a New User

The following steps describe how to create a new user that will be authenticated using SAML 2.0.

  1. Using a MySQL client, connect to the master aggregator node as a user that has CREATE USER permissions.
  2. Replace the fictional values and execute the following statement to create a new user:
CREATE USER `johndoe`@`localhost` IDENTIFIED WITH authentication_saml;

The important part of this statement is the authentication_saml identification type. This user can no longer authenticate using the default method, but must provide a SAML 2.0 security token to establish their identity.

Authenticating a User

Once a user has been created and identified with the SAML 2.0 authentication method, they can connect to the database using a valid security token.

Using a MySQL client, connect to an aggregator node by replacing fictional values and executing the following statement:

mysql --host=localhost --port=3306 --user=johndoe --password=PHNhbWxwOlJlc3B...25zZT4= --enable-cleartext-plugin

The most important observation about this connection statement is the value of the password field. You must use a Base64-encoded SAML response as the password for a SAML-enabled user. In the example above, the password value is a truncated sample Base64-encoded SAML response — a real value will be much longer.

In addition to the Base64-encoded password, there are two other important details about this connection statement:

  • The --enable-cleartext-plugin flag ensures that the user’s password is passed to the internal SAML 2.0 authentication module in clear text. This flag is required.
  • More than one MemSQL node may be hosted at any given address, so the correct port must be specified. In this example, port 3306 is specified because it’s the default port for master aggregator nodes.
Was this article useful?