Essentials

...

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 (version 7.1.4.4 for Super Ticker and Flow for Brando version 7.5.4.2) now create 2 3 users for our apps to use to connect with by default. (The blade user was added in build 8.6.2.5)

• Blade

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

• Player

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

• Updater

  • has update and execute privileges

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,

...

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 WITH mysql_native_password AS '*4D8DE8683A65AFAB16B91226426A0D9116C664D6';

-- 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,
	SHOW VIEW,
	EVENT,
	TRIGGER,
	SHOW DATABASES,
	RELOAD
	ON *.* TO 'blbackup'@'%';

...

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

Code Block
language sql
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.

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 

• Administrative Roles

  • ReplicationAdmin

  • BackupAdmin

For backups

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

• SHOW VIEW

• EVENT

• TRIGGER

If dumping all databases at once

• SHOW DATABASES

If pulling master data from a server

• RELOAD

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

REVOKE ALL PRIVILEGES ON superticker.* FROM 'BL';

...

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

CREATE USER 'BLreadonly' IDENTIFIED BY 'bl-password';  -- make your own password 
GRANT SELECT ON `superticker`.* TO 'BLreadonly';
GRANT EXECUTE ON FUNCTION `superticker`.`CalcUtilityStatus` TO 'BLreadonly'; 

...