WL#7455: Credentials for Fabric

Status: In-Documentation

When connecting to a Fabric instance, we need to ensure that we
authenticate the user with the Fabric instance. The purpose is to
avoid that connectors manipulate the meta-data stored in the Fabric
backing store either intentionally or accidentally without having
proper privileges.

High-Level Description 

The normal usage of a connector is that the user connects through the
connector and provide a user and a password as part of setting up a
virtual connection:

  import mysql.connector
  fabric = Fabric(host="fabric.example.com")
  conn = connector.connect(fabric=fabric, user="mats", password="xyzzy")

When authenticating, there are now two servers that the authentication
need to be done to: the MySQL server that is finally going to be
chosen, and MySQL Fabric. For the MySQL server there is already an
authentication protocol following a traditional zero-knowledge
password authentication approach[1], but for authentication
towards MySQL Fabric there is no defined protocol.

The idea is to use the user and password provided for both access the
servers in the farm as well as the Fabric node itself, while other
parameters (such as the database) will be relevant only for the
servers that you connect to.

Keeping the same user for both Fabric and the managed servers avoid
managing separate users for the servers and for Fabric. In addition,
since Fabric is using a MySQL server as backing store, it is possible
to keep different privileges on the servers in the farm and the
meta-data. It also allows control of user privileges independently on
all accessed servers.

Despite this, it will in some cases be necessary to provide a
dedicated Fabric user through the parameters to the Fabric
instance. This could be the case if, for example, a single read-only
user is distributed to all connectors but they authenticate towards
the managed servers using a different user. For that reason, the
design support using different users for Fabric and the managed
servers, but the default is to use the same user for both the managed
servers and the Fabric node.

Secure Authentication in MySQL 

In MySQL 4.1 and later, the secure password protocol is implemented in
the follows:

The ~mysql.user~ table store a HASH of the password for the user
stores where HASH = SHA1(SHA1(password)).

1. The server sends a random string (scramble) to the client
2. The client calculates:
   - H1 = SHA1(password), using the password that the user has entered.
   - token = SHA1(scramble + SHA1(H1))) XOR H1
3. The client sends the token to the server
4. The server calculates
   - H1' = token XOR SHA1(scramble + HASH))
