Configuring MySQL Users

Owned by Jonathan Schmale
Last updated: Sept 04, 2024
6 min read

Essentials

The  BL Flow product uses the root MySQL user by default during installation. The newer versions of MySQL server install without access to the root user from outside the localhost (i.e. it cannot be used from any other server except its own.) To support this secure choice the Flow installer for Chameleon creates users for our apps to connect with - Blade, Player, Updater and FLOW. 

Remote Privileges and Supporting Apps

During the MySQL install, the user is no longer asked if they would like to allow remote access for the root user. Limiting root access to the local machine only is a good security practice. To support this Flow for Chameleon now creates specific users for our apps to use to connect with by default.

  • Blade

    • is a read-only user to allow accessing data for display only - no update privileges

    • added in 8.6.2.5

  • Player

    • is read-only with updates only allowed for a few select reporting tables

    • added in 7.1.4.4

  • Updater

    • has update and execute privileges

    • added in 7.1.4.4

  • FLOW

    • is a full access user for installing and running Flow.

    • added in12.12.3.1

Further to this Flow has a configuration preference value that can be set that allows other BL apps to connect to the correct database server when they aren't running on the database server. It is the System > Database Location for Apps setting.

How The MySQL Users are Defined

The following MySQL code is a sample of how the users are created.

-- to use the default password authentication method CREATE USER 'Player'@'%' IDENTIFIED BY 'password'; CREATE USER 'Blade'@'%' IDENTIFIED BY 'password'; CREATE USER 'Updater'@'%' IDENTIFIED BY 'password'; -- to use the new caching_sha2_password instead add a WITH clause like: -- CREATE USER 'Player'@'%' IDENTIFIED WITH caching_sha2_password BY 'password'; GRANT SELECT ON `chameleon`.* TO 'Player'@'%'; GRANT SELECT ON `chameleon`.* TO 'Blade'@'%'; GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE ON `chameleon`.* TO 'Updater'@'%'; -- as of MySQL 8.0.35 use of [The treatment of % by the server as a synonym for localhost] is deprecated: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-35.html GRANT SELECT ON `chameleon`.* TO 'Player'@'localhost'; GRANT SELECT ON `chameleon`.* TO 'Blade'@'localhost'; GRANT SELECT, UPDATE, INSERT, DELETE, EXECUTE ON `chameleon`.* TO 'Updater'@'localhost';

MySQL User Passwords

The passwords assigned to the user accounts can be customized. Note there are only a few restrictions on the allowed characters for use in apps and in the Flow installer:

Apps

Any password used in an app might not allow a semicolon ; if it doesn’t have updated password handling. Other characters such as ” may also fail.

Flow Installer

Pre 12.11.2.3

A password used for Flow installer may not work correctly with some special characters including: ;, ^, ”

12.11.2.3 or newer

A password used for Flow installer is expected to work correctly will all characters with one caveat:

If the password includes a double-quote character ” then you will need to escape it with a backslash when used in the installer form. Example: pass”word would need to be entered as pass\”word.

Replication Privileges

In situations where you want to use replication to provide a ongoing backup source you need a user with specific privileges.

Replication User Using Workbench

 If you are doing so using the MySQL Workbench app you may find that it's easy to get the necessary privileges on the user account by selecting the 

Login Info

  • Administrative Roles

    • ReplicationAdmin

    • BackupAdmin

    •  

Global Privileges

If you want to use this user for dumping the data from the server it will need additional Global privileges.

  • SHOW VIEW

  • PROCESS (as of MySQL 8.x)

  • EVENT

  • TRIGGER

  • SHOW DATABASES

  • RELOAD

Recommended Replication User

Creating a user with these privileges will provide the ability to do everything required for master dump for for a replication reset through to continuous replication monitoring.

You will want to create this user on both the master and the backup/slave servers.

Using SQL

Create and grant privileges required by a replication user

