BookmarkSubscribeRSS Feed

SAS Federation Server for GDPR - The logging facility

Started ‎06-11-2019 by
Modified ‎06-11-2019 by
Views 1,517

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.

 

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

 

new1.png

 

 

  • in the Federation Server Properties window that opens, click the SQL Log tab, and select the events you want recorded.

 

new2.png

 

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

 

new3.png 

 

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.

 

 

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

 

 new4.png

 

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

 

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

  new5.png

 

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

  new6.png

 

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.

  new7.png

 

 

You can also create User reports, or aggregated views.

  new8.png

Comments

Hello,

Thanks for post and it is very useful for newbies, 

to monitor logging, step 3.1 is very clear and i can locate table in firebird.

What about step 3.2 SAS Federation Server Manager, "Open SQL Log" option, there are records as  rows, as it stated.

i could not find column "request" in the event table, so is there any other table? how come request column created with request column, user sql query, in the "Open SQL Log" and what is the source of that column?

Version history
Last update:
‎06-11-2019 09:46 AM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Labels
Article Tags