BDS Sercuring And Auditing Data

Problem

As an FNZ Studio Administrator, I want to make sure the information handled by the Business Data Storage (BDS) is secured from unauthorized access and that I can trace all the Business Data Storage-related operations performed on the Database by FNZ Studio users for auditing purposes. Which tools can I use for this purpose?

Solution

The security of the information handled by the Business Data Storage (from now on, BDS) can be achieved at two levels:

  • Storage of data in the Database.
  • Network exchanges between FNZ Studio and the Database.

On the other hand, you can audit all BDS-related operations performed by logged-in FNZ Studio users on the Database.

Following is some information on how to achieve data security and data auditing for the Business Data Storage (BDS), PostgreSQL, Oracle, and Microsoft SQL Server.

Information Security for PostgreSQL

Storage of Data for PostgreSQL

To secure the BDS data persisted in the Database, if required, you are recommended to encrypt the disk where the information is stored.

As an example, complete information on the encryption of Amazon RDS can be found at the following links:

Network Exchanges for PostgreSQL

You can secure the communication between FNZ Studio BDS and the PostgreSQL Database to protect data while it is being transferred across the two systems.

For complete information, see the following sections of the PostgreSQL documentation:

The following section describes a sample procedure to set up such configuration (using OSX):

1. Generate keys:

Copy 
<<openssl req -new -x509 -days 365 -nodes -text -out server.crt \ >>
    -keyout server.key -subj "/CN=localhost"
change permission on the server key:

sudo chmod 640 server.key

2. Move the server certificate in the postgresql data folder:

sudo cp /Users/usr/certificates/server.crt /Library/PostgreSQL/10dop/data

3. Add the self-signed certificate to JVM truststore:

sudo /Library/Java/JavaVirtualMachines/jdk1.8.0_161.jdk/Contents/Home/bin/keytool -import -v -trustcacerts -alias server-alias -file server.crt -keystore cacerts.jks -keypass <password> -storepass <password>

4. Change the server configuration in /Library/PostgreSQL/10dop/data/postgresql.conf:

Copy

from
#ssl = off
to
ssl = on

5. Restart the server:

Copy

{{sudo -u postgres bash
sudo ./pg_ctl -D /Library/PostgreSQL/10dop/data restart}}

6. In the FNZ Studio Composition (PostgreSQL Data Source), add ssl=true&sslmode=require as Connection Properties.

As an example, information regarding securing network exchanges with PostgreSQL on Amazon RDS can be found at Using SSL with a PostgreSQL DB Instance.

Information Security for Oracle

Storage of Data for Oracle

Similarly, Oracle provides two preventive measures to avoid unwanted access to information: Transparent Data Encryption, which encrypts data to prevent accessing sensitive information in storage, and Data Redaction, which reduces the exposure of sensitive information by redacting database query results according to defined policies.

For more information on security, see the following links:

Network Exchanges for Oracle

You can secure the communication between FNZ Studio BDS and the Oracle Database to protect the data while it is being transferred across the two systems.

See more details about the setup of such configuration in the Oracle documentation: Configuring Secure Sockets Layer Authentication.

Information Security for MsSQLServer

Storage of Data for MsSQLServer

MsSQLServer provides different preventive measures to avoid unwanted access to information. Similarly to what is BDS Sercuring And Auditing Data, a suitable option is Transparent Data Encryption, which encrypts data to prevent accessing sensitive information in storage.

For more information on security, see the following links:

Network Exchanges for MsSQLServer

You can secure the communication between FNZ Studio BDS and the MsSQLServer Database to protect the data while it is being transferred across the two systems.

See more details about the setup of such configuration in the Microsoft documentation: Configuring Secure Sockets Layer Authentication.

Auditing Operations in PostgreSQL

Auditing is achieved through the logging feature of PostgreSQL (see the PostgreSQL Error Reporting and Logging documentation section).

The following paragraph describes a sample procedure to setup such configuration on PostgreSQL.

1_Set the BDS configuration property log.userids.enabled to true.

2_Change the PostgreSQL configuration file /Library/PostgreSQL/10dop/data/postgresql.conf setting so that:

  • the property log_line_prefix contains the application_name %a variable content: log_line_prefix = ' %a -> %m [%p] '
  • and the property _log_statement _ specifies which kinds of statements have to be logged. log_statement = 'all'

3_Restart the server as database bash user:

Copy
cd /Library/PostgreSQL/10dop/bin
./pg_ctl -D /Library/PostgreSQL/10dop/data restart
log files are saved in : /Library/PostgreSQL/10dop/data/log

The application_name is populated by the BDS in the format: 'FNZ Studio BDS [userID]'.

The following text file contains an example procedure: the first actions have been performed by the user NM while the following ones have been performed by the user AnotherUser. [Text File](../../Resources/Downloads/DataIntegration/BusinessDataStorage/Text File).

Auditing Operations in Oracle

An Application Context labeled with 'bds_user' and managed by a PL/SQL procedure is used as a mechanism to add the user information in auditing entries through the Unified Audit.

Prerequisites:

  • An FNZ Studio Data Source connected to an Oracle Database. The Database user used in the Data Source to connect to the Database is referred to as 'DataSourceUser' from now on.
  • SYS access to the Database (or an equivalent one with rights to perform listed actions).
  • Bash shell access on the Database host and a PL/SQL client.
  • The BusinessDataStorage configuration property userid.log.enabled set to true.

As a SYS user, follow these steps: 1_Instruct the database to include the values of 'namespace bds_namespace' and attribute 'bds_user' in all audit records for the Data Source user. See audit statement information.

Copy
AUDIT CONTEXT NAMESPACE bds_namespace
ATTRIBUTES bds_user
BY <DataSourceUser>

2_Grant the EXECUTE privilege for the DBMS_SESSION PL/SQL package and the CREATE PROCEDURE privilege.

3_Set some audit policies.

As DataSourceUser, follow these steps on the Database:

1_Create the required context. CREATE CONTEXT bds_namespace USING bds_package;

2_Create the required package and package body.

Copy
CREATE OR REPLACE PACKAGE bds_package IS
PROCEDURE set_value (bds_user IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY bds_package IS
PROCEDURE set_value (bds_user IN VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_CONTEXT('bds_namespace', 'bds_user', bds_user);
END;
END;
/

Auditing Operations in MsSQLServer

A session context value labeled as "bds_user" is set through the execution of the set session context function to provide the user information for auditing purposes.

This value can be then accessed from a MsSQLServer customizable audit mechanism by performing this query:

SELECT SESSION_CONTEXT(N'bds_user');

Related Links

Business Data Storage (BDS) article.