7.6. Anatella SQL Logging

<< Click to Display Table of Contents >>

Navigation:  7. Anatella Global settings >

7.6. Anatella SQL Logging

 

When your data scientists are managing sensitive data stored inside a database, you need to closely monitor their actions to prevent any catastrophic event that might jeopardize the confidentiality of the data (or even damage your database!). To handle this delicate situation properly, you might be interested in logging all the SQL commands that were sent to your databases. These “SQL logs” are usefull to make data scientist accountable for all their actions that are related to your sensitive databases. This section explains how to activate the “SQL logging” mechanism included inside Anatella.

 

A typical sequence found inside these logs is the following 4 rows:

 

clip0763

 

 

Here is a small explanation on the meaning of each of the 4 above rows:
 

Row 1: The user clicked on the Pin 0 of the “readODBC” action (with id=1) to start the execution of the Anatella graph named “f:/testLogging.anatella”.
 

Row 2: The “readODBC” action (with ActionID=1) started the execution of the SQL command “select * from income”. This returned a table with 43 columns.
 

Row 3: The “readODBC” action (with id=1) extracted 10 rows out of the database.
 

Row 4: The execution of the Anatella graphed stopped at 17:58:33 on the 2020/4/5.

 

 

The columns of the “AnatellaLogs” table are:
 

ID: A primary key that uniquely identifies the row
 

ProgramID: The ID of the tool that initially created the row. The ID’s are: Anatella=0, Modeler=1.
 

PID: This is the “Process ID”: it’s an identifier that uniquely identifies the Anatella process that created the row (in the possibly case when there are several Anatella instances running simultenously on the same server/host).
 

Host: This is the (Windows) name of the server that generated the row inside the AnatellaLogs table.
 

User: This is the (Windows) name of the ysed that generated the row inside the AnatellaLogs table.
 

Graph: This is the filename of the .anatella graph file that generated the row inside the AnatellaLogs table.
 

Date: Self-explaining.
 

SeverityCode: This is a number that refers to this table:

 

SeverityCode

Meaning

0

SUCCESS

1

ERROR

2

WARNING

3

INFO

4

DEBUG

 

ActionName: The name of the Action inside your .anatella graph file that generated the row inside the AnatellaLogs table.
 

ActionID: The ID of the Action inside your .anatella graph file that generated the row inside the AnatellaLogs table.

 

MessageText: A description of the event represented by the current row inside the AnatellaLogs table.
 

Optional: empty/null (possible usage in future extension).

 
Inside Anatella, all the SQL commands are logged. This means that Anatella is logging each execution of the readDOBC action, the upsertODBC action, the createTable action, the teradataWrite action and the OracleOCI action.

 

 
The steps required to activate the logging mechanism included inside Anatella are:
 

1.Request a serial number (i.e. a license) where the “SQL Logging” option is enabled. Please contact your TIMi sales representative to request your new serial number with this special option. The sections 7.4.2., 7.4.3 and 7.4.4 explain how to enter your new serial number.

 

2.Create inside your database a table named “AnatellaLogs”: i.e. run the following SQL command inside your database:

 

CREATE TABLE AnatellaLogs (

 ID                INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

 ProgramID          INTEGER NOT NULL,

 PID                INTEGER NOT NULL,

 Host                nvarchar(100),

 User                nvarchar(100),

 Graph                nvarchar(300),

 Date                nvarchar(20),

 SeverityCode  INTEGER NOT NULL,

 ActionName          nvarchar(20),

 ActionID          INTEGER NOT NULL,

 MessageText          TEXT NOT NULL,

 Optional          TEXT

);

 

 

clip0764

Inside the above “CREATE TABLE’ statement, the columns “ID” and the column “Optional” are not required: you can omit/remove them.

 

 

3.Create inside your database a database-user named “AnatellaLogs” that can only “INSERT” new rows inside the “AnatellaLogs” table. In particular, the “AnatellaLogs” user should not be authorized to DELETE ROWS or UPDATE ROWS from the “AnatellaLogs” table (otherwise an ill-intentioned user might be tempted to corrupt the “AnatellaLogs” table to “cover its traces”).

 
 

4.Create on the local machine a type-1 ODBC connection to your database that is named “AnatellaLogs”. This is important that this new type-1 ODBC connection is inside the “System DSN” category because this prevents a non-adminstrative user to temper with the ODBC connection:
 

clip0765

 

 

 

clip0766

Please, pay attention to the ODBC manager: If you are using a 64-bit version of Anatella, you need to use the 64-bit ODBC manager. Likewise, if you are using a 32-bit version of Anatella, you need to use the 32-bit ODBC manager. You’ll find more details on this subject inside the section 5.1.6.1.

 
 

5.Test your setup: To run a simple quick test, you can click the “Test Loggin” button here:

 

clip0767

 
 

clip0768

If the “Test Logging” button is disabled (i.e. grayed out, as in the example above), it means that you forgot to request a serial number with the “SQL Logging” option that is enabled (see step 1).

 

 

After clicking the “Test Logging” button, you should see a new row inside the “AnatellaLogs” table inside your database. This new row looks like this:

 

clip0769

 

 

For your convenience, to run a quick test, you’ll find a sqlite database with a proper “AnatellaLogs” table already pre-configured inside the file “<TIMi_install_dir>/bin/AnatellaLogs.sqlite”. This sqlite database is only useful for a quick test: i.e. it should NEVER be used in a production setting because of one limitation of the sqlite database engine: i.e. The sqlite database engine does not handle user rights: i.e. All sqlite users can DELETE ROWS from a sqlite database (and, in our case, this should be forbidden: For more details, see the step 3, here above). A good database engine to store the “AnatellaLogs” table is MS-SQLServer, Oracle, Postgres, etc. You’ll find the ODBC drivers for SQLite databases here: http://download.timi.eu/ODBC/ODBC_drivers_SQLite/