GDPR Compliance - the ‘Right to be Forgotten'

1. Open HeidiSQL. The following screen appears (Fig. 1):

   

   Fig. 1 - HeidiSQL - Session Manager

2. At the bottom left of the screen, click on New > Session in root folder (Fig. 2).

   

   Fig. 2 - HeidiSQL - Session in Root Folder

   A new session appears called Unnamed with its details in the panels to the right. For simplicity, we will leave this session as its default name.

   

   Fig. 3 - HeidiSQL - Settings/ Advanced

3. In the Settings and Advanced tabs (Fig. 3), enter the appropriate details for location and access to the calling list. If using Softdial Campaign Manager™ in production, this will have been set in Database Input.

   As the location is dependent on the customer’s preferences, administrator access may be required to navigate to and edit the file.

   For this walk-through, the server is local and there is no authentication access, so we can use the password root with the default settings.

4. To start the session and gain access to the available databases, at the bottom right, click Open. A new window appears, similar to Fig. 4 below.

   

   Fig. 4 - HeidiSQL - New Session

   This window displays the following:

   • Left pane - database navigation
   • Right pane - database details
   • Bottom pane - the query code used to display the options
5. In the left pane, click on the database sccdemo. The following screen appears (Fig. 5).

   

   Fig. 5 - HeidiSQL - sccdemo database

6. The database sccdemo contains only one table, mobiles, shown in the right pane. This is where the personal information is kept (Fig. 6). Double click mobiles to open the table.

   

   Fig. 6 - HeidiSQL - mobiles table

At the top of the screen, the tabs of interest in this walk-through are;

1. Table: mobiles

   This tab displays details for the table, including the column headings. Other fields control the type and amount of data that goes into each of the table’s fields. Reading the names of the headings for the table (Name column) will likely give you a good idea of which fields contain personal information and which do not; e.g. Forename and Surname certainly do, whilst Agent_Result does not.

2. Data

   Displays the table's data

Click on the Data tab.

Fig. 7 - HeidiSQL- Data tab

Fig. 7 shows just some of the fields holding personal data that might be found in an SCC calling list. In the mobiles table, while the names given to the example contacts are comprehensible, the addresses are just random characters. By default, the list is ordered by its Key field, Contact_ID.

As the data is not editable through this screen, we will enter SQL commands via the last tab at the top of the page; Query.

1. Click on the Query tab (Fig. 8):

   

   Fig. 8 - HeidiSQL - Query tab

   The page is split into two horizontally:

   • the top half is for writing an SQL query
   • the bottom half is for displaying the results of that query
2. To test the database’s query search functions, we will enter a simple SELECT statement that should return some results.
   1. In the top section, enter the code:

      SELECT * FROM Mobiles WHERE Forename = 'Wendy';

   2. Run the query. Either
      1. in the top toolbar, press the Run icon , or
      2. press F9

   The following data is returned (Fig. 9).

   

   Fig. 9 - HeidiSQL - Query results

3. As seen in Fig. 9, simply searching the database for someone with the forename Wendy results in a number of records. To narrow down the results, more specifications are necessary.

   We could query Forename Wendy and Surname Trevally:

   1. Enter the code:

      SELECT * FROM Mobiles WHERE Forename = 'Wendy' AND Surname = 'Trevally';

   2. Run the query

   But two results still appear (Fig. 10).

   

   Fig. 10 - HeidiSQL - Narrower query

4. From the information we have so far, the customer requesting their data to be deleted could be either one of the two results returned. To narrow the results further, add her age of 106 into the query:
   1. Enter the code:

      SELECT * FROM Mobiles WHERE Forename = 'Wendy' AND Surname = 'Trevally' AND Age = '106';

   2. Run the query

   We have identified our correct customer as Contact_ID 2533.

5. To confirm that we have the correct entry:
   1. Replace the query with the following line:

      SELECT * FROM Mobiles WHERE Contact_ID = 2533;

   2. Run the query

   The result is a single record.

Next, we will update the record to remove personal data.

When editing a database, commands can either be entered individually or as part of a transaction. The advantage of a transaction is that you will be able to rollback the database to its state before the transaction was started. Here, we use a transaction:

1. Enter the following code:

   START TRANSACTION;

   This indicates to the program that

   • if the ROLLBACK command is used, changes will be rolled back to this point, or
   • if the COMMIT command is used, all changes from this point will be confirmed

Next, we can do either of the following:

2) UPDATE the personal data fields

1. On the next line of the query, enter the following code:

   UPDATE Mobiles SET Forename = NULL WHERE Contact_ID = 2533;

   This code tells the Mobiles database to update the details stored in the Forename field to NULL, (meaning empty/ no data), in every result where the Contact_ID field has a value of 2533. As mentioned previously, as we are using a key field’s value to locate our target, only one result will be matched.

2. We will also add the code to retrieve the record, both before and after it has been updated. The reason will become clear once the query is run, and is given below. The code now reads (Fig. 11):

   SELECT * FROM Mobiles WHERE Contact_ID = 2533;
   
   START TRANSACTION;
   UPDATE Mobiles SET Forename = NULL WHERE Contact_ID = 2533;
   
   SELECT * FROM Mobiles WHERE Contact_ID = 2533;

   

   Fig. 11 - HeidiSQL - Before setting Forename

