Confidentialité et sécurité des données : Un guide pour les développeurs sur la gestion des données sensibles avec DuckDB

Tutoriels

SQL

Comprendre DuckDB pour la confidentialité et la sécurité des données

La confidentialité et la sécurité des données sont devenues critiques pour toutes les organisations à travers le monde. Les organisations doivent souvent identifier, masquer ou supprimer des informations sensibles de leurs ensembles de données tout en maintenant l’utilité des données. Cet article explore comment tirer parti de DuckDB, une base de données analytique en processus, pour une remédiation efficace des données sensibles.

Pourquoi DuckDB ? (Et pourquoi cela devrait-il vous intéresser ?)

Pensez à DuckDB comme au cousin analytique doué de SQLite. C’est une base de données intégrée qui fonctionne directement dans votre processus, mais elle est spécifiquement conçue pour traiter des charges de travail analytiques. Qu’est-ce qui la rend parfaite pour la remédiation des données ? Eh bien, imaginez pouvoir traiter de grands ensembles de données à la vitesse de l’éclair, sans mettre en place un serveur de base de données compliqué. Ça a l’air bien, non ?

Voici ce qui rend DuckDB particulièrement génial pour notre cas d’utilisation :

C’est incroyablement rapide grâce à son stockage orienté colonne.
Vous pouvez l’exécuter directement dans votre environnement Python existant.
Il gère plusieurs formats de fichiers comme si de rien n’était.
Il fonctionne bien avec le stockage en nuage (plus d’infos à ce sujet plus tard).

Dans ce guide, j’utiliserai Python avec DuckDB. DuckDB prend également en charge d’autres langages, comme mentionné dans leur documentation.

Introduction à DuckDB pour la confidentialité des données

Prérequis

Python 3.9 ou version supérieure installé
Connaissance préalable de la configuration de projets Python et d’environnements virtuels ou d’environnements Conda

Installez DuckDB dans un environnement virtuel en exécutant la commande suivante :

Shell

pip install duckdb --upgrade

Maintenant que vous avez installé DuckDB, créons une connexion DuckDB :

Python

import duckdb

import pandas as pd

​

# Create a DuckDB connection - it's this simple!

conn = duckdb.connect(database=':memory:')

Techniques avancées de masquage des données PII

Voici comment mettre en œuvre un masquage robuste des PII (Informations personnellement identifiables) :

Supposons que vous ayez un ensemble de données rempli d’informations clients qui doivent être nettoyées. Voici comment vous pouvez gérer des scénarios courants.

Créons des données d’exemple :

SQL

CREATE TABLE customer_data AS 

SELECT 

    'John Doe' as name,

    '123-45-6789' as ssn,

    '[email protected]' as email,

    '123-456-7890' as phone;

Cela crée une table appelée customer_data avec une ligne de données sensibles d’exemple.
Les données incluent un nom, un numéro de sécurité sociale, un e-mail et un numéro de téléphone.

La deuxième partie implique le masquage des modèles à l’aide de regexp_replace :

SQL

-- Implement PII masking patterns

CREATE TABLE masked_data AS

SELECT 

    regexp_replace(name, '[a-zA-Z]', 'X') as masked_name,

    regexp_replace(ssn, '[0-9]', '*') as masked_ssn,

    regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email,

    regexp_replace(phone, '[0-9]', '#') as masked_phone

FROM customer_data;

Laissez-moi vous expliquer ce que fait le code SQL ci-dessus.

regexp_replace(name, '[a-zA-Z]', 'X')
- Remplace toutes les lettres (majuscules et minuscules) par 'X'
- Exemple : "John Doe" devient "XXXX XXX"
regexp_replace(ssn, '[0-9]', '*') as masked_ssn
- Remplace tous les chiffres par '*'
- Exemple : "123-45-6789" devient "--***"
regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email:
- (^[^@]+) capture tout ce qui est avant le symbole @
- (@.*$) capture le @ et tout ce qui le suit
- Remplace la première partie par '****' et conserve la partie domaine
- Exemple : "" devient "****@email.com"
regexp_replace(phone, '[0-9]', '#') as masked_phone:
- Remplace tous les chiffres par '#'
- Exemple : "123-456-7890" devient "###-###-####"

