Many customers have valuable information in many different databases in their environment. It is extremely useful for a audit report, to correlate relevant information need to make these reports easy and useful to understand. The External Data Connector allows users to create custom tables on the Guardium appliance for enterprise information that is needed in addition to the existing Guardium internal data. This can be done either manually within the GUI or based on an existing table on a database server. Queries and reports can then be created for this information just as if it were pre-defined data.
For example, perhaps a table exists on a database servers containing all employees, their database usernames, and the department to which they belong (e.g. Development, Financial, Marketing, HR, etc.). If the customer were to upload this table and all its data they could cross-reference this table with Guardium's internal tables to see, for example, which employees from Marketing are accessing the financial database (which may constitute a suspicious activity).
A custom table contains one or more attributes that you want to have available on the Guardium appliance. For example, you may have an existing database table relating encoded user names to real names. In the network traffic, only the encoded names will be seen. By defining a custom table on the Guardium appliance, and uploading data for that table from the existing table, you will be able to relate the encoded and real names.
Before defining a custom table, first verify that the data you need from the existing database is a supported data type. For each database type, the following table summarizes the supported and unsupported data types for uploading to a custom table.
|
Database |
Supported Data Types |
Unsupported Data Types | |||||||||||||||||||||||||||
|
Oracle |
|
| |||||||||||||||||||||||||||
|
DB2 |
|
| |||||||||||||||||||||||||||
|
Sybase |
|
| |||||||||||||||||||||||||||
|
MSSQL |
|
| |||||||||||||||||||||||||||
|
Informix |
|
| |||||||||||||||||||||||||||
|
MySQL |
|
|
A custom domain contains one or more custom tables. If it contains multiple tables, you define the relationships between tables when defining the custom domain.
A custom query accesses data from a custom domain. You use the Custom Query Builder to create queries against custom domains. Custom queries can then can be used like any other query to generate reports or audit tasks, populate groups, or to define aliases.
There are a few different methods to opening the Custom Domain Builder panel.
Click on the Comply tab.
Click on the Custom Domain Builder from the left hand column menu items.
-or-
Click on the Monitor/Audit tab.
Click on the Build Reports tab.
Click on the Custom Domain Builder from the left hand column menu items.
-or-
Click on the Tools tab.
Click on the Report Building tab.
Click on the Custom Domain Builder from the left hand column menu items.
Creating a custom table can be accomplished by the uploading of a table definition by accessing its metadata from the database server on which it is defined.
Click on the Upload Definition button to open the Import Table Structure panel.
Enter a description for the table in the Entity Desc field. This is the name you will use to reference the table when creating a custom query.
Enter the database table name for the table in the Table Name field. This is the name you will use to create the table in the local database.
Enter a valid SQL statement for the table in the SQL Statement field. The result set returned by the SQL statement must have the same structure as the custom table defined. For example, if the custom table contains all columns from the table named my_table, enter select * from my_table.
Note: Do not include any newline characters in the SQL statement.
Click Add Datasource to open the Datasource Finder in a separate window. This will allow us to define where the external database is located, and the credentials needed to retrieve the table definition and content later in the process.
Use the Datasource Finder to identity the database from which the table definition will be uploaded.
See Datasources for assistance.
Click on the Manually Define button to open the Define Entity panel.
Enter a description for the table in the Entity Desc field. This is the name you will use to reference the table when creating a custom query.
Enter the database table name for the table in the Table Name field. This is the name you will use to create the table in the local database.
For each column in the table to be defined:
Enter a name in the Column Name box. This will be the name of the column in the database table.
Enter a name in the Display Name box. This is the name you will use to reference the attribute in the Custom Domain Builder and the Custom Query Builder.
Select a data type (Text, Date, Integer, Float, or Time).
For a Text attribute, enter the maximum number of characters in the Size box. (The Size box is not available for other data types.)
If uniqueness is to be enforced on the column, check the Unique box.
If the attribute being defined corresponds to a group type, select that group type from the Group Type list.
Click the Add button to add the column.
Use the Entity Key drop-down list to identify which column will be used as the entity key
If additional changes are made after the Add button, such as deletion of a column, or changing an attribute, Click on the Apply button to save any changes.
Click the Done button when you have added all columns for the table.
If you modify the definition of a custom table, you may invalidate existing reports based on queries using that table. For example, an existing query might reference an attribute that has been deleted, or whose data type has been changed. When applying changes to a custom table, if any queries have been built using attributes from that table, the Queries are displayed in the Query List panel. Note: You can also use the Modify to view and validate the table structures that were imported.
Choose a custom table by clicking on the entity label and highlighting it
Click on the Modify button to open the Modify Entity panel.
See Defining a Table Manually for assistance
When applying changes to a custom table, if any queries have been built using attributes from that table, the Queries are displayed in the Query List panel. Use the Query List panel to choose and change queries. You do not have to make all changes immediately as you can always come back and use the Check for Invalid Queries option.
If you modify the definition of a custom table, you may invalidate existing reports based on queries using that table. For example, an existing query might reference an attribute that has been deleted, or whose data type has been changed. It is a good idea to check for invalid queries after the table modification process.
Choose a custom table by clicking on the table name and highlighting it
Click on the Invalid Queries button.
The Queries are displayed in the Query List panel. Use the Query List panel to choose and change queries.
Data can be purged from custom tables on the Guardium server on demand, or on a scheduled basis.
Choose a custom table by clicking on the table name and highlighting it
Click on the Purge button to open the Custom Table Data Purge panel.
Click the Purge All button to purge now.
In the Configuration panel, enter the age of the data to be purged, as a number of days, weeks or months prior to the purge operation date.
Click Run Once Now to run a schedule purge operation once.
Click the Modify Schedule button to open the standard Schedule Definition panel and schedule a purge operation.
Click Done to close the panel.
Choose a custom table by clicking on the table name and highlighting it
Click on the Upload Data button to open the Import Data panel.
In the SQL Statement box, enter a valid SQL statement for the table. The result set returned by the SQL statement must have the same structure as the custom table defined. For example, if the custom table contains all columns from the table named my_table, enter select * from my_table.
The following fields, which are internal to Guardium, are available for use within SQL statements:
^FromDate?^ and ^ToDate?^ where the value is equal to the previous upload date and the current upload date respectively.
^fromID^ and ^toID^ where, when used with Id Column Name consist of the maximum value of the Id Column from the previous upload and the maximum value of the current upload respectively.
Note: Do not include any newline characters in the SQL statement.
Specify, if needed, a column name in the Id Column Name (from the table defined within the datasource) will be used and allow for tracking by ID and be used in conjunction with the internal Guardium fields ^fromID^ and ^toID^.
In the DML command after upload box, enter a DML command (an update or delete SQL statement) with no semicolon, to be executed after uploading the data.
Note: Do not include any newline characters in the SQL statement.
Check the Overwrite box if you wish to have data purged in the custom table before the upload
Click Add Datasource to open the Datasource Finder in a separate window:Use this window to identify one or more databases from which the table data will be uploaded. You may add multiple datasources to upload from multiple sources.
You can click Check/Repair to compare the schema of the custom table to the schema of the meta-data. For central management environments: In a central management environment, the custom table definition resides on the central manager, and the custom table may not exist on the local (managed unit) database. Click the Check/Repair button to check if the custom table exists locally, and create one if it does not.
Click the Save button.
To upload data to this custom table, do one of the following:
Click the Upload button to upload data manually.
Mark the Add to Tables Scheduled to Upload Data checkbox to add this table to the schedule of custom table uploads. See Scheduling Custom Data Uploads.
Click the Save button
Once a custom table definition is in place, data can be uploaded to custom tables on the Guardium appliance on a scheduled basis. Note that there is only one job that does this, and all custom tables that have been flagged for scheduled data upload will be updated by the scheduled job, one at a time. The total amount of disk space reserved on the Guardium appliance for custom tables is 4GB.
Choose a custom table by clicking on the table name and highlighting it
Click on the Upload Data button to open the Import Data panel.
Mark the Add to Tables Scheduled to Upload Data checkbox to add this table to the schedule of custom table uploads.
Click the Modify Schedule button to open the standard Schedule Definition panel and modify the schedule.
Click Done when you are finished.
After defining one or more custom tables, define a custom domain so that you can perform query and reporting tasks using the custom data. The information collected is organized into domains, each of which contains a different type of information relating to a specific area of concern: data access, exceptions, policy violations, etc. There is a separate query builder tool for each domain. Custom domains allow for user defined domains and can define any tables of data uploaded to the Guardium appliance.
Click the Domains button to open the Domain Finder panel.
Click the New button to open the Custom Tables Domain panel.
Enter a Domain Name. Typically, you will be including a single custom table in the domain, so you may want to use the same name for the domain.
The Available Entities box lists all custom tables that have been defined (and to which you have access). Select an entity. Optionally, click the (Filter) tool to open the Entity Filter and enter a Like value to select only the entities you want listed, and click Accept. This closes the filter window and returns you to the Custom Tables Domain panel, with only those entities matching the Like value listed in the Available Entities box. Select the entity you want to include.
Click the right arrow button to move the entity selected in the Available Entities list to the Domain Entities list.
To add an entity to a domain that already has one or more tables, follow the procedure outlined below. You will need to use the Join Condition to define the relationship between the entities.
For each additional entity:
From the Domain Entities box on the right, select an entity. All of the attributes of that entity will become available in the field drop-down list below the Domain Entities box. Select the attribute from that list that will be used in the join operation.
From the Available Entities list on the left, select the entity you want to add. All of the attributes of that entity will become available in the field dropdown list below the Available Entities box. Select the attribute from that list that will be used in the join operation.
Select = (the equality operator) if you want the join condition to be equal (e.g., domainA.attributeB = domainC.attributeD). Select outer join if you want the join condition to be an outer join using the selected attributes.
Click Add Field Pair.
Repeat the above steps for any additional join operations.
Select the Timestamp attribute for the custom domain entity.
Click the Apply button
The goal is to create a linkage between external data and the internal data.
Choose the Custom Table that is in the domain you wish to clone
Click the Domains button to open the Domain Finder panel.
Click the Modify button to open the Custom Tables Domain panel.
See Defining Custom Domains and Linking External Data to Internal Data for assistance
Click the Apply button to save the changes.
Choose the Custom Table that is in the domain you wish to clone
Click the Domains button to open the Domain Finder panel.
Click the Remove button to remove the custom domain
Choose the Custom Table that is in the domain you wish to clone
Click the Domains button to open the Domain Finder panel.
Click the Clone button to open the Custom Tables Domain panel.
Change the Domain Name to reflect the new domain
See Defining Custom Domains and Linking External Data to Internal Data for assistance
Click the Apply button to save the changes.
The goal is to create a linkage between external data and the internal data.
Choose the Custom Table that has your external data
Click the Domains button to open the Domain Finder panel.
Click the Modify button to open the Custom Tables Domain panel.
Click the Filter icon next to the Available Entities
Un-check the Custom box for the filter and optionally fill in a Like condition to filter entity names, click the Accept button
Select an entity from the Available Entities that you would like to link with your external data
Select the Field that will be used to join data with your external data
Highlight the table from the Domain Entities that contains your external data
Select the Field that will be used to join data with the internal data
Click the Add Field Pair to add the relationship
Click the double arrow ">>" to add the internal table to the Domain Entities list.
Click the Apply button to save the changes.
This section describes how to open the Custom Query Builder. See Building Queries and Building Reports for assistance in defining a query and building a report. Use the Custom Query Builder to build queries against data from custom domains, which contain one or more custom tables.
Click the Tools tab.
Click the Report Building tab.
Click the Custom Query Builder item in the left column menu to open the Domain Finder.
Select a custom domain from the list
Click the Search button to open the Query Finder
To view, modify or clone an existing query, select it from the Query Name list, or select a report using that query from the Report Title list.
To view all of the queries defined for a specific custom table, select that custom table from the Main Entity list and click the Search button (only the custom tables included in the selected custom domain will be listed).