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
+ HASH)
= 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
\ref{fig:secure-protocol}.
[fig:secure-protocol.png]
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.
References
==========
[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 file. 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 available) 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 permissions. 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 authenticate. 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.
Description
===========
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
Fabric.
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.
[file:fabric-mitm.png]
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
needs.
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
"nonce".
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).
[file:rfc2617-fabric.png]
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.
References
==========
[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
Storage.
Users
-----
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Default user is 'admin' for the XMLRPC protocol:
+---------+----------+----------+----------------------------------+
| user_id | username | protocol | password |
+---------+----------+----------+----------------------------------+
| 1 | admin | xmlrpc | 1d60091873e75f7a8dadeeddf6931809 |
+---------+----------+----------+----------------------------------+
Roles
-----
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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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 |
+---------+------------+-------------------------------------------------+
Permissions
-----------
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
"core".
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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
`role_permissions`.
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Configuration File
==================
The configuration has a few changes which manages credentials. For example, the
following changes in the [protocol.xmlrpc] section:
[protocol.xmlrpc]
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
authentication.
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):
Password:
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
user:
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
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.