Ainsi, vos données sont transformées comme suit :

Données originales :

name: John Doe
ssn: 123-45-6789
email: [email protected]
phone: 123-456-7890

Données masquées :

masked_name: XXXX XXX
masked_ssn: ***-**-****
masked_email: ****@email.com
masked_phone: ###-###-####

Implémentation Python

Python

import duckdb

import pandas as pd

​

def mask_pii_data():

    # Create a DuckDB connection in memory

    conn = duckdb.connect(database=':memory:')

    try:

        # Create and populate sample data

        conn.execute("""

            CREATE TABLE customer_data AS 

            SELECT 

                'John Doe' as name,

                '123-45-6789' as ssn,

                '[email protected]' as email,

                '123-456-7890' as phone

        """)

        # Implement PII masking

        conn.execute("""

            CREATE TABLE masked_data AS

            SELECT 

                regexp_replace(name, '[a-zA-Z]', 'X') as masked_name,

                regexp_replace(ssn, '[0-9]', '*') as masked_ssn,

                regexp_replace(email, '(^[^@]+)(@.*$)', '****$2') as masked_email,

                regexp_replace(phone, '[0-9]', '#') as masked_phone

            FROM customer_data

        """)

        # Fetch and display original data

        print("Original Data:")

        original_data = conn.execute("SELECT * FROM customer_data").fetchdf()

        print(original_data)

        print("\n")

        # Fetch and display masked data

        print("Masked Data:")

        masked_data = conn.execute("SELECT * FROM masked_data").fetchdf()

        print(masked_data)

        return original_data, masked_data

    except Exception as e:

        print(f"An error occurred: {str(e)}")

        return None, None

    finally:

        # Close the connection

        conn.close()

Rédaction des données basée sur des règles

Laissez-moi expliquer la rédaction des données en termes simples avant de plonger dans ses aspects techniques.

La rédaction des données est le processus de dissimulation ou de suppression d’informations sensibles dans des documents ou des bases de données tout en préservant la structure générale et le contenu non sensible. Pensez-y comme utiliser un marqueur noir pour cacher des informations confidentielles sur un document imprimé, mais sous forme numérique.

Implémentons maintenant la rédaction des données avec DuckDB et Python. J’ai ajouté cet extrait de code avec des commentaires afin que vous puissiez facilement suivre.

Python

import duckdb

import pandas as pd

​

def demonstrate_data_redaction():

    # Create a connection

    conn = duckdb.connect(':memory:')

    # Create sample data with various sensitive information

    conn.execute("""

        CREATE TABLE sensitive_info AS SELECT * FROM (

            VALUES

                ('John Doe', '[email protected]', 'CC: 4532-1234-5678-9012', 'Normal text'),

                ('Jane Smith', '[email protected]', 'SSN: 123-45-6789', 'Some notes'),

                ('Bob Wilson', '[email protected]', 'Password: SecretPass123!', 'Regular info'),

                ('Alice Brown', '[email protected]', 'API_KEY=abc123xyz', 'Basic text')

            ) AS t(name, email, sensitive_field, normal_text);

    """)

    # Define redaction rules

    redaction_rules = {

        'email': r'[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}',  # Email pattern

        'sensitive_field': r'(CC:\s*\d{4}[-\s]?\d{4}[-\s]?\d{4}[-\s]?\d{4}|SSN:\s*\d{3}-\d{2}-\d{4}|Password:\s*\S+|API_KEY=\S+)',  # Various sensitive patterns

        'name': r'[A-Z][a-z]+ [A-Z][a-z]+'  # Full name pattern

    }

    # Show original data

    print("Original Data:")

    print(conn.execute("SELECT * FROM sensitive_info").fetchdf())

    # Apply redaction

    redact_sensitive_data(conn, 'sensitive_info', redaction_rules)

    # Show redacted data

    print("\nRedacted Data:")

    print(conn.execute("SELECT * FROM redacted_data").fetchdf())

    return conn

