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. 

...

-- 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:

...

-- 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

...

You may choose to grant the privileges to an existing user or create a specific user for replication on the master/primary server.  That 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 

...

• SHOW VIEW

• PROCESS (as of MySQL 8.x)

• EVENT

• TRIGGER

• SHOW DATABASES

• RELOAD

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) 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.

...

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) in the parameter group and apply it.)

...