Enhanced Ransomware Detection and Response Using Programmable Data Infrastructure

Corso di Laurea Sicurezza dei Sistemi e delle Reti Informatiche Enhanced Ransomware Detection and Response Using Programmable Data Infrastructure Relatore: Chiara Braghin Tesi di Laurea di: Diego Gobbetti Matr. 22987A Anno Accademico 2023/2024 Contents Abstract 2 Introduction 3 1 Background & Motivation 5 1.1 DevOps .............................. 5 1.1.1 Microservices : DevOps tailored architecture . . . . . . 14 1.1.2 CI/CD Security . . . . . . . . . . . . . . . . . . . . . . 16 1.1.3 CI/CDThreats...................... 18 1.2 Hybrid Multi-Cloud Infrastructure . . . . . . . . . . . . . . . 34 1.2.1 Cloud Security Challenges . . . . . . . . . . . . . . . . 35 1.3 DataCrucialRole......................... 39 1.3.1 DataOps.......................... 41 1.3.2 Programmable Data Infrastructure . . . . . . . . . . . 42 1.3.3 Ransomware........................ 45 1.4 Delphix - DevOps Data Platform . . . . . . . . . . . . . . . . 52 1.4.1 Continuous Data Engine : Technical Overview . . . . . 55 1.4.2 Continuous Compliance Engine : Technical Overview . 59 2 Project Enterprise Recovery Vault(ERV) 69 2.1 Architecture............................ 71 2.2 Data Integrity Control Mechanism . . . . . . . . . . . . . . . 78 2.3 Delphix Engines’ Key Role in ERV Solution . . . . . . . . . . 89 2.4 Custom Masking Algorithm . . . . . . . . . . . . . . . . . . . 104 2.5 Orchestration Appliance . . . . . . . . . . . . . . . . . . . . . 135 Conclusion 168 Bibliography 174 1 Abstract This paper presents the design and implementation of a managed detection and response (MDR) system for ransomware threats targeting relational databases. The solution is built on top of Delphix, leveraging its capabilities as a Programmable Data Infrastructure to enable a fast, flexible, and fully automated control process. The development is based on the client’s existing infrastructure, where highly confidential database records are replicated across multiple testing environments within a CI/CD pipeline. These environments reside in a less isolated network segment, accessible by heterogeneous identities by design, introducing inherent security risks. The paper begins with a background and motivation section, outlining key concepts necessary to understand the solution and its alignment with business requirements. The discussion is risk-driven, starting with critical vulnerabilities and documented attack kill chains in CI/CD pipelines, followed by security constraints in complex cloud environments, and culminating in an analysis of the most pervasive and damaging threat, the ransomware. Next, the project chapter details the state-of-the-art solution, covering the final infrastructure design, control logic, data flows, and critical software components. The execution workflow of these components is explored through code snippets, highlighting key design and implementation choices that establish the solution as a flexible framework for enforcing various security controls across heterogeneous environments. 2 Introduction The era of digital transformation, from an enterprises operations outlook, forced enterprises to increasingly adopt modern IT paradigms such as DevOps, DataOps, and Hybrid Multi-Cloud infrastructures to enhance agility, scalability, and efficiency. However, these advancements introduce new security challenges, particularly in software development, data protection, and cloud security. One of the outcomes of such paradigms adoption is the widespread of Continuous Integration and Continuous Deployment (CI/CD) pipelines. Their rise has revolutionized software delivery, yet it has also emerged as a prime target for cyber threats. Attack vectors such as supply chain attacks, pipeline poisoning, and insufficient artifact integrity verification pose significant risks, as highlighted by cybersecurity frameworks like OWASP and MITRE ATT&CK. Furthermore, as businesses rely on hybrid and multi-cloud infrastructures, they face increased complexities in ensuring regulatory compliance, data governance, and system resilience. Adhering to stringent data protection regulations such as GDPR and HIPAA requires robust strategies to secure data across heterogeneous environments. The paper cover in depth the threat targeted by the solution - Ransomware. The ransomware threats target landscape is wide and scattered across multiple nations and sectors, this is just one of the main reasons, along with the missing reporting of incidents, why ransomware data articles from leading research organizations report similar but not identical results. Nevertheless, the trend is evident: ransomware has become the predominant monetization strategy employed by attackers. Its prevalence continues to increase annually, along with its associated financial impact. Particularly, the client’s system, which integrates production data in testing environments, through Delphix engines, further expands the attack surface. Designing systems to provision masked data directly from production data repositories to testing environments is actually a common pattern to assure the quality of testing, and in some cases, the data is not even masked. This continuous integration between production and multiple testing environments across different segments of the perimeter can be leveraged to in3 ject malicious payloads in production environments, hosting mission-critical databases. Since, testing environments, by design, are often exposed to various threats and cannot always be secured further without negatively impacting efficiency and quality. To address these challenges, organizations are increasingly leveraging Programmable Data Infrastructure (PDI) and DataOps methodologies to streamline data provisioning, ensure data fidelity, and enhance security measures. Delphix, a leading DevOps data platform, plays a crucial role in mitigating these risks by providing automated data masking, virtualization, and compliance capabilities. Its Continuous Data Engine and Continuous Compliance Engine enable enterprises to manage sensitive data securely while maintaining agility in software development and deployment processes. The second part of this paper illustrates a real-world implementation of a solution that seek to mitigate ransomware risks employing the illustrated technologies in the client infrastructure. The Enterprise Recovery Vault (ERV) solution was designed to ensure data integrity simultaneously to an existing CI/CD-driven software development environment. By integrating Delphix engines, custom masking algorithm and an orchestration appliance, the ERV project aims to mitigate ransomware threats in relational databases, satisfying all requirements outlined in the ransomware chapter, including control robustness, speed and frequency. While establishing an automated back-up workflow, which continuously update back-up repository(Vault) with clean copies of data with an high frequency, ensuring few to none discrepancies to production. Therefore the system seek to guarantee an high degree of operational resiliency, ensuring continuous data integrity verification and minimal recovery time and data loss in the event of an incident. The following sections provide an in-depth analysis of the background concepts, including DevOps security, hybrid multi-cloud challenges, DataOps principles, and ransomware threats. Subsequently, the ERV project is presented, detailing its architecture, data integrity control mechanism, Delphix engine integration, and custom security measures. By bridging state-of-theart security methodologies with a real-world enterprise implementation, this paper contributes to the ongoing discourse on securing modern IT environments. 4 Chapter 1 Background & Motivation 1.1 DevOps In recent years, many companies have faced an unrelenting demand for digital transformation. It has become imperative to leverage the new opportunities offered by technological advancements to remain competitive. The majority of corporate technology domains have shifted their primary focus to emphasize digital transformation initiatives as a means of survival in this rapidly evolving landscape. These transformations have driven organizations to reshape their core structure across three fundamental dimensions, simultaneously: Modernization of Technologies: Modernization of technologies has been crucial to reducing costs and increasing the efficiency of development processes. Modernization refers to transitioning from legacy systems, such as mainframe-based systems with high resource demands and fully managed by the owner, to cloud computing services. There are three main cloud computing models, each varying in the degree of configuration and resource management offered to the client, as summarised in Figure 1.1: Infrastructure as a Service (IaaS): In an IaaS model, the service provider offers computing, storage, and network resources, giving the client the flexibility to deploy and run applications, including operating systems and third-party software. Platform as a Service (PaaS): In a PaaS model, the cloud infrastructure provided by the service provider includes the resources of IaaS, along with a pre-configured operating system. The client 5 can install third-party applications compatible with the system provided within the infrastructure. Software as a Service (SaaS): In a SaaS model, the service provider grants access to proprietary applications through specific client applications such as web browsers or mobile apps. The client cannot modify the cloud infrastructure, except for minimal customizations made by the service provider for that client. Figure 1.1: Cloud computing models [15] In recent years, these models have continued to evolve in various ways, most notably through the adoption of Content Delivery Networks (CDNs) in the context of web applications. CDNs enable greater resource distribution, allowing personalized delivery based on the client’s location. Common examples of IaaS include AWS EC2, Microsoft Azure VM, and Google Cloud Platform (GCP). These serve as foundations for the corresponding PaaS offerings. Other popular PaaS platforms include Heroku, IBM Cloud, and Salesforce. Resources Migration: Strongly tied to the modernization of technologies, 6 as technologies like IaaS, PaaS, and SaaS are cloud-based, organizations have had to migrate their resources from their traditional infrastructure to cloud services, both for storage and computing, utilizing different architectures. Adopting a cloud-based approach is a complex process that demands a range of technical skills. It is crucial to ensure proper orchestration and integration of services and fully leverage their capabilities. To ensure maximum synergy with the cloud service, it is essential to evaluate which type of architecture to adopt: private cloud, public cloud, hybrid, or pure SaaS. Software Microtization: Microtization refers to the process of breaking down traditional monolithic applications, which are self-contained and lack flexibility, interoperability, fault tolerance, and scalability, into smaller, independent components called microservices. These microservices typically communicate with each other through APIs, using lightweight protocols such as LDAP. This architectural choice is often referred to as a microservices architecture. It is characterized by weak dependencies between components, service separation, and high granularity. This approach brings several benefits, including ease of scalability and understanding, greater flexibility, faster release cycles, and resilience to architectural changes. One of the main advantages of this microservices architecture is its high synergy with DevOps practices, which are aimed at optimizing and shortening the software development lifecycle, ensuring an efficient and stable product. The implementation of DevOps practices in development processes involves key approaches such as Continuous Delivery & Deployment (CD) and Continuous Integration (CI). 7 Figure 1.2: Organization transformation over different macro aspects [5] However, the implementation of DevOps practices, typically embodied in CI/CD pipelines, is crucial today to enable organizations to meet the relentless demand for new digital features or updates to existing ones in response to technological or natural changes, within unprecedented timeframes. As a result, many organizations have shifted their architectural choices towards systems organized into micro, self-contained, and independent units that can be developed, tested, and released individually by smaller, dedicated teams, as opposed to larger teams in traditional development settings. Each team, however, adheres to shared guidelines and is typically supervised by a designated figure. Figure 1.3: Microservice tailored release workflow [17] DevOps is a term widely used in the software development industry to refer to a set of practices, tools, and a cultural framework aimed at integrating software development teams (Dev) and IT operations teams (Ops) to automate and continuously enhance the processes of application development, testing, and deployment. The primary goal of DevOps is to accelerate the software development lifecycle while improving product quality and reducing 8 that determines release speed. To ensure good flexibility, the software must exhibit modularity, extensibility, and interoperability. Additionally, in an application with tightly coupled components, achieving high test coverage during CI and ensuring reliable unit tests is challenging. These aspects are priorities in a microservices architecture.[3] An application with a traditional monolithic architecture must undergo a transformation to excel in a CD process. This transformation will inevitably disrupt the original design in favor of a microservices-based paradigm, which offers: Independent deployment: Changes to individual services can be deployed to production or designated environments regardless of the state of other services. This contrasts with monolithic architectures where: ”When one team made a change, that team was unable to release it independently. Instead, they first needed to merge the change into the master branch. To avoid chaos caused by multiple simultaneous merges, they often needed to queue the merge. The merge could involve conflicts, requiring coordination with other teams. After merging, the entire application needed to be re-tested to identify any new errors.” Shorter release times: Releasing a single service requires significantly less testing effort compared to the entire application. With a dedicated pipeline for each service, workload is distributed, and release times are drastically reduced, unlike a single pipeline for the entire application, which required comprehensive testing. Simplified deployment procedures: Closely tied to the ability to create a unified toolchain for all pipelines. Faster incremental changes: “For most small incremental functional changes, we have reduced the cycle time from multiple months to two to five days.” Fewer people are involved in decision-making for changes, and communication pathways from the end user to the assigned engineer are shorter. Easier technology changes: “After we moved a system to microservices, each team can upgrade independently because each service no longer shares the same codebase, compilation process, and language runtime (e.g., Java Virtual Machine) with other parts of the system. This independence accelerates the adoption of new features in updated libraries or language versions, enabling us to deliver enhancements to customers faster than competitors.” In addition, language and library developers are also adopting CD, increasing the speed at which they release 15 new versions. This trend underscores the importance of simplifying upgrades for languages and libraries in microservices-based applications. 1.1.2 CI/CD Security As with the entire landscape of cyber attacks, attacks on software supply chains and CI/CD pipelines are growing rapidly, as reported by organizations such as the Cybersecurity and Infrastructure Security Agency (CISA) and StepSecurity. This is not surprising given the critical role a CI/CD pipeline plays in the SDLC, the flow of intellectual property it processes, and the sensitive data stored by the tools supporting the pipeline. SolarWinds Supply Chain Hack It is therefore not surprising that the hack of the supply chain of one of SolarWinds’ products, a company that provides monitoring and management solutions for systems, networks, and infrastructures, is considered one of the most significant in terms of the magnitude of its impact. The Orion hack was a compromise of its code signing and build process. Essentially, the attackers were able to first gain access to the CI/CD pipeline, then modify the behavior of the msbuild.exe compiler to allow, after auditing the source code (to verify the legitimacy of the code and detect tampering), the insertion of a malicious payload into the source code before it was compiled. The attackers then modified the legitimate and digitally signed SolarWinds.Orion.Core.BusinessLayer.dll file. This .dll file acted as a plugin for Orion, opened by the SolarWinds process SolarWinds.BusinessLayerHost- (x64).exe when Orion ran. The tampered .dll file, added to the legitimate HotFix #5 package for SolarWinds Orion (CORE-2019.4.5220.20574SolarWinds-Core-v2019.4.5220-Hotfix5.msp), was a trojanized technique in the software supply chain attack. The attack technique can be traced to a Command and Control (C2) approach. The trojan contained an obfuscated backdoor that allowed the attackers to access the infected environments completely invisibly to the victim’s intrusion detection systems, as the HTTP traffic between the backdoor and the attackers was disguised as legitimate Orion performance data (Orion Improvement Program (OIP) protocol). The backdoor also employed evasion tactics such as remaining dormant for two weeks after installation and disabling all antivirus and forensic applications by exploiting the elevated privileges required by Orion to function properly. Orion was a widely used tool by various organizations worldwide, primarily in the U.S., which SolarWinds itself estimated to be about 275,000, including more than 425 Fortune 500 organizations, all 10 major U.S. telecommunications companies, all five branches of the U.S. military, and five U.S. govern16 Figure 1.8: Attack process overview [19] ment agencies. Once released into production, it was estimated that around 18,000 organizations installed one of the updates released between March and June 2020, and around 100 organizations were actually compromised. This estimate was made by FireEye (a company providing network and system security solutions and consulting) following the analysis of systems from potential malware victims. FireEye itself was one of the compromised companies and the first to detect the intrusion into their systems. The actual malicious activities performed by the malware on the victim systems began with data exfiltration, allowing the attackers to identify the victim and, if they were a potential target, assign a C2 domain to the backdoor to manage the attack individually or otherwise completely disable the malware. Once a unique C2 domain was assigned to the victim, the attackers proceeded with manual activities to expand within the victim’s perimeter, establish persistence, and exfiltrate additional confidential information. They then carried out what is called RainDrop and TearDrop, an attack methodology aimed at installing a well-known penetration testing application on the victim’s system to detect further vulnerabilities in the infrastructure, Cobalt Strike Beacon, in order to find more dangerous ways to exploit the victims. Considering the limited metadata available, it is difficult to outline the attack’s timeline precisely. It is still unclear how the attackers were able to access SolarWinds’ CI/CD pipeline. However, the biggest question left behind by this attack is its impact. Given how sophisticated it was, the extent of the victims, and its duration, it is almost impossible to trace the potential 17 information exfiltrated and determine the full scope of this attack. Three years later, it is believed that this attack was the root cause of several APTs (Advanced Persistent Threats) that followed in the subsequent years worldwide. 1.1.3 CI/CD Threats According to OWASP [23], potential risks that can impact a CI/CD pipeline include: Insufficient Flow Control Mechanism: Insufficient flow control refers to the ability of an attacker, who has obtained highly privileged permissions in the system running a CI/CD process, to upload malicious code or artifacts into the source control management (SCM) and trigger the pipeline due to the lack of a mechanism enforcing additional approval and review checks. At least theoretically, a CI/CD flow is designed for full automation, allowing artifacts to reach production environments with minimal human intervention. It is essential to enforce targeted controls to ensure that unauthorized entities cannot arbitrarily upload code, and these controls must be stringent. Otherwise, an attacker who has bypassed identity and access controls in the SCM, CI systems, or any systems in the CD or monitoring components could exploit this. The closer the compromised node is to the end of the pipeline, the more likely it is that the malicious artifact will reach production environments, as compliance and security checks are typically performed at earlier nodes. Although nodes at the end of the pipeline do not natively support artifact uploads, complex manipulation could result in indirect uploads through provided functions. Final nodes typically include artifact storage repositories (e.g., JFrog, integrated repositories in providers like Azure, AWS, Google), performance evaluation systems (e.g., Apache JMeter, Gatling, k6), or deployment orchestration systems for staging environments (e.g., Ansible, Kubernetes, Chef, Puppet), as well as parallel pipeline monitoring systems (e.g., ELK stack, Splunk). In fact, such attacks often involve injecting malicious code through the initial SCM system, such as GitLab, GitHub, or Bitbucket, which serve as the root repositories for application code and configuration files. These systems are all based on Git, a widely used utility in software development that enables version tracking, rollback to previous versions, conflict-free collaboration among developers, isolated development environments for functional integration, and granular change history for debugging and compliance. These systems are essentially 18 Git interface platforms, which may seem trivial but in DevOps contexts expose essential functionalities for seamless CI/CD integration, including fully managed, pre-configured, auto-scalable CI/CD pipelines with predefined tools, requiring minimal configuration of job triggers, identity and access management policies, secret management policies, and optionally additional controls. That said, it is crucial to clarify that one of the highly recommended practices in CI/CD processes, widely adopted, is the manual review of the final artifact before release to production, at least for sensitive application components. However, despite meticulous manual reviews, attackers often employ obfuscation techniques that make malicious attributes difficult to detect even for human experts. These techniques are beyond the scope of this thesis. Speaking of attack techniques that can be linked to this threat, we have: •Pushing code to a repository branch that is automatically deployed through the CI/CD pipeline to production. •Pushing code to a repository branch and then manually triggering a pipeline that deploys the code to production. •Directly pushing code to a third-party library used by code running in a production system. •Exploiting an auto-merge rule in the continuous integration (CI) system that automatically merges pull requests meeting predefined criteria, thereby pushing unreviewed malicious code. •Exploiting insufficient branch protection rules—for example, excluding specific users or branches to bypass protections and push unreviewed malicious code. •Uploading an artifact to an artifact repository, such as a package or container, embedded as a legitimate artifact generated by the build environment. In this scenario, the absence of controls or checks could allow the deployment pipeline to acquire and deploy the artifact to production. •Directly accessing the production environment and modifying application code or infrastructure (e.g., AWS Lambda function) without further approval or verification. Mitigating this type of threat involves implementing the following measures: 19 •Configuring branch protection rules: Apply branch protection rules to branches hosting code used in production or other sensitive systems. Avoid, where possible, excluding user accounts or branches from branch protection rules. If user accounts are granted permission to push unreviewed code to a repository, ensure these accounts cannot trigger deployment pipelines linked to that repository. •Limiting the use of auto-merge rules: Minimize scenarios where auto-merge rules are used. Carefully review the code of all automerge rules to ensure they cannot be bypassed. Avoid importing third-party code into the auto-merge process. •Preventing unauthorized activation of production nodes: Where applicable, prevent accounts from triggering build and deployment pipelines in production without further approval or review. •Controlling artifact flow in the pipeline: Allow artifacts to pass through the pipeline only if created by a pre-approved CI service account. Block artifacts uploaded by other accounts unless they undergo secondary review and approval. •Detecting and preventing inconsistencies: Detect and correct discrepancies between code running in production and its CI/CD origin. Modify any resource containing deviations to align with expected standards. Dependency Chain Abuse: Risks related to dependency chain abuse refer to an attacker’s ability to exploit vulnerabilities in how development workstations and build environments retrieve code dependencies. This type of abuse can lead to the unintended retrieval and execution of a malicious package locally. Dependencies are often managed via dedicated clients for each programming language, using a combination of internally managed repositories, such as JFrog Artifactory, and language-specific SaaS repositories, such as npm for Node.js, PyPI for Python, and RubyGems for Ruby. When using dependencies, it is crucial to implement controls ensuring the security of the dependency ecosystem, including securing the dependency retrieval process and additional analyses like SCA and DCA once dependencies are integrated into the code. Inadequate configurations can lead to an engineer or, worse, a build system downloading a malicious package instead of the intended one, resulting in its immediate execution via pre-installation scripts configured in the pipeline. The main attack vectors for this threat include: 20 •Dependency confusion: Publishing malicious packages in public repositories with the same name as private packages. •Dependency hijacking: An attacker gaining control of a maintainer’s account to upload malicious versions of widely used packages. •Typosquatting: Creating packages with names similar to popular ones to induce typos. •Brandjacking: Creating malicious packages using naming conventions associated with trusted brands to deceive developers. Through these techniques, malicious code is executed in a pipeline node that downloaded it, typically resulting in credential theft or lateral movement via Command and Control (C2) attack methods. In some cases, the malicious code can reach production environments, also exploiting the previously mentioned threat of insufficient flow control mechanisms. Once in production, this type of malware, known as Trojans, can potentially have devastating impacts, constituting advanced persistent threats that disrupt business continuity or, in the worst cases, necessitate complete reconstruction of enterprise infrastructure. Mitigation measures are numerous and depend on the specific configuration of dependency management clients for different languages, primarily regarding the use of internal proxies and external repositories. That said, all guidelines share common principles: •Where possible, always use local or internal repositories containing pre-verified packages, such as centralized repositories provided by major cloud computing providers (e.g., GitLab Package Registry, Azure Artifacts, AWS CodeArtifact). If it is not possible to use only local repositories and third-party packages must be downloaded from external, mostly public repositories, it is good practice to configure an internal proxy (reverse proxy) between the machine hosting the final source code and the internet, to implement additional compliance checks and caching mechanisms on the proxy server. Applications like Nginx or Squid Proxy are widely used as reverse proxy engines. •Implement additional package verification, such as checksum comparison and signature verification with official packages to ensure package integrity. With npm, use npm audit and npm –verifysignatures; with pip, use pip install –require-hashes; and with Maven, use the maven-gpg-plugin. 21 •Enforce the installation of specific stable and secure versions instead of the latest version, using techniques provided by the framework or directives, such as specifying dependency versions in packagelock.json, requirements.txt, and pom.xml files for npm, pip, and Maven, respectively. •Associate private packages with an organizational scope and configure clients to access them exclusively from the internal registry. These practices are generally implementable via the dependency management clients by modifying their configuration files (e.g., .npmrc, pip.conf, or settings.xml for Maven). Alternatively, tools like Artifactory or Nexus Repository offer more specific functionalities to automate these practices, easy integration with standard tools like Snyk (SCA/DCA), and active monitoring. Poisoned Pipeline Execution (PPE): The risk of manipulated pipeline execution refers to an attacker’s ability, with access to version control systems (SCM) but without access to build environments, to manipulate the build process by injecting malicious code/commands into the pipeline configuration, essentially “poisoning” the pipeline and causing the execution of malicious code as part of the build process. Users with permissions to modify CI (Continuous Integration) configuration files or other files on which the pipeline job depends can alter these files by inserting malicious commands, thereby compromising the CI pipeline execution. Pipelines executing unreviewed code, such as those triggered directly by pull requests or commits to arbitrary repository branches, are more susceptible to PPE. This is because such scenarios inherently include code that has not undergone any review or approval. In an ideal scenario, the CI configuration file resides in the same repository as the source code or in one of its branches, accessible via the version control system (SCM). If an attacker gains access to this repository, they could modify the CI configuration file, for example, by directly uploading the change to an unprotected branch or submitting a pull request (PR) with the change from a branch or fork. Since the CI pipeline execution is triggered by these “push” or “PR” events, and the execution is defined by the commands in the modified CI configuration file, the attacker’s malicious commands are inevitably executed in a build node when the pipeline is triggered. This threat is known as Direct Poisoned Pipeline Execution (D-PPE). Commonly, however, the CI configuration file is not available in the repository or a branch accessible to the attacker. The pipeline might 22 be configured to retrieve the CI configuration file from a protected branch of the same repository. The file could be stored in a separate repository from the source code, with no direct modification capability for users. Alternatively, the CI build configuration might be defined directly within the CI system. Even in these cases, an attacker could still poison the pipeline by injecting malicious code into files referenced by the pipeline configuration, inevitably leading to the execution of malicious commands in pipeline nodes, for example: Makefile: Execution of commands defined in the “Makefile,” used to provide build instructions specific to the language. Referenced scripts: Scripts directly invoked by the pipeline configuration file, stored in the same repository as the source code. Code tests: Testing frameworks operating on application code during the build process rely on dedicated files stored in the same repository as the source code. Attackers who manipulate these files can execute malicious commands during the build. Automated tools: Linters and security scanners used in CI often rely on configuration files in the repository. These configurations often involve loading and executing external code defined in the configuration file itself. In both scenarios, it is assumed that the SCM is protected by authentication mechanisms forcing the attacker to impersonate an authorized entity to inject malicious code into the configuration. However, in some cases, CI pipeline poisoning is accessible even to anonymous attackers on the internet. Public repositories (e.g., open-source projects) often allow any user to contribute, typically via pull requests with suggested code changes. These projects are commonly tested and built automatically using CI solutions, similar to private projects. If the CI pipeline of a public repository executes unreviewed code suggested by anonymous users, this is known as a Public PPE attack or simply 3P. This attack method typically leads to the exfiltration of sensitive information. Malicious code executed on one or more pipeline nodes assumes the same permissions as the dedicated user with whom the CI/CD server accesses resources, which are typically highly privileged. This allows access to credentials or other secrets stored in environment variables, for example, which nodes are rich in as they need to access various external assets like cloud environments, artifact registries, and the SCM. Alternatively, a common practice is to exfiltrate com23 mand history to extract still valid keys or codes; in Linux-based shell languages, this is achievable simply with the history command or by downloading the contents of specific files like .bash history, .zsh history, or .dash history. This sensitive information is then exploited to perform lateral movement within the organization’s infrastructure. Practices to prevent this type of threat primarily involve a remote repository or dedicated branch for the pipeline configuration file and all configuration files used throughout the pipeline, with protection rules. Where the triggered pipeline involves nodes with sensitive information, security checks on configuration files must be performed before execution, and manual approval should be sought if possible. Another highly recommended practice is ensuring the execution of pipeline steps in as isolated nodes as possible, meaning nodes not potentially exposed to secrets and sensitive environments. Secret management is a broad topic beyond the scope of this thesis; in general, in CI/CD contexts with multi-hybrid cloud infrastructures, centralized secret management with cloud-agnostic services and robust client-side encryption with ephemeral keys (single-use) is recommended. Insufficient Pipeline-Based Access Controls (PBAC): Actions resulting from pipeline poisoning almost always involve lateral movement within the CI/CD infrastructure or the entire organization’s infrastructure, exploiting the permissions attributed to the pipeline job. The executor nodes (runners) of pipeline jobs (steps), i.e., the commands specified in the pipeline configuration, perform inherently sensitive activities already extensively mentioned earlier. PBAC includes access controls to numerous elements such as environment variables, sensitive files, other pipelines, other nodes, the virtual machine’s file system, and the hosting host, network filters to internal and external resources. They therefore concern the context in which each pipeline—and each individual job within it (step)—is executed. Given the highly sensitive and critical nature of each pipeline, it is essential to limit each pipeline to the exact set of data and resources necessary for its intended functionality. Ideally, each pipeline and step should be restricted in such a way that, in the event an adversary manages to execute malicious code in the pipeline’s context, the potential damage is minimized. The extent of potential damage caused by malicious code is strongly determined by the granularity and robustness of PBAC in the environment where it is executed. These controls favor role-based access control (RBAC) models. Roles are assigned based on access mechanisms fol24 using the following MITRE ATT&CK TTPs, and MITRE enumerated the corresponding countermeasures in D3FEND Figure 1.11: Execution ATT&CK TTPs and D3FEND countermeasures Persistence: Persistence involves techniques adversaries use to maintain long-term access to systems, even after reboots, credential changes, or other disruptions that might otherwise terminate their access Figure 1.12: Persistence ATT&CK TTPs and D3FEND countermeasures Privilege Escalation: Privilege escalation consists of techniques adversaries use to gain higher-level permissions on a system or network. While adversaries often begin with limited access, they require elevated privileges to achieve their objectives 31 Figure 1.13: Privilege Escalation ATT&CK TTPs and D3FEND countermeasures Defense Evasion: Defense evasion encompasses techniques adversaries use to avoid detection during the compromise process. These techniques include uninstalling or disabling security software, obfuscating or encrypting data and scripts, exploiting trusted processes to mask malicious code. Figure 1.14: Defence Evasion ATT&CK TTPs and D3FEND countermeasures Credential Access: Credential access involves techniques for stealing credentials, such as keys and passwords, which enable adversaries to perform lateral movement. The adoption of cloud infrastructures has made credential access a highly prevalent tactic among adversaries. Figure 1.15: Credential Access ATT&CK TTPs and D3FEND countermeasures 32 Lateral Movement: Lateral movement refers to techniques adversaries use to exploit and control additional remote systems on a network, starting from an already compromised system. Achieving their primary objectives often requires exploring the network to identify and access targets. Figure 1.16: Lateral movement ATT&CK TTPs and D3FEND countermeasures Exfiltration: Exfiltration involves techniques adversaries use to steal data from the network. Once collected, data is often packaged and encoded according to system standards to avoid detection during transfer to adversary-controlled systems Figure 1.17: Exfiltration ATT&CK TTPs and D3FEND countermeasures It is important to specify that only a subset of mitigation tactics has been provided for illustrative purposes. Multiple mitigation strategies can be identified depending on the specific context. Additionally, both the MITRE ATT&CK and D3FEND frameworks are continuously evolving to address emerging threats and techniques. 33 1.2 Hybrid Multi-Cloud Infrastructure Nowadays, cloud computing is a prominent paradigm for enabling new business models and economies to scale their applications based on automatic on-demand provisioning of IT resources (both hardware and software) over a network as metered services, where consumers are billed only for what they consume. Cloud services providers offer a wide spectrum of services, all roughly share the same set of services and each offers an experience better tailored for specific use cases, furthermore several organizations are interested by heterogeneous workload requirements, increasingly restricting regulations and vendor lock-in boundaries. Therefore, especially when it comes to general purpose applications, a multi cloud approach is the best suited for performance, cost, flexibility and reliability enhancement. All widespread CSPs(Azure, AWS, GCP) are well-known for a different specialization: Azure for enterprise integration with the microsoft ecosystem, AWS for scalability and serverless infrastructure implementation and GCP for advanced data analytics, machine learning and container orchestration. These specializations can be leveraged simultaneously, in several synergic real-word use cases. The most common use case is a meticulously designed data analytics pipeline: GCP’s BigQuery for real-time data processing, AWS’s S3 for scalable data storage, and Azure Synapse Analytics for seamless integration with its Microsoft-powered enterprise applications. Even though cloud computing come with massive benefits, a pure cloud based infrastructure presents some drawbacks that must not be overlooked in the design or migration phase. The foremost concern is a single data point of failure, full reliance to a single cloud provider for data retention, following a impairment can lead to the exfiltration of all the sensitive data and intellectual property, before being eventually encrypted or even worse, wiped. A partial remediation to this would be the replication of such sets of sensitive data across multiple locations or even providers with automated workflows, but this isn’t always possible due to hosting country data retention policies. Similarly, an infrastructure with full cloud reliance comes with low fault-tolerance, an interruption of a core component of one provider can impact directly the business continuity, leaving the consumer with no choice than waiting for service recovery. Next, adhering to stringent regulatory requirements (e.g., GDPR, HIPAA) might result demanding with heterogenous data and wide product distribution. Such challenges are just some of the peculiarities of cloud adoption, depending on the context several more can be identified. Hence, an infrastructure solution distinguished by multiple providers and distribution of resources between on-premises private and 34 public domains represents a compelling solution, also known as multi-hybrid cloud infrastructure. This model embodies adaptability, resilience and optimization as main features and allows consumers to navigate the complexities of always evolving digital ecosystem. 1.2.1 Cloud Security Challenges Nevertheless, enterprises consider security as the #1 inhibitor to cloud adoptions (Fortinet 2024 Cloud Security Report). Especially SMEs are reluctant to the adoption of cloud computing due to the difficulty in evaluating the trade-off between cloud benefits and the additional security risks and privacy issues that may arise. Most concerns are related to data protection, regulations compliance and other issues related to the lack of know-how regarding controls and governance processes in the outsourcing of data and applications: data confidentiality, trust on aggregators, control over data and/or code location, and resource assignment in multi-tenancy. The most challenging applications in heterogeneous cloud ecosystems are those that are able to maximise the benefits of the combination of the cloud resources in use. Such applications have a Microservice architecture with components deployed across different resources and each interested by a single CI/CD pipeline, these are also known as multi-cloud applications. Hence, for the context of this paper, a multi-cloud application is conceived as a distributed application over heterogeneous cloud resources whose components are deployed in different cloud service providers and still they all work in an integrated way and transparently for the end-user. Multi-cloud application solutions have to deal with the security of the individual components as well as with the overall application security including the communications and the data flow between the components. Even if each of the cloud service providers offered its own security controls, the multi-cloud application has to ensure an integrated security across the whole composition. Therefore, the overall security depends on the security properties of the application components, which in turn depend on the security properties offered by the cloud resources they exploit. For instance, the database component in charge of storing sensitive data cannot ensure a high confidentiality if the cloud storage resource in which it is deployed does not use strong encryption algorithms. Consequently, the whole multi-cloud application may be not sufficiently safe. The threats landscape is wide and therefore cloud security is itself a broad term, though some major areas of concerns can be addressed in IAM, data at rest and in transit, ingress and egress traffic, architecture framework and threat management. 35 Federated IAM Identity and Access Management is the beating heart of a strategy aimed to safeguard a cloud based infrastructure. Each cloud platform often has its own access control methods, user directories, and authentication protocols. This lack of native integration features and centralized control makes it difficult for organizations to enforce consistent security policies and ensure uniform access management across multiple clouds. It may lead to security flaws, ineffective access rights enforcement, and challenges in auditing and observing access events. Uniformed and centralized management is obtained with a federated paradigm, where all domains have multiple service providers whose access is granted with the domain Identity Provider, and a trust relationship is established between these different IdPs. A trust relationship involves establishing shared traffic layer protocol(mTLS), users data structures, authorization policies, authentication mechanism(OAuth, OIDC), and more details based on the context. A strong federated approach enables a seamless authentication and authorization to access resources across different domains, privacy and compliance with governments regulations. Federated access control relies on the concepts of single sign-on (SSO), security tokens, role-based access control(RBAC), attribute-based access control (ABAC), and the federated protocols like the security assertion markup language (SAML), OAuth, and OpenID Connect (OIDC) to provide its functionalities. An hybrid multi cloud infrastructure is highly synergic with Data Loss Prevention strategies adoption. Data loss prevention (DLP) is a strategy to ensure that sensitive or confidential data are not lost, stolen, or accidentally leaked. It can control access to data, monitor for unauthorized access, and encrypt data at rest or in transit. DLP can also refer to the software and hardware used to implement these security controls. IAM is a process for managing who has access to which data and resources in an organization. These security controls include policies and procedures for authenticating and authorizing users to access data and resources, thereby bolstering the DLP strategy. DLP and IAM are thus interlinked and are important tools for protecting sensitive data. DLP can help prevent loss by encrypting data and controlling access. IAM can help prevent unauthorized data access by authenticating and authorizing users. Data Protection To ensure robust cloud data security, three critical aspects must be addressed: data at rest, data in transit, and intrinsically encryption. Data at rest, stored on physical storage devices like hard drives or SSDs, can be protected through encryption tools such as BitLocker for Windows and FileVault for macOS. These tools employ the Advanced Encryption Standard 36 (AES) to scramble data, ensuring only authorized users with the appropriate keys can access it. Both tools offer flexibility to encrypt entire drives, startup disks, or specific partitions, enabling enhanced security against unauthorized access even if a device is physically compromised. A recommended practice for critical environments is to use separate partitions or isolated storage, ensuring sensitive data remains segregated and reducing the risk of crosscontamination in case of breaches. Data in transit, moving between endpoints such as servers and user devices, is safeguarded using encryption protocols. Communication can leverage secure transport layers like SSL/TLS, forming the backbone of many encrypted tunnels. Virtual Private Networks (VPNs) add an additional layer of security by creating isolated communication channels. Modern VPN implementations, such as OpenVPN, rely on the OpenSSL library to ensure that traffic adheres to robust SSL/TLS standards. This combination not only prevents eavesdropping but also ensures that even if data packets are intercepted, their contents remain inaccessible without the necessary decryption keys. Beyond conventional methods, homomorphic encryption introduces a revolutionary approach to data security. Unlike traditional encryption, which requires data to be decrypted before processing, homomorphic encryption allows computations directly on encrypted data. This ensures data remains secure even during analysis, significantly enhancing privacy in scenarios involving sensitive information like healthcare or financial data. Its ability to maintain encryption throughout the data lifecycle addresses both performance concerns and the risk of exposure during processing. To manage encryption effectively, cloud providers offer robust key management services (KMS), such as AWS KMS, Azure Key Vault, and Google Cloud KMS. These services handle the secure storage, distribution, and rotation of cryptographic keys, simplifying encryption for both data at rest and in transit. Regular key rotation further mitigates risks by limiting the exposure window for compromised keys. Network Traffic Control Besides designing the perimeter in micro-segments, egress and ingress network traffic filtering is fundamental, both within each segment and in the whole infrastructure. Egress traffic refers to data that leave the cloud environment, whereas ingress traffic is data that enter the cloud. One of the most serious risks with egress traffic is data leakage, which can occur if data are not properly encrypted or there are gaps in the security barrier. To tackle this threat, the employment of a robust security perimeter barrier, with multiple layers of security, is necessary. Ingress traffic can also be a security concern, allowing mali37 cious actors to access the cloud environment. To prevent this, multi-cloud providers must have strong firewall rules, coordinated with intrusion detection and prevention systems. A robust firewall, besides applying a dynamic filtering with a context-aware approach, it performs a deep packet inspection enabling the analysis of the application layer of packets, detecting also malicious payloads even if encrypted by checking for known encrypted exploits, such firewall solutions go by the name of Next Generation Firewalls(NGFW). Architecture Model Several architectural patterns can be outlined in hybrid multi-cloud systems that allow to address complexities, leverage all cloud possibilities and ensure safety, across different layers. In terms of policies management, the recommendation is a federated governance pattern where there is an highly protected centralized governance portal for system-wide or cross environments operations, while allowing autonomy in each environment for local decisions. For cross-provider communication the suggested paradigm is a service mesh for a secure and reliable management, especially in microservices architectures. Similarly create a unified data layer that abstracts and integrates data storage and access across on-premises and cloud environments. The operational model to handle high work loads should apply a cloud bursting, that employs an execution on premises during normal workloads and burst into fallback cloud environments during peak demands, through a properly configured load balancer. Even thought not directly correlated to architecture, to ensure application portability and seamless orchestration including version alignment, workload mobility and disaster recovery, employ as much as possible containerization (e.g., Docker) and related automated orchestration services(e.g Kubernetes). Asynchronous communications is tough argument to handle and in event-driven systems, the employment of message brokers like Kafka or AWS EverBridge can reduce the overhead and reduce consumption. Finally in order to guarantee business resilience to cyber threats, cross-cloud redundancy with back-ups is widely applied, such backups are constantly updated with properly created workflows to ensure a close to none discrepancy with production data. Threat Management Threat management in hybrid multi-cloud systems relies heavily on effective event logging and active countermeasures to ensure visibility, detect anomalies, and mitigate risks. The fragmented nature of these environments presents challenges, including dispersed logs, high data volumes, and compliance requirements. Centralized log aggregation using tools like Splunk, Elastic Stack, or cloud-native services (e.g., AWS CloudWatch, Azure Mon38 itor) is essential to consolidate and correlate data across platforms. Granular and highly detailed information logging must be employed, using native logging tools offered by CSPs like AWS CloudWatch, Azure Monitor. Aggregating their data, previously parsed correctly by a custom middleware given the likely different formats(e.g. JSON, XML, YAML), in a central environment enables a correlated analysis. A distributed and well orchestrated SIEM architecture employes a thorough threat detection and if properly integrated with SOAR solutions, active countermeasure practices. To bolster security, organizations increasingly adopt Security Orchestration, Automation, and Response (SOAR) solutions. SOAR platforms like Palo Alto Cortex XSOAR, Splunk Phantom, or IBM Resilient automate threat detection and response workflows. These solutions actively analyze logs, correlate data, and trigger automated playbooks to respond to incidents in real time. For example, a SOAR system can isolate compromised workloads, block malicious IPs, or enforce stricter access controls during a suspected breach, all without human intervention. Best practices include log normalization, real-time monitoring with SIEM/XDR tools (e.g., Microsoft Sentinel, IBM QRadar), and integrating external threat intelligence for identifying indicators of compromise. Retention policies and scalable storage solutions like AWS S3 ensure compliance and forensic readiness 1.3 Data Crucial Role As pointed out multiple times in former chapters, testing is a critical cornerstone in DevSecOps processes. Automated quality validation and security lay the foundation of a CI/CD pipeline, therefore an exhaustive, seamless and accurate testing is the core joint of development, security and operation integration. Besides, its role transcends mere flaw detection, whether of security or efficiency, it ensures the delivery of robust, scalable, and secure applications in increasingly complex systems such as hybrid multi-cloud ones. Accounting this, in a well designed software delivery life cycle there are multiple testing phases, with heterogeneous goals. It can figure as a continuous testing paradigm, that other than being automated it guarantees full coverage of different nature(code, requirement, risk, environment) and especially a comprehensive validation strategy that includes static, dynamic, functional, non-functional, regressive, exploratory and random methods. This escalated paradigm involves the introduction of several challenges that, as in every system, should be tackled as early as possible in the design phase. The common denominator of all these challenges is testing data, inevitably representing the main catalyst for testing success. 39 Data Bottleneck: traditional approaches to test data provisioning often represent a significant bottleneck, impeding the pace of software delivery. This challenge is particularly pronounced when provisioning involves extraction, copying, and movement from production systems to test environments, that require manual intervention. Inefficient data provisioning directly impacts the speed of testing, leading to longer development cycles and slower time-to-market. Ensuring data completeness while maintaining its structure and interdependencies significantly increases the time and effort required for provisioning. Overall slow data delivery undermines DevOps core principles of agility and rapid iteration. Innovated privacy trade-off: Non-production environments are particularly vulnerable to data breaches due to weaker security controls compared to production systems. The presence of sensitive information, including personally identifiable information (PII), magnifies these risks. Manual and resource-intensive processes for identifying and protecting sensitive data—such as data masking or anonymization—further exacerbate delays and increase the likelihood of errors or not faithful data. Additionally, regulatory legislations like GDPR or HIPAA impose stringent requirements on how organizations handle sensitive data, regardless of whether it resides in production or non-production environments. Failure to meet these requirements can result in significant financial and reputational consequences. Synchronized Data Delivery: in current DevOps workflows, cloud environments and virtual machines can now be provisioned in minutes, thanks to advancements in infrastructure automation. However, the delivery of corresponding test data often lags behind. Data delivery workflows remain heavily reliant on manual tasks, including data extraction, anonymization, and transfer. - Heterogeneous Data Landscape : Enterprises often maintain a mix of legacy and modern systems, including relational databases, NoSQL stores, and cloud-native data solutions. The coexistence of disparate architectures creates challenges in extracting, normalizing, and provisioning data for DevOps purposes. Moreover, technologies bound siloed environments worsen the integration of data across systems. These siloes lead to isolated data repositories, hindering efforts to create unified and consistent test environments Data Fidelity: The use of subsetted data, often employed to reduce provisioning time, typically lacks the diversity and coverage necessary to 40 erage ransom of 2.73$million, of which 57% attempts to access back-ups were successful. Even though, the sample is small, central/federated governments reported the highest attack hit rate of 69%, with other notable rates being in healthcare, both lower and higher education; with the latter having also one of the highest rate of back-up compromission success with 71%. Back-ups compromission is noteworthy since it results in on average doubling of the ransom demand, correlated by a higher probability of ransom payment. Additionally, is severe to mentions some medium-big organizations that filed for bankruptcy or insolvency following a ransomware attack, like the 150-year long thriving KNP Logistic Group in 2023, one of the UK’s largest privately owned logistics firms, that lead to the loss of 730 jobs[25]. One of the most interesting insight of the previously cited report, is that 93% of ransomware tied malwares are Windows based executables, this is largely due to the fact that most enterprise and personal devices host Windows OS and one of the most widespread storage technology across enterprise contexts is Microsoft’s Active Directory. Moreover, often these technologies have legacy versions, with several vulnerabilities unpatched. Therefore, heterogeneous attack vectors mentioned formerly can be leveraged to exploit a wide range of vulnerabilities, ensuring that no single sector or organization type is immune. A core ransomware concept is the targeting of an asset that fuel organizations of all kinds, data. Hence, in the target recognition phase attackers evaluate if the potentially in scope data is critical for the target business operations, or highly sensitive whether they are customer records, intellectual property or financial data. Given critically valuable data, the analysis proceed with the value estimation that paired with an evaluation of the target ability to afford the ransom lead to create a proper ransom proposal. Another core ransomware concept is its universal operational impact, as it affect three businesses operations that mark all organizations : data encryption, financial strain, reputation damage. These characteristics of the ransomware threat target landscape and intrinsically of the attack itself, make it a threat that transcend from industry sector, therefore is also known as cross-business threat. An operational approach that it’s taking over the ransomware widespread is Ransomware as a Service(RaaS). From a business outlook, RaaS is a subscription-based business model that enables hackers to use pre-developed ransomware kind exploitation code, phishing tools, as well as all payment gateways. Much like legitimate Software-as-a-Service offerings, this service provides everything needed to launch and manage ransomware attacks without requiring extensive technical expertise. In essence, it allows cybercriminals to rent the malware infrastructure created by experienced developers, who typically receive a cut of any successful ransoms collected. RaaS recog47 nized an extraordinary success in the recent years, to the extent that nowadays service providers scaled their offering with fully self-managed platforms that follow the serverless paradigm offered through a web portal, where consumers(or affiliates according to the business model) can start tailored phishing campaigns, see the status of infections, total payments, total files encrypted and other information about their targets. An affiliate can simply log into the RaaS portal, create an account, pay with Bitcoin, enters details on the type of malware and execute the attack. Subscribers may have access to support, communities, documentation, feature updates, and other benefits identical to those received by subscribers to legitimate SaaS products. Proliferation of this service-oriented approach has profound implications for the cybersecurity landscape, as it lowers barriers to entry and enables even those without sophisticated technical know-how to launch ransom attacks. Mainly due to this shit in the approach ransomware has rapidly evolved from a mere cybersecurity threat into a full-fledged industry. Several types of ransomware attacks can be classified according to their attack kill chain and intended final impact. The following is a basic comprehensive list of macro ransomware types: •File Encrypting : Ryuk •Locker : LockBit •Double Extortion : Maze •Wiper : figure as a ransomware but disrupt business operations by forcing deletion of data from disks Preventing a ransomware attack involves applying defense mechanisms to detect and inhibit, briefly mentioned previously, attack techniques that fall in Initial Access tactic. Some have already been covered with CI/CD threats, but when it comes to ransomwares some attack techniques are more accentuated, which means that they appear in IOCs of several known ransomware malwares, than others, therefore likewise determined defense best practices must be enforced strongly. Prevention best practices are grouped by common initial access vectors of ransomware and data extortion actors : Internet-Facing Vulnerabilities and Misconfigurations: besides standard best practices like regularly patching softwares and underlying OS, conduct routine vulnerability scans and implement a robust logging of internet-facing services, further practices regarding mainly exploited protocols in ransomware contexts(RDP, SSH) are : 48 Limit Remote Desktop Protocol (RDP) Exposure: avoid exposing RDP on the web. If required, enforce public key based authentication and compensate with controls like multi-factor authentication (MFA) and account lockouts. Regularly audit and close unused RDP ports (TCP 3389) Harden SMB protocols: Update SMB to his minimum secure standard version(3.1+), and enable message encryption or signing. Block external SMB access at firewalls (e.g., TCP port 445) and limit internal traffic to essential systems. The best configuration requires the use of SMB over QUIC, which employs always Transport Layer Security (TLS) 1.3 encryption and use of certificate authentication to encapsulate all SMB traffic inside a VPNlike transport. Additionally, always require Kerberos-based IPsec handshake for lateral SMB communications(ZTA)[16]. Compromised Credentials: strictly related to the management of identities access, as well as credentials and secrets : Implement multi-factor authentication (MFA): Deploy phishingresistant MFA for critical services like email, VPNs, and sensitive accounts , and consider password-less MFA methods(e.g., fingerprints, facial recognition, cryptographic keys). Credential management and monitoring: Subscribe to credential monitoring services to detect compromised credentials on the dark web. Where possible, use password managers, securing them with MFA and all available protections. Phishing: involves sending messages through main communication medias (email, sms, social medias) with malicious payload to gain access to the victim’s system. Often, employe social engineering tactics, with a context-aware approach, in such cases it’s also referred as spearphishing : Flagging and filtering: application of email gateway filtering that block emails with external domain, suspicious IPs and overall known indicators of compromise(IOCs). Filtering is also applied at attachment level, blocking emails attachments with common malware file type; in this regard, attention should be brought to password-protected archives that often evade widespread email filters. Likewise, for Microsoft Office files disabling of macro scripts should be enforced. 49 Domain-based email verification: implement Domain-based Message Authentication, Reporting, and Conformance (DMARC) policies to minimize spoofed or modified emails. DMARC builds on the widely deployed Sender Policy Framework (SPF) and Domain Keys Identified Mail (DKIM) protocols, adding a reporting function that allows senders and receivers to improve and monitor protection of the domain from fraudulent email[11] Once a malicious actor got a foothold in the target system, compromising one or more of the previously mentioned prevention tactics; detection, mitigation and response practices must be employed in order to minimize the magnitude of the impact and therefore guarantee business resilience. Such practices must be implemented through industry standard tools, whose priority is continuous update and integration to the latest IOCs and attack patterns. The core practice are controls, requests of all kinds, related logs, resources usage, local command executions. A control system can reach high levels of complexity, that often force attackers to discover cutting-edge vulnerabilities, also know as zero-day, to achieve their goal. Nevertheless, ransomware, for the nature of the attack goal, is distinguished by several different paths of attack execution, very few of the current control infrastructure available in the market, if configured thoroughly and continuously reviewed, mitigate ransomware attacks promptly. As a matter of fact, previous ransomware attacks, that fall in the categories previously mentioned, besides being resilient to intrusion detection(defense evasion) have as a key feature a high speed of encryption, eluding automated data integrity controls and mass modification threshold. Such ransomware defenses, which can be applied widely to other threats, require a combination of practices, technical tools, and a focus on speed and frequency to ensure an active response before significant damage occurs. This involves a multi-layered, behavioural based defense approach, with a variety of tools synchronised together among which IDS, XDR, SIEM and SOAR play a crucial role. When it comes to mitigation after a successful foothold in the system perimeter, high attention must be placed on IDS controls. First of all, two macro levels of intrusion detection can be distinguished, network-based and host-based, with the former focusing on unusual network traffic indicative of ransomware(C2 servers, exfiltration, spike in suspicious type of traffic) and the latter monitoring endpoint behaviours (mass file modification, unrecognized edit patterns, known malware file hashes). Focusing on endpoint behaviours, three main challenges, also mentioned briefly, encounter IDS : •Control robustness : integrity controls are still bound to traditional approaches, often limited to signature and hash or deviation from nor50 mal execution patterns, which modern ransomwares can evade respectively by replicating file-system signature mechanism, manipulating data completeness and masking itself as a legitimate administrative process. Lack of granular context tracking is another major factor that contribute to this weakness, without considering data modification trends scheduled legitimate jobs often get blocked. •Control speed : most modern ransomware employ cutting-edge encryption techniques, also known as zero-day, that encrypt whole file systems in minutes, data integrity monitoring mechanisms isn’t often equally fast. This is simply due to the nature of operations and fundamental differences in computational efficiency. Encryption can be reduced to a one computational cycle operation, leveraging on-the-fly encryption as it is directly written and modern encryption algorithms such a Advanced Encryption Standard with hardware acceleration(AES-NI) designed for gigabit-per-second throughput. Conversely, integrity control require at least three computation cycles : reading expected, reading effective and comparison; and often all the data in scope must be verified before eventually triggering response mechanisms. Of course, it may looks obvious that this encryption outpacing concern can be resolved by comparing the hash of data, but this comes with several drawbacks and complexities that will be illustrated later. On the other hand, some ransomwares employ a slow and steady encryption, which is below data modification threshold, therefore looking harmless. •Control frequency : data integrity controls are often scheduled with a frequency directly correlated with data sensibility, size and resources availability, and usually spans from few days to minutes. From a security point of view, the shorter the control cycle the better it is, but highly frequent controls come with few drawbacks such as an overhead to other processes using the controlled data and high resource consumption. The perfect trade-off would be an event-driven control, which require a real-time monitoring of data. Though, as cited multiple times a correct malicious modification detection can be highly complex Once a ransomware attack has been detected, further tools are used to perform an attack response in order to reduce attack impact. This involves the integration of IDS with SOAR. In most scenarios, SOAR playbooks include : 51 •Isolation : the first priority is to contain the infection to prevent lateral movement across the network. By applying further VLAN segmentation, disabling network interfaces, updating firewalls, especially WAF to block attacker controlled domains and IPs. If possible, would be even better to switch them off before acquiring snapshots. •Terminate malicious processes and remove all its artifacts : all malware related processes, associated files, registry keys, scheduled tasks are terminated or removed. Usually, before a snapshot of volatile memory is acquired to extract IOCs. •Disable compromised accounts : compromised accounts leveraged for initial access or lateral movement are disabled and completely reset. •Mount clean back-up in production : pre-verified, secure, and malwarefree golden image is mounted in production environment to replace compromised system. Likewise, for database technologies clean backups are deployed in production environments. Besides these automated operations, following operations to reduce security gaps are : threat intelligence, forensic analysis, notification of all stakeholders and with results edit of security posture of the system : which can include new perimeter segmentation, additional enhanced firewall rules, accounts permissions downgrades. The aforementioned challenges in ransomware mitigation highlight the need for a solution that emphasizes efficiency, granularity, accuracy and guarantees an high degree of operational resilience. The solution presented in the next chapters seek to satisfy these requirements by leverage the features of a PDI(Delphix Data Platform) not in its native settings. The proposed approach aims to execute a control on relation data structures with single cell granularity, on virtualised version of data, allowing to witness mismatches, data versioning and finally trigger an attack response that notifies all stakeholders and following a manual confirmation loads the latest clean version in production. 1.4 Delphix - DevOps Data Platform As defined by Perforce, the organisation behind Delphix, Delphix is a data platform that enables organizations to manage, secure, and deliver data efficiently across hybrid and multi-cloud environments. It provides automated data virtualization, masking, and compliance capabilities to accelerate application development, cloud migrations, and AI/ML initiatives while ensuring 52 data security and regulatory compliance. By integrating Continuous Data, Continuous Compliance, and centralized orchestration, Delphix allows teams to rapidly provision, refresh, and version data, reducing bottlenecks in software delivery and enhancing data governance. Figure 1.18: Delphix data platform overview [9] It’s fundamental to assess that it is offered via SaaS, hence Delphix engines are delivered and maintained as a closed virtual software appliance. Like all virtual appliances, these engines are a tightly integrated combination of a special-purpose operating system and business logic; their usage directions specify that the Continuous Data Engine can be solely configured for data virtualization, while the Continuous Compliance Engine for data masking. Additionally, obviously conforming to the SaaS paradigm, consumers do not have any administrative permission on the underlying operating system and software dependencies, including installing custom software or perform any kind of analysis. Delphix provide a level of abstraction that allows the creation of a purely data plane infrastructure. This data plane infrastructure is highly flexible, scalable, observable and above all efficient; enabling a data management completely independent of underneath technologies, data entities and architectural context. Till a certain extent, Delphix could be exposed as a previously illustrated Programmable Data Infrastructure(PDI), as it employs the paradigm concept of abstracting or facilitate the complexities of integration, processing, validation, and delivery of data. Nevertheless, there is a noteworthy gap between the abstraction provided by the Programmable Data Infrastructure depicted before and Delphix, actually they are symmetric. A PDI regards the low level, resolving directly around network devices(i.e. switches, hubs, routers) implementing a custom logic in their technology stack, underpinned by a specifically crafted DSL; whereas Delphix data platform operate an abstraction toward an high level, its software components are designed 53 and developed to be extremely technology agnostics, transcend from data structures and allow an high degree of customisation. Figure 1.19: Delphix infrastructure agnosticism [9] Figure 1.20: Delphix technology agnosticism [9] Furthermore, Delphix engines are built with a strong emphasis on API-first design, which allows full automation and integration with modern Infrastructureas-Code(IaC) based workflows. Its highly widespread use case to date is the automation of test environments provisioning. Further main use-case are cloud data migration and disaster recovery, all of these usage scenario are combined in this control solution. Another key application of Delphix data platform is to secure of data, both in terms of privacy and compliance, as well as protection from exfiltrations. Once data management is abstracted to a data plane infrastructure, operations on it are significantly easier. Delphix’s data privacy and compliance capabilities abstract the core data management paradigm functionalities such as data governance, data security, data protection, data integrity, and data optimization. These functions together form a comprehensive strategy for ensuring that data is protected, compliant, and accessible while minimising risk, optimising usage, and adhering to regulatory standards. Through data masking, data subsetting, and data redaction, Delphix ensures that production data is obfuscated or minimized in non-production environments, preserving privacy without compromising data utility for testing and development. The platform also offers audit trails and access controls to track and manage data usage, ensuring accountability and transparency. Additionally, Delphix supports compliance reporting, enabling organisations to demonstrate alignment with regulatory requirements like GDPR, HIPAA, and CCPA, ultimately ensuring secure, compliant, and efficient data management practices . Hence, features offered by the Delphix DevOps Data Platform can be aggre54 gated within 4 main services, each of which plays a very important part in delivering fresh secure data to anybody that needs it : Virtualize: Delphix compresses the data that it gathers, often to one-third or more of the original size. From that compressed data footprint, Delphix virtualizes the data and allows operators to create lightweight, virtual data copies. Virtual copies are fully readable/writable and independent. They can be spun up or torn down in just minutes. And they take up a fraction of the storage space of physical copies – 10 virtual copies can fit into the space of one physical copy. Identify and secure: The Delphix platform continuously protects sensitive information with integrated data masking. Masking secures confidential data – names, email addresses, patient records, SSNs – by replacing sensitive values with fictitious, yet realistic equivalents. Delphix automatically identifies sensitive values and then applies custom or predefined masking algorithms. By seamlessly integrating data masking and provisioning into a single platform, Delphix ensures that secure data delivery is effortless and repeatable. Manage: Data operators can now quickly provision secure data copies – in minutes – to users in their target environments. The Delphix platform serves as a single point of control to manage those copies. Data operators maintain full control and visibility into downstream environments. They can easily audit, monitor, and report against access and usage. Self-service: Provides developers, testers, analysts, data scientists, or other users with controls to manipulate data at will. Users can refresh data to reflect the latest state of production, rewind environments to a prior point in time, bookmark data copies for later use, branch data copies to work across multiple releases, or easily share data with other users. With that being asserted, two core components components in the software suite can be distinguished and contribute more broadly to provide the aforementioned services. They are interconnected by design and therefore constantly collaborate : Continuous Data Engine and Continuous Compliance Engine 1.4.1 Continuous Data Engine : Technical Overview The Delphix Continuous Data Engine is a sophisticated programmable data infrastructure designed to integrate with diverse data sources, including relational database management systems (RDBMSs), no-SQL, JSON, YAML, 55 XML files. The platform leverages advanced data ingestion and virtualisation techniques to optimise storage, enhance compliance, and facilitate efficient data provisioning. The Continuous Data engine connects to your source database(s) to take in a full readable, writable copy of your data. When it does this, the data is compressed (usually to about 1/3 the size). After the initial data ingestion, the Delphix Continuous Data Engine stays connected to your source environment. Each time the data from your source environment changes, the Continuous Data engine ingests only the new data blocks rather than an entire 1:1 copy. This collection of source data blocks is referred to as a dSource. The Delphix Continuous Data Engine maintains synchronization either by using product native replication such as Dataguard, SQL Server log shipping, and Postgres replication, or via product native incremental backups. User defined policies dictate the frequency of synchronization. Delphix maintains a Timeflow of the source database; a record of snapshots and log changes. Virtual copies of your database (VDBs) can then be provisioned from dSource (or VDB) snapshots, from any point in time in a timeflow. VDBs are read-write virtual databases, and changes made to the VDB are written to new, compressed blocks in Delphix storage. The data within the VDB can be refreshed as needed, to any point in time along its source’s timeline. Figure 1.21: continuous data engine web dashboard Data Ingestion and Virtualisation The data ingestion process within the Delphix Continuous Data Engine is 56 To comprehend thoroughly sensitive data discovery process, some objects and concepts the masking mechanism is built upon must be clarified : Rule Set: a collection of database tables or flat files within a specific data source that users designate for profiling, masking, or tokenization operations. By organizing related data structures into rule sets, the Masking Engine streamlines the process of identifying and securing sensitive information across various environments. Each rule set is associated with a specific environment and utilizes a connector to define the connection parameters to the data source. This association ensures that masking operations are accurately targeted and executed within the intended context. Figure 1.26: rule sets web interface Profile Set: defines the logic that will be used to determine which columns or fields in the rule set contain sensitive information. A profile set contains a set of classifiers, that define the recognition logic for the ASDD profiler. As each classifier is associated with a domain, the composition of the profile set determines which types of sensitive data may be detected by a profiling job using a particular profile set. 63 Figure 1.27: profile sets web interface Domain: represents a particular type of sensitive information, such as first name or tax ID number. Based on the detection logic in the profile set, a profile job may assign a domain to a particular field or column in the rule set; when this occurs, the default masking algorithm defined for that domain will also be assigned. The domain mechanism helps to ensure that the same masking algorithm is applied consistently across rule sets whenever a particular type of sensitive data is discovered. Figure 1.28: domains web interface Classifier: defines a specific piece of logic for recognizing sensitive data. Classifiers may only be used with the ASDD profiler. Classifiers use a framework and instance model, similar to algorithms. A framework represents a particular software module for detecting sensitive infor64 mation, while an instance provides the configuration for a framework and associates it with a particular domain. The following classifier frameworks are available by default : •PATH : examines the path to the data in question and applies regular expression and/or exact match logic to match domains. For databases, the path includes the table and column name. •TYPE : uses the data type and length of a field or column to detect possible domain matches. Supported types are String, Number, Date and Binary. •REGEX : matches the data itself using regular expressions to match or reject domains. •LIST : checks whether data values are present in a list of value to match or reject domains. Figure 1.29: classifiers web interface Hence, fundamentally the sensitive data discovery process involves the creation of a rule set, then the rule set is profiled by a specifically crafted profiling job. This profiling job examines the metadata, such as column names and types, and potentially the data itself, based on the configured classifier, to determine which columns or fields contain sensitive information. Upon determining that a data item is sensitive, the profiler assigns the matching domain and associated masking algorithm to the column or field. Being fully automated, this process is known as Automated Sensitive Data Discovery (ASDD) and requires a specific profiler. Securing Data 65 Data masking is a comprehensive solution designed to safeguard sensitive information across diverse data environments. By replacing original data with fictitious yet realistic equivalents, it ensures data privacy while maintaining the functional integrity necessary for development, testing, and analytical processes. Masking is characterised by two methods and two architectural modality. Therefore, four different masking implementation can be distinguished. The two secure methods Delphix currently supports are masking (anonymization) and tokenization (pseudonymization), depending on your need : Masking: masking secure data by replacing values with realistic yet fictitious data. Seven out-of-the-box algorithm frameworks are provided to ease the definition of tailored precise algorithms, which can mask everything from names to social security numbers to images and plain, structured text. Figure 1.30: masking example [8] Tokenisation: contrarily, tokenisation employs reversible algorithms so that data can be returned to its original state. Tokenisation is a form of encryption where the actual original data is converted into tokens that do not convey any meaning. Figure 1.31: tokenisation example [8] In addition, as introduced, two flow of data masking can be distinguished : In-Place masking: sensitive data is secured by overwriting it directly within the original data source. Involves reading the data from its current location, applying the necessary masking algorithms within the engine, 66 and then updating the same source with the masked data. As a result, the original sensitive information is overwritten, ensuring that the data remains protected within its initial environment. This approach is particularly beneficial when it’s essential to maintain the existing data infrastructure without creating additional copies or moving data between environments. By transforming only the columns identified as containing sensitive information, in-place masking preserves the integrity of the remaining data, ensuring that non-sensitive information remains unaltered On-the-Fly masking: this process instead reads sensitive data from a source environment, applies masking algorithms to obfuscate the data within the Delphix engine, and then writes the masked data to a separate target environment. This approach ensures that the original data remains unaltered, while the target environment receives the anonymized data, effectively maintaining data security and compliance across environments. This ETL-like (Extract, Transform, Load) workflow ensures that sensitive information is protected before it reaches less secure environments. Figure 1.32: masking methods [8] They key component of data masking is indeed the algorithm. In the context of data masking an algorithm refers to a defined method or procedure applied to data fields or columns to obfuscate sensitive information, ensuring that the original data cannot be reconstructed or retrieved. Algorithms are generally implemented on the structure of algorithm frameworks : foundational structures that define the general approach to masking. Delphix provides various algorithm frameworks, each suited to different data types and masking requirements. For instance, the Secure Lookup framework maps original data to masked values using a predefined list, ensuring consistent and irreversible masking. Key features of masking algorithms are : 67 •Irreversibility: Delphix masking algorithms are designed to be one-way functions, ensuring that masked data cannot be reverted to its original form. This is crucial for maintaining data security and compliance. •Consistency: Certain algorithms, such as Secure Lookup, ensure that the same input value is consistently masked to the same output value across all instances. This feature is essential for preserving referential integrity within and across datasets. •Customization: Users can create new algorithms or modify existing ones to meet specific requirements. This flexibility allows organizations to tailor masking processes to their unique data landscapes and compliance obligations. •Multi-Column Support: Delphix supports multi-column algorithms, enabling coordinated masking across related data fields. This ensures that complex data relationships are maintained post-masking. 68 Chapter 2 Project Enterprise Recovery Vault(ERV) Within the broader context of business continuity, the client has identified the implementation of an Enterprise Recovery Vault (ERV) as a critical requirement for a set of databases classified as mission-critical. These databases are simultaneously exposed across QA, test, and analytics environments, making them highly susceptible to elevated risk factors such as data corruption, unauthorized modifications, and operational disruptions. Enterprise Recovery Vault solution, following the speed principle previously highlighted, must guarantee a data recovery strategy that minimise data loss and downtime. Which translate in the reduction of both Recovery Point Objective(RPO) and Recovery Time Objective(RTO). The functional and business requirements outlined a RTO and RPO respectively of 30 and 10 minutes, given that the application is not critical for the organization business but the loss of new data could lead to significant damage to the business. As mentioned briefly, a peculiar characteristic of this data is that its masked version is brought to three relieability, security and performance testing containerised environments within the wider context of multiple CI/CD pipelines, where single services of a core application are dynamically tested. Data provisioning and tests execution is orchestrated by ArgoCD which pulls subsets of masked data from a virtual database and delivers it to tailored structures in target environments on a time-based schedule and trigger tests once provisioning is completed. The provisioning mechanism and test execution is orchestrated by ArgoCD, leveraging Delphix as engine to fetch data from virtual databases and Kubernetes as deployment infrastructure, it is based on multiple parameters to guarantee maximum reliability, but goes beyond the scope of this work. In order to comprehend the risk posed to data confidentiality and integrity, it 69 is fundamental to understand the perimeter segmentation and the authentication methods used by all the infrastructure components involved. Delphix, besides the execution of a password-based authentication at the production database where a copy of data is retrieved, authenticates with a password based approach at the target containerised testing databases to provision this data. Meanwhile, ArgoCD jobs must initially authenticate at Delphix and databases in test environments as well as to private repository where Kubernetes manifests are stored. Credentials are stored in Delphix and ArgoCD hosts, the security of these authentication processes is accomplished with encryption at rest and in transit, wrapping connections in mTLS tunnels. All databases involved in this perimeter expose a REST API for authentication, likewise Delphix engine. In the view of the infrastructure two segments can be distinguished, the production segment where the production database is hosted, and the testing segment where reside CI/CD pipelines nodes among which a Delphix data Engine, ArgoCD and three testing containers hosted on a single system Figure 2.1: infrastructure ArgoCD managing 3 testing kubernetes pods with a delphix provisioned VDB Furthermore, the target application has an architecture that follows a microservice paradigm and the infrastructure is strongly bound to heterogeneous cloud-based resources. What concerns the scope of this paper is the data flow from production storage to the database that undergoes integrity control. The application leverages cloud providers managed databases both for application usage log retention and production data, the latter is provisioned on a time-based schedule to an archive database cluster, which is the object of the control. 70 Deepening further in the application architecture and CI/CD structure is out of the scope of this thesis, but this brief overview and the previously illustrated CI/CD threats paired with cloud security challenges set the context to understand the reasons why the client pointed out the CI/CD pipeline as main attack vector that could eventually lead through some lateral movements and privilege escalation to the production database. Similarly, the illustration of dataOps and PDI set the base context to comprehend the solution crafted to face malicious data encryption also known as ransomware. This solution leverages the data infrastructure abstraction provided by Delphix, a data virtualization and masking software offered via SaaS; a broad overview of Delphix is depicted in the following chapter. 2.1 Architecture The infrastructure architecture of the crafted ERV solution is represented in the below picture Figure 2.2: ERV solution architecture At first glance, it’s clear how the infrastructure is divided in two macro sites. Source site hosting the production data center, one Delphix data engine and 71 a recovery database. Whereas the control site features a database whose scope is to host virtualised data, provisioned from a data engine noted as vault and checked by a compliance engine noted as discovery engine, and finally results are evaluated by the orchestration component. These two macro sites are separated with a robust firewall. With a deny by default approach, it’s configured to apply a stateful control and strictly allow only traffic necessary for data replication and control of data engine. Since communications are encrypted the analysis of packets is limited up to the control layer. An IP/TCP session parameters filtering could already be strong enough but since the separation of these two sites is critical and communication between orchestration appliance and data engine uses HTTPS, which is often leveraged by C2 malwares, packet filtering is extended with detection of known malicious signatures and certificates, malicious encrypted payload patterns recognition, weak cipher suites, malicious SNI, self-signed or not trusted CA certificates during TLS handshake, finally there are some native behavioural based anomalies detection like packets frequency, size, replay. This firewall component is fundamental for attack detection, if the attacker is able to move laterally to the control site and access the orchestration component it could impair the control evaluation and inhibit discrepancies detection. All the appliances are deployed on Azure cloud infrastructure, including Delphix engines and orchestration unit as well as all databases. Even though this aspect should’t be overlooked, the solution transcend from underlying cloud provider, since it is technology agnostic and none of the native feature of Azure are really leveraged. Besides, the just mentioned firewalling solution, implemented with Azure native managed firewall, nevertheless its characteristics are replicable in any other cloud provider native firewall solution. When it comes to database technologies, on both database servers Oracle c19 database are deployed. Both databases are multi-tenant type with a container database model(CDB) which allows multiple full databases tenants within the same instance, also called Pluggable Databases(PDB). The network topology diagram point out three different type of communications : Data replication: these communications concern the provisioning of virtualised data to all required target environments. Virtualisation of production data, and its replication to a data engine in control site, testing environment and a fast recovery database and back-up data center Ransomware DR: communications closely related to ransomware attack detection and response measures. Data integrity control, control results 72 disks but it needs to be refreshed on demand to show updates from the underlying tables. Whereas catalogs, which are by fact static views, constitute a central repository that stores metadata about a database, acting as data dictionary, including tenants, tables, views, users, constraints, triggers and other objects, the core view, where all required data to execute the control is gathered, used in this mechanism within the virtual database are of this type. On the discovery engine, a pre-configured job is loaded to perform a data masking on the provisioned virtual database according to a custom algorithm. This algorithm does not mask data, but queries it to extract each single value occurrences at a column level. Queries target are based on an aptly created table data, where columns interested of control are listed with indication of database tenant, table and column ID, this allows the client to exclude not critical columns from the control, the paper dives deeper in the algorithm in a dedicated chapter. These clustering results are written in the same aptly created table with a json format, in their corresponding column row. These columns are indexed by database, table and column ID retrieved from each object catalog, each row has an ID that will play a key role in the results evaluation and finally a timestamp of the execution is also reported. This table isn’t provisioned from the source site, but native to this virtual database, so interested columns indexing can be inserted manually. Figure 2.6: CHECK2 - data clustering results table Likewise, the table reporting expected values is native to the control database, but values are populated automatically with specifically crafted SQL procedural code(PL/SQL). Executed on demand by the orchestration appliance, following a clean data detection. The expected results table exposes a column referred to the ID column of results table(that plays a crucial role in the control results evaluation), a value column which reports all distinct values in the column indexed by the database, table and column ID corresponding to the ID of the results table referenced and finally a column with occurrences of corresponding value, which by fact are expected results. 79 Figure 2.7: results table DDL code Figure 2.8: CHECKBASE - expected values table Once data clustering results are elaborated and all rows in the results table are populated. The orchestration appliance proceed to evaluate the results. The evaluation of results involves a query that parse results, written in json format, and builds an ephemeral data structure specular to the expected results table(CHECK BASE). Below an example table called for reference CHECK RESULTS. This data structure and CHECK BASE are confronted based on its ID and CHECK BASE check id and both tables values(in the example table “chiavi”), this ensures the correct matching of values across all column of all tables. The result of this comparison is a list of values with discrepant occurrences, actual from expected. This list size is checked against the threshold and accordingly to the check result the data snapshot is saved as golden copy 80 Figure 2.9: expected values table DDL code or not and countermeasures practices are performed. Figure 2.10: ephemeral data structure with current values The procedural code to populate the expected results table(CHECK BASE) involves a record and table type definition to handle the input parameters(defined only once initially) and two procedures : a core one that updates expected values occurrences and one that wraps it and call it for each distinct value retrieved in all columns. These procedures process actual current values occurrences and the results table ID(CHECK 2) that index the column, the table and the database where they have been retrieved. The record type definition is made in order to allow the creation of records that can hold three attributes of type NUMBER, VARCHAR2 and NUMBER. Which are the types of the attributes that constitute the triple processed by the core procedure. CREATE OR REPLACE TYPE CheckBaseRecType AS OBJECT ( check_id NUMBER, val VARCHAR2(4000), 81 count NUMBER ); Whereas, the table type definition enables the processing of a collection of values with a given record type. In this settings, the record type is the one previously defined, therefore it allows the wrapper procedure to process a list of triple with values of type NUMBER, VARCHAR2 AND NUMBER. CREATE OR REPLACE TYPE CheckBaseTableType AS TABLE OF CheckBaseRecType; The core procedure (update check base) takes as input the mentioned triple of values which are respectively : id of the results table, value and its occurrences. Then a number variable defined to hold current expected occurrences of the current interested value in the table. The procedure implements a logic that basically checks if current and retrieved occurrences are the same, in such case it ends, otherwise checks if effectively only the occurrence is changed or the value or column are new, in the former case the corresponding row is identified and occurrences updated; in the latter a new row is created and populated with input parameters. CREATE OR REPLACE PROCEDURE update_check_base ( p_check_base_data IN CheckBaseTableType ) IS CREATE GLOBAL TEMPORARY TABLE processed_check_base ( check_id NUMBER, val VARCHAR2(4000) ) ON COMMIT PRESERVE ROWS; BEGIN FOR i IN 1..p_check_base_data.COUNT LOOP MERGE INTO check_base tgt USING ( SELECT p_check_base_data(i).check_id AS check_id, p_check_base_data(i).val AS val, p_check_base_data(i).count AS count FROM dual ) src ON (tgt.CHECK_ID = src.check_id AND tgt.VALUE = src.val) WHEN MATCHED THEN UPDATE SET tgt.RES_EXPECTED = src.count 82 WHEN NOT MATCHED THEN INSERT (CHECK_ID, VALUE, RES_EXPECTED) VALUES (src.check_id, src.val, src.count); INSERT INTO processed_check_base (check_id, val) VALUES (p_check_base_data(i).check_id, p_check_base_data(i).val); END LOOP; COMMIT; DELETE FROM check_base WHERE NOT EXISTS ( SELECT 1 FROM processed_check_base WHERE check_base.CHECK_ID = processed_check_base.check_id AND check_base.VALUE = processed_check_base.val ); COMMIT; DROP TABLE processed_check_base; END; The retrieval of the current values occurrences which are the input to the procedure that updates expected results is realised with a combination of queries that will be illustrated in a dedicated chapter. For the sake of this chapter comprehension, first the results table is queried to obtain target columns, then such columns are queried in a loop. For each column, a list of distinct values and their occurrences are extracted, this pair of columns are joined with results table ID that index the corresponding values column. These queries leverages the whole database container(CDB) catalogs to index columns where values must be retrieved. Such CDB catalogs are a fundamental objects for the control mechanism. They are specifically built to facilitate the indexing in the CDB structure : Databases, Tables and Columns catalog tables abstract the complexities of underlying system views. In addition, a catalog view built upon these catalogs and the results table is defined to ease even further the control visibility. Following a clean data evaluation, likewise CHECK 2 and CHECK BASE, these catalog tables are refreshed on demand by the orchestration appliance to fetch potential new columns, tables and databases. In the following images, catalogs and relative DDL for tables definition and DML for data 83 update SQL code is depicted. In the database catalog, all pluggable database tenants in the container are listed, with their system name and an ID that replicates the connection ID. Data is retrieved from the system view cdb pdbs, connection id is used as ID since it is a unique number for each database instance and it’s selection is always required when querying these system views. Only active connection are selected and the whole container database reference(ORCL) is hardcoded as it doesn’t appear in cdb pdbs. Figure 2.11: Databases catalogue example Figure 2.12: Databases catalogue table DDL code INSERT INTO c##control_user.databases (ID, DB_NAME) SELECT con_id, pdb_name FROM cdb_pdbs WHERE status = ’NORMAL’ UNION ALL SELECT 1, ’ORCL’ FROM dual; CREATE OR REPLACE PROCEDURE update_databases_catalog IS BEGIN MERGE INTO c##control_user.databases tgt USING ( SELECT con_id, pdb_name FROM cdb_pdbs WHERE status = ’NORMAL’ ) src ON (tgt.DB_NAME = src.pdb_name) WHEN NOT MATCHED BY TARGET THEN -- in current values not in catalog INSERT (ID, DB_NAME) VALUES (src.con_id, src.pdb_name) 84 WHEN MATCHED THEN NULL WHEN NOT MATCHED BY SOURCE THEN -- in catalog, not in current values DELETE; ; COMMIT; END; Similarly, the tables catalog list all tables and their schemas along with the ID of the pluggable database they belong to. This data is retrieved from the cdb tables system view. The user owner of the table correspond to the table schema and the connection ID to the database ID which is equal to the ID in the databases catalog. Results are filtered by tables space, removing tables that reside in default system spaces, which are not of interest in the control. Figure 2.13: Tables catalogue example Figure 2.14: Tables catalogue table DDL code INSERT INTO c##control_user.tables (TABLE_SCHEMA, TABLE_NAME, DB_ID) SELECT owner, table_name, con_id FROM cdb_tables WHERE tablespace_name NOT IN (’SYSTEM’, ’SYSAUX’) CREATE OR REPLACE PROCEDURE update_tables_catalog IS BEGIN MERGE INTO c##control_user.tables tgt USING ( SELECT owner, table_name, con_id FROM cdb_tables WHERE tablespace_name NOT IN (’SYSTEM’, ’SYSAUX’) ) src ON (tgt.TABLE_SCHEMA = src.owner AND tgt.TABLE_NAME = src.table_name) WHEN NOT MATCHED BY TARGET THEN INSERT (TABLE_SCHEMA, TABLE_NAME, DB_ID) VALUES (src.owner, src.table_name, src.con_id) 85 WHEN MATCHED THEN NULL WHEN NOT MATCHED BY SOURCE THEN DELETE; COMMIT; Columns catalog exposes column name and relative table id from tables catalog. Data is therefore retrieved from the combination of tables catalog and system view cdb tab columns joined on tables schema and name. Reported tables aren’t in system table spaces. Figure 2.15: Columns catalogue example Figure 2.16: Columns catalogue table DDL code INSERT INTO c##control_user.columns (COLUMN_NAME, TB_ID) SELECT c.column_name, t.ID FROM cdb_tab_columns c JOIN c##control_user.tables t ON c.owner = t.TABLE_SCHEMA AND c.table_name = t.TABLE_NAME; CREATE OR REPLACE PROCEDURE update_columns_catalog IS BEGIN MERGE INTO c##control_user.columns tgt USING ( SELECT c.column_name, t.ID FROM cdb_tab_columns c JOIN c##control_user.tables t ON c.owner = t.TABLE_SCHEMA AND c.table_name = t.TABLE_NAME ) src ON (tgt.COLUMN_NAME = src.column_name AND tgt.TB_ID = src.ID) WHEN NOT MATCHED BY TARGET THEN INSERT (COLUMN_NAME, TB_ID) VALUES (src.column_name, src.ID) 86 WHEN MATCHED NULL WHEN NOT MATCHED BY SOURCE THEN DELETE; COMMIT; END; For all these catalogs, the procedure requires them to be populated initially with a standard insert query and in consequent control cycles data will be refreshed by a stored procedure that leverages the combination of the clauses MERGE INTO and WHEN NOT MATCHED. All procedures share the same executive template, current and newly retrieved data is matched against database name for databases, tables schema and names for tables and column name and table id for columns. Whenever there is match on both sides the entry remains the same, whereas all rows with only the current retrieved values result in an insertion in the catalog, on the opposite rows where only data from the catalog appear are deleted. Upon these catalogs is defined a master view, that joins the catalogs and expand them with more intelligible information like actual databases, tables and columns name that will be retrieved from discovery engine and orchestration appliance to build queries strings dynamically. It it built upon results table(CHECK 2) so only columns interested of control are reported. Underlying catalogs are never actually queried, but their value is abstracted in this view CREATE OR REPLACE FORCE EDITIONABLE VIEW "C##CONTROL_USER"."CHECK_VIEW_2" ("ID", "DB_ID", "DB_NAME", "TABLE_ID", "TABLE_NAME", "COLUMN_ID", "COLUMN_NAME", "DATATYPE", "ALGO_NAME", "RESULT", "TIMESTAMP") AS SELECT c.id, d.id AS "DB_ID", d.dbname AS "DB_NAME", t.id AS "TABLE_ID", t.name AS "TABLE_NAME", clmn.id AS "COLUMN_ID", clmn.column_name AS "COLUMN_NAME", c.result, c.timestamp FROM "C##CONTROL_USER"."CHECK_2" c, "C##CONTROL_USER"."DATABASES" d, 87 "C##CONTROL_USER"."TABLES" t, "C##CONTROL_USER"."COLUMNS" clmn, "C##CONTROL_USER"."CHECK_ALGO" ca WHERE c.database_id = d.id AND c.table_id = t.id AND d.id = t.database_id AND t.id = clmn.table_id Figure 2.17: Master view Delphix and orchestration appliance users are specifically configured to guarantee LPP. Their permissions are restricted solely to the operations they are intended to execute, strictly on the target instances, tables spaces and single tables. In the design phase, it was decided to not apply a role-based approach to permissions control, since there are only two users the applications are executing operations on behalf and in any potential scaled scenarios users should be always two. Furthermore, this purely grant-based approach allowed a fine-grained object access, guaranteeing lowest privilege principle to its maximum extent, which was indeed difficult with a role-based approach given the complexities of the multi-tenant database architecture : multiple databases tenants, inner table spaces and table schemas. Hence, we can identify two application users : “c##delphix” and “c##control user”, both are by default common oracle database users with CDB wide administration scoping and centralised management, but initially they don’t have any read/write permissions. The former, as the name suggests, is the user that both vault and discovery engine act on behalf, its write(i.e. INSERT, UPDATE, ALTER) permissions regard all tables within the not-system table spaces(exclusion of 88 Figure 2.19: Oracle environment standard topology [8] engine to remote services, to the engine, and to the Source and Target Environments. Based on the above diagram a series of network requirements, communication protocols both at control and higher layers and therefore ports can be depicted for oracle dSources and VDBs Outbound from the Delphix engine Protocol Port Use TCP 22 SSH connections to source and target environments TCP NA sConnections to the Oracle SQL*Net Listener on the source and target oracle environments(typically to port 1521) Table 2.1: Outbound requests port allocation Inbound to the Delphix engine 95 Protocol Port Use TCP/UDP 111 Remote Procedure Call(RPC) port mapper for NFSv3 mounts TCP 1110 NFSv3 server daemon status and keepalive(client info) TCP/UDP 2049 NFSv3 server daemon from VDB to the Delphix engine, both v3 and v4 TCP 8341 Logsync communication data from source to the Delphix engine TCP 8415 SnapSync control and data from source to Delphix engine TCP 54043 NFSv3 mount daemon TCP 54044 NFSv3 stat daemon(lock state notification service) TCP 54045 NFSv3 lock daemon/manager TCP 54046 Encrypted NFS connection from source and target environments Table 2.2: Inbound requests port allocation 96 In this connections overview, protocols used by Delphix to interact with remote soruce/target environments for different scopes are pointed out : •SSH : Delphix engines leverage SSH to connect to both source and target host •HTTP : an inbound connection to the engine from the source environment wrapped in DSP •NFS : likewise an inbound connection to the engine from the source environment wrapped in DSP •SQLNet : SQL*Net (also known as Oracle Net Services), Oracle’s networking component, is used to comunicate with both source and target RDBMS agent The configuration of the host and database component themselves are pretty straightforward, as default oracle deployment integrates seamlessly with Delphix engine base settings. The most demanding aspect of the oracle environment configuration is the set up of correct permissions of Delphix engine specific user. Delphix requires specific file permissions, OS group memberships, and system configurations to properly discover, manage, and provision Oracle databases. In order to achieve these capacities, the Delphix user, labelled as ‘delphix os’ here for semplicity, needs : •Auto-Discovery : reading access to system inventory files, including ‘/etc/oratab‘ or ‘/var/opt/oracle/oratab‘ ; ‘/etc/orainst.loc‘ or ‘/var/opt/oracle/orainst.loc‘ and oracle specific inventory (inventory.xml). As well as, sudo access to ‘ps‘ utility or any other process discovery utility •Source Host : a specifically created $ORACLE HOME env variable must be set to the instance startup path and delphix os user must have execute permissions on all binaries in $ORACLE HOME/bin, which requires also to set the PermitUserEnvironment parameter to True in ssh config(sshd config) •Target Host : suggested requirements involve adding delphix os as a member of SYSDBA OS group, which grants to members of the group direct access to oracle instance bypassing credential authentication. Whereas in the solution scenario, to guarantee better fault resilience, a more secure approach was adopted by just granting the Delphix user(c##delphix) connection, session, read and write permissions to specific table spaces. Likewise at the system level, NFS related 97 permissions were granted, like the /mnt/provision/ directory must be owned by the user with all files with permissions 0770 (rwxrwx—-). Next, specific utilities execution permissions were granted, including : mount, umount, mkdir, rmdir and ps. Regarding sudo privileges, it is required to specify the NOPASSWD qualifier within the ”sudo” configuration file, in order to ensure that no password is demand on super-user assumption. All outlined file paths, utilities and any other system objects are part of linux-based systems, which are the hosts used in the solution over Azure infrastructure. Starting with ingestion, ingestion is the important process by which a consistent, ready-to-run copy of the data is prepared and captured, and persisted as a Delphix snapshot in Delphix storage, so that it can be provisioned to virtual copies as Delphix vDB(s). Before beginning, it’s important to understand the recommended ways to ingest. There are two architectural models by which Delphix performs ingestion in Oracle databases and any other technology : direct Ingestion and staging push ingestion. The former is the approach preferred for our solution so only that will be covered. Direct ingestion is an approach where the Delphix Continuous Data Engine is able to extract (directly from the true production source) the necessary information to reconstruct the source database and persist it into storage on the Delphix Continuous Data Engine. This direct methodology requires no intermediate host nor instance of the source database. The Delphix connector must interact directly with the true production source system to request the data to be persisted. There is no activity whatsoever on an intermediate staging host; instead the blocks are directly written to storage on the Delphix Continuous Data Engine. Once an environment is added, Delphix Engine will automatically ‘discover’ databases on it, which are compatible to ingest from, to create new dSources. Following the creation of a dSource, delphix continuous engine will leverage RMAN(Recovery Manager) utility and JDBC to create snapshots of the source by taking a full database backup. The newer snapshots take incremental backups of the database to ingest incremental data between snapshots. The result is a TimeFlow with various snapshots from which you can provision a VDB. The key component in this process is Oracle RMAN utility, the built-in backup and recovery utility for Oracle databases. It is designed to automate backup, restore, and recovery tasks while providing efficient management of database files. Hence, it is able to ship the entire storage block map to Delphix when Delphix requests it initially, then later just the changed blocks since the previous ingestion (an incremental roll forward capability). 98 Following, VDB provisioning involves, initially the creation of the virtual database, whose settings are a copy of the source environments settings, linked in the dSource, which must be compatible with the target environment, therefore source and target environments are almost identical storage solutions. Hence, as of the solution context a vCDB will be created on the target environment during initial provisioning. Creating a vCDB seems conceptually easy but in reality resolves around several complex steps, all the steps can be gathered in two main phases : 1. Data mounting : leveraging the binaries installed during environment configuration, Delphix mounts the virtualised data files onto the target environment, this involves the export of the storage containers associated with the VDB, making them available for mounting; and then mounts the exported storage containers over NFS to designated directories. These mount points correspond to the VDB’s data files and structures. 2. Environment configuration : Delphix sets up the Oracle environment on the target host, ensuring that environment variables such as ‘ORACLE HOME‘ and ‘ORACLE SID‘ are correctly configured to recognise the mounted file systems. Respectively, they define the root directory where oracle is installed containing all executables, libraries and configuration file; and a unique name assigned to an Oracle instance which defines the instance to connect to. These variables play a fundamental role to ensure Oracle tools and applications correctly interact with the intended database. Following virtual database refreshes are similar to the initial data mounting phase, with a middle action of comparison between data in the snapshot intended to be provisioned and current data, in order to adopt an incremental approach and therefore write solely new/changed data. Hence, refresh process can be summarised in three steps : 1. Unmount. : the current NFS file system is un mounted, which involves first of all gracefullty stopping the running VDB instance and if configured unregister it from the oracle listener; then actually unmounting the NFS file system underlying the VDB 2. Update : updating data set includes checking for actual changes and new data in advance, by performing a thorough comparison. This comparison is employed at file system data blocks level and it is performed directly on Delphix with the Block Change Tracking (BCT) process. BCT fundamentally involves comparing the latest with the previous 99 dSource snapshot, block by block through their hashes, which are indexed, where differences or new blocks are revealed, Delphix analyse redo and archive logs to track committed transaction which resulted in the changes, finally database control files are evaluated for structure consistency to detect any table spaces and schemas change. Based on comparison results, data blocks are updated in the target unmounted NFS file system, including adding new blocks, updating current ones and removing deleted ones. 3. Mount : the updated NFS file system is re mounted in the environment file system, database service is restarted along with the VDB instance. Database availability and consistency is checked with a test connection leveraging JDBC, fetching for some metadata like its status. Data masking When Delphix performs in-place masking on an Oracle VDB, multiple components are involved on both the Delphix and Oracle database side. The interaction is primarily SQL-based, using SQLNet (Oracle Net Services). Delphix compliance engine connector leverages JDBC to interact with target Oracle database and the connection follows the SQLNet paradigm, where substantially request payloads are in plain SQL DML language and similarly authentication use clauses defined by Oracle standard, used by sql plus default oracle utility. Hence, the masking workflow involves the following macro steps : 1. Establish connection : Delphix Oracle connector initiates a JDBC based connection wrapped in a TLS tunnel, through SQLNet to the oracle VDB at standard port 1521, obviously in advance there is the establishment of the TLS tunnel with a TLS1.2/1.3 handshake. On port 1521 an Oracle listener process is listening for new connection initiations, once it detect the request it spawns a dedicated Oracle Server process to handle the masking session 2. Sensitive data detection : once a connection has been established, delphix engine comunicate with the associated oracle service to detect sensitive to be masked according to the profiling set assigned to the rule set, this detection involves a series of batched SELECT queries executed by the Oracle SQL execution engine, which retrieves data from buffer cache or NFS file system if there are any cache miss. 3. Data update : following the detection and association with predefined masking algorithms according to the domain associated with the classifier. SQL UPDATE queries are prepared for batch execution and 100 they are sent in batches with a size pre-defined in the Delphix masking job configuration. In the solution, masking is multi-threaded with updated rows partitioned between threads and write collision avoided by row level lock handled by the SQL execution engine. Simultaneously, updates are stored in the redo log buffer before being committed and likewise undo Tablespaces store pre-masking values to allow rollback. 4. Commit and Storage update : finally each thread commit all transaction performed in parallel, hence changes are written to the redo logs, and gradually the Oracle chekpoint process write in the NFS storage. Commit size is pre-configured in the engine and actual transactions size could exceed the limit, it is a fundamental to set it correctly to balance performance and redo log growth. The following is the configuration panel of the masking job, among others in-place masking method, 5 update threads, 20000 rows batch size, 10000 transaction commit size are reported. Additionally, further noteworthy parameters are feedback size and memory boundaries, which respectively are the number of rows processed before sending a status update to the job execution logs and the amount of JVM heap memory allocated to the masking job in megabytes. With these settings the job is configured to split total rows to be updated as much as possible evenly across five threads, each thread further split rows to be updated in batches of 20000 rows and execute an update by which every 10000 rows updated transaction is committed and every 50000 rows a status feedback is sent back to the engine, with details of any encountered error and number of rows processed before the error. JDBC A core component of Delphix engines software architectures is JDBC (Java Database Connectivity). JDBC is a Java module which provide an API to interact with relational databases objects through java objects, therefore abstracting the complexities of relational database technologies and ease the interaction with them. JDBC is leveraged to interact with several types of relational databases, enabling virtualisation, masking, and replication. Delphix uses JDBC in several ways : •Database Discovery & Inventory : gather environment metadata, including fetching information on schemas, tables, users, and storage configurations. •Data Masking : execute SQL queries over JDBC to apply masking rules on sensitive data. 101 Figure 2.20: Masking job configuration panel •Replication & Synchronization : When synchronizing datasets across environments, JDBC is used to retrieve change logs or execute queries for replication. API Delphix provides a comprehensive API that facilitate seamless integration and automation within its ecosystem. Its API is designed to manage and interact with various components of the Delphix Engine, including data ingestion, virtualization, and masking processes. Delphix API blends RESTful architecture with RPC-like (Remote Procedure Call) behavior, making it a hybrid approach that borrows concepts from both REST and gRPC. It’s an hybrid approach since the communication channel at application level is HTTP and data is exchanged in JSON format conforming to RESTful principles but the API connection employs a stateful approach with a session based communication mechanism and additionally some endpoints effectively expose procedure-driven actions rather than pure CRUD, belonging to the gRPC paradigm. As mentioned, communication with the API require the establishment of an 102 HTTP, or optionally HTTPS if wrapped in TLS tunnel, session before actually authenticating. Session is established by sending a POST request to a specific endpoint, the request involves a a‘Session‘ object to the URL ‘/resources/json/delphix/session‘. This session object will specify the ‘APIVersion‘ to use for communication. Once a session has been established a POST request can be sent to the authentication endpoint, which differs between data and compliance engines, with credentials as payload, by fact using basic HTTP Auth as authentication method. Following the authentication API interaction in terms of request legitimacy control differs between data and compliance engine. As the former does not require an authorisation header with a token in following requests as it implicitly tracks the authenticated session using a server-side session mechanism, while it’s documented that it lasts 30 minutes by default Delphix does not document low-level details of this mechanism, allegedly it bind the session to the TCP connection or if wrapped within a TLS channel the TLS connection or performs a client fingerprinting. Whereas, the compliance engine API requires an authorisation header with a token provided in response of the successful authentication request. Regarding the structure of the API, mainly when it comes to the data engine, it is strongly resource oriented which translate in endpoint organised by resource type; i.e. : database, environment, job. Custom Masking Algorithm Plugins & Masking Extensible SDK The Delphix Continuous Compliance Engine offers an Extensible Masking Model which enables the integration of custom masking algorithm plugins. This capability allows for tailored data protection strategies, accommodating unique data formats and compliance requirements. The Extensible Masking Model is designed to enhance the Delphix Masking Engine’s flexibility by supporting the creation and integration of custom masking algorithms. This model is facilitated through the Delphix Masking Extensible SDK, a toolkit that provides the necessary resources for developers to build, test, and deploy custom plugins, including the masking Java framework to build algorithms upon. This framework abstracts the core masking logic, enabling developers to focus on implementing their specific algorithms, it outlines clear interfaces and APIs that custom plugins must adhere to, ensuring seamless integration with the engine software. These plugins can introduce new masking algorithms or extend support to additional data sources, thereby broadening the engine’s applicability. A custom masking algorithm plugin developed using the Delphix Masking Extensible SDK is constituted by the following macro components: •Algorithm Class: The core of the plugin, this Java class implements the ‘MaskingAlgorithm‘ interface provided by the SDK. It contains the 103 logic for the custom masking operation. •Metadata Files: These files describe the plugin’s properties, such as its name, version, author, and any dependencies. •Build Scripts: dependencies managers and builders like Gradle which automate the compilation and packaging of the plugin into a deployable JAR file. Once installed, the plugin becomes an integral part of the Masking Engine’s ecosystem. The engine dynamically loads the plugin, making its functionalities available for use in masking jobs. Plugins operate within a sandboxed environment on the Masking Engine. This sandboxing ensures that the plugin’s operations do not adversely affect the overall system stability or security. 2.4 Custom Masking Algorithm As introduced formerly in the overall solution illustration, a custom masking algorithm was developed to perform a data clustering on target columns and write results in the corresponding column of the result table in JSON format along with a timestamp. This functionality is implemented leveraging a key feature and a fundamental characteristic of the compliance engine, respectively sensible data masking and its extendibility, both covered in previous chapter. Though, the in-place kind of masking performed in this solution does not replace sensitive data identified with fictitious values as intended natively by the feature design but essentially perform custom SQL queries according to the data retrieved from the results table. This custom masking algorithm therefore leverages extendibility as it is integrated with the engine through a masking plugin. The algorithm attached with the plugin is then associated with a pre defined making job which employs in advance the automatic sensitive data discovery. Hence, during job creation the discovery is configured by setting rule set, classifier, domain, profile set. Initially, after the source critical environment has been connected the whole set of tables among all pluggable databases, besides catalog tables, have been defined in the scope of the rule set. Since the data in the results table are not masked but simply read for further dynamic query creation, it is therefore known that discovery does not require complex domain or classifiers. In this scenario, a general purpose “CHECK” domain, with associated the custom masking algorithm, has been defined to identify sensitive data and therefore select it as input to the algorithm to create dynamic queries. Likewise a profile set 104 –Runtime dependencies : finally all library dependencies are declared, similarly to the previous declaration in the buildscript block. Likewise, here all JARs in the lbs directory are limited to compile time, not runtime, besides javapasswords.jar. This is made in order to avoid the aforementioned collision between dependencies in the plugin code and engine plugins interface, hence code is written with the same version used by the engine so when the plugin is injected and run code will be compatible with the interface but required libraries will be retrieved by jars on the engine, whereas javapasswords is used to connect to CyberArk password vault and not used by the engine Lastly, settings.gradle role is to define JAR wide settings like name, which is this plugin use case. Source Code The last, and indeed most important, macro component of the plugin is the actual Java executive source code. The plugin source can be additionally divided in two main blocks, the actual source code inside the sample directory and the final JAR name declarations inside the file at META-INF/services directory com.delphix.masking.api.plugin.MaskingComponent. In order to comprehend fully the source code, few software objects established by Delphix, to provide the masking extendibility features properly and target a broad range of data types, must be understood in advance : MaskingAlgorithm: One of these and probably the most noteworthy is the afore introduced MaskingAlgorithm interface. Any Java class that should be recognized as a masking algorithm (whether standalone or configurable) must implement this interface. This interface is parameterized with the data type that the algorithm masks, which defines the input and output data type of the mask method. Interface, class and method Parimeterization in Java is employed with Generics, a powerful software components configuration concept, which allows such components to operate on objects of various types while providing compiletime type safety; the type specified as a placeholder in your code will be replaced by a specific type when the class is instantiated or method invoked, in this plugin case when the class implementing the masking interface is instantiated. The masking class can be parimeterized to a specific set of data types, in order to simplify algorithm development while maintaining the ability to mask data from many sources : •Binary data - java.nio.ByteBuffer •String data - java.lang.String 111 •Numeric data - java.math.BigDecimal •Date time data - java.time.LocalDateTime •Multi-column data - com.delphix.masking.api.plugin.utils.GenericDataRow Each algorithm is expected to input, process, and emit objects of one of the above Java types, but is free to use any intermediate types (as needed) to access library methods. The MaskingAlgorithm interface inherits from another masking general interface, always provided in the masking algorithm jar, MaskingComponent, an higher level component for masking algorithms, providing the methods getAllowFurtherInstances() , getDefaultInstances(), setup() and tearDown() covered later. This interface extends further parent interfaces : •JsonConfigurable : class with the responsability of ensuring the JSON configuration data is valid, including its json compliant overall data format; values proper format, lenght and any other constraint; empty or null properties. provide this functionality through the method validate() leveraging the Jackson JSON deserializer. •PluginComponent : kernel interface that all plugins of all types looking to expose functionality to the Delphix Masking engine must implement. It provides the methods getName(), getDescription() and getDocumentation() covered later. Hence, when it comes to all the methods offered by the masking interface, the following can be identified : •getName and getDescription - These methods are used to determine the name and description of frameworks and algorithm instances included in the plugin. For user-created instances, these methods are never called. •getDefaultInstances and getAllowFurtherInstances - These methods control the set of instances of the algorithm framework that are defined by the plugin, and whether the user should be allowed to create additional instances. •validate - This method is called after configuration is applied to allow the algorithm class to check whether the injected configuration is valid. 112 •setup and tearDown - These methods are called before the algorithm object is used for masking, and after, respectively. Typically, any resources, such as input files, are acquired during setup and released during tearDown. •mask - This is the method that does the actual data masking in the algorithm class. The input and output values are parameterized for type safety. •listMultiColumnFields - This method needs to be implemented only for Multi-Column Algorithms. It returns a list of AlgorithmLogicalField objects that define the set of fields that the multicolumn algorithm masks. For each column it is required to define the core data type using the Delphix enumeratoin, and optionally the method offers the possibility to declare a description, permissions(i.e. : read-only/read-write). Usually, the specular listMaskedFields method is preferred to it, similarly it returns a map of field names (String) to the Core Data Type GenericDataRow: A GenericDataRow is by fact a map of field names (String) to GenericData objects. Each GenericData object contains the value, along with methods to return the respective typed object. When accessing the value from a GenericData Object it will be necessary to read it into a core data type. To do so, one of the following methods must be used : •getStringValue() •getBigDecimalValue() •getLocalDateTimeValue() •getByteBufferValue() Once the value has been masked it should be re-set by calling setValue and passing as an argument the value as a core data type. GenericDataRow objects are mainly used in contexts where the target data type is unknown before the configuration or anyway dynamic; this is usually the scenario when masking is the function of multiple related columns, which is the solution setting. MultiColumn: this type allows the definition of masking algorithms whose result is the function of multiple columns. Each column processed must be defined as AlgorithmLogicalField by the previously illustrated listMultiColumnFields. Furthermore, columns must be configured engine 113 side through web UI or API, configuring a column means defining column’s metadata with the following information : •AlgorithmFieldId : index of the column among fields processed by the associated algorithm •AlgorithmGroupNo : is a group number (integer) for the columns treated by the same algorithm instance. It is introduced for cases where there are multiple columns of a similar type, which are masked by the different Masking Jobs using the same algorithm. In such a case that’s important to unite the columns per algorithm run, by assigning the same group number •AlgorithmName : name of the sensitive data classifier framework, previously illustrated. •DomainName : domain assigned to column data, previously illustrated. A final important concept is algorithms configurability, the extensible algorithms feature supports the creation of algorithm frameworks. When an algorithm class is constructed to be a framework, the Continuous Compliance Engine may create additional instances of the algorithm - with different configurations - after the plugin has been installed, based on provided JSON data. The JSON schema for configuration is determined by which data members in the framework class are marked as configurable, and may vary from framework to framework. To integrate an algorithm as a configurable framework within the Continuous Compliance Engine, specific requirements must be met: •Method requirement: The algorithm class should have the getAllowFurtherInstances method implemented to return true. •Data member annotation: The algorithm class must include one or more public data members. These members are crucial for configuration and must be annotated with @JsonProperty from the com.fasterxml.jackson.annotation.JsonProperty package. This annotation enables the data members to be recognized and processed as configurable parameters. For each algorithm marked as configurable, the SDK or Continuous Compliance Engine inspects the class annotations to identify which parameters can be configured. When new JSON data is provided and the process of creating a new instance of the algorithm is triggered, the system tries to apply the 114 user-provided JSON configuration to the algorithm class object, this process includes a level of validation described before by the JsonConfigurable component. So given all of these functional and software concepts, in the following paragraphs snippets of final production code will be depicted according to the masking algorithm workflow, with the premise that basic concepts will be overlooked. Most of the code belong to the RansomCheck class implementing the MaskingAlgorithm interface, but three further are defined to properly separate functional responsibilities : TableDetails, WhereCondition and Toolbox; all fall within the utilities category as they provide a functionality that ease the masking workflow abstracting the complexities of underlying java native modules. A short parenthesis on non functional aspects is necessary, the plugin source code follows design best practices to guarantee most non functional requirements at a professional level. An high degree of modularity ensures maintainability, reusability, interoperability and therefore scalability; whereas the broad coverage of not expected execution scenarios with constructs try-catch strongly supports reliability, fault tolerance and security, similarly the highly meaningful scenarios descriptions allows ease the debugging also at scale; and of course the core set of design principles outlined by the SOLID are respected, as demonstrated by the following examples : •Single Responsibility Principle (SRP) : RansomCheck class is solely handling data extraction process logic, outlining all various step of the algorithm, while functions like executing queries, logging, connecting to database instances, storing data in efficient data structure, are all handled by specifically crafted classes. •Open Closed Principle(OCP) : all software entities are open for extesion, new amsking algorithms can be defined just by creating another class that implements the MaskingAlgorithm interface. Similarly, a new data processing query can be defined by writing a further class that builds a different kind of query and calling it inside the build query step, which will be eventually appended by the final table scoping part. •Liskov Substitution Principle (LSP) : there are not really sub-classes in the code, but current classes design set a proper base for an easy future extension that guarantees proper interchangeability. •Interface Segregation Principle (ISP) : this is guaranteed by Delphix interface requirements, since the masking engine is the only real client that interacts with the plugin, the correct way of interaction is through the class implementing the MaskingAlgorithm interface. 115 •Dependency Inversion Principle(DIP) : just like the previous, the degree of complexity of the code does not require multiple level classes with a higher level interface that allows choice flexibility, but any future extension is facilitated by the current design. Software entities names besides being highly meaningful being made of few descriptive words, comply under all aspects to Java naming convention, hence Pascal case for classes and interfaces, camel cases for methods and attributes, while full upper case for constants and enumerations. The methods and attributes surrounding the actual masking workflow logic, previously illustrated, will be depicted. All of these fall belong to the core RansomCheck class, among them there is getAllowFurtherInstances method to establish the algorithm as a framework, enabling it’s further configuration @Override public boolean getAllowFurtherInstances () { return true; } Illustrated before, being inherited from MaskingComponent it features the Override annotation. Which is required at compile time to verify the implementation of the interface contract. This method combined with the following declaration of configuration public variables, makes by fact the algorithm a framework from which instances can be released @JsonProperty (" db_dbType ") public String db_dbType ; @JsonProperty ("db_hostname") public String db_hostname ; @JsonProperty ("db_port") public String db_port ; @JsonProperty ("db_username") public String db_username ; @JsonProperty (" db_schema ") public String db_schema ; @JsonProperty ("db_password") public String db_password ; @JsonProperty ("db_instance") public String db_instance ; @JsonProperty ("db_addParams") public String db_addParams ; All variables are annotated with the annotation @JsonProperty to allow the recognition as configuration data members, by the engine during plugin inspection. Hence, a file with a specular structure must be provided as framework configuration on the engine once the pluing has been loaded. This configuration features all technical details to establish a connection and reach the 116 database general view(CHECK VIEW 2), peculiar is the db dbType which is effectively the name of a technology as defined by the enumeration that the connection class relies on. This data is the igniting point of the algorithm, it’s fundamental to avoid hardcoding which would make the algorithm insecure and preclude its interoperability with different databases. Further class variables comprehend mainly private objects designed to provide a specific functionality and therefore leveraged along the data processing workflow. As a matter of fact these attributes have been declared at the class level to be visible by all methods, though the keyword private is leveraged to limit their visibility within the class. Since a masking algorithm constructor wasn’t defined, once the engine plugins interface will instantiate the algorithm, the java Object default no arguments constructor will be invoked and all reference types variables will be initialised to null, including the list and String. private LogService logger ; private Toolbox toolbox; private Connection containerConnection ; private Connection targetTableConnection ; private ArrayList < String > values_list ; private WhereCondition condition ; private TableDetails resultRowData ; private TableDetails targetTableData ; private String checkQuery ; private ResultSet valuesClustersResultSet ; private JSONObject retrievedValuesClusters ; These objects assume a proper initial value once the setup() method is called. Each reference typed variable is initialised with an instance of the respective objet, besides the java sql module ResultSet which is abstract. Setup method is called only once following object creation, its intended usage is to initialise class variables and let computational space to elaborate on the algorithm metadata in advance, leveraging the ComponentService argument. @Override public void setup ( @Nonnull ComponentService serviceProvider ) { this . logger = serviceProvider . getLogService () ; this . toolbox = new Toolbox (); this . condition = new WhereCondition () ; this . values_list = new ArrayList <String >() ; this . checkQuery = ""; this. valuesClustersResultSet = null ; this. retrievedValuesClusters = new JSONObject (); } Another fundamental method is the aforementioned method to declare the 117 list of columns with their core data type of the target table(rule set) which should be object of processing by the algorithm. This method is listMaskedFields() and returns a map with column name and masking core data type, VARCHAR used for index parameters on database side correspond to string core data type, whereas the result column is parameterised as byte buffer being a BLOB(Binary Large Object), which holds binary data. @Override public Map <String , MaskingType > listMaskedFields () { Map < String , MaskingType > maskedFields = new HashMap < String , MaskingType > (); maskedFields.put("DATABASE_ID", MaskingType . STRING ); maskedFields.put(" TABLE_ID " , MaskingType . STRING ); maskedFields.put(" COLUMN_ID " , MaskingType . STRING ); maskedFields.put("RESULT", MaskingType.BYTE_BUFFER); maskedFields.put(" TIMESTAMP " , MaskingType . LOCAL_DATE_TIME ); return maskedFields; } The actual data extraction and processing logic is defined in the mask() method, as mentioned formerly. @Override public GenericDataRow mask(@Nullable GenericDataRow genericData ) throws MaskingException { try { getResultRowData ( genericData ); getTargetTableData (); getColumnExpectedValues(); buildClusteringQuery(); extractColumnEffectiveValuesClusters(); parseEffectiveValues(); } catch ( Exception e) { if (e instanceof InvalidIndexParametersException ) { writeEffectiveValues((( InvalidIndexParametersException) e). printMessageWithIndexes()); return genericData ; } else if(e instanceof IllegalArgumentException ) { writeEffectiveValues ((( IllegalArgumentException ) e). getMessage () ); return genericData ; } else { StringWriter sw = new StringWriter(); e. printStackTrace (new PrintWriter ( sw)); throw new MaskingException ( sw . toString () + "\n Exception message : \n" + e. getMessage () + " \n With following parameters : \n db_id : " + 118 resultRowData . getDb () + "\n tb_id : " + resultRowData . getTable () + " \n col_id : " + resultRowData . getCol () + " \n\n With following values : \n " + String . join (", ", values_list )); } } return genericData ; } This method has a central role, receiving as input, through an argument, the GenericDataRow element, map of genericData variables, and return it following an elaboration, which in this solution scenario are performed on the RESULT column. All the instructions inside are wrapped within a trycatch construct, that catches for a general exception, since all the nested methods which will be covered later can return several different kinds of exceptions. The exception handling is performed according to the exception type. A specific exception InvalidIndexParametersException has been crafted to properly handle invalid index parameters in the results table, in case of such exception through the writeEffectiveValues method, the exception message is written in the result column and the current iteration of the algorithm ends returning the genericData object, the method input. public class InvalidIndexParametersException extends Exception { ArrayList < String > indexes ; public InvalidIndexParametersException ( String message ) { super ( message ); } public InvalidIndexParametersException ( String message , String ... Idx ) { super ( message ); this . indexes = new ArrayList < String >( Arrays . asList ( Idx)); // Directly convert array to list } @Override public String getMessage () { return super . getMessage () ; } public ArrayList <String > getIndexes () { return this . indexes ; } public void setIndexes ( ArrayList < String > indexes ) { this . indexes = indexes ; 119 } public String printMessageWithIndexes () { return getMessage () + "\n" + String . join (", ", getIndexes ()); } } Whereas the IllegalArgumentException exception is sought for to handle null values retrieved in the general database view, such case is handled by writing the exception massage int eh result column and returning the genericData object. All additional exceptions are handled by throwing a MaskingException, retrieved from the masking plugin library, which stops the engine masking process and prints the passed argument in the masking job info logger. The passed string reports the current execution stack trace, along with the current analysed database, table and column indexes and the list of values being checked looked upon. The first step of the algorithm is to place current row data in a specifically crafted data structure, which ease its elaboration. Since index parameters are encoded as VARCHAR database side, a proper parsing requires the use of getStringValue() method, this method belong to the object crafted by Delphix to hold cell values, which likely is an interface to multiple data types private void getResultRowData ( GenericDataRow genericData ) { this . resultRowData . setDetails ( genericData . get ("DATABASE_ID").getStringValue(), genericData . get (" TABLE_ID ").getStringValue(), genericData . get (" COLUMN_ID ").getStringValue(), genericData . get ("RESULT"), genericData . get (" TIMESTAMP ") ); } This data structure is implemented with a class TableDetails which exposes all database related details with several attributes and respective getter methods. public class TableDetails { // results table data private String db; // all for security purposes private String table ; private String col ; private GenericData result ; private GenericData timestamp ; // data required to connect to table where reside column to extract values from private String technology ; 120 db_schema , resultRowData . getDb () , resultRowData . getTable () , resultRowData . getCol () ); if ( values_rs != null && values_rs . next ()) { while ( values_rs . next ()) { values_list .add ( values_rs . getString (1) ); } values_rs . close (); this. containerConnection . close () ; } else { this . logger . info ("No expected values found in expected values table ( CHECK_BASE ) with these parameters \n db_id : " + resultRowData . getDb () + " \n tb_id : "+ resultRowData . getTable () +"\n col_id : "+ resultRowData . getCol ()); throw new InvalidIndexParametersException("No expected values found in expected values table ( CHECK_BASE ) with these parameters ", resultRowData . getDb () , resultRowData . getTable () , resultRowData . getCol ()); } } Distinct values are retrieved executing a parameterized query using toolbox.executeQuery() from the CHECK BASE table, filtering by the parameters indexing the column being processed : database ID, table ID, and column ID, held by the resultRowData object. If no values are found (result set null), detailed information are logged in the job execution logging interface and likewise an InvalidIndexParametersException is thrown with the same information. Whereas if the query was successful and there is at least one row, through an iteration values are added to the list, finally the connection to the container instance of the database is closed as queries to general view won’t be executed anymore. Next, the query crafting is wrapped in the buildClusteringQuery method, including the build of the where condition and its appending to the whole final query private void buildClusteringQuery () throws SQLException , ClassNotFoundException , InvalidIndexParametersException { if ( values_list . size () > 1) { condition . setValues ( values_list ); } else { this . logger . info ("No expected values found in expected values table ( CHECK_BASE ) with the following parameters \n db_id : " + resultRowData . getDb () + "\n tb_id : "+ resultRowData . getTable () +"\n col_id : "+ resultRowData . getCol ()); throw new InvalidIndexParametersException("No expected 127 values found in expected values table ( CHECK_BASE ) with the following parameters ", resultRowData . getDb () , resultRowData . getTable () , resultRowData . getCol ()); } condition . setCol ( targetTableData . getCol () ); checkQuery += condition . getWhere (); } The where condition is build with the previously retrieved values list, after checking that the list actually contains at least one value, which is, by fact, a redundant control since in the previous method the values result set has already been checked in this view. The WhereCondition object exposes the list of values and the column name that is processed by the current iteration, along with the the relative getter and setter methods. Additionally, both a constructor with only values and one with the column as well is implemented. public class WhereCondition { private ArrayList < String > values ; private String column ; public WhereCondition ( ArrayList < String > values ) { this.values = values; } public WhereCondition (ArrayList < String > values , String column) { super (); this.values = values; this.column = column; } public String getCol() { return column; } public void setCol(String col) { this . column = col ; } public ArrayList <String > getValues () { return values; } public String getValue ( int index ) { return values . get ( index ); } public void setValues ( ArrayList < String > value ) { this . values = value ; } public String getWhereBindvar () { String where = ""; for (int i = 0; i < values .size (); i++) { if ( where . equals ("")) where += "SELECT "; 128 where += " ’?’ || sum ( case when ( TRIM ( UPPER (?) ) = ’?’ ) then 1 else 0 end ) || ’; ’|| "; } if (! where . equals ("")) where = where . substring (0 , where . length () -7); where += " AS "+ this . getCol ()+""; return where ; } public String getWhere () { String where = ""; for ( String val : values ) { if ( where . equals ("")) where += "SELECT "; where += " ’"+ val +":’ || sum ( case when ( TRIM ( UPPER ("+ this . getCol () +")) = ’"+ val . toUpperCase ()+"’ "; where += ") then 1 else 0 end ) || ’; ’|| "; } if (! where . equals ("")) where = where . substring (0 , where . length () -7); where += " AS "+ this . getCol ()+""; return where ; } } Where Condition Crafting is Implemented in the getWhere() method. Essentially, a specialized SQL-like query string that performs aggregation and formatting of results is built leveraging SQL language native constructs and clauses. Its crafting involves the iteration on the values list and for each value creates a CASE expression that counts occurrences of the value in the columns, by performing an SQL comparison with WHEN-THEN-ELSE-END clause, between the column normalized with TRIM and UPPER clauses, and the value likewise normalised with Java String object native methods. Hence, an iteration on all rows is integrated in the expression as the column name resolves to all values in the column, and if the column resolves to the value being checked, the expression resolves to 1, the sum of these resolutions is calculated with the SUM clause and equals by fact to the value occurrences in the column. The output is formatted in a flavor of JSON format : ‘value : count ; value : count’. The string ’value:’ is concatenated to the whole expression resolution leveraging the SQL ‘——’ text chaining operator, and the whole text is object of selection, with the SELECT clause, finally the result trailing test is removed and the whole text aliased with the column name, the result set is a column with the same name of the column being processed and one row, which contains lis of values occurrences in the format ‘value : count’ separated by ‘;’. A template of the resultant query is reported 129 below : SELECT ’VALUE_1 : ’ || SUM ( CASE WHEN ( TRIM ( UPPER ( ’NOME_COL ’)) = ’VALUE_1’) THEN 1 ELSE 0 END ) || ’;’ || ’VALUE_2 : ’ || SUM ( CASE WHEN ( TRIM ( UPPER ( ’NOME_COL ’)) = ’VALUE_2’) THEN 1 ELSE 0 END ) || ’;’ || ...... .... .. ’VALUE_3 : ’ || SUM ( CASE WHEN ( TRIM ( UPPER ( ’NOME_COL ’)) = ’VALUE_3’) THEN 1 ELSE 0 END ) AS NOME_COL All the unusual SQL clauses, operators and constructs are illustrated briefly below : •CASE : the case statement implements the general Switch-case paradigm, as it chooses from a sequence of conditions and runs a corresponding statement. In this scenario it is used with its searched flavour as defined by Oracle documentation as it evaluates multiple Boolean expressions and chooses the first one whose value is TRUE Figure 2.22: Execution Diagram Searched Case Statement [20] •SUM : returns the sum of values of expr. This function takes as an argument any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type Figure 2.23: Execution Diagram Sum Statement [21] 130 The algorithm workflow proceeds with the execution of this query which features the where condition previously built, with the implementation of the method. extractColumnEffectiveValuesClusters() private void extractColumnEffectiveValuesClusters() throws ClassNotFoundException , SQLException { if ( targetTableData . getTech () . equals ("DB2")) { db_addParams = ":securityMechanism=9; encryptionAlgorithm=2;defaultIsolationLevel=1;"; } this.targetTableConnection = toolbox.prepareDBConnection( databaseType . valueOf ( targetTableData . getTech () ), targetTableData . getHost () , targetTableData . getPort () , targetTableData . getSid () , db_addParams , targetTableData . getUsr () , targetTableData . getPwd () ); this . logger . info ( checkQuery + " FROM " + targetTableData . getSchema () + ".\" " + targetTableData . getTable () + "\" "); this . checkQuery += " FROM ?.\"?\" "; this . valuesClustersResultSet = toolbox . executeQuery ( this . targetTableConnection , checkQuery , targetTableData . getSchema () , targetTableData .getTable ()); } It passes exceptions raised by called methods to the outer scope(mask method) which will handle them gracefully as mentioned formerly. If the technology is DB2 a required string is added as additional parameters to execute properly the query. Next, a new connection is established to the target table database instance, if successful the query is first logged to the job logging interface for any future debugging activity and finally executed, appending target table schema and name held by the targetTableData object. The result text in a JSON flavoured format is then parsed. This parsing is implemented in a specific method parseEffectiveValues() private void parseEffectiveValues () throws SQLException { HashMap < String , String > values = new HashMap <String , String >(); if(this . valuesClustersResultSet != null && this. valuesClustersResultSet . next () ) { while (this . valuesClustersResultSet . next ()) { if( this. valuesClustersResultSet . getString (1) . split (":;", -1). length -1 != 2 && this . valuesClustersResultSet . getString (1) . split (":0", -1). length -1 != condition . getValues () .size ()) { // check verfiica almeno un match for ( String t: this . valuesClustersResultSet . getString (1) . split (";")) { if (! t. split (":") [1]. equals ("0")) values . put (t. split (":") [0] , t. split (":") [1]) ; 131 } } else { this. valuesClustersResultSet . close (); this . logger . info (" neither one match found or ’:; ’ bug occurred in results writing , raise problem to support"); writeEffectiveValues(" neither one match found or ’:; ’ bug occurred in results writing , raise problem to support"); } } retrievedValuesClusters . putAll ( values ); this. valuesClustersResultSet . close (); writeEffectiveValues(); } else { this . logger . info (" error in query to retrieve effective values execution "); writeEffectiveValues(" error in query to retrieve effective values execution "); } } nitially, a temporary HashMap type data structure for processed values is initialised. the result set is checked for it’s validity and not emptiness, in such scenario a detailed description of the faulty scenario is both logged to the job logging interface and written in the result column though the method writeEffectiveValues(), result set and connection are then detached. Likewise, if the format validation isn’t passed or no expected value has neither one occurrence, detailed description of the faulty scenario is both logged to the job logging interface and written in the result column always though the method writeEffectiveValues(). Contrarily, if checks are passed, results are processed, splitting based on elements and values separator, ‘;’ and ‘:’, values with zero occurrences are filtered out while values with at least one occurrence are encoded as key-value pairs in the temporary HashMap. Finally the Java native JSON object retrievedValuesClusters is valued with the crafted HashMap and the result set is detached. Finally results are assigned to the data structure referencing the genericData object, along with a date-time timestamp. This final write is implemented in the writeEffectiveValues() method. private void writeEffectiveValues () throws SQLException { this . resultRowData . getResult (). setValue ( ByteBuffer . wrap ( retrievedValuesClusters . toJSONString () . getBytes ( StandardCharsets . UTF_8 ))); this . resultRowData . getTimestamp (). setValue ( LocalDateTime . now () ); 132 toolbox . closeConnection ( this . targetTableConnection ); } // faulty scenario , desciption of the error is written private void writeEffectiveValues ( String errorString ) throws SQLException { this. resultRowData . getResult () . setValue ( ByteBuffer . wrap ( errorString . getBytes ( StandardCharsets . UTF_8 ))); this . resultRowData . getTimestamp (). setValue ( LocalDateTime . now ()); toolbox . closeConnection ( this . targetTableConnection ); } Similarly to some of the previous depicted methods, two version of the method are defined in order to handle both contrary scenarios. If it is called with a string as argument the latter is executed, otherwise the former. Nevertheless, the execution logic is specular, assign results or error through the data structure referencing genericData, assign timestamp and close the connection. Since the result property(GenericData) has been declared as Byte Object, the results or error string must undergoe a specific data transformation sequence before being actually assigned to genericData, which involves converting retrievedValuesClusters to JSON string, encoding the JSON string to UTF-8 bytes and wrapping bytes in a ByteBuffer for efficient handling. Once the mask method returns the genericData object, Delphix engine will apply its properties values to the corresponding columns in the results table. Service Discovery Java service discovery is used to determine which classes in the plugin JAR present relevant functionality to the Delphix Masking Engine. When a plugin is loaded, the file com.delphix.masking.api.plugin.MaskingComponent under META-INF/services in the JAR is consulted for a list of classes that implement the MaskingComponent interface. As MaskingAlgorithm includes this interface, each algorithm in the plugin will be discovered this way. If an algorithm class is missing from the services file, it will not be usable when the plugin is loaded. It is essentially invisible to the extensibility framework. If a class is mentioned in this file but not present in the JAR, the plugin will fail to load [6]. Hence such a file has been created in META-INF/services directory containing the value ‘com.sample.RansomCheck’ which is java format path to the file hosting the class that implements the MaskingAlgorithm interface. Plugin Security Plugin design and implementation considered to an high degree security concerns, as demonstrated by the hefty error handling part across the whole codebase. Faulty scenarios are mainly related to null data, or discrepancies between code expected and processed data types. 133 During execution, all plugin code is sandboxed using the Java Security Manager, being run with a Delphix defined security police [7]. Plugins are granted all permissions exceptfor few non-FilePermission like : •Communication to [localhost](http://localhost) through sockets : the java.net.SocketPermission class can’t accept, connect, listen or resolve connection with target localhost and any port. •VM settings modification : the java.lang.RuntimePermission class methods exitVM, createClassLoader, AccessClassInPackage.sun and setSecurityManager can’t be called •Security Police modification : the java.security.SecurityPermission class methods setPolicy and setProperty.package.access can’t be called. Whereas, regarding file permissions read access is granted to all, though write is only allowed for the masking user’s home directory (System.getProperty(“user.home”)) and the JVM’s default temp directory (System.getProperty(“java.io.tmpdir”‘)[7] The JSON document describing the configuration of each algorithm is stored encrypted on the Continuous Compliance Engine, and the actual values are made visible only to users with access privileges through the UI and web API. The whole codebase, both production and testing, can be consulted at the following public GitHub repository [13] Life cycle of custom algorithms loaded through plugins In the following paragraphs, the steps involved to execute the custom algorithm, once uploaded to the engine, are depicted. As mentioned, compliance engine feature an extendibility framework, which handle plugins from uploading to execution of contained algorithm. It relies on multiple software components, illustrated formerly, which are required to be implemented by the plugin. Three main phases in the life cycle of a plugin can be distinguished, from the engine outlook : Plugin discovery: the extensibility framework evaluates the capabilities present in a class implementing the MaskingAlgorithm interface : 1. Java object creation - an object of the algorithm class is created 2. getName - determines framework name 3. getDescription - determines framework description 4. getDefaultInstances - determines all plugin-provided algorithm instances. For each instance: (a) getName - determines instance name 134 (b) getDescription - determines instance description (c) validate - ensure object passes validation (d) Serialize configurable fields - these are saved as a JSON document defining the instance’s configuration (e) Disposal - the Java object is discarded 5. getAllowFurtherInstances - determines whether the framework is visible in the algorithm/framework API endpoint 6. Disposal - the Java object is discarded Algorithm configuration: a new instance of a plugin algorithm framework is created by uploading a configuration file. The algorithm definition is saved only if each step succeeds. 1. Java object creation - an object of the algorithm class is created 2. Configuration injection - the values in the user-provided JSON document are injected into the object 3. validate - the object’s validate method is called 4. Disposal - the Java object is discarded Algorithm use: finally the algorithm object is used to mask data. 1. Java object creation - an object of the algorithm class is created 2. Configuration injection - the saved JSON document defining this instance is injected in the object 3. setup - the setup method is called once 4. mask - the mask method is called on each value to be masked 5. tearDown - the tearDown method is called once 6. Disposal - the Java object is discarded 2.5 Orchestration Appliance The beating heart of this solution is with no surprise the orchestration unit. This unit has been properly developed to talk with all engines through their REST API to trigger replication to the control site, refresh of virtual database and control execution. Then, it executes a fundamental final query to evaluate control results and extract discrepancies. Eventually, if an amount of discrepancies higher than custom predefined threshold is revealed it alerts all stakeholders through pre-configured medias and delete the latest virtualised 135 data snapshot from the data engine in source site, otherwise data engine is instructed to provision fast recovery database.It plays a key role as it is the component enabling a full detection and response automation. The orchestration directives, being centralised, allow a central management. It has been purposefully built to offer a central point of management of the integrity control and set a proper context for any future expansion to further controls, of different kind. This is also the main reason why it couldn’t be implemented leveraging technologies, natively built for orchestration(Jenkins, Github Actions, GitLab CI). Being an appliance whose purpose is to orchestrate a process, implementing a workflow which handles all scenarios, the computational consumption is rather low and it is limited to the monitoring of single tasks execution. Besides in the scenario of several discrepancies revealed, where a result set with multiple rows must be parse to extract actual values and write them along with the column in the report, in such case an high speed is required to trigger the attack response as soon as possible. The design and implementation choices made guarantee reliability, security(both of data processed and executive), efficiency(both in term of memory capacity and compute time), interoperability, flexibility and indeed scalability. With this outlook, the appliance has been implemented in Python, fully functionally tested for Python3.9+ with version 3.12.8 highly recommended for seamless integration with dependant modules used version. It has been implemented and tested on a 64-bit ARM machine with Ubuntu 22.04, and even though it is meant to be always hosted on such machine, the final artifact is wrapped in a container which replicated the settings of the machine and include all its dependencies, being by fact a stand-alone package. Python has been chosen for its simplicity, readability, ease of integration, rich standard library and ecosystem, including its backing community which provide a wide range of modules and frameworks, but first of all for its by design affinity to automation and scripting contexts. These characteristics allowed myself to implement a solution that features : •Complete flexibility to the target database technology and architecture, leveraging third party database API modules maintained by the community to communicate with any Oracle, PostgreSQL, MySQL, Microsoft SQL or IBM database, and easily extendible with any other technology. All these modules are compliant to PEP-249 specification for database API design. •High degree of observability in the process by being transparent to the execution of tasks committed to peers, showing their progress and logging all steps. Furthermore, discrepancies are listed reported in a 136 continue os . system ( ’clear ’) print (" Controls Terminated ") Indeed, the execution starts with the parsing of the configuration json file, through a specifically crafted utility method ‘load config()’ def load_config (): try: with open(’ config . json ’,’r’) as cfg : cfg_dict = json_load ( cfg ) return cfg_dict except Exception as e: print (f" Error opening config : { str (e)}") Inside the context created by opening the configuration file with the python built in open procedure, it is deserialized leveraging JSON standard library load method, aliased as json load. It essentially returns a dictionary which models the configuration file elements, hence it will have controls, dpx engines, vdbs control and mail keys whose values are further dictionaries, and so on all nested dictionaries represent all the configuration data. Following the parsing, all retrieved controls, and therefore the whole execution is wrapped in a loop that first instantiate the control through a specifically crafted factory class and then effectively execute the instantiated control within the controlDatabase method. The control factory class responsibility is to strengthen the framework polymorphism by instantiating a control instance according to the retrieved control name. class controlFactory: def __init__ (self , controlName : str ): self . control = controlName def instance_control (self , control_data : dict , cfg_data : dict) -> None: try: match self . control . strip () . lower () : case ’ransomcheck’: return ransomCheck ( control_data = control_data , cfg_data = cfg_data) case _: # Default case to handle any unmatched control type raise Exception (f"Control {self.control} not supported ") except Exception as e: raise Exception (f" Error creating control instance { self. control }. \n Error : {str(e) if str (e) else e }") This design employs the factory pattern : the framework delegates the creation of controls to the factory, which based on the control name a control143 kind of factory is instantiated, and the respective control is then instantiated. The general factory expose a method which returns sub-classes(RansomCheck) that implement the general lower level ControlClass class ControlClass ( ABC ): @abstractmethod def __init__ (self , data : dict ) -> None : return @abstractmethod def start ( self ) -> None : return All control sub-classes must implement the constructor, control start and finish methods. Below is reported the implementation of the constructor by RansomCheck, called within the factory instantiation method. class RansomCheck ( ControlClass ): def __init__ (self , control_data : dict , cfg_data : dict ) -> None: for key , val in control_data . items () : setattr (self , key , val ) try: self . initialize_objs ( cfg_data = cfg_data ) except Exception as e: raise Exception (( str (e) if str (e) else f" Error initializing engines . \n Error : {e}")) The core class which exposes methods that implement the effective integrity check phases, ‘RansomCheck’, present a constructor that process two dictionaries, respectively control type specific details and all required resources details. First, control-specific details are assigned to the object leveraging the python built-in method setattr(), then all objects modelling required resources are instantiated and referenced by an object attribute, through the class method initialize objs def initialize_objs (self , cfg_data : dict ) -> None : try: cfg_source_engines = cfg_data . get (’dpx_engines’).get ( ’source_engines’) cfg_vault_engines = cfg_data . get (’dpx_engines’). get (’ vault_engines ’) cfg_disc_engines = cfg_data .get (’dpx_engines’). get (’ discovery_engines’) cfg_vdbs_control = cfg_data .get (’vdbs_control’) 144 if not ( isinstance ( cfg_source_engines , dict ) and isinstance ( cfg_vault_engines , dict ) and isinstance ( cfg_disc_engines , dict ) and isinstance ( cfg_vdbs_control , dict )): raise Exception (" Engines are not configured properly in config file ") for key , val in cfg_source_engines . items () : if key == self . sourceEngine : self . source_engine = DelphixEngine ( decrypt_value ( val [’host ’])) self . source_engine . create_session (val[ ’ apiVersion ’]) self . source_engine . login_data ( decrypt_value ( val [’usr ’]) , decrypt_value ( val [’pwd ’])) for key , val in cfg_vault_engines . items () : if key == self . vaultEngine : self. vault_engine = DelphixEngine ( decrypt_value ( val [’host ’])) self . vault_engine . create_session ( val[ ’ apiVersion ’]) self . vault_engine . login_data ( decrypt_value ( val [’usr ’]) , decrypt_value ( val [’pwd ’])) for key , val in cfg_disc_engines . items () : if key == self . discEngine : self . disc_engine = DelphixEngine ( decrypt_value ( val [’host ’])) self . disc_engine . login_compliance ( decrypt_value ( val [’usr ’]) , decrypt_value ( val [’pwd ’])) for key , val in cfg_vdbs_control . items () : if key == self . vdb : if isinstance (val , dict ): self . vdb_target = val . copy () self . vdb_target . tech = DBConnector . get_technology ( self . vdb_target . tech ) else: raise Exception (f" Error in config data : missing or wrong formatted vdb control details") if isinstance ( cfg_data . get (’mail ’), dict ): self .mail = cfg_data . get (’mail ’). copy () else: 145 raise Exception (f" Error in config data : missing mail details ") except AttributeError as e: raise Exception (f" Error initializing engines . Invalid config , missing parameter in control data ") except Exception as e: raise Exception (f" Error initializing engines . \n Error : {str (e) if str(e) else e}") It process a dictionary with further nested dictionaries and besides checking for a proper data formatting, it retrieves and instantiate the referenced source, vault, discovery engines and target virtual database. For each engine, a session is established and authentication is performed. Below are reported related engines class methods along with the constructor. class DelphixEngine : def __init__ (self , ip: str ): self.ip = ip self . base_uri = f" https ://{ ip }" self . session = Session () header = { "Content - Type ":" application /json " } self . session . headers . update ( header ) def create_session ( self , api_version : str ) -> Dict : uri = r" resources / json / delphix / session " major , minor , micro = api_version . split ( ’.’) data = { "type ":" APISession ", "version": { "type ":" APIVersion ", " major ": int ( major ), " minor ": int ( minor ), " micro ": int ( micro ) } } try: response = self . _post (uri , data ) return response . json () except RequestException as e: raise Exception (f" error creating session version { api_version } with engine { self .ip}, bad request likely . Response received {e. response }") except Exception as e: raise Exception (f" error creating session version { api_version } with engine { self .ip} \n Error : { str (e) if str(e) else e}") 146 For continuous data engines sessions are established through an HTTP post request to the endpoint provided in the documentation, leveraging Session class from requests library, which models an http session. In the body of the request, API version is declared. def _post (self , uri : str , data : Optional [Dict ] = None , key : Optional [ str] = None) -> Dict: api = f"{ self . base_uri }/{ uri }" try: response = self . session . post (api , json =data , verify = False ) except RequestException as e: raise RequestException ( response =e. response ) if " Authorization " in self . session . headers or uri == " masking / api / login ": if not response .ok or "errorMessage" in response . json (): raise Exception (f"{ uri }: { response . json () }\ ndata : { data } \n code : { response . status_code }") elif not response .ok or response . json (). get (’status’) == ’ERROR ’: raise Exception (f"{ uri }: { response . json () }\ ndata : {data }") return response This method is strictly mean for internal use in the class and shouldn’t be called by external classes, it wraps requests library ‘post’ method, crafting the uri and handling gracefully any faulty scenario with constructs try-catch, the object modelling the http response is returned. Two similar methods are implemented to perform authentication to both types of engines. def login_data ( self , username : str , password : str ) -> Dict : uri = r" resources / json / delphix / login " data = { ’type ’:’LoginRequest’, ’username ’: username . strip () , ’password ’: password . strip () , ’target’:’DOMAIN’ } try: response = self . _post (uri , data ) return response except RequestException as e: raise Exception (f" error logging in data engine { self . ip }, bad request likely . Response received {e. response }") except Exception as e: raise Exception (f" error logging in data engine { self . 147 ip} \n Error : {str(e) if str(e) else e}") def login_compliance (self , username : str , password : str ) -> str: uri = " masking / api / login " data = { ’username ’: username . strip () , ’password ’: password . strip () } try: response = self . _post (uri , data ) self . session . headers . update ({ ’ Authorization ’: response . json ()[" Authorization " ]}) return response . json ()[" Authorization "] except RequestException as e: raise Exception (f" error logging in compliance engine {self .ip}, Bad request likely . Response received { e. response }") except Exception as e: raise Exception (f" error logging in compliance engine \n Error : {str (e) if str (e) else e}") In both methods, login request uri and payload are crafted, before sending the post request, though while for the continuous data engine the response is simply returned, for the compliance the authorization header is parsed to retrieve the authentication token used in subsequent requests, basically a bearer token encoded in base64, adhering to REST API standard, as explained in the Delphix engine overview. Hence, once the control object has been completely instantiated, it is passed to the controlDatabase method, an utility method which provide a further level of abstraction, as it calls the starting point of all controls(start) def controlDatabase ( check ): print (f" Starting control { check . name } ") s_time = datetime . now () try : check . start () f_time = datetime . now () os . system ( ’clear ’) print (f" Finished control { check . name } in { f_time - s_time } seconds ") except Exception as e: raise Exception ( msg =( e. msg if e. hasattr ( ’msg ’) else f " Error executing control { check . name }: \n Error : {e}")) raise Exception (f"{ str (e) if str (e) else f" Error executing control { check . name }: \n Error : {e}"}") 148 It’s sole responsibility is to set a context for the control, including start e finish time for analysis. The start procedure is the execution starting point and all controls must implement it try: self . source_engine . replication ( self . replicationSpec ) self . vault_engine . refresh_control_vdb ( self . vdbContainerControl , self . dSourceContainer ) self . update_catalogs () self . disc_engine . mask ( self . jobId ) self . evaluate () if len (self . discrepant_values ) > self . max_discrepancies : self . register_report () self . create_report () self . send_alert () self . source_engine . delete_latest_snap ( self . dSourceContainer) self. register_discrepancies () self.stop() else: self . source_engine . refresh_recovery_db ( self . vdbContainerRecovery) self. update_expected_values () if not len (self . discrepant_values ) ==0: self . register_report () self . create_report () self. register_discrepancies () self . backup_report () except Exception as e: raise Exception (f" Error executing control . \n Error : {( str(e) if str (e) else e)}") The list of methods wrapped within this method implement the various control steps with the same degree of granularity used formerly in the control process. Hence, as explained the control starts with the replication of the dataset in the control scope to the control site. def replication (self , reference : str ) -> None : uri_repExe = rf" resources / json / delphix / replication / spec /{ reference }/ execute " uri_SourceState = r" resources / json / delphix / replication / sourcestate" try: job_id = ( self . _post ( uri_repExe )). json () [’job ’] uri_jobId = rf" resources / json / delphix / job /{ job_id }" with IncrementalBar ( message = rf " Preparing replication 149 job {( self . _get ( uri_jobId , key =’ result ’))[’ target ’]}", suffix=’%( index )d /%( max )d [%( elapsed )d / %( eta )d / %( eta_td )s] (%( iter_value )s) ’, color = ’blue ’, max =100) as bar: while self . filter_by_string ( self ._get ( uri_SourceState , key ="result"), reference , "spec ")[0]["activePoint"] is None : bar.next (1) with IncrementalBar ( message = rf " Waiting for replication {( self . _get ( uri_jobId , key =’ result ’)) [’ target ’]} to finish " , suffix=’%( index )d/%( max)d [%( elapsed )d / %( eta)d / %( eta_td )s] (%( iter_value )s)’, color = ’blue ’, max =100) as bar : while self . filter_by_string ( self ._get ( uri_SourceState , key ="result"), reference, " spec")[0]["activePoint"] is not None : bar.next (1) except RequestException as e: raise Exception (f" error executing replication job , Bad request likely . Response received {e. response } ") except Exception as e: raise Exception (f" error executing replication job \n Error : {str (e) if str(e) else e}") All methods that implement engines operations execution follow the same pattern, initially the required endpoints URI of the engine API are declared, a get or post request is sent with internal formerly covered methods and with the job ID returned in the response by the engine the job execution is monitored. Additionally, to offer a better user experience, the job execution is showed with a progress bar, implemented leveraging the progress module. Next, the target database is refreshed with the received virtual dataset. def refresh_control_vdb ( self , vdb_ref : str , dSource_ref : str ) -> None: uri_snap = rf" resources / json / delphix / capacity / snapshot " uri_refresh = rf" resources / json / delphix / database /{ vdb_ref }/ refresh " try: self . snaps = self . filter_by_string ( self . _get ( uri_snap , key="result"), dSource_ref , " container ") data = { "type ":"OracleRefreshParameters", "timeflowPointParameters": { "type ":"TimeflowPointSnapshot", " snapshot ": self . snaps [0][ ’snapshot ’] } } 150 job_id = ( self . _post ( uri_refresh , data )). json () [’job ’ ] uri_jobId = rf" resources / json / delphix / job /{ job_id }" with IncrementalBar ( message = rf " Waiting for refresh of { vdb_ref } to finish " , suffix =’%( index )d /%( max )d [%( elapsed )d / %( eta)d / %( eta_td )s] (%( iter_value )s)’, color = ’blue ’, max =100) as bar : while self. _get ( uri_jobId , key="result")[’ jobState’] != " COMPLETED ": bar.next (1) except RequestException as e: raise Exception (f" error executing vdb refershing job , Bad request likely . Response received {e. response }") except Exception as e: raise Exception (f" error executing vdb refershing job \n Error : { str (e) if str (e) else e}") This involves, first fetching the id of the latest data set snapshot, which is used also in some following methods. Once the latest source data snapshot has been provisioned to the control virtual database, all catalogs must be updated, as the new data set could have new data repositories or removed some. As illustrated formerly, the update of such catalogs is executed by stored procedures on the database. Triggering the execution of these procedures is implemented in a specific method def update_catalogs ( self ): try: with self . vdb_target . tech (** self . vdb_target ) as db_conn: for proc in self . procedures : db_conn . execute_procedure ( proc_name = proc ) except Exception as e: raise Exception (f" Error refreshing catalogs by triggering execution of stored procedures \n Error : {e} ") Just like with any other connection to the control database, the dictionary with database networking details is unpacked, with this data is created the database connector object whose class is referenced by the property ‘tech’ of the vdb target dictionary, contained method ‘execute procedure’ is covered later. Next, the custom masking algorithm, against expected values calculated at step t-1, is triggered. 151 def mask (self , jobId : str ) -> None : uri = " masking /api / executions " data = { " jobId ": jobId } try: execId = self . _post ( uri , data ). json () [’executionId’] uriExec = rf" masking / api / executions /{ execId }" with IncrementalBar ( message = rf " Waiting for control to finish", suffix=’%( index )d/%( max)d [%( elapsed )d / %( eta )d / %( eta_td )s] (%( iter_value )s) ’, color = ’ blue’, max =100) as bar : while self. _get ( uriExec , key="status") == " RUNNING": bar.next (1) if self ._get ( uriExec , key ="status") == " SUCCEEDED ": print (rf " Control completed successfully ") elif self ._get ( uriExec , key ="status") == "FAILED": print (rf " Error encountered during Control ") raise Exception (f" values control job with discovery engine failed . Check delphix dashboard ") except RequestException as e: raise Exception (f" error executing masking job to check values , Bad request likely . Response received {e. response }") except Exception as e: raise Exception (f" error executing masking job to check values \n Error : {str(e) if str (e) else e}") Finally, the algorithm results are evaluated against expected values def evaluate ( self ) -> None : try: start_time = datetime . now () with self . vdb_target . tech (** self . vdb_target ) as db_conn: self . discrepant_values = db_conn . execute_query ( query = """ SELECT DB_NAME , TABLE_NAME , COLUMN_NAME , VALUE , result , RES_ATTESO FROM \ ( SELECT DB_NAME , TABLE_NAME , COLUMN_NAME , VALUE , result , RES_ATTESO , \ CASE WHEN CAST( result AS VARCHAR (200)) = RES_ATTESO THEN 1 ELSE 0 END AS EVALUATION \ FROM C## control_user . CHECK_BASE CB LEFT JOIN ( \ WITH numbers AS( \ SELECT LEVEL AS n \ 152