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.
SQL Logging is disabled by default. There are two ways to enable it.
This is then active for the server session only:
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.
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
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
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.
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?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning and boost your career prospects.