Database Data Stores
The tables in the PosgresQL database must have a Primary Key (PK), otherwise the scan results may be incomplete.
PostgreSQL by default blocks remote connections to the PostgreSQL server, so you have to configure it to allow remote connections. For instructions, see Allowing Remote Connections to PostgreSQL Server.
To connect to Microsoft SQL DDC requires the ODBC drivers to be installed in the same environment as the DDC agent. If DDC cannot find a suitable agent, make sure that these drivers are installed. If necessary, upgrade them to the latest available version. Thus, if your MSSQL Server is configured with TLS 1.2 only, install the ODBC Driver 17 (or newer) for MSSQL Server.
Before adding an Oracle database, make sure that you have the schema name or the database and service name to hand. For information on how to get this information, see Obtaining the Oracle Configuration Details.
Scanning of large binary objects is now supported for database data stores. For a full list of these data stores and the supported binary file types, refer to the table in Binary Large Objects.
Adding Database Data Stores
Use the Add Data Store wizard to add a database type data store. Adding a database data store involves the steps described in the following sections.
1. Select Store Type
In the Select Store Type screen of the wizard select Database in the Select Data Store Category.
From the Select Database Type drop-down list select:
IBM DB2: Select to add an IBM DB2 database.
Oracle: Select to add an Oracle database
Microsoft SQL: Select to add a Microsoft SQL database.
PostgreSQL: Select to add a PostgreSQL database.
SAP HANA: Select to add a SAP HANA database.
MySQL: Select to add a MySQL database.
MongoDB: Select to add a Mongo DB database.
Click Next to go on to the Configure Connection screen.
2. Configure Connection
In the Configure Connection screen of the wizard, provide the following configuration details for your data store:
IBM DB2
IBM DB2 is a relational database providing data management and analytics capabilities for transactional workloads.
Note
DB2: Windows Agent built-in drivers are required to connect to a DB2 data store.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for IBM DB2 is50000
.In the Database field, specify the name of the database service.
In the Authentication part, specify valid user credentials, User and Password.
In effect, after you specify the hostname and port, the path syntax for a IBM DB2 data store target should look like this:
For a specific database:
<database[:<port>]>
For example:DDCDB:65535
For a specific schema:
<database[:<port>]/schema>
For example:DDCDB:65535/HRAdmin
For a specific table:
<database[:<port>]/schema/table>
For example:DDCDB/HRAdmin/Employees
ORACLE
Note
Windows and Linux Agent built-in drivers are required to connect to an Oracle data store.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for Oracle is1521
.In the Database field, specify the name of the database service.
Use a schema name
SCHEMA
or a database name and service nameDB(SERVICE_NAME=XXX)
. For example:
* Schema name:HR
* Database name and service name:MYDB(SERVICE_NAME=XE)
If you are using Oracle 12x, or if the Oracle database displays aTNS: protocol adapter error
, you must specify a database and service name in the Database field. For example:HR(SERVICE_NAME=XE)
Note
Due to a known limitation, when creating an Oracle DB data store the agents cannot be assigned if the Schema (which is defined in the Database field) does not contain any tables.
In the Authentication part, specify valid user credentials, User and Password.
For details of the configuration of the Oracle data store, see Oracle Database.
MICROSOFT SQL
Microsoft SQL Server is a relational database management system designed for storing and retrieving data as requested by other software applications.
Note
Windows host ODBC drivers are sufficient to connect to a MS SQL data store. ODBC Drivers version 17 are required to support TLS 1.2 connections.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for Microsoft SQL is1433
.In the Database field, specify the name of the database service.
In the Authentication part, specify valid user credentials, User and Password.
The path syntax for a Microsoft SQL data store target should look like this:
For all locations:
[:<port>]
For example: Leave the Path blank, or :65535For a specific database:
<database[:<port>]>
For example: DDCDB:65535For a specific schema:
<database[:<port>]/schema>
For example: DDCDB:65535/HRAdminFor a specific table:
<database[:<port>]/schema/table>
For example: DDCDB:65535/HRAdmin/EmployeesTo scan a specific SQL Server instance (with multiple instances running):
<database(instance=<instance_name>)[:<port>][/schema][/table]>
For example: DDCDB(instance=MsSQLInst2):65535/HrAdmin/Employees
By default, pagination is enabled when scanning Microsoft SQL databases. To disable pagination, set the option (paged=false).
For all locations:
(paged=false)[:<port>]
For example: Leave the Path blank, or (paged=false):65535For a specific database:
<database(paged=false)[:<port>]>
For example: DDCDB(paged=false):65535For a specific schema:
<database(paged=false)[:<port>]/schema>
For example: DDCDB(paged=false):65535/HRAdminFor a specific table:
<database(paged=false)[:<port>]/schema/table>
For example: DDCDB(paged=false):65535/HRAdmin/Employees
POSTGRESQL
Note
Windows and Linux agent built-in drivers are required to connect to a PostgreSQL data store. The built-in driver does not support password authentication with 'scram-sha-256' method.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for PostgreSQL is5432
.In the Database field, specify the name of the database service.
In the Authentication part, specify valid user credentials, User and Password.
For details of the configuration of the PostgreSQL data store, see PostgreSQL.
SAP HANA
Note
Windows Agent built-in drivers are required to connect to a SAP Hana data store. If the Agent host has SAP HANA ODBC drivers installed, the Agent will use those drivers instead of its built-in drivers.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for SAP HANA is30015
.In the Database field, specify the name of the database service.
In the Authentication part, specify valid user credentials, User and Password.
MYSQL
Note
Windows and Linux Agent built-in drivers are required to connect to a MySQL data store. The built-in driver does not support password authentication with 'caching_sha2_password' method.
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for MySQL is3306
.In the Authentication part, specify valid user credentials, User and Password.
MONGODB
Specify Hostname/IP of the database server. Specify a valid hostname, IP address, or Uniform Resource Identifier (URI). The hostname must be longer than two characters. This is a mandatory field.
Specify Port of the database server. The port must be a number between
1
and65535
. The default port for MongoDB is27017
.In the Authentication Database field, specify the name of the database service.
User and Password - specify the Username, password and authentication database in the following manner:
- Username: <authentication_database>/<user_name>
Example: pgdb1/user1 - Password: <password>
Example: myPassword123
- Username: <authentication_database>/<user_name>
The Agent Selection section allows you to specify the minimum and maximum number of proxy agents when adding a datastore. Employing a group of agents instead of a single agent to run the scan should improve the scan execution time.
In the Select Number of Agents menu set the number of agents for the datastore:
Minimum: - Set the minimun number of agents to use to scan the datastore. At least that number of proxy agents must be able to connect to the datastore.
Maximum: - Set the maximum number agents to use to scan the datastore.
Warning
• As there is no limit on the number of minimum and maximum agents that you can set, you should exercise caution so that you do not impact the system performance by using too many resouces for a single scan.
• You will not be able to add a datastore if the minimum number of agents cannot be assigned.
• A scan will fail if the assigned agent is unavailable after adding the datastore.
• The minimum number of agents must be less than or equal to the maximum number of agents.In the Add Label: field, add an agent label, by entering a label or removing and existing label. Agent labels represent the agent capabilities.
Click Next to go to the General Info screen.
3. General Info
Configure the General Info part per the information in General Info.
Click Next to go to the Add Tags & Access Control screen.
4. Add Tags & Access Control
Configure the Tags & Access Control par per the information in Tags & Access Control.
Click Save. The newly created data store appears on the Data Stores page. By default, data stores are displayed in alphabetic order by name. Depending on the number of entries per page, you might need to navigate to other pages to view the newly created data store.
Oracle Database
Oracle Database is a database commonly used for running online transaction processing, data warehousing, and mixed database workloads. The table below lists the default and recommended settings for the Oracle data store, with examples.
Settings | Description |
---|---|
Default Port | 1521 Please configure the port in the Data Store "Configure Connection" wizard step, or edit the already created Data Store and configure it in the "Connection" section. |
Recommended Proxy Agents | Windows Agent Linux Agent |
Libraries | Requires the following libraries to be installed on the Linux 3 Agent host, using this command:sudo apt-get install libaio1 libaio-dev |
Path Syntax | Depending on the Oracle version and configuration, you need to specify either the schema name or the database plus the service name in the "Database or schema" field in Data Store "Configure Connection" step of the wizard, or edit the already created Data Store and configure it in the "Connection" section. Example of a schema: hr Example of a database and service name: hrdb (SERVICE_NAME=XE) When you launch a scan, you can: • Either restrict the scan to specific table/s by configuring the table/s as targets in the "Add Targets" step in the scan definition wizard • Or scan all objects by not configuring any target. |
Obtaining the Oracle Configuration Details
To find the schema for the current user you can run this query:
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL;
To find the schema (or owner) for a particular table, you can run:
SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = '[your table]';
To find all tables for a particular schema (or owner), you can run:
SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = '[your schema]';
To get the information about the service name contact your Oracle database administrator.
PostgreSQL
PostgreSQL is a free and open-source relational database management system designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. The table below lists the default and recommended settings for the PostgreSQLOracle data store, with examples.
Settings | Description |
---|---|
Default Port | 5432 Please configure the port in the Data Store "Configure Connection" wizard step, or edit the already created Data Store and configure it in the "Connection" section. |
Recommended Proxy Agents | Windows Agent Linux Agent |
Path Syntax | Specific database: Please configure the database in the Data Store "Configure Connection" step of the wizard or edit the already created Data Store and configure it in the "Connection" section. Example: gldb All locations: Do not add any target in the "Add Targets" step in the scan definition wizard. Specific schema: Please configure the schema in the "Add Targets" step in the scan definition wizard. Example: hr Specific table: Please configure the schema and table in the "Add Targets" step in the scan definition wizard. Example: hr/employees Note: PostgreSQL by default blocks remote connections to the PostgreSQL server. To configure PostgreSQL to allow remote connections, see Allowing Remote Connections to PostgreSQL Server. |
Allowing Remote Connections to PostgreSQL Server
PostgreSQL by default blocks all connections that are not from the PostgreSQL database server itself. This means that to scan a PostgreSQL database, the Agent must either be installed on the PostgreSQL database server itself (not recommended), or the PostgreSQL server must be configured to allow remote connections.
To configure a PostgreSQL server to allow remote connections:
On the PostgreSQL database server, locate the
pg_hba.conf
configuration file. On a Unix-based server, the file is usually found in the/var/lib/postgresql/data
directory.Open
pg_hba.conf
in a text editor, as root.Add the following to the end of the file:
# Syntax: # host <database_name> <postgresql_user_name> <agent_host_address> <auth-method> host all all all md5
The above configuration allows any remote client to connect to the PostgreSQL server if a correct user name and password is provided. For a more secure configuration, use configuration statements that are specific to a database, user or IP address. For example:
Note
host database_A scan_user 172.17.0.0/24 md5
Open the
postgresql.conf
file and modify the Connections and Authentication section.You should change the
#listen_addresses = 'localhost'
line to this:listen_addresses = '*'
Tip
You can also use a specific IP address of the PostgreSQL server to listen on, instead of the global
*
.Save the file and restart the PostgreSQL service.