5. The server compares SHA1(H1') and HASH
6. If they are the same, the password is okay.

Note that:
H1' = token XOR SHA1(scramble + HASH)
     = SHA1(scramble + SHA1(H1) XOR H1 XOR SHA1(scramble + HASH)
     = SHA1(scramble + SHA1(SHA1(password)) XOR SHA1(password) XOR SHA1(scramble
     = SHA1(scramble + HASH) XOR SHA1(password) XOR SHA1(scramble + HASH)
     = SHA1(password)

So SHA1(H1') = SHA1(SHA1(password)) = HASH.

The protocol can be described with the sequence diagram in Figure


The secure password authentication protocol is designed to be safe in
the sense that the password should not be sent in clear-text, but it
does not protect against man-in-the-middle attacks (MITMA). This is a
property that we exploit when creating an authentication protocol for
Fabric. In short, the connector will authenticate towards Fabric as if
it authenticated towards the backing store that Fabric uses for
storage of meta-data: the Fabric node is "transparent" for
authentication purposes.


[1] [http://en.wikipedia.org/wiki/Zero-knowledge_password_proof]
General Requirements
1.1. Fabric shall be stored in the Fabric Data Storage and use a role-based
system to give users access to features.

1.2. There will be an administrative user that can do everything with a
mandatory password.

1.3. The Fabric setup mechanism shall create users and must ask for a password
for the administrative user.

1.4. For each operation that is not starting or setting-up Fabric node or data
storage, the Fabric client tool will ask for the administrative password.

1.5. It shall be possible to disable authentication through the configuration

1.6. It shall be possible to add a default authentication user and/or password
to the configuration file in the XMLRPC section. This shall be used to
authenticate and not prompt for credentials all the time.

1.7. Connectors must authenticate with the Fabric Node(s) using a Fabric
specific user and password. Since we use XMLRPC we shall use Digest
Authentication as described in RFC 2617 using MD5 (since this hashing commonly

1.8 Each user shall be associated with a particular protocol. It will be
possible to set the same or different password per protocol, but the hashing
the password is different for each protocol.

1.9. Each user will be given one or more roles. Each role shall contain

1.10. The user password for connecting using XMLRPC stored in the Fabric Data
Storage shall be hashed (and salted) as specified in the RFC2617.

1.11. The default realm for XMLRPC is 'MySQL Fabric'. This will be configurable
using the configuration file.

User Administration Requirements
The Fabric command line tool will have a new manage command to add users and
assign roles to them. This will be done through options on the command line and
through a wizard.

2.1 Command line tool will be able to add users to Fabric asking for password.
Password are not allowed to be empty.

2.2 Adding user will ask for password and ask which role the user can have. It
will be possible to choose multiple roles either by ID or by name.

2.3 It will be possible to change the password for a user for a given protocol.

2.4 It will be possible to remove a user for a given protocol.

2.5 It shall be possible to list all users as well as roles.

2.6 All commands will have an option which will allow to select with which user to 

SSL Requirements
We do not support clear text password, neither for storage or sending over
the wire. Passwords are hashed through digest authentication by the client and
for each protocol the password is stored salted and hashed.
However, we do support SSL.

3.1 We will support with WL#7455 whatever Python can support. We shall not
be able to verify the certificate.

3.2 The procedure to create the SSL CA, certificate and client keys can be
created and used just like with the MySQL server.

3.3. The Fabric server will have 3 new options for the XMLRPC protocol which
will point to the CA of the server, the certificate and the private key.

3.4. When SSL is active, no non-SSL connection will be possible and connectors
and other clients using the Fabric node will need to use SSL.

Authentication towards the managed servers is already established and
the traditional protocol will be used for that. This section will
therefore specify authentication towards the Fabric node.

In Figure \ref{fig:fabric-MITMA} you see an example of an
authentication done on behalf or an application. The application
establishes a logical session with the Fabric-aware connector and
provide the password. The Fabric-aware connector authenticates and
fetch data for the internal cache, after which is disconnect from

In the diagram, we have ignored the error case where the user provides
an incorrect password. This do not affect the validity of the diagram,
but simplify the description.


This allow the privileges for the meta-data and the application data
to be managed separately. For example, an application user might be
granted read and write permissions on the application servers but not
allowed to change any meta-data.

There are a few assumptions being made that affect the design:
- We try to keep the call sequence the same as for the server so that
  it is possible to use the same code for authentication when
  MySQL-RPC is implemented.

Detailed Description 

Connector Interface Changes 

There are no real changes to the interface of the connector, but the
use of the parameters are specified. For the purpose of this
specification, we will outline how the different parameters are used.

Note that when connecting to fabric, some parameters are dependent on
the protocol being used. For the time being, Fabric only support
XML-RPC protocol, so we consider only this case here.

When connecting to the Fabric node, the user, password, port, and host
can be provided. Host, user and password are required. The default realm for 
authentication will be 'com.mysql.fabric.xmlrpc'.

To illustrate how the user and password is used, we will here consider
two examples. The user and password used to authenticate towards the
Fabric node is given in the table below.

In the following example we have 2 users defined. The 'admin' user is default 
administrative user. The 'connector' we have added with a role having a 
permission for using the 'dump' commands.

             connector user         admin user            
  host       ~fabric.example.com~   ~fabric.example.com~  
  port       32274                  32274                 
  user       ~connector~            ~admin~           
  password   ~xyzzy~                ~magic~
  role       'dump'                 'superadmin'

Example #1 

This is a straightforward use of the connector interface and is the
standard case of how the connector interface will be used.

  import mysql.connector
  fabric_config = {
    'host': "fabric.example.com",
    'username': "connector",
    'password': "xyzzy",
  conn = connector.connect(fabric=fabric_config,
                           user="mysqluser", password="secret")

The user and password provided to the connector will be used to
authenticate towards the Fabric node. In this case, the user 'connector' will 
only be able to execute 'dump' commands.

Example #2 

  import mysql.connector
  fabric = Fabric(host="fabric.example.com",
                  user="admin", password="magic")
  conn = connector.connect(fabric=fabric, user="mats", password="xyzzy")

Since 'admin' has the 'superadmin' role, all commands can be executed which are 
available through XMLRPC.

Fabric Interface Changes 

The interface to the Fabric is dependent on the protocol used, so we
need to handle each protocol separately. The only protocol currently
implemented is the XML-RPC protocol, but since one of the goals is to
allow the same authentication mechanism for authenticating to the
Fabric node as for a MySQL server, we mention the MySQL Protocol
briefly here.

XML-RPC Protocol 

XML-RPC is based on HTTP where the payload is XML. Normally, a
connection is established and the RPC is sent to the server after
which the reply is processed.

Since several packets are sent over each connection, and the scramble
have to be sent back to the client, it is necessary to keep the HTTP
protocol open for the duration of the request, which is something
supported by HTTP/1.1 but not HTTP/1.0.

Included with HTTP/1.1, an authentication scheme[2] similar
to the MySQL Secure Authentication scheme is provide. We use this
protocol to piggyback on that specification and adapt it to suit our

Some parameters provided in the specification[2] are given
specific meanings:

algorithm: As part of the specification, an algorithm can be
               provided, which default to MD5.
nonce: In the negotiation, the scramble is passed back as the

The hashing in RFC2617 is defined in terms of two functions ~KD~ and
~H~, which we define as:

  H(x) = MD5(x)
  KD(password, scramble) = H(scramble + H(H(password)))) XOR H(password)

A typical session using RFC2617 looks like in Figure
\ref{fig:rfc2617-fabric}, where the header are shown with the relevant
values highlighted.

The digest authentication scheme is not supported in the standard
Python library, but there are other libraries that support it, such as
~request~[3] (distributed under the Apache License).


MySQL-RPC Protocol 

The MySQL-RPC protocol does not exist yet, but implementation of the
handshake and querying is straightforward: it just follows the same
protocol as for the server.

Fabric Executor Changes 

Since procedure execution access the backing store, some requirements
are made on how the connection is managed. There are two types of
queries that we need to support: procedure execution and read-only
requests. For both cases, authentication is done in the same manner.

For procedure execution, the challenge-response can be executed when
the request is sent to the Fabric node. In contrast to RFC2617, it is
not possible to re-use a token that has been computed[4], so
for that reason, it is necessary to establish a connection to the
backing store when the procedure execution is requested. The
connection then need to be maintained while the procedure
executes. Note that the password is available in the client only: the
Fabric node do not have the password so if the connection is lost, it
is not possible to re-authenticate. For this reason, if the connection
is lost, procedure execution have to be aborted or, if a

For a read-only request, the connection is short-lived so there should
be a small risk of losing the connection. The connection still need to
be maintained for the duration of the request.

Open Issues 

Halting procedures 

Instead of aborting procedure execution on lost connection, it might
be possible to just halt the execution and allow the administrator to
resume the execution by providing a password.


[2] Available at [http://tools.ietf.org/rfcmarkup?doc=2617]

[3] [https://pypi.python.org/pypi/requests]

[4] The reason is because the scramble is re-computed each
time and not passed back with the authentication response. Because of
this, there is no way for the server to check that a nonce is not
replayed and have to issue a new nonce with each connection.
Credential storage in Fabric Data Storage

The credentials are role-based and stored in several tables inside the Fabric

The Fabric users are stored in the Fabric Data Storage in the `users` table. A
user is defined by a username and a protocol. The password is stored specifically
for each protocol. For example, for XMLRPC we store the password as described in
RFC2617. For other protocols in the future, for example using MySQL protocol, we
could store it using the MySQL function `PASSWORD()`.

CREATE TABLE `users` (
  `user_id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `protocol` varchar(200) NOT NULL DEFAULT 'xmlrpc',
  `password` varchar(128) DEFAULT NULL,
  PRIMARY KEY (user_id),
  UNIQUE KEY (`username`, `protocol`)

Default user is 'admin' for the XMLRPC protocol:

| user_id | username | protocol | password                         |
|       1 | admin    | xmlrpc   | 1d60091873e75f7a8dadeeddf6931809 |

We are using a role-based system for giving permissions to users. This is rather
a simple implementation but should serve for now.

Each role is defined in the table named `role`:

CREATE TABLE `roles` (
  `role_id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `description` varchar(1000),
  PRIMARY KEY (role_id)

The default roles are:
| role_id | name       | description                                     |
|       1 | superadmin | Role for Administrative users                   |
|       2 | useradmin  | Role for users dealing with user administration |
|       3 | connector  | Role for MySQL Connectors                       |

We define permissions in the `permissions` table. Each permissions is made up by
a subsystem, component and a function. Currently, there are not real subsystems
available in Fabric but we wil define Fabric itself as a system and call it

CREATE TABLE `permissions` (
  `permission_id` int unsigned NOT NULL AUTO_INCREMENT,
  `subsystem` varchar(60) NOT NULL,
  `component` varchar(60) DEFAULT NULL,
  `function` varchar(60) DEFAULT NULL,
  `description` varchar(1000),
  PRIMARY KEY (permission_id),
  UNIQUE INDEX (`subsystem`, `component`, `function`)

The default permissions inserted are:
| permission_id | subsystem | component | function | description                                  
|             1 | core      | NULL      | NULL     | Full access to all core 
Fabric functionality |
|             2 | core      | dump      | NULL     | Access to dump commands                      
|             3 | core      | user      | NULL     | User administration                          
|             4 | core      | role      | NULL     | Role administration                          
|             5 | core      | threat    | NULL     | Reporting to Fabric                          

A user with permission 1 will have access to all components and all functions in
those companents in the core subsystem. The permission with id 2 will give
access to the dump component in the core system and all its functionality. When
a value is missing for component or function, this means as much as a wildcard.
Note that there is no permission for giving access to every subsystem.

Mapping Permissions to Roles
Each role is made up by permissions. We define this mapping in the table

CREATE TABLE `role_permissions` (
  `role_id` int(10) unsigned NOT NULL,
  `permission_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `fk_role_permissions_permission_id` (`permission_id`),
  CONSTRAINT `fk_role_permissions_role_id` FOREIGN KEY (`role_id`)
   REFERENCES `roles` (`role_id`),
  CONSTRAINT `fk_role_permissions_permission_id` FOREIGN KEY (`permission_id`)
   REFERENCES `permissions` (`permission_id`)

Mapping Roles to Users
A user is assigned one or more roles (and consequently permissions) through the
table `user_roles`:

CREATE TABLE `user_roles` (
  `user_id` int(10) unsigned NOT NULL,
  `role_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `fk_user_roles_role_id` (`role_id`),
  CONSTRAINT `fk_user_roles_user_id` FOREIGN KEY (`user_id`)
   REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_roles_role_id` FOREIGN KEY (`role_id`)
   REFERENCES `roles` (`role_id`) ON DELETE CASCADE

Configuration File
The configuration has a few changes which manages credentials. For example, the
following changes in the [protocol.xmlrpc] section:

address = localhost:32274
realm = MySQL Fabric
user = admin
password =
disable_authentication = no
ssl_ca = /path/to/fabric_ca.pem
ssl_key = /path/to/fabric_key.pem
ssl_cert = /path/to/fabric_cert.pem

The above means that the admin user will be used for authenticating with Fabric
when using the `mysqlfabric` tool. You can also add the password.
The `disable_authentication` setting has to be set to 'yes' to disable all

The SSL CA, certificate and private key files can be entered as absolute
file locations. If relative is used, it will be relative to the folder from
which the configuration file is read.

Digest Authentication
Note that we do not implement all of RFC2617. For example, auth-int and auth-
sess as well as sending 'stale' to the client is not implemented.

The SimpleXMLRPCRequestHandler coming with Python does not implement
authentication. Therefor, we need to overload some functionality to send a 401
error to the client. Most of this is done in the `do_POST()` method which
handles XMLRPC commands. After making sure the user is valid and is
authenticated, we have to check if the user has permission for executing the
XMLRPC command. This is done in the handler's `decode_request_content()` method.

The XMLRPCServer itself needs to manage the nonces it generates and needs to
also expire them. When a client connects and is not authorized, the XMLRPCServer
generates a nonce and provides it to the client. The client does magic and gives
a response. Using the username, the realm and URI, the server can verify the
response and authenticate. The nonce can only be used by one client connection.
The server stores in and periodically expires unused nonces.

The XMLRPCServer also keeps a counter per nonce. Each time the client reuses a
nonce, the server checks if the client has the expected counter. If not, the
client is not authorized.

The hashing function used is MD5. If we can support different once with
other client, we can add them on request.

Fabric Setup
Fabric is setup exactly as before, but at the end we ask for the password for
the administrative user 'admin':

    Finishing initial setup
    Password for admin user is not yet set.
    Password for admin:

Passwords can't be empty and it has to be set.

When the configuration files has a username and password set for the 'admin' user 
in the [protocol.xmlrpc] section, no user input required. However, if the user is 
different than 'admin', for example 'scott', a user 'scott' will be created using 
the password found in the configuration file, but _no_ roles will be assigned. 
Since we have no password for the 'admin' user, there will be a prompt like above.

Adding Users
Adding users is accomplished with the `user add ` command. It will
go through all steps adding the user:

    shell> mysqlfabric user add scott
    Add a new Fabric user
    Username: scott
    Protocol (default xmlrpc):
    Repeat Password:

    Select role(s) for new user
      ID  Role Name   Description and Permissions
      --  ----------  ---------------------------
       1  superadmin  Role for Administrative users
                      + Full access to all core Fabric functionality
       2  useradmin   Role for users dealing with user administration
                      + User administration
                      + Role administration
       3  connector   Role for MySQL Connectors
                      + Access to dump commands
                      + Reporting to Fabric

    Enter comma separated list of role IDs or names: 2
    Fabric user added.

It's possible to enter multiple roles by either ID or name:

    Enter comma separated list of role IDs or names: 2, connector

The `user add` command has two options to reduce interaction. However, a password 
will always be required to be entered interactively:
* --protocol
* --roles, this is a comma separate list of roles

Changing roles of Users
It is possible to change or remove all of them for a particular user using
the `user roles ` command:

    shell> mysqlfabric user roles scott
    Change roles for a Fabric user
    Username: scott
    Protocol (default xmlrpc): 
    Select new role(s) for user, replacing current roles.
    Current roles are marke with an X.
      ID  Role Name   Description and Permissions
      --  ----------  ---------------------------
       1  superadmin  Role for Administrative users
                      + Full access to all core Fabric functionality
    X  2  useradmin   Role for users dealing with user administration
                      + User administration
                      + Role administration
       3  connector   Role for MySQL Connectors
                      + Access to dump commands
                      + Reporting to Fabric
    Enter comma separated list of role IDs or names:

Note that the current roles are marked with an 'X'.

The `user roles` command has two options to reduce interaction:
* --protocol
* --roles, this is a comma separate list of roles

Changing Password of Users
Use the `user password ` command to change the password of a

    shell> mysqlfabric user password scott
    Change password a Fabric user
    Username: scott
    Protocol (default xmlrpc):
    New password:
    Repeat Password:
    Password changed.

The `user password` command has two options to reduce interaction:
* --protocol

Deleting Users
To delete a Fabric user, you can use the `user delete ` command:

    shell> mysqlfabric user delete scott
    Delete a Fabric user
    Username: scott
    Protocol (default xmlrpc):
    Really remove user scott/xmlrpc? [y/N] y
    Removing user cancelled.

The `user password` command has two options to reduce interaction:
* --protocol
* --force, do not prompt for confirmation

Listing Users
To list users, use the `user list` command:

    Username  Protocol     Roles
    --------- ------------ --------------------
    ham       xmlrpc       superadmin, useradmin
    admin     xmlrpc       superadmin
    spam      xmlrpc       useradmin
    foo       xmlrpc       (no roles set)

Listing Roles
To list the currently available roles, you can use `role list` command:

    shell> mysqlfabric role list
      ID  Role Name   Description and Permissions
      --  ----------  ---------------------------
       1  superadmin  Role for Administrative users
                      + Full access to all core Fabric functionality
       2  useradmin   Role for users dealing with user administration
                      + User administration
                      + Role administration
       3  connector   Role for MySQL Connectors
                      + Access to dump commands
                      + Reporting to Fabric