General Personal Data Protection Act (LGPDP or LGPD) applied to SQL Server databases

It stands for the General Data Protection Act, which aims to increase the privacy of personal data and to prevent cases such as large leaks of information and scandals that involve the misuse of personal information that we have been following in recent years.

The creation of this law places Brazil in the list of more than 100 countries that today can be considered adequate to protect privacy and data use in the global scenario.

The theme mobilized Congress mainly after the leak of data from users of Facebook, one of the largest social networks, collected by the company Cambrigde Analytica and used in the last elections in the United States.

What is personal data?
According to the law, personal data is “information related to an identified or identifiable natural person” and sensitive personal data is “personal data about racial or ethnic origin, religious belief, political opinion, union membership or organization of a religious, philosophical or political nature. , data related to health or sexual life, genetic or biometric data, when linked to a natural person ”.

However, this concept is still very broad. Personal data can be any information that identifies a person, such as first and last name, mother's name, social security number, email, among others. In addition, it is also considered personal data if an information, when crossed with other data, allows to identify a person. That is, the account ID of a social network, such as Facebook, can be considered a personal data as well.

What is the purpose of LGDP?
LGPD's main goal is to ensure the privacy of personal data (especially sensitive data) and to force companies to use more controls to protect this information from intrusion, improper access and leakage of sensitive personal data.

In addition, the law creates clear rules about the processes of collection (user must agree to this), storage (in secure and encrypted locations) and sharing of this information (only with user authorization). Such consent shall be provided in writing or by other means demonstrating the holder's expression of will and may be revoked at any time.

Among its principles, the relevance of transparency for the use of personal data and its respective accountability, adequacy, that is, the compatibility of the use of personal data with the informed purposes, the protection of the user in all business architecture (of particular relevance) is of particular relevance. privacy by design), the purpose that data should only be used for the specific purposes for which they were collected and previously informed to their owners, and also the principle of necessity, which means limiting the use of the data to the minimum necessary in order to achieve the intended purpose, from which emerges the indispensable immediate deletion of data, after such purpose has been achieved.

Data Protection Officer
With LGDP, organizations should establish an Information Security Committee to review internal procedures and how data is being stored, collected, protected and shared. Within this body it is suggested that there is an exclusive data protection officer (Data Protection Officer), thus being responsible for compliance with this new law, handling complaints from owners and leading with any issues regarding data protection.

Controller, Operator and Charge
The law created so-called Personal Data Processing Agents - in the figures of the Controller and the Operator - who may be a natural or legal person, whether public or private. The first (controller) decides on the processing of personal data, while the second (operator) performs the processing on behalf of the first.

It was also defined the figure of the person in charge, who also as a natural or legal person, under public or private law, will act as a communication channel between the Controller and the personal data holders and the National Data Protection Authority (ANPD).

And what changes for users with this new law?
For service users, both online and offline, the biggest change is in access to information about their data. Once the law is in place, citizens will be able to know how companies, public or private, treat personal data:

• how data were collected
• why they collect your data
• how they store the collected data
• how long do you keep your data
• with whom they share

What changes for companies with this new law?
With the new Brazilian General Data Protection Act, all small, medium and large companies will have to invest in cybersecurity and implement effective compliance systems to prevent, detect and remedy personal data breaches, notably because the law provides that The adoption of a good practice policy will be considered as a mitigating criterion of penalties.
They are also guaranteed the right to data revocation, portability and rectification.

In addition, companies will be required to provide user information to users in a clear and simple way, where many already adopt this practice on their websites, but from LGDP this will be mandatory and no longer an option.

LGDP Exceptions
The only exceptions to the application of LGDP are the processing of personal data by a natural person for exclusively private and non-economic purposes, in addition to those performed solely for (i) journalistic, artistic or academic purposes (in which case consent is not waived), (ii) public security, national defense, state security or investigation and prosecution activities of criminal offenses or (iii) data in transit, ie those not intended for Treatment Agents in Brazil.

Information from minors
When it comes to a service or product intended for children, language should be age-appropriate, clearer and more understandable, but also directed to parents or guardians - including because consent is required in Article 20, 2, consent at least one parent or legal guardian for the processing of child and adolescent data.

