This readme file contains instructions on how to open TIBCO LogLogic SEM for integration with TIBCO Spotfire. In the following procedure 10.0.0.1 is the default Spotfire IP. This will need to be changed if server is already configured differently.

This article explains how to configure the SEM database to allow Spotfire to access it for analytics purposes.

There are 3 areas that need configured: the firewall, the database bind configuration and the database permissions. All these changes occur on the SEM appliance.

 FIREWALL
On SEM, the firewall (iptables) is enabled by default. Its configuration has to be changed in order to allow Spotfire to connect to the internal database:
1. Edit the file /home/exaprotect/scripts/exa_iptables.sh
The following line will enable the port 3306 (TCP) to be accessed from 10.0.0.1. It has to be created in the “FILTRAGE : INPUT” section at the end of it:
      # Authorize Spotfire to access the MySQL db
   $IPT -A INPUT -m state --state NEW -p tcp -m tcp -s 10.0.0.1 --dport 3306 -j log_accept_2

2. Execute the following script:
    /home/exaprotect/scripts/exa_iptables.sh

3. Save the modification into the Firewall configuration:
       iptables-save > /etc/sysconfig/iptables
 
MYSQL BIND
MySQL process binds only on the loopback interface (127.0.0.1). This means even if port 3306 (access to MySQL) is open, MySQL will not listen for anything that is not coming from the loopback interface.

1. To change that, edit this file:
       /usr/local/exaprotect/mysql/my.cnf

And change this line:
       bind-address                    = 127.0.0.1
by this one:
       bind-address                    = 0.0.0.0

2. In this case, MySQL is listening from anywhere. It is then necessary to restart the MySQL process (and the sub processes) with this command:
       /etc/init.d/exa_all restart

 
MYSQL GRANT
Now it is required to create a new user with the correct rights to access the MySQL (read-only rights to tables/databases selected):

1. Connect to mysql using this script (no authentication required) with the user “root” from the shell/bash:
       /usr/local/exaprotect-setup/scripts/others/utils/sql.sh

2. Grant the access for one user to every table of the MySQL instance “exa_<INSTANCE>”:
    grant select on exa_<INSTANCE>.* to 'spotadmin'@'10.0.0.1' identified by 'spotadmin';
or
   grant select on exa_<INSTANCE>.Exa_TAT* to 'spotadmin'@'10.0.0.1' identified by 'spotadmin';
where <INSTANCE> represents the name of the instance to be used and “10.0.0.1” represents the IP address of the Spotfire server.

Result should be like this:
Query OK, 0 rows affected (0.00 sec)

To know the list of available instances, launch the command “show databases” after being successfully connected to MySQL. The correct one should start with “exa_”
The first “spotadmin” in the SQL statement is the new user and the second “spotadmin” is the password assigned to it.

This will enable Spotfire server (10.0.0.1) to access the reporting tables (read-only) into the SEM with user spotadmin and password spotadmin on port 3306. (password and user are just for example, any can be used).