Easy Sql data anonymization with SAP Sql Anyhwere

[Luca Casavola]

#101926

Starting May 25, 2018 all worldwide organizations and companies that handle personal information of EU citizens have to comply with the General Data Protection Regulation (GDPR) or face heavy annual fines and penalties in case of non-compliance.
Two typical ways to lessen the burden regarding compliance with the GDPR is to implement:

• Anonymization that eliminates personal data so that data subjects can no longer be identified. Anonymized data is excluded from GDPR regulation altogether because anonymized data is no longer “personal data.”
• Pseudonymization, that replaces personal identifiers with nonidentifying references or keys so that anyone working with the data is unable to identify the data subject without the key. This type of data may enjoy fewer processing restrictions under the GDPR.

A common question in the internet is how to implement (pseudo)anonymization on pure Sql databases.
If your SQL engine doesn’t offer native data anonymization mechanisms the sensible choice could be the use of the VIEW concept.
For example let’s consider the following table with an integer key and three char attributes: first name, last name and e-mail address. This is the DDL SQL code snippet:

create table nogdpr_table (
id int,
firstname varchar(30),
lastname varchar(30),
email varchar(20)
)

Then suppose you have rows inside with personal data you would like to mask:

insert into nogdpr_table values(1,’Edgar’,’Codd’, ‘edcodd@domain.com’)
insert into nogdpr_table values(2,’Christopher’,’Date’, ‘cjdate@domain.com’)
….

By building a SQL View you can implement both naive or complex transformation rules, exploiting the power of the SQL language, on the attribute you want to anonymize. For example, the following view can simply do the job:

create view gdpr_view as
select id, ‘XXXXXX’ as firstname ,
‘YYYYYY’ as lastname ,
‘xxx@yyy’ as email
from nogdpr_table

In a more realistic scenario we aim for some authorized users to be able to see the original data and all others only the masked data. Of course this behaviour should be achieved without changes on the application code.
What you would need is a mechanism to access a table or an underlying view transparently, depending on the user profile, a mechanism to change the object reference at runtime based on a state (or variable).
It turns out that the Sql Anywhere can implement such a mechanism with the TABLE REF data type.
The user guide states : TABLE REF data type stores a reference to a base table, temporary table, or view. This data type is only for use with connection-scope variables. We get exactly what we need: dynamic reference based on variables. Let’s see how to apply the TABLE REF mechanism in our example. It is simply a matter of creating a connection-scope variable, i.e. create variable @reftable TABLE REF and setting the appropriate value, depending on the user profile:

** authorized users **

set @reftable= TABLE REF ( nogdpr_table )
SELECT * FROM TABLE REF ( @reftable ) as T

** non authorized users **

set @testtable= TABLE REF ( gdpr_view )
SELECT * FROM TABLE REF ( @reftable ) as T

It turns out that the variable content has changed but the sql query has not changed:

SELECT * FROM TABLE REF ( @reftable ) as T

The last concern is how to set the variable content based on the user profile.Another Sql Anywhere feature allows this: the login_procedure option.
As reported by the user guide the login_procedure option specifies a stored procedure that is called when a user connects via a database login or web service.
Basically you can implement a specific different logic for each database user.
For our purposes the only logic to implement is to create a connection-scope variable initialized to a specific value. So let’s assume we have two different users: gdpr, nogdpr
As DBA administrator let’s create two stored procedures owned by these two users:

ALTER PROCEDURE “nogdpr”.”nogdpr_login”()
BEGIN
create variable @reftable TABLE REF;
set @reftable= TABLE REF (DBA.nogdpr_table);
CALL dbo.sp_login_environment;
END

ALTER PROCEDURE “gdpr”.”gdpr_login”()
BEGIN
create variable @reftable TABLE REF;
set @reftable= TABLE REF (DBA.gdpr_view);
CALL dbo.sp_login_environment;
END

Then let’s set the user’s login_procedure option to link to the respective stored procedure:

set option gdpr.login_procedure=’gdpr_login’
set option nogdrp.login_procedure=’nogdpr_login’

Done! Now, if gdpr or nogdpr user will connect to the database, for the same query, they will get these different results:

As a final suggestion remember to provide the users with all the grants in order to access the table and the view used on this example. Enjoy your data anonymization.

 

[Autor]

Entradas