Targeted Advertising
With LGDP, targeted advertising-based digital business models will need to be audited. For example, if a person buys a smart bracelet that measures heart rate, the purpose is to get information about their health. If the bracelet company decides to share the data with an insurance mark, the purpose of consent conflicts with the business interest.

Under today's rules, health insurance could offer a costly plan to a client knowing that he has heart problems, for example, and with LGDP this can no longer occur without the explicit consent of the user.

Subcontractors (companies or third parties)
LGPD also applies to these professionals, such as technology providers and partners. They are also subject to obligations and may make indemnity payments, for example.

What if my business doesn't fit? What happens ?
The LGDP provides sanctions for those who do not comply with good practice. They include warnings, fines and, in the worst case, a total or partial ban on data processing activities. Fines can range from 2% of the company's previous year's turnover to R $ 50 million and daily penalties.

What if even with all protections, there are data leaks in the company?
LGPD mandates that companies collect only the data necessary for the services they provide. In case of data leaks, the incumbent should inform the appropriate body and the owners (owners of the leaked data), which is already a best practice in this situation (even before the law), although companies often try to omit the leak. , further aggravating the situation.

When does this law come into effect ??
From the date of publication of the law (14 August 2018), companies have 24 months to adapt to the law, which will be August 2020.

It will be 24 months for the adequacy of companies and the main challenges that already arise are:

• appointment of a supervisor
• performing a data audit
• data mapping
• security policy review
• contract review
• Privacy Impact Report

A summary of LGPD

Firewall

A very important feature to prevent attacks and unauthorized access, Firewall is available in On-Premises (Windows Firewall and Third-Party Firewall) environments as well as Cloud environments and it serves to prevent unauthorized access to your database.

The idea of ​​the Firewall is to block accesses from IP's that are not on the list of allowed IP's, causing attackers to not get any type of access, regardless of the technique used by them, which in practice is a very effective security against various types of attacks .

Although it is not an activity specifically for the DBA (it is more of an infra), the DBA must have knowledge about how a Firewall works, how to configure it and analyze the logs. Especially in Cloud environments, scenarios in which the DBA ends up “inheriting” this responsibility to manage the Firewall rules of databases are common.

Care of brute force attacks

By storing virtually all customer and company data as a whole, databases are potentially one of the most sought after targets by attackers trying to steal information or simply gain privileged access to that bank for some other purpose.

Speaking specifically about SQL Server, there are a few ways to identify the occurrence of this type of attack and protect yourself from trying to log into the database using incorrect passwords over and over without a penalty for it. One such way is to enable SQL Server logging to log all failed login attempts for incorrect password. Keep an eye on the SQL log and always look for scenarios where there are a lot of login failures!

To learn more about brute force attacks in SQL Server and prevent such attacks, be sure to check out my article SQL Server - How to Avoid Brute Force Attacks on Your Database.

Care of permissions

Another very important point is regarding permissions on the database. You, as a DBA, should always cherish the lowest possible access rule for all users. Forget sysadmin and db_owner, always reinforce the security of your environment by granting the specific permission a user needs to perform their task (and periodically review permissions). If a system needs read access on 10 tables and write access on 5, this is the access you should allow, especially for integration users.

A scenario that makes information leakage much easier is applications that share the same database server and all have full access to the data. In the event of any security breach in one of these applications, the attacker will have access to all data from all applications on that database server, exponentially increasing the damage caused by this invasion.

Some articles that can help you increase the security of your environment:

• Checking a user's permissions in SQL Server
• SQL Server - Understanding the Risks of TRUSTWORTHY Property Enabled in a Database
• SQL Server - How to use auditing to map actual required permissions on a user
• SQL Server - How to disable “sa” login minimizing impacts
• SQL Server - How to Hide Databases from Unauthorized Users

Care of user passwords

Another extremely important topic for preventing data leaks in your business is ensuring the security of database user passwords. Although this is obvious in theory, in practice we see a great deal of neglect on this topic.

Although Microsoft recommends using Windows authentication as a best practice because all password control is the responsibility of Active Directory (AD) or the operating system (OS), in practice system users almost always use SQL Server authentication.