-- check if user exists SELECT EXISTS(SELECT 1 FROM `mysql`.`user` WHERE `user` = 'blbackup' AND `host` = '%') AS 'BackupUserExists 1=Yes'; -- create the user if it doesn't exist CREATE USER 'blbackup'@'%' IDENTIFIED BY 'pwd'; -- Add a WITH clause if necessary: WITH caching_sha2_password -- it goes after IDENTIFIED: WITH mysql_native_password -- if not specified it uses the system default password format -- grant privileges to user (can be more limited if you prefer - see notes below) GRANT REPLICATION SLAVE ON *.* TO 'blbackup'@'%'; GRANT REPLICATION CLIENT ON *.* TO 'blbackup'@'%'; GRANT SELECT, PROCESS, SHOW VIEW, EVENT, TRIGGER, SHOW DATABASES, RELOAD ON *.* TO 'blbackup'@'%'; -- MySQL 8 added requirement for the PROCESS priv -- as of MySQL 8.0.35 use of [The treatment of % by the server as a synonym for localhost] is deprecated: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-35.html -- so repeat above with the use of localhost instead of %

Replication User for essential replication support

This option describes in more detail the options you have if you want a more restricted replication user and how to build privileges on to increase functionality.

You may choose to grant the privileges to an existing user or create a specific user for replication on the master/primary server. That account will need only the global privilege:

  • REPLICATION SLAVE.

The DMan app can be used in scenarios with replication to provide status monitoring. To do so it will require the privileges:

  • REPLICATION CLIENT

  • SELECT 

Using SQL 

Privileges required by a replication user

GRANT REPLICATION SLAVE ON *.* TO 'blbackup'@'%'; GRANT REPLICATION CLIENT ON *.* TO 'blbackup'@'%'; GRANT SELECT ON *.* TO 'blbackup';

NOTE that the REPLICATION privileges can only be granted at the global level and not specific database on the server.

Flow User Sample

Create a user with all the necessary privileges to install and run Flow for Chameleon. SUPER privilege is not required assuming server is configured correctly (need log_bin_trust_function_creators=1 set (deprecated in MySQL 8.0.34 but no other option made available so keep using it for now) when bin logging enabled.

The use of 127.0.0.1 instead of localhost below is used to enable connecting to a second instance of MySQL on a non-default port. If you use localhost the MySQL command line interface ignores any other port specification and always uses the default 3306 port.

Instead of SUPER in MySQL 8.x I think we can use: SET_USER_ID but still need log_bin_trust_function_creators=1

This privilege can be found by using SELECT * FROM mysql.global_grants;

FLOW user and Stored Procedures error

If you try to run the procedure scripts with a FLOW user but they were originally created by the root user then you will get an error in MySQL 8.0.16 or greater. The error will tell you you need the SYSTEM_USER privilege. To resolve - you can temporarily assign the SYSTEM_USER privilege to allow the FLOW user to drop the code created by root and recreated them. Once created by FLOW the user retains the right to drop them in the future. (Obviously this only works in situations where you have a MySQL login with the ability to grant SYSTEM_USER to the FLOW user. You probably won’t on an RDS instance and then you need to set log_bin_trust_function_creators to 1 (deprecated in MySQL 8.0.34 but no other option made available so keep using it for now) in the parameter group and apply it.)

 

Suspending Privileges

You may want to temporarily disable the access to the database. In that case you would use a REVOKE command:

Remove Privileges from the superticker database from the BL user

Read-Only Privileges

If you wished to grant read-only privileges you would need to assign:

  • SELECT ON all tables

  • EXECUTE ON FUNCTION CalcUtilityStatus

Flow will then be able to display the content but no changes will be able to me made. Note that Flow is not truly designed to operate in view mode except when using a browsing only user and as a result when using other user types you may encounter errors if you try to perform an action that requires a change to the content.

Flow version will need to be greater then 6.11.4.2 to support even this basic access when only SELECT privileges are given.

Grant view-only access to superticker for user named BL