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