During consultations where I work, it is very common to see bank users for over 5 years without changing their password. That is, everyone who has had access to this password during this time, even if they have already left the company, still KNOW THE PASSWORD. I've seen cases where the company spent over 10 years without changing the main user password used by ALL applications and even business users had Excel spreadsheets with that username and password fixed there .. rs

To solve this problem of old passwords, I recommend turning on the Expiration property of all logins, so that SQL Server itself is responsible for assigning a password to users and forcing them to change their password periodically (default is every 180 days).

To prevent application user passwords from expiring in the middle of the day and impacting the environment, the DBA must create a schedule and schedule for periodically changing these passwords.

In addition to old passwords, we must pay attention to the complexity of passwords. No 123456 in database user passwords. Passwords must be large, complex, and difficult to crack by brute force attacks. Preferably, use password generators, including letters (upper and lower case), numbers, symbols, and length above 50 characters.

Need help identifying possible fragile passwords in your SQL Server? See how I can help you in the article SQL Server - How to identify fragile passwords, empty or equal to username.

Column Encryption with Always Encrypted

Feature since SQL Server 2016 in Express, Standard, Enterprise, and Developer editions (Express and Standard from 2016 SP1), Always Encrypted Lets you encrypt certain columns that physically hold sensitive data and customer people. Unlike Dynamic Data Masking, this solution is not simply data masking but an encryption solution applied to the original data, remaining encrypted in memory, physical files (MDF, LDF and NDF) and backup files.

Once Always Encrypted is applied, only users who have the encryption key can view the original data. This is the only protection that prevents even database administrator (sysadmin) users from viewing the original data.

Because of all security features, this solution is ideal for ensuring the privacy of your users' data, as the data is always encrypted and never trafficked unsafe (plain-text). If a person can access your data (MDF), log (LDF), or backup (BAK) files, they will not be able to access data that has been encrypted by this technology, even if they try to restore the files for use in another environment. .

This avoids a scenario of information leaking through physical access to database files. Another important factor that is avoided is internal data leaks, where employees themselves make information available. Using Always Encrypted, they will not be able to access this sensitive data even with full database access.

And finally, this feature will ensure that another application using the same server cannot see the original data.

Always Encrypted Example

To learn more about Always Encrypted, be sure to check out my article. SQL Server 2016 - How to Encrypt Your Data Using Always Encrypted.

Dynamic Data Masking

Feature present from SQL Server 2016, the Dynamic Data Masking allows you to mask data from certain database columns and prevent applications and users from accessing sensitive and personal user data.

Although the best option is to mask this in the application (because Dynamic Data Masking is not that secure), masking the data in the database is very quick to implement and will certainly make it difficult to improperly access personal data, either through an application as internal accesses made by other users, routines or tools.

By masking this data, we are preventing this information from circulating both outside the company (in an application, for example) and within the company (a BI area reporting, for example), as these users will not be allowed from UNMASK.

Note: This data masking does not change the original data, it just masks the display (SELECT).

Dynamic Data Masking Example

To learn more about Dynamic Data Masking, be sure to check out my article. SQL Server 2016 - Data Masking with Dynamic Data Masking (DDM).

Row level security - Row Level Security (RLS)

Another important feature of SQL Server that can help us decrease the likelihood of information leakage is the Row Level Security (RLS), available since SQL Server 2016, which allows you to limit the records that will be returned according to each user. This makes a manager, for example, only have access to customer data that they buy at that branch.

This type of restriction is described in the LGPD as a principle of necessity, which means limiting the use of data to the minimum necessary to achieve the intended purpose. If someone else gets access to the manager credentials in this example, the scope of customers they will have access to is much smaller than if they had access to the entire base.

Result:

SQL Injection Care

It is not today that attacks by SQL Injection concern companies around the world. Since the 90 years this technique has been used by attackers to hack into systems, alter and access customer data, orders, etc., using system programming breaches to force malicious commands and gain direct access to the database.

This very old and simple technique (both in execution and correction) to this day still causes many problems even in large companies and with LGPD this tends to be a risk to the privacy of user data, since when using SQL Injection, An attacker may have access to the personal data of all base clients if they are not properly masked / encrypted.

