Sending Data in Guardium to an External Database Using the External Feed

by John Haldeman, Security Practice Lead

Guardium has the capabilities to send data to external databases. Traditionally this is done through CSV exports of the data where an audit process are set up to create CSV files which are moved off the appliance using the results export functionality of the Administration Console.

There was another method of exporting data that, until recently, was not available for most customers to use directly. This method is where Guardium creates a connection to an external database and inserts the results from a report directly into that database. External feeds work by mapping column names from the Guardium database to another database. This used to be a manual process of accessing the Guardium MySQL database directly and creating that mapping. That process required root access, which means you needed support to help you do it.


In Guardium V9.1 and above, this mapping can now be done without support or rooting via a simple grdAPI call. To keep things simple they assume the column names in the report you are exporting to your external database are going to be the same as the column names internal to Guardium. So, you don't have to build a mapping. This blog post explains how to create these external feeds.

At the end of this post I will discuss the downsides of this function and why you may still want to use CSV exports instead.

Step 1: Build the Report You Want to Export

The first step is to build a report that has all of the data in it that you wish to send to the external database. This can be any report in Guardium. In this example I am going to use the Oracle entitlement report called "ORA Roles Granted". 

The report we plan to export

 

Step 2: Create the Table Mapping Definition in Guardium

To do this, all you need to do is execute the following grdAPI call from the CLI:

grdapi create_ef_mapping reportName="ORA Roles Granted"

Replace "ORA Roles Granted" with the report you are interested in exporting. Again, this is only going to work with newer versions of Guardium (V9.1+).

 

Step 3: Create the Table in the Destination Database

The next step is to create a table in the destination database. When you created the mapping in Step 2, Guardium conveniently created you a CREATE TABLE template that you can use for this. To get a hold of that template, start the fileserver on the appliance where you ran the mapping grdAPI mapping command. Access the fileserver with your browser and click on the "Sqlguard logs" section. The file containing the CREATE TABLE template should be prefixed with "ef_sample" and end with the report name. In our example it's called "ef_sample_ORA_ROLES_GRANTED.sql".

The template SQL file as shown in the log file explorer

When viewed, the file contains the following CREATE TABLE statement:

create table ORA_ROLES_GRANTED(
ORA_ROLES_GRANTED_ID int not null auto-increment
,GRANTEE varchar(255)
,USER_OR_ROLE varchar(255)
,GRANTED_ROLE varchar(255)
,DATASOURCE_DETAILS varchar(255)
,SQLGUARD_TIMESTAMP Date
 ); 

The "int not null auto-increment" designation seems to suggest this is a MySQL statement. If you try and run this command in other databases, it may fail. This is true if your destination is Oracle which is what we'll use in our example. The important thing here is the column names of all of the columns except the first column, which Guardium assumes is an automatically generated ID. You can change the create table statement in any way you like as long as you keep all of the column names the same for everything except the first column.

In fact you don't technically need the first column at all, so let's remove it and create the table in our destination Oracle database:

Creating the Destination Table in an Oracle Database

Be sure that you create the table in the schema of the service account that you are using to connect to the external database from Guardium. So, if the username you use in your Guardium datasource is "GUARD", you would create a table called GUARD.ORA_ROLES_GRANTED. Also, make sure your server account has the ability to read and write data to the table.

 

Step 4: Create an Audit Process with an External Feed and Datasource

The next step is to create an Audit Process with an external feed and attach the datasource to that feed. To do that, create an audit process as you would normally and specify no receivers. Add an audit task and choose the task type of "External Feed". Pick the feed parameters of the mapping you created previously and add a datasource to that audit task

External Feed definition in a Guardium Audit Process

Now the only thing left to do is run the audit process. Click run once now or schedule it to occur on a regular basis. When it runs, the audit process will report on how many rows were inserted into the remote source.

Results from Publishing to the External Feed

After that, the data should be available in the database external to Guardium:

The Guardium report data in Oracle

 

Problems with the External Feed

The advantages of the external feed are immediately apparent. You can get your data into another database without having to use CSV files as intermediaries. The problem with this approach (other than the data being outside Guardium and thus in an editable environment), is that if you want to move large amounts of data, you may run into performance problems. The reason is that Guardium is using a JDBC connection and writing INSERT statements to populate the destination table. This traditionally is not a recipe for fast bulk-loading of data. A much better approach for the loading of large amounts of data is to take advantage of database loader utilities that are optimized for writing as much data as quickly as possible,

Additionally, because you are using Guardium to move the data, you are limited by Guardium's capabilities for data movement. If you export data to CSV files instead, you have the option of using ETL tools to manage and modify the data before you load it into the destination environment.

As such, the external feed makes sense for smaller data sets, but may not make sense when you plan on exporting large amounts of data out of Guardium.