Skip to content

Connecting JDBC-based Systems

The following systems can be connected to Privacera Platform or PrivaceraCloud as data sources via Java Database Connectivity (JDBC):

  • Amazon Aurora

  • Microsoft SQL Server

  • MySQL

  • Oracle

  • Postgres

  • Redshift

  • Snowflake

  • Spark SQL

  • Synapse

The general process is as follows:

  1. Create or identify a service user in the target system with read/write privileges.

  2. Determine the JDBC connection string to the data and database name in that target.

  3. Define these details as properties in the Privacera Platform.

Prerequisites#

Have the following details ready to enter into the data source definition in Privacera:

  • A username and password in the target system that has read/write permission.

  • The name of the JDBC driver you need.

  • A JDBC connection string to communicate with the target data source.

Required Properties in Privacera#

Values for the following properties are described in Required Name of JDBC Driver per Target System, Username and Password, and Required JDBC Connection String.

Note

The format of the jdbc.url value varies by target system. Not all systems require databaseName.

jdbc.driver.class=<jdbc_driver_name>
jdbc.username=<user_with_readwrite_permission>
jdbc.password=<login_credentials_of_identified_user>
jdbc.url=jdbc:<protocol>://<hostname>:<port>;databaseName=<database_name>

Required Name of JDBC Driver per Target System#

Depending on the target system, for the jdbc.driver.class definition you enter in the Privacera properties, use one of the JDBC drivers shown below.

  • Amazon Aurora: org.mariadb.jdbc.Driver
  • Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • MySQL: com.mysql.jdbc.Driver
  • Oracle: oracle.jdbc.driver.OracleDriver
  • Postgres: org.postgresql.Driver
  • Redshift: org.postgresql.Driver
  • Snowflake: net.snowflake.client.jdbc.SnowflakeDriver
  • Spark SQL (Databricks): org.apache.hive.jdbc.HiveDriver
  • Synapse: com.microsoft.sqlserver.jdbc.SQLServerDriver

Username and Password#

Identify the name of a user who must have read/write permission in your data source and the login credentials for that user. These values are needed for jdbc.username and jdbc.password properties in Privacera.

Required JDBC Connection String#

The jdbc.url value you enter in the Privacera properties must be one of the following, where , , and other variables are for your specific environment:

  • Amazon Aurora: jdbc:mysql://<domainName>:<port>/<dbname>
  • Microsoft SQL Server: jdbc:sqlserver://<domainName>:<port>;databaseName=<db_name>
  • MySQL: jdbc:mysql://<domainName>:<port>/<dbname>
  • Oracle: jdbc:oracle:thin:@//<domainName>:<port>/<dbname>.localdomain
  • Postgres: jdbc:postgresql://<domainName>:<port>/<dbname>
  • Redshift: jdbc:postgresql://<domainName>:<port>/<dbname>
  • Snowflake: jdbc:snowflake://<domainName>:<port>/?warehouse=<name_of_policysync_warehouse>
  • Spark SQL (Databricks): jdbc:hive2://<domainName>:<port>/default;transportMode=http;ssl=true;httpPath=sql/protocolv1/o/0/xxxx-xxxxxx-xxxxxxxx;AuthMech=3;
  • Synapse: jdbc:sqlserver://<domainName>:<port>;databaseName=<dbname>

Add JDBC-Based Data Source in Privacera#

These are the setup and steps to add a JDBC-based data source.

Setup#

Have the details listed in the planning sections above ready to enter into the data source definition in Privacera

Steps#

To add a JDBC-based data source in Privacera Platform:

  1. Navigate to: Settings > Data Source Registration.

  2. Optionally click Add System or modify an existing data source.

  3. Enter a mnemonic name for this data source and a useful description.

  4. Click Save.

  5. Locate the new data source system name and from the wrench icon on the right, select Add Data Source.

  6. In the Add Data Source dialog, on the Choose tab, select JDBC APPLICATION.

  7. On the Configure tab:

  8. Enter a required Application Name of your choice.

  9. Enter a required Application Code of your choice. This is an identifier for your own use.

  10. If you have prepared a properties file in JSON format, click Import Properties and load the file.

  11. Scroll to find the following properties and enter the values you prepared:

    • jdbc.username

    • jdbc.password

    • jdbc.driver.class

    • jdbc.url

  12. Accept the default values for all other properties or modify them if desired.

  13. At the bottom left, to verify the properties, click Test Connection.

  14. At the bottom right, click Next to save the data source or Back to return to the Choose tab.


Last update: August 24, 2021