If you want to comply with audits and prevent your business from data leaks and system intrusions, this is a very critical point that should be urgently checked on all your systems (especially if the system dynamically mounts queries). in a string, without input validation and sends the string straight to the bank), as the effects of such an attack can be devastating.

After conversations with the security expert and also MVP, Alberto Oliveira, he warned me that to avoid application-side SQL Injection attacks, data entry validation is not enough, and therefore requires a more accurate code-building criterion, and may even have a WAF to ensure protection. beyond the code.

Result:

To learn more about SQL Injection and how to identify potential breaches and prevent this type of attack, check out my article SQL Server - How to Avoid SQL Injection? Stop using Dynamic Query as EXEC (@Query). Now..

Data Discovery and Classification

Very interesting feature that was made available in SQL Server Management Studio (SSMS) from the 17.5 version, the feature of Data Discovery and Classification (SQL Data Discovery and Classification) allows you to generate a report with possible sensitive data identified using an internal SSMS algorithm, where you define the criticality level of this information and what this information refers to, so that you could identify columns that could contain information confidential or interfere with compliance with various standards (HIPAA, SOX, PCI, and of course GDPR).

The wizard uses an algorithm to suggest columns that are likely to cause compliance issues, but you can add your own, tweak your suggestions, and eliminate any columns from the list. It stores these classiciations using extended properties; An SSMS-based report uses this same information to display the columns that have been identified. Out of the report, these properties are not highly visible.

In SQL Server 2019, there is a new command for this metadata, now available in Azure SQL Database, called ADD SENSITIVITY CLASSIFICATION. This allows you to do the same type of SSMS wizard, but the information is no longer stored as extended property, and any access to this data is automatically displayed in audits in a new XML column called data_sensitivity_information. Contains all types of information that were accessed during the audited event.

Connection Encryption (TLS)

Another way to protect against possible information leaks is by using encryption in the database connection and for this we can use the TLS feature so that data between the bank and the customer is not made available in text form. plain text.

In particular, companies involved in customer payment processing, in particular, must adhere to the PCI DSS (Payment Card Industry Data Security Standard). For PCI compliance, enterprise application needs to start using the Transport Layer Security (1.2) TLS protocol before 30 June 2018.

Prior to TLS 1.2, there were two other versions 1.1 and 1.0, also known as Secured Socket Layer (SSL), developed by Netscape. Particularly TLS 1.1 has been referred to by many as SSL V3. The need to switch to the new TLS 1.2 is to address the vulnerabilities posed by previous protocols.

To move applications using SQL Server to the new protocol, we would need to involve application developers with DBAs to make the transition.

There are 2 methods of encrypting connections:

• Self-Certification Usage: Prone to Man-In-Middle (MIM) attacks and should only be used in scenarios where all clients reside with the same domain.
• Certification Authority Certificates: These are certificates issued by the certification authority and should always be the preferred method.

To learn more about how to configure TLS on your SQL Server, I recommend reading of this article.

Azure Advanced Threat Protection

A very nice feature of Azure, which fits the requirement to notify concerned stakeholders and orphans about data leaks, is the Azure Advanced Threat Protection, which in addition to having a number of defenses against potential attacks, allows you to monitor users and their behavior.

Azure Advanced Threat Protection (ATP) is a cloud-based security solution that identifies, detects, and helps you investigate advanced threats, compromised identities, and malicious insider actions directed at your organization. Azure ATP enables SecOp analysts and security professionals struggling to detect advanced attacks in hybrid environments:

• Monitor users, entity behavior, and activities with learning-based analytics
• Protect user identities and credentials stored in Active Directory
• Identify and investigate suspicious user activity and advanced attacks throughout the cyber attack chain
• Provide clear incident information in a simple timeline for quick screening

Azure ATP Interface:

Master Data Services (MDS)

Maintains complete personal data and ensures that requests to edit, delete, or discontinue data processing are propagated throughout the system using Master Data Services with Microsoft SQL Server.

Using the concept of "golden record", the idea of ​​this service is to guarantee a single customer base and their personal data, so that all systems use and always keep that base updated. In case of request for rectifications or deletion of data at the client's request, it is enough to apply this change in just one place to be applied to all systems and routines of the company.

