SAS Federation Server for GDPR - The logging facility
According to Articles 33 and 34 of the GDPR regulation, organizations must be able to notify the supervisory authority as to the type of data subjects and how many personal data records are impacted in case of a breach; and to notify the affected data subjects.
In SAS Federation Server, the SQL Logging capability can help fulfilling this obligation. It will allow organizations to monitor who is accessing the data, how and, when.
We will first see how to enable this log and configure it to record the events you like, then where the information are stored, and finally how to access it.
-
Enabling SQL Log
SQL Logging is disabled by default. There are two ways to enable it.
- A dynamical change made from the Federation Server Manager
This is then active for the server session only:
- select your Federation Server in the tree,
- select “Properties” option in the drop-down Menu at the upper-left corner,
- in the Federation Server Properties window that opens, click the SQL Log tab, and select the events you want recorded.
- A Federation Server XML configuration file
The Federation Server always starts with the SQL Logging set in the dfs_log_SQL_Logging.xml configuration file, therefore any change made to this file requires a restart to be taken into account. The file is located in the /etc directory of the Federation Server Config path.
There are two loggers that must be set to TRACE to enable SQL Logging: Perf.ARM.FederationServer and Perf.ARM.SQLServices. By default, they are set to WARN, which is for SQL Logging disabled.
Other loggers commented by default below in the file are used to control the level of details you want to record. For example, if you want to enable DBC, SQL CURSOR like in the screenshot above, you will have to uncomment the Perf.ARM.SQLServices.Connection.Transaction.DBC, Perf.ARM.SQLServices.Statement.Transaction.SQL and Perf.ARM.SQLServices.Statement.Transaction.CURSOR loggers.
-
Storing the SQL Log information
2.1 By default in the SQL_LOG database
By default, the information are stored in the SQL_LOG Firebird database that installs with SAS Federation Server. The default configuration will create the Data Service, DSN, database and EVENTS table even if the SQL_LOG is disabled – so that it is available should you enable it dynamically. Catalog functions and privileges are restricted: you can’t create new tables or insert into tables through the SQL_LOG data service. An administrator can grant CONNECT, SELECT and DELETE privileges only.
2.2 In a different database
You can configure a different database: examples of configuration files for Oracle, DB2, SQLServer and Teradata are provided in the /etc directory of the Federation Server Config path. You need to
-
Viewing the SQL Log
Messages from the SQL Logging are written to the EVENTS table and to the log file. They can therefore be monitored in several ways.
Note: if something goes wrong while inserting to the EVENTS table, errors will be logged to the server log file with a prefix of DBAppender<SQL_LOG>.
3.1 In the database itself
The messages are written to the EVENTS table of the database chosen. You can simply query that database to retrieve the information you need: a complete list of the columns and data types is available in SAS Federation Server Administration Guide http://support.sas.com/software/products/fedserver/index.html under “The EVENTS table”. You can also view the data from SAS Federation Manager.
3.2 In SAS Federation Server Manager
- Select your Federation Server in the tree
- select “Open SQL Log” option in the drop-down Menu under the Summary tab
This opens a new tab showing requests sent to the databases. You can choose your outputs into a large number of columns (User ID, Login ID, Requests, IP Address, Rows Fetched, Connection Driver, etc.). You can filter the number of records, and the time period you want to see. You can export to CSV.
You can also create User reports, or aggregated views.
