Common Reference

MySQL User Configuration

It is possible to use users with a lower-privilege level and without as many rights. For more information, see "MySQL Unprivileged Users".

The tungsten user connects to the MySQL database and applies the data from the replication stream from other datasources in the dataservice. The user must therefore be able execute any SQL statement on the server, including grants for other users. The user must have the following privileges in addition to privileges for creating, updating and deleting DDL and data within the database:

SUPER privilege (MySQL 5.x) is required so that the user can perform all administrative operations including setting global variables.
If using MySQL 8+ then the CONNECTION_ADMIN privilege should be used as well as the SUPER privilege. In future MySQL releases it is expected that SUPER will eventually be removed.
GRANT OPTION privilege is required so that users and grants can be updated.

To create a user with suitable privileges:

mysql> CREATE USER tungsten@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO tungsten@'%'  WITH GRANT OPTION;

The connection will be made from the host to the local MySQL server. You may also need to create an explicit entry for this connection. For example, on the host host1, create the user with an explicit host reference:

mysql> CREATE USER tungsten@'host1' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO tungsten@'host1'  WITH GRANT OPTION;

The above commands enable logins from any host using the user name/password combination. If you want to limit the configuration to only include the hosts within your deployment you must create and grant individual user/host combinations:

mysql> CREATE USER tungsten@'client1' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO tungsten@'client1'  WITH GRANT OPTION;

For Tungsten Cluster users

If you later change the cluster configuration and add more hosts, you will need to update this configuration with each new host in the cluster.

Applies to: Tungsten Clustering for MySQL

Tungsten Connector requires a user that can be used as the application user to connect to the MySQL server. The login will allow connections to the MySQL databases servers to be used in a consistent fashion across different hosts within the cluster. You must configure this user with access to your database, and then use it as the 'application' user in your cluster configuration.

mysql> CREATE USER app_user@'%' IDENTIFIED BY 'password!';
mysql> GRANT ALL ON *.* TO app_user@'%';
mysql> REVOKE SUPER ON *.* FROM app_user@'%';
mysql8+> REVOKE CONNECTION_ADMIN ON *.* FROM app_user@'%';

If using MySQL 8+ then the use of SUPER is deprecated in favour of the CONNECTION_ADMIN privilege, at the time of publishing, both privileges exist and should be revoked. It is advisable to check the official documentation for your specific release of MySQL to check which roles are available and in use. If your application user is granted either one of these privileges, it is possible for that user to still make DML changes to a Read-Only replica which could result in data corruption.

Additional application user logins can be configured by using the user.map file within your Tungsten Cluster configuration.

As noted above, the creation of explicit host-specific user entries may be required.

In situations where only minimal privileges are desired for the required application user, nothing additional is needed beyond the implied usage granted by the create user command:

mysql> CREATE USER app_user@'%' IDENTIFIED BY 'password!';
mysql> SHOW GRANTS FOR app_user;
+-----------------------------------+
| Grants for app_user@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'app_user'@'%' |
+-----------------------------------+

The Connector requires this user to be able to gather critical configuration information, listed below:

An internal-only show slave status request needs access to the trep_commit_seqno table in the replication tracking schema:
```
tungsten_%DATASERVICE%.trep_commit_seqno
```
Configuration fetch:
```
select @@wait_timeout;
```
Configuration fetch:
```
select @@version;
```

Configuration fetch:

SELECT ID FROM INFORMATION_SCHEMA.COLLATIONS WHERE COLLATION_NAME=@@collation_server LIMIT 1;

Keepalive mechanism:
```
SELECT 'KEEP_ALIVE';
```
Calling a tungsten command inside the Connector during a command-line client session:
```
tungsten connection status;
```
will execute the following SQL query:
```
SHOW STATUS LIKE 'ssl_cipher';
```
Calling a tungsten command inside the Connector during a command-line client session:
```
tungsten show processlist;
```
will execute the following SQL query:
```
SHOW FULL PROCESSLIST;
```

If you configure the connector to run in Proxy mode, and you issue the show replica status command (show slave status in MySQL version up to, and including, 8.0), then any user executing this statement will require the select privilege on the tracking schema table trep_commit_seqno. The following DDL can be used as an example:

GRANT SELECT ON tungsten_<servicename>.trep_commit_seqno TO '<user>'@'<host>';

This will need to be executed after installation, following the initial creation of the tracking schema and tables.

Tungsten User Login​

MySQL Application Login​

Tungsten User Login

MySQL Application Login