GDPR, NAV Data discovering from SQL Server, Part I

GDPR, NAV Data discovering from SQL Server, Part I

Hi people, The GDPR deadline is approaching!

…for users who use older versions of NAV (and who do not want to upgrade NAV or purchase third-party tools) it may be useful to have useful functions to search for data (in NAV SQL Server Database). Having a tool that allows raw data to be extracted (data hypothesized as sensitive) is already something.

I show this simple solution for searching the data in NAV reading from SQL Server.

Elements:

• SQL Store Procedure (Search only in defined tables for GDPR sensible data)
• NAV Pages (for GDPR Configuration & Data Search)
• NAV Codeunit (to recall SQL Stored Procedure)

SQL Server Stored Procedure: “SP_SearchNAVData2018”

USE [Demo Database NAV ITA (11-0)]
GO

/****** Object: StoredProcedure [dbo].[SP_SearchNAVData2018] Script Date: 03/04/2018 16:19:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_SearchNAVData2018]
(
@SearchStr nvarchar(100)
)

AS
BEGIN
CREATE TABLE #Columns (ColumnID integer not null , ColumnName nvarchar(370));
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630));
SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
DECLARE @ColumnID integer ;
DECLARE @sql_string nvarchar(256);
DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
DECLARE @CategoryID integer;
SET @ColumnID = 1 ;
SET @TableName = ”;
SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””);
SET @ColumnName = ”
SET @sql_string = ”
SET @CategoryID = 0;

–DELETE LAST SEARCH DATA
DELETE [dbo].[CRONUS Italia S_p_A_$Privacy Data Search Output]

–TABLE CURSOR
DECLARE db_cursor CURSOR FOR
SELECT [Table] FROM [CRONUS Italia S_p_A_$Privacy Tables Configurator]

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE fields_cursor CURSOR FOR
SELECT (QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’, ‘int’, ‘decimal’)

–FIELD CURSOR
OPEN fields_cursor
FETCH NEXT FROM fields_cursor INTO @ColumnName
PRINT @SearchStr + ‘ Foud IN: ‘ — TARGET SEARCH

WHILE @@FETCH_STATUS = 0
BEGIN
IF @ColumnName IS NOT NULL
BEGIN

–SQL PREPARE
SET @sql_string = ”;
SET @sql_string = ‘IF EXISTS (SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘
LIKE ”%’ + @SearchStr + ‘%”) PRINT ”’ + @TableName + ‘, ‘ + @ColumnName + ””

— DATA PRINT RESULTS
EXECUTE(@sql_string)

— DATA WRITE IN TABLE
DECLARE @DynamicSQl varchar(250);
DECLARE @ColumnLocal varchar(50);
SET @DynamicSQl = ‘SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ”%’ + @SearchStr + ‘%”’;
EXEC (@DynamicSQl)

If @@RowCount > 0
BEGIN
PRINT ‘Entrato’
— EXEC(@SomeOtherSQLStatement)
INSERT INTO [dbo].[CRONUS Italia S_p_A_$Privacy Data Search Output]
([Table]
,[Field]
,[Search Results])
VALUES
(@TableName
,@ColumnName
,@SearchStr)
END
END

FETCH NEXT FROM fields_cursor INTO @ColumnName
END — END Fields Cursor

CLOSE fields_cursor
DEALLOCATE fields_cursor

FETCH NEXT FROM db_cursor INTO @Tablename
END –END db Cursor

CLOSE db_cursor
DEALLOCATE db_cursor
RETURN
END

NAV OBJECTS “GDPRBOB” TAG

TABLES ON SQL

 

“HOW-TO RUN” FROM SQL SERVER

#1 – By SQL Query Window, Example:

— RUN DATA SEARCH

EXEC [dbo].[SP_SearchNAVData2018] ‘Computer’;  EXEC [dbo].[SP_SearchNAVData2018] ‘Roberto’

— TABLES TO CHEK

SELECT [Table] FROM [CRONUS Italia S_p_A_$Privacy Tables Configurator]

RESULTS

“HOT-TO RUN” FROM NAV

#2, From NAV with ADO.NET

Page Privacy Data Search Output

You can define the GDPR Tables (tables with sensible data)

How to Search DATA: Put the string to search in “Search Field”

The page launch the Codeunit 50001 Privacy Data Management, this codeunit call the store procedure ‘SP_SearchNAVData2018’

Function SearchDataNAV2018(parString : Text[50])

IF ISCLEAR(ADOConnection) THEN

CREATE(ADOConnection,FALSE,TRUE);

ADOConnection.ConnectionString:= ‘Driver={SQL Server};’ + ‘Server=’+’XXX1\NAVDEMO’+’;’+ ‘Database=’+’Demo Database NAV ITA (11-0)’+’;’+ ‘Uid=’+’XX’+’;’+ ‘Pwd=’+XXXX’+’;’;

ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN

CREATE(ADOCommand,FALSE,TRUE);

VarActiveConnection := ADOConnection;

ADOCommand.ActiveConnection := VarActiveConnection;

ADOCommand.CommandText := ‘SP_SearchNAVData2018’;

ADOCommand.CommandType := 4;

ADOCommand.CommandTimeout := 0;

ADOParameter:=ADOCommand.CreateParameter(‘@SearchStr’, 200,1,50, parString);

ADOCommand.Parameters.Append(ADOParameter);

ADOCommand.Execute;

SLEEP(10000);

COMMIT;

ADOConnection.Close;

CLEAR(ADOConnection);

MESSAGE(‘GDPR Data Extracted’);

SELECTLATESTVERSION;

 

Stay tuned for Part II (Data Mascherade & Data Encryption)

… many requests from my Contact Page !  .ZIP object is now linked below !

also on Technet Gallery https://gallery.technet.microsoft.com/GDPR-Data-discovering-from-2f69d5df

Download   SQL Script  https://github.com/rstefanetti/NAV_SQL

NAV Objects GDPRBOB