3. Run the query.

   The Forename field for our specific customer is now (NULL) (Fig. 12).

   

   Fig. 12 - HeidiSQL - After setting Forename

   Although (NULL) is returned by the query, the field itself is completely empty, i.e. all the data of Wendy’s forename has been deleted from this database.

   As shown in Fig. 12, having two of the same SELECT statements present before and after the UPDATE code will return two tabs along the bottom half of the screen; one for each result. By default, after the query has been run, the query result tab shown is pre-UPDATE (left tab); the post-UPDATE tab is on the right. Use these to compare the data and ensure that only the correct entry’s data has been changed.

4. If the data does not contain (NULL) and has changed unexpectedly or hasn’t changed at all, the UPDATE statement may be incorrect. To revert the database to its state before the transaction was started,
   1. Enter the following code:

      ROLLBACK;

   2. Highlight it
   3. Click the drop down arrow by the Run Icon
   4. Select the option Run Selection to run only the ROLLBACK line of code

   This will

   • revert the table, and
   • close the transaction

   This means that before doing another UPDATE, you will have to open a new transaction.

5. Once adjustments to the code have been made and the correct outcome has been displayed,
   1. add another new line of code:

      COMMIT;

   2. Run Selection

   This will

   • apply the changes made to the database, and
   • close the transaction

   While a transaction is open, in order to prevent the data from changing, the database will be unable to connect to any outside source. Therefore, the shorter the time the transaction is open, the better.
   
   Best practice for changing and/ or removing customers’ personal data from your databases is to note each request and perform them at the end of each working day.

3) DELETE the entire record

If the customer is to be removed entirely from the database, the DELETE statement may be used rather than UPDATE.

Ensure that you test the data as mentioned in the 2) UPDATE the personal data fields section above, using the START TRANSACTION and ROLLBACK code, to avoid damaging the database.

To completely remove Wendy from the table:

1. Enter the code :

   DELETE FROM Mobiles WHERE Contact_ID = 2533;

2. Run the query.

Due to the way that database key fields function, the Contact_ID field will now appear to be missing row 2533, and key values, even if deleted, can never be repeated.

The recordings table is created for each tenant when the first recording is made for that tenant. To edit this table, we must again use an SQL tool:

1. Open a HeidiSQL session (as described in calling list tables above)
2. In the left panel (at the same level as the sccdemo database reviewed above) click on the {tenant} database

   For the default tenant, the database is _default, as default is a reserved SQL keyword.

3. Click on recordings, to open the table in the right panel
4. At the top, click on the Query tab
5. As in the example shown in calling list tables above, use the SELECT query with the customer's phone number to identify the relevant rows

   Note that there may be

   • several phone calls
   • several entries for each phone call, as explained below.

6. Before editing or deleting these rows, make a note of the file paths/ names of the recordings. You will need to locate them when Deleting recorded files below.

File locations

The file locations shown in the SELECT query results are determined by 2 keys in the RecordMonitor.exe.config XML file:

1. LocalRepositoryPath - This is the location for all processed files (.mp3/ .wav/ .mp4) (by default c:\Softdial\RecordMonitor\localStorage)
2. TenantArchiving - files are moved from the default location to the TenantArchiving location, according to the other Softdial Recording Monitor™ settings.

As with calling list data, we can either

1. UPDATE to remove TelephoneNumber and FilePath (as it identifies which file contains personal information), or
2. DELETE the entire row

The SELECT query used in Editing the recordings table above may have resulted in up to 2 files per recording, with the same file name (which is the recording ID), but a different file extension, as follows:

1. *.wav (uncompressed audio)
2. *.mp3 (compressed audio)

   These contain the same identical conversation, but which of these is saved is determined by the following key in the Softdial Recording Monitor™ configuration file (RecordMonitor.exe.config, located by default in C:\Softdial\RecordMonitor):

   If the key Compression is

   • disabled (value="") - the original audio file (.WAV) is saved and there is no compressed (.MP3) file
   • enabled (value="MP3") - the compressed (.MP3) file is saved and the uncompressed (.wav) file is deleted

The only way to identify whether or not a recording file contains personal information is to listen to it. If any personal information is present, the recording file must be deleted.

Deleting backed up files

Audio files (.mp3/ .wav) may also have been backed up to the location set in the key mirrorRoot.

Even if this key is commented out at the time of running the query, it may have been in operation at the time the recording was made, so the location should be checked, and files deleted if found.

As with the code presented in the calling list tables section, use the UPDATE or DELETE queries, along with the recommended transaction codes to help prevent any errors.

Show example

In the following example, we will remove Jordan Chevis’ personal data (KEYVAL and PHONENUM) from the audit log while retaining the outcome information for further use.

To update the database:

1. Enter the following code:

   START TRANSACTION;
   UPDATE audit0001 SET PHONENUM = 0, KEYVAL = 0 WHERE PHONENUM = 01234000076;

2. Run the query.
3. To ensure this has been actioned, search the database for either the KEYVAL or PHONENUM where the value is 0:

   SELECT * FROM audit0001 WHERE KEYVAL = 0;

   Note that we are setting the values to 0 instead of NULL as the audit table we are using for this example does not allow values in these two fields to be NULL (see Fig. 13, Allow NULL column). This may be changed to allow NULL for the purposes of this example. However, future attempts will not adhere to this change and the table will have to be manually set to allow NULL each time.

4. COMMIT or ROLLBACK the data to confirm or undo these changes, depending on your results.

To delete all rows containing the customer’s phone number, including the KEYVAL information and all the connection and outcome information noted during the call process, use the DELETE command:

DELETE FROM audit0001 WHERE PHONENUM = 01234000076;

As this removes the outcome information, it will affect the reporting processes and give slightly less accurate data.