Using tables containing prefixes within DATPROF Privacy (for Runtime)

Some applications use prefixes before a table names to create multiple instances of the same table. For example; a table contract, can exist three times within the same database like A$contract, B$contract and C$contract. In some occasions the prefix differs per implementation. Different prefixes are used per installation. When developing a template within DATPROF Privacy, you can use functionality of DATPROF Runtime to keep your template simple and clean.

When DATPROF Privacy generates an application for Runtime, it will add a variable called DPF_TABLE_PREFIX to each table name. This variable can be changed within a Runtime environment. This will make it possible to create a template containing just the contract table. After deploying your template within your Runtime environment, you can update the DPF_TABLE_PREFIX parameter to A$ for example.

When executing the masking run almost all references to the contract table will be prefixed with A$. You could use the API to automatically update the prefix to the next prefix after the run has finished and start a new run with the new prefix. Contact support@datprof.com for more information about this topic.

Exceptions

Referring to prefix tables inside conditions or scripts

When referring to tables that contains prefixes for example inside a condition or a SQL Script, just must add a bit of Runtime code in order to tell Runtime to replace the table name with a prefix variant.

You can add the following dynamic code that will result an prefix variant of the table.

${dpf.db.format($dpf.parameters.DPF_SCHEMA_MAP.lookup("GaswareDB","dbo").withTable($dpf.strings.join($dpf.parameters.DPF_TABLE_PREFIX,"contract")))}

this will result in:

[GaswareDB].[dbo].[A$contract]

This function also helps out when your template is installed on a different database with different schema names. Inside your Environment parameters, you can overrule the database/schema mapping.

Referring to tables without prefixes

In some occasions you have to refer to tables without a prefix that are outside of your template. For example if you have added a value/random lookup on table containing a prefix that refers to the lookup table that does not contain a prefix.

If you enter a lookup table like this: [GaswareDB].[dbo].[customer], this will automatically be prefixed when running the value lookup inside an environment that contains a DPF_TABLE_PREFIX. In some occasions this is not what you want, because the table should not be prefixed.

There are two options to prevent an error because the wrongfully prefixed table could not be found.

Create a temporary synonym with the prefix that refers to the table without prefix. This synonym will be used to execute the generated code. Within your template you can add a script which creates the required synonyms and add another script that deletes them afterwards.

SQL

--SQL Server example
create synonym ${dpf.db.format($dpf.parameters.DPF_SCHEMA_MAP.lookup("dbo").withTable($dpf.strings.join($dpf.parameters.DPF_TABLE_PREFIX,"customer")))} 
	for ${dpf.db.format($dpf.parameters.DPF_SCHEMA_MAP.lookup("GaswareDB","dbo").withTable("customer"))};


-- This will result in the following create statement that will be executed.
create synonym [dbo].[A$customer]
	for [GaswareDB].[dbo].[customer]

You could also enter only the table name like customer as lookup table without database and schema identifiers. In this case the entered table name will not be prefixed during Runtime execution. This is however only recommended when the table always exist in the same schema and the connected user can select data without adding schema identifiers.