Data Audit

One of the pillars of LGPD is that any activity that manipulates personal data must be logged in and your company needs to answer the question of how user data was collected. Well, to answer that question, we can use Audit or Triggers features to identify data changes in tables and thus track when data for a particular customer has been entered / changed / deleted in the database, as well as changes made, registered system, hostname, IP and other information regarding the change made in the data of this client.

Some articles that can help you use these resources:

• SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
• Audit on SQL Server (Server Audit)
• SQL Server - How to Monitor and Audit Data Changes in Tables Using Change Data Capture (CDC)
• SQL Server 2016 - How to “Time Travel” Using the Temporal Tables Feature

Data Auditing in Azure SQL Database

Available for both Azure SQL Database and Azure SQL Data Warehouse, the database audit It is an Azure Portal feature that allows you to define filters and metrics to audit various information in your data and group them into audit categories (LoginFailed, LoginSucess, DataChanges, etc.).

More information in this link.

File Encryption with Transparent Data Encryption (TDE)

Another encryption solution, available since SQL Server 2008 in Enterprise and Developer editions, Transparent Data Encryption (TDE) It is a feature that encrypts SQL Server data files, which is known as at rest data encryption.

In a scenario where physical media (such as backup drives or tapes) is stolen, a malicious third party can restore or attach the database and browse the data. One solution to this is to encrypt sensitive data in the database and protect the keys used to encrypt the data with a certificate. This prevents anyone without the keys from using the data.

With essentially “a touch of magic”, all contents of MDF files, LDF files, snapshots, tempdb and backups are encrypted. Encryption occurs in real time, as data is written from memory to disk, and decryption occurs when data is read from disk and moved into memory. Encryption is done at the database level, so you can choose to encrypt as many databases as you want.

Always Encrypted	Transparent Data Encryption (TDE)
Column level	Database Level
Client Encryption (using a driver)	Server Encryption (Database Engine)
Server does not know encryption keys	Server knows encryption keys
Data in memory is encrypted	Data in memory is unprotected (plain-text)
Data on the network is encrypted	Data on the network is unprotected (plain-text)
Only users with key access can view the original data. Not even the DBA can view the original data without the key.	DBA can view original data without key
Backups and log files are encrypted	Backups and log files are encrypted
Requires application changes (may be small or large depending on the encryption algorithm you choose)	No app changes required
Available from SQL Server 2016 - All editions, up to Express (Express and Standard from 2016 SP1)	Available from SQL Server 2008 - Enterprise and Developer Only

To learn more about Transparent Data Encryption, be sure to check out my article SQL Server 2008 - How to Encrypt Your Data Using Transparent Data Encryption (TDE).

Encrypted Backups

Still talking about how to store customer data, we can not fail to mention the feature of Backup Encryption, which, as its name suggests, applies an encryption algorithm to the generated backup file, preventing unauthorized persons from restoring backup files on instances that do not have a valid certificate and have access to the original data.

Cloud Backup

Another solution that can improve the security of your business is to use the feature of BACKUP TO URLby having your database backups created directly from the Microsoft (Azure) cloud.

This improves the security of your data by preventing third parties from gaining access to database backup files as the files are written to the cloud and may not even be stored on your corporate network, which may be susceptible to intrusion by network attacks, physical access, employee data leakage, etc.

Meanwhile, Azure has a specialized security team to address this issue, including data redundancy and barring any kind of attempted attack, as well as allowing passwordless authentication through Azure Active Directory.

Cloud Bank

One topic that is constantly under discussion among IT professionals is about Cloud Computing, but people are starting to agree on how efficient and practical it is to migrate and store databases in the cloud, and with SQL Server that's not. is different.

Azure offers a range of security features to prevent any kind of attack (even brute force) on your data, which is being protected by a team of Microsoft Security experts, working in both the physical, networking, firewall and security. also mitigating possible attempts to break into storage, operating system and others.

All of this allows us to comply with LGPD good practices by keeping our data more difficult to leak and accessed by unauthorized persons who end up being barred by multiple levels of Azure security.