Essentials

A single database user can be used to grant access to the BL product databases. By default some BL products will try to connect The  BL Flow product requires using the root user that always exists in the MySQL installation. Recent 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 version 7.1.4.4 for Super Ticker and Brando version 7.5.4.2 now create 2 new secure choice the Flow installer for Chameleon creates 2 users for our apps to connect with (Player and Updater) Your options then at this point include:

- Player and Updater

CREATE USER 'BL' IDENTIFIED BY 'bl-password';  -- make your own password 

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

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 (version 7.1.4.4 for Super Ticker and Flow for Brando version 7.5.4.2) now create 2 users for our apps to use to connect with by default

• Player
  • is read-only with updates only allowed for a few select reporting tables
• Updater
  • has update privileges

The simplest but least secure option used in the past was to simply provide access to the database from remote systems use the following commands to enable non-local access for the root user account.

UPDATE mysql.user SET host = '%' WHERE host = '127.0.0.1' AND user == 'root';
FLUSH PRIVILEGES;

Required Privileges

You may create a single user that provides access for both Flow and any other BL apps you are using.

Flow

Flow requires an extensive set of privileges to allow it to update the database schema during installation and/or upgrades as well as managing the data in use.

Replication Privileges

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

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

GRANT REPLICATION 	ALTERSLAVE, 
	ALTERREPLICATION ROUTINECLIENT,
	CREATE,
	CREATE ROUTINE,
	CREATE TEMPORARY TABLES,
	CREATE VIEW,
	DELETE,
	DROP,
	EXECUTE,
	INDEX,
	INSERT,
	LOCK TABLES,
	SELECT,
	SHOW VIEW,
	TRIGGEREVENT,
	UPDATE
ON superticker.*
TO 'BL';

GRANT 
	CREATE USER, 
	RELOAD, 
	TRIGGER,
	SHOW DATABASES, 
	SUPER RELOAD
	ON *.* 
TO 'BL'@'%blbackup' WITHIDENTIFIED GRANT OPTION;

BL Apps

You could use the same user for both Flow and the BL apps. If you wish to separate them note that most BL apps run with a much more limited privilege requirements.

GRANT 
	DELETE,
	EXECUTE,
	INSERT,
	LOCK TABLES,
	SELECT,
	UPDATE
ON superticker.* 
TO 'BLapps';

Suspending Privileges

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

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

Replication Privileges

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

Replication User for copying and monitoring

BY PASSWORD '*4D8DE8683A65AFAB16B91226426A0D9116C664D6';

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 

GRANT REPLICATION SLAVE, 
	REPLICATION CLIENT,
	SELECT
	ON *.* 
TO 'blbackup' IDENTIFIED BY PASSWORD '*4D8DE8683A65AFAB16B91226426A0D9116C664D6';

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

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

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.

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