​

def redact_sensitive_data(conn, table_name, rules):

    """

    Redact sensitive data based on specified patterns.

    Parameters:

    - conn: DuckDB connection

    - table_name: Name of the table containing sensitive data

    - rules: Dictionary of column names and their corresponding regex patterns to match sensitive data

    """

    redaction_cases = []

    # This creates a CASE statement for each column

    # If the pattern matches, the value is redacted

    # If not, the original value is kept

    for column, pattern in rules.items():

        redaction_cases.append(f"""

            CASE 

                WHEN regexp_matches({column}, '{pattern}') 

                THEN '(REDACTED)' 

                ELSE {column} 

            END as {column}

        """)

    query = f"""

    CREATE TABLE redacted_data AS

    SELECT 

        {', '.join(redaction_cases)}

    FROM {table_name};

    """

    conn.execute(query)

​

# Example with custom redaction patterns

def demonstrate_custom_redaction():

    conn = duckdb.connect(':memory:')

    # Create sample data

    conn.execute("""

        CREATE TABLE customer_data AS SELECT * FROM (

            VALUES

                ('John Doe', '123-45-6789', 'ACC#12345', '$5000'),

                ('Jane Smith', '987-65-4321', 'ACC#67890', '$3000'),

                ('Bob Wilson', '456-78-9012', 'ACC#11111', '$7500')

            ) AS t(name, ssn, account, balance);

    """)

    # Define custom redaction rules with different patterns

    custom_rules = {

        'name': {

            'pattern': r'[A-Z][a-z]+ [A-Z][a-z]+',

            'replacement': lambda match: f"{match[0][0]}*** {match[0].split()[1][0]}***"

        },

        'ssn': {

            'pattern': r'\d{3}-\d{2}-\d{4}',

            'replacement': 'XXX-XX-XXXX'

        },

        'account': {

            'pattern': r'ACC#\d{5}',

            'replacement': 'ACC#*****'

        }

    }

    def apply_custom_redaction(conn, table_name, rules):

        redaction_cases = []

        for column, rule in rules.items():

            redaction_cases.append(f"""

                CASE 

                    WHEN regexp_matches({column}, '{rule['pattern']}') 

                    THEN '{rule['replacement']}'

                    ELSE {column} 

                END as {column}

            """)

        query = f"""

        CREATE TABLE custom_redacted AS

        SELECT 

            {', '.join(redaction_cases)},

            balance  -- Keep this column unchanged

        FROM {table_name};

        """

        conn.execute(query)

    # Show original data

    print("\nOriginal Customer Data:")

    print(conn.execute("SELECT * FROM customer_data").fetchdf())

    # Apply custom redaction

    apply_custom_redaction(conn, 'customer_data', custom_rules)

    # Show results

    print("\nCustom Redacted Data:")

    print(conn.execute("SELECT * FROM custom_redacted").fetchdf())

​

# Run demonstrations

print("=== Basic Redaction Demo ===")

demonstrate_data_redaction()

print("\n=== Custom Redaction Demo ===")

demonstrate_custom_redaction()

Résultats d’exemple

Avant la rédaction :

name       email              sensitive_field
John Doe   [email protected] CC: 4532-1234-5678-9012

Après la rédaction :

name       email      sensitive_field
(REDACTED) (REDACTED) (REDACTEd)

Conclusion

DuckDB est une base de données en mémoire simple mais puissante qui peut aider à la remédiation des données sensibles.

N’oubliez pas de toujours :

Valider vos données masquées.
Utiliser le traitement parallèle pour les grands ensembles de données.
Tirer parti de l’intégration S3 de DuckDB pour les données cloud.
Surveillez votre utilisation de la mémoire lors du traitement de fichiers volumineux.

Source:
https://dzone.com/articles/developers-guide-handling-sensitive-data-with-duckdb