Friday, October 13, 2023

A Beginners Guide To MySQL Replication Part 6: Security Considerations in MySQL Replication

This article is part of Aisha Bukar's 6 part series: A Beginners Guide to MySQL Replication. The entries include:

Protecting and controlling access to your data against unauthorized person(s) is crucial in any organization. Unauthorized entry or modification of your data can lead to severe and sometimes irreversible damage.

Just like we mentioned at the beginning of this series, MySQL Replication is a process where data from one MySQL database known as the source is copied over to one or more other databases called replicas. It is possible for data to get breached during this process.

When setting up replication in MySQL, it is essential to understand the security issues that may arise as well as understand ways to counteract them. Choosing the suitable security method for your server instances and setting up ways to recover corrupted or lost information is necessary. In the last part of this series, we will look at security risks, best practices, and backup and disaster recovery methods in MySQL replication. 

Importance of Security in Database Systems

Security in database systems cannot be overemphasized especially at a time when careless exposure of data could lead to serious damages. The amount of data stored in databases has significantly increased due to the rapid development of technology around the world, therefore, careful structure needs to be put in place to avoid these data falling into the wrong hands. There are so many factors to consider when looking at security in databases such as data protection, accountability, compliance, and so on. Yet, all of these factors can be distilled into these four essential categories, which are:

Confidentiality

 Keeping your data private and preventing unauthorized access to these data is super important. Data confidentiality aims to protect your data from unwanted access. Neglecting data confidentiality can lead to privacy violations, financial setbacks, legal repercussions, and harm an organization’s reputation. Employing security measures such as access controls, encryption, and login checks helps safeguard confidentiality, they are like your data’s bodyguards.

Integrity

Data integrity is all about ensuring that data remains accurate and reliable from the moment it’s first generated or input into a system, right through its entire life cycle. This is especially crucial in fields like finance, healthcare, and research, where the precision and trustworthiness of data are paramount for making informed decisions and complying with regulations To safeguard data integrity, it’s important to have regular data backups and a robust recovery plan in place. These measures can prove invaluable in restoring data to its accurate and consistent state in the event of data loss or corruption.

Availability

 Data availability simply means making sure you can get your data when you want it. Its primary goal is to guarantee that data remains consistently accessible and functional. To maintain data availability, it’s crucial to regularly generate data backups and establish disaster recovery strategies. This is effective in the case of data loss. Also, enforcing strong security measures like firewalls and access control methods helps safeguard data availability and protect against unauthorized entry.

Authenticity

Data authenticity is all about making sure the information in a database is real, correct, and reliable. We need to check that no one has changed it without permission and that it truly shows the right things. Organizations use this data to make big decisions. If the data isn’t authentic, they might make choices that can be harmful. Keeping data authentic is a big part of keeping it safe and making sure it stays authentic.

Security Risks in MySQL Replication

While MySQL Replication is a powerful feature for data transfer and availability, it still introduces different potential security risks that need to be considered and mitigated. Here are some of the security risks you may encounter in MySQL replication:

  1. Unauthorized access to replication data: When an attacker gains access to a replication stream, they can intercept sensitive data that is being replicated between servers. This sensitive data could be passwords or confidential files or maybe customer data. This could lead to compromising existing data by modification, or sometimes even deletion of the data.
  2. Authentication and Authorization: If your MySQL credentials are mishandled, it opens the door for attackers to infiltrate your databases, including gaining access to replication privileges. When an attacker gets hold of replication privileges, they can potentially increase their control over the target server, which can result in more serious security breaches. Ultimately, this could lock out authorized users from accessing the database. 
  3. Network Security: If there are no adequate encryption methods in place such as SSL/TLS, the data traveling between MySQL servers during replication becomes vulnerable to interception. This means that if an attacker has access to the network, they can secretly monitor the replication traffic. In the absence of encryption, attackers could even go as far as intercepting (man-in-the-middle attacks) and altering the replication data packets, which can result in data tampering or unauthorized modifications.
  4. Vulnerabilities in Replication Protocols: Just like any software, MySQL replication itself may be susceptible to some vulnerabilities that attackers can exploit to disrupt the replication process or the entire database system. Regularly updating MySQL and monitoring security performance is essential in addressing these issues.

Best Practices for Securing MySQL Replication

Understanding the potential risks that could occur in a replication topology is one thing, knowing how to counteract these risks is another. There are standard methods available for securing replication, here are some of the best practices:

Implement SSL/TLS encryption

SSL/TLS (Secure Sockets Layer/Transport Layer Security) encryption safeguards the data exchanged between MySQL servers by encrypting it, thus preventing unauthorized monitoring and unauthorized alterations. To use SSL/TLS encryption for replication traffic, you must configure MYSQL replication to use these encrypted connections by taking advantage of OpenSSL. OpenSSL is a freely available software library and set of tools that offers comprehensive support for SSL/TLS protocols, as well as an extensive array of cryptographic features and utilities. Here’s a very basic step on what you need to do:

  1. Generate a security certificate and the necessary key files using the OpenSSL command line and provide the necessary information. 
  2. Move the certificate and key files to the appropriate location
  3. Add the certificate and key file-related parameters to the [mysqld] section of the MySQL configuration file (my.cnf or my.ini). Here’s an example of how this may look like:     [mysqld]
    ssl-ca=/etc/mysql/ssl/mysql-cert.pem
    ssl-cert=/etc/mysql/ssl/mysql-cert.pem
    ssl-key=/etc/mysql/ssl/mysql-key.pem
  4. Restart the MySQL server to apply the configuration changes.
  5. Create an equivalent certificate from the identical certificate authority generated on the source server for each replica.
  6. Add the appropriate file parameters to the “CHANGE REPLICATION SOURCE” or “CHANGE MASTER” statement. Here’s an example of how this should look like:                                 CHANGE MASTER TO
    MASTER_HOST='source_server_hostname',
    MASTER_USER='replication_user',
    MASTER_PASSWORD='replication_password',
    MASTER_LOG_FILE='mysql-bin.xxxxxx',
    MASTER_LOG_POS=xxx,
    MASTER_SSL=1,
    MASTER_SSL_CA='/path/to/replica-ca-cert.pem',
    MASTER_SSL_CERT='/path/to/replica-cert.pem',
    MASTER_SSL_KEY='/path/to/replica-key.pem';
  7. After configuring the replication source on each replica, start the replication process using the ‘START REPLICA’ or ‘START SLAVE’ command.

This is just a basic guide on how to implement SSL encryption for replication environments. For more information on obtaining an SSL certificate and key files using OpenSSL, please visit the official documentation.

Strong Authentication and Authorization

It is advisable to use complex passwords for your MySQL root accounts.  Avoid the use of default usernames or passwords, rather take advantage of password policies that implement different combinations of numbers, letters, and symbols. An important guideline mentioned in the MySQL documentation is also to: “Restrict access to the ‘user’ table in the ‘MySQL’ system database to MySQL root accounts ONLY”. This grants the necessary permissions only to the appropriate users.

Role-Based Access Control (RBAC) can be a valuable approach in MySQL replication. RBAC can improve security by reducing the potential for human error in assigning and managing permissions. It also makes it more difficult for malicious users to gain unauthorized access. Here are some ways to consider implementing RBAC for enhanced security in MySQL replication:

  1. Clearly define specific replication roles based on the responsibilities and privileges required for replication tasks. For example, you might have roles like “Replication Master,” or “Replication Slave”.
  2. Determine the specific privileges each replication role should have. For example, a “Replication Master” role might have privileges for creating and modifying replication configurations, while a “Replication Slave” role might have read-only privileges for replication monitoring.
  3. Users involved in replication should be assigned the relevant replication roles, while users with other database responsibilities should be assigned appropriate database roles.
  4. Conduct regular reviews of role assignments and privileges to ensure they align with the current requirements and responsibilities of users involved in replication.
  5. Ensure that each role is assigned the minimum necessary privileges to perform its tasks. This reduces the risk of unintended actions and security breaches.

Implement network security measures

Implementing network security measures is an important aspect of security in MySQL replication. Firewalls are important when it comes to managing incoming and outgoing traffic to and from MySQL servers engaged in replication. Below are key firewall strategies to consider:

  1. IP Whitelisting: Employ the practice of IP whitelisting to exclusively permit replication traffic from trusted IP addresses or specific subnets. This involves configuring firewall rules that exclusively authorize connections originating from the designated IP addresses of validated MySQL servers, while actively blocking access from all other sources.
  2. Port Restriction: Limiting the exposure of MySQL replication ports enhances security, typically port 3306 for MySQL. By doing so, you confine access to the MySQL service solely to systems that have been explicitly authorized.
  3. Logging and Monitoring: Enable the logging and monitoring features of your firewall to actively observe network traffic. This vigilant approach allows for the identification of any unauthorized or suspicious connection attempts, enabling timely detection and response to potential security threats.

It is also advisable to incorporate a Virtual Private Network(VPN). Using a VPN for replication traffic introduces an additional security layer. A VPN typically establishes a secure, encrypted tunnel across the public network, guaranteeing the confidentiality and security of replication data as it traverses the network.

Backup and Disaster Recovery

Preparing for disaster recovery and regularly backing up databases is crucial in case unexpected accidents or disasters occur. MySQL offers replication features such as asynchronous replication, which can be used for disaster recovery. In this setup, the replica server(s) replicates data from the source server. If the source server fails, the replica can take over.

MySQL offers different types of backup methods such as physical backups, logical backups, online backups, offline backups, and snapshot backups, to name a few. It’s important to know some key differences between these backup methods.

For example, logical backups are made using tools like mysqldump to generate SQL files containing both the database schema and its data. Although these files are easy for humans to read, they may perform more slowly for large databases. On the other hand, physical backups entail copying the actual database files, resulting in a faster backup and restore process. However, they may not be as easily transported as logical backups.

Another example would be snapshot backups. Snapshot backups are like quick pictures of your MySQL database that show exactly how it looked at one specific moment in time. For more information on backup and recovery methods, please visit the MySQL official documentation.

Conclusion

At this point, I can safely say it’s crystal clear how security is of paramount importance in MySQL replication. I’d like to express my gratitude for your unwavering support from the start to the very end of this series. I hope you’ve understood how replication works and can put it into practice in real-world scenarios. Thank you for reading!

 

 

 

 

 

 

 

The post A Beginners Guide To MySQL Replication Part 6: Security Considerations in MySQL Replication appeared first on Simple Talk.



from Simple Talk https://ift.tt/XtdbJ9u
via

Tuesday, October 10, 2023

PostgreSQL Indexes: What They Are and How They Help

In the previous blog in this series, we learned how to produce, read and interpret execution plans. We learned that an execution plan provides information about access methods, which PostgreSQL use to select records from a database. Specifically, we observed that in some cases PostgreSQL used sequential scan, and in some cases index-based access.

It might seem to be a better idea to talk about indexes before we discussed execution plans, but query plans are a good place to start the performance journey and work our way in! This blog is going to be about indexes, why we need them, how they can help, and how they can make things worse.

What is an index?

What is an index? One might assume that any person who works with databases knows what an index is. However, a surprising number of people, including database developers and report writers and, in some cases, even DBAs, use indexes, even create indexes, with only a vague understanding of what indexes are and how they are structured. Having this in mind, let’s start with defining what is an index.

Since there are many different index types in PostgreSQL (and new index types are constantly created) we won’t focus on structural properties to produce an index definition. Instead, we define an index based on its usage. A data structure is called an index if it is:

  • A redundant data structure
  • Invisible to an application
  • Designed to speed up data selection based on certain criteria

Let’s discuss each of the bullet points in more detail.

Redundancy means that an index can be dropped without any data loss and can be reconstructed from data stored in tables (the postgres_air database dump we will use with the examples comes without indexes, but you can build them after you restore the database. Full instructions are in the appendix of this article to create the database you will need if you wish to follow along with the examples.)

Invisibility means that a user cannot detect if an index is present or absent when writing a query other than the time the query takes to process. That is, any query produces the same results with or without an index.

Finally, an index is created with the hope (or confidence) that it improves performance of a specific query or (even better!) several queries. Although index structures can differ significantly among index types, the speed-up is achieved due to a fast check of some filtering conditions specified in a query. Such filtering conditions specify certain restrictions on table attributes.

Figure 1 shows how an index can speed up the access to the specific table rows.

A table with blue lines and black text Description automatically generated with medium confidence

Figure 1. Index access

The right part of Figure 1 shows a table, and the left represents an index that can be viewed as a special kind of a table. Each row of the index consists of an index key and a pointer to a table row. The value of an index key usually is equal to the value of a table attribute. The example in Figure 1 has airport code as its value; hence, this index supports search by airport code.

For this particular index, all values in the airport_code column are unique, so each index entry point to exactly one row in the table. However, some columns, like the column city in the airport table, can have the same value in multiple rows. If this column is indexed, the index must contain pointers to all rows containing this value of an index key. That is, a key may be logically associated with a list of pointers to rows rather than a single pointer.

Figure 1 illustrates how to reach the corresponding table row when an index record is located; however, it does not explain why an index row can be found much faster than a table row. Let’s take a closer look and find out.

Index Types

PostgreSQL supports a lot of types of indexes.

For this article, I am going to only discuss B-Tree indexes because they are the most common index structure you will typically use. There are other types of indexes you can add to tables, but the B-Tree is the most common and what I will focus on for this article. (Note that later you will see a bitmap index show up in the query plan, but it is different than a typical index).

The basic structure of a B-tree is shown in Figure 2, using airport codes as the index keys. The tree consists of hierarchically organized nodes that are associated with blocks stored on a disk.

Records in all blocks are ordered, and at least half of the block capacity is used in every block.

A diagram of a diagram Description automatically generated

Figure 2. An Example of a B-Tree

The leaf nodes (shown in the bottom row in Figure 2) contain index records exactly like those in Figure 1; these records contain an index key and a pointer to a table row. Non-leaf nodes (located at all levels except the bottom) contain records that consist of the smallest key (in Figure 2, the lowest alphanumeric value) in a block located at the next level and a pointer to this block.

Any search for a key K starts from the root node of the B-tree. During the block lookup, the largest key P not exceeding K is found, and then the search continues in the block pointed to by the pointer associated with P until the leaf node is reached, where a pointer refers to table rows. The number of accessed nodes is equal to the depth of the tree. Of course, the key K is not necessarily stored in the index, but the search finds either the key or the position where it could be located.

B-Tree indexes provide the widest variety of uses (for example, single row and ordered range lookups), and can be built for any data type for which we can define ordering (otherwise called ordinal types), this is where I will start in this series.

When Indexes are Useful

Based on what we learned about indexes so far, would we say that indexes can help to speed up any query? Absolutely not! Whether indexes will be useful or not for a specific query, mostly depends on what type of query we are talking about. It should be no surprise that indexes are most useful for short queries.

But wait – we need to produce one more definition here! Which queries are considered short? Does it depend on the size of a query? The code of the below query is very short; does it mean it’s a short query?

SELECT 
     d.airport_code AS departure_airport,
      a.airport_code AS arrival_airport
FROM  airport a,
      airport d;

Spoiler alert – it is not! This query produces a Cartesian product of two copies of the airport table, generating a domain of all possible flights.

The next query is “longer”:

SELECT 
       f.flight_no,
       f.scheduled_departure,
        boarding_time,
        p.last_name,
        p.first_name,
        bp.update_ts as pass_issued,
        ff.level
FROM flight f
   JOIN booking_leg bl ON bl.flight_id = f.flight_id
   JOIN passenger p ON p.booking_id=bl.booking_id
   JOIN account a ON a.account_id =p.account_id
   JOIN boarding_pass bp 
       ON bp.passenger_id=p.passenger_id
   LEFT OUTER JOIN frequent_flyer ff 
       ON ff.frequent_flyer_id=a.frequent_flyer_id
WHERE f.departure_airport = 'JFK'
      AND f.arrival_airport = 'ORD'
      AND f.scheduled_departure BETWEEN
                  '2023-08-05' AND '2023-08-07'

However, it is a short query because the result contains just a handful of rows.

Maybe, it’s the number of rows in the output that matters? Wrong again! Look at the next query:

SELECT 
   AVG(flight_length),
   AVG(passengers)
FROM (SELECT 
         flight_no,
         scheduled_arrival -scheduled_departure 
                                 AS flight_length,
         COUNT(passenger_id) passengers
      FROM flight f
      JOIN booking_leg bl ON bl.flight_id = f.flight_id
      JOIN passenger p ON p.booking_id=bl.booking_id
GROUP BY 1,2) a

This query produces the output of exactly one row, however, it is a long query because we need to go through all records in flight, booking_leg and passenger tables to obtain the result.

Now, we are ready for a definition:

A query is short when the number of rows needed to compute its output is small, no matter how large the involved tables are. Short queries may read every row from small tables but read only a small percentage of rows from large tables.

How small is a “small percentage”? It depends on system parameters, application specifics, actual table sizes, and possibly other factors. Most of the time, however, it means less than 10%. ratio of rows that are retained to the total rows in the table. This ratio is called selectivity.

Figure 3 presents a graph which shows a dependency between selectivity of the query and a cost of data access with and without indexes.

A graph with colored lines Description automatically generated

Figure 3. Cost vs Selectivity graph

Note, that PostgreSQL provides two different index-based access methods both of which will be covered in this series of blog posts.

The graph in Figure 3 makes it evident why indexes are most useful for the short queries. For low – selectivity queries, we need to read a small number of rows, thus, the extra cost of additional access to the index pays off. The lower is a query selectivity, the more efficient indexes are. For queries with high selectivity, the cost of additional access to index quickly makes it more expensive than sequential scan.

Execution plans for short queries

When we optimize a short query, we know that in the end, we select a relatively small number of records. This means that the optimization goal is to reduce the size of the result set as early as possible. If the most restrictive selection criterion is applied in the first steps of query execution, further sorting, grouping, and even joins will be less expensive. Looking at the execution plan, there should be no table scans of large tables. For small tables, a full scan may still work.

You can’t select a subset of records quickly from a table if there is no index supporting the corresponding search. That’s why short queries require indexes on larger tables for faster execution. If there is no index to support a highly restrictive query, one needs to be created. It might seem easy to make sure that the most restrictive selection criteria are applied first; however, this isn’t always straightforward.

Let’s look at the following query:

SELECT * 
FROM flight
WHERE departure_airport='LAX'
  AND update_ts BETWEEN '2023-08-13' AND '2023-08-15'
  AND status='Delayed'
  AND scheduled_departure 
          BETWEEN '2023-08-13' AND '2023-08-15'

Can you tell which selection criteria will be the most restrictive? Not without

Delayed status might be the most restrictive, because ideally, on any given day, there are many more on-time flights than delayed flights.

In our training database, we have a flight schedule for six months, so limiting it by two days might not be very restrictive. On the other hand, usually the flight schedule is posted well in advance, and if we are looking for flights where the timestamp of the last update is relatively close to the scheduled departure, it most likely indicates that these flights were delayed or canceled.

Another factor that may be taken into consideration is the popularity of the airport in question. LAX is a popular airport, and for Listing 5-1, a restriction on update_ts will be more restrictive than on departure_airport.

Let’s look at the execution plan!

A screenshot of a computer Description automatically generated

We can see that PostgreSQL query planner took advantage of three indexes on the flight table and performed bitmap index scan – a special kind of index-based data access which I will cover in detail in the next post. In short, the query planner uses three indexes: flight_departure_airport, flight_update_ts and flight_scheduled_departure. Scanning these three indexes helps to identify the blocks which can potentially contain the records which satisfy the search criteria, and then examines these blocks to recheck whether the conditions are indeed met.

What is important for us now is that none of the existing indexes had a clear advantage over others. Does it mean we didn’t build the most restrictive index? Possibly that’s true, we will keep experimenting with additional indexes.

What if we will restrict the time interval even further?

SELECT * 
FROM flight
WHERE departure_airport='LAX'
  AND update_ts 
       BETWEEN '2023-08-13' AND '2023-08-14'
  AND status='Delayed'
  AND scheduled_departure 
         BETWEEN '2023-08-13' AND '2023-08-14'

Do you think the execution plan will stay the same or will it change?

Actually, it will!

A screenshot of a computer Description automatically generated

Now, the conditions on both update_ts and scheduled_departure became significantly more restrictive than on departure airport, so only these two indexes will be used.

How about changing the filtering on departure_airport? If we change it to FUK, the airport criterion will be more restrictive than selection based on update_ts and execution plan will change dramatically:

As you can see, if all the search criteria are indexed, there is no cause for concern, PostgreSQL will choose the optimal execution plan. But if the most restrictive criterion is not indexed, the execution plan may be suboptimal, and likely, an additional index is needed.

Performance overhead

The performance improvement does not come for free. As an index is redundant, it must be updated when table data are updated. That produces some overhead for update operations that is sometimes not negligible. However, many database textbooks overestimate this overhead. Modern high-performance DBMSs use algorithms that reduce the cost of index updates, so usually, it is beneficial to create several indexes on a table.

Did you have fun exploring indexes in PostgreSQL? Would you like to learn more about indexing? Look for the next up in this series.

Appendix – Setting up the training database

The instructions for installing the base database can be found in the Appendix of the first article in this series. If you are starting here, use those instructions to download the database.

The experiments in this article require you to create the following additional indexes on your copy of the postges_air database:

SET search_path TO postgres_air;

CREATE INDEX flight_arrival_airport 
     ON flight  (arrival_airport);

CREATE INDEX booking_leg_flight_id 
     ON booking_leg  (flight_id);

CREATE INDEX flight_actual_departure 
     ON flight  (actual_departure);

CREATE INDEX boarding_pass_booking_leg_id 
     ON boarding_pass  (booking_leg_id);

CREATE INDEX booking_update_ts 
     ON booking  (update_ts);

Don’t forget to run ANALYZE on all the tables for which you build new indexes:

ANALYZE  flight;

ANALYZE  booking_leg;

ANALYZE  booking;

ANALYZE boarding_pass;

 

The post PostgreSQL Indexes: What They Are and How They Help appeared first on Simple Talk.



from Simple Talk https://ift.tt/mPqMpSQ
via

Friday, October 6, 2023

The Most Valuable, Undesirable Thing: Feedback

In 1964, the Beatles added something interesting to one of their songs. I Feel Fine started out with a twang of feedback. It wasn’t planned to happen, but by a happy accident, the first use of feedback in a song was created (and it wouldn’t be the last, which you know if you have ever heard of Jimi Hendrix.) 

Of course, the feedback I want to discuss today has nothing to do with audio looped circularly through a speaker, amp, and microphone. Instead, I want to talk about the feedback we get from others about the work we do. 

Early in my career, in fact in my second job as a DBA, I had a boss who, for the life of me I couldn’t read. I rarely received feedback from this person, and I didn’t really know if I was doing a good enough job. One day, he called me into his office, had me close the door. He instructed me to have a seat and he put a piece of paper on the table. I was still in my 20s, so the cardiac event I experienced was mild, but it was still memorable. I had just been…given a substantial raise. Thank you for doing a good job.

Since then, I have given and received a lot of feedback, and I often think back to that day as I am giving it. I wasn’t sure what was about to happen and that is not how it should be. The more feedback you can get and give the better. No one should be unsure as to where they stand. At the very least, no one should ever have even a mild cardiac event when they are getting a raise.

While I have never been a professional manager before (something I decided after my attempts at being an assistant manager at a Wendy’s long before I moved into tech), I have always desired to get feedback on the job I am doing (even if it still cares me!). Good or bad, it is better to know how you are doing in your job, or really in your life.

One place where I do tend to give and get a lot of feedback is in writing. As the editor of the Simple-Talk website, I regularly give feedback to writers. I do my best to give honest, useful, kind, but occasionally harsh feedback. As a writer myself, I know the value of this…the goal is a great product and sometimes it hurts to hear what you have written is not in that category. (It happens to the best of us!)

The most important adjective in the previous paragraph was kind. Sometimes feedback does needs to be harsh. But my first experience as a writer was on a book with 10 technical editors and a few development editors. They beat me up so much in a way that while it did make the book better, it occasionally really hurt my feelings. When it was all said and done, I never wanted to be someone who delivered feedback in this manner. Nitpicky feedback that strives for the best outcome doesn’t have to feel like a bat to the back of the head.

So anytime you give feedback to people, try your best to do it in the kindest way possible, but also the most honest too. People you work with and for just want to know how they might improve.

The post The Most Valuable, Undesirable Thing: Feedback appeared first on Simple Talk.



from Simple Talk https://ift.tt/vMlCS52
via

First Normal Form Gets No Respect

Dr. Codd first described the relational model in a paper in Communications of the ACM (CACM 13 No 6; June 1970). Some more work followed up after that by other people, giving us normal forms and other things we have taken for granted for 50+ years. 

I was looking at some postings on a SQL newsgroup. The original poster wanted help with a problem he had storing International Classification of Diseases (ICD) values. Alternatively, to put it more accurately, he needed help fixing the problems he had created for himself. The non-table he was creating had a single text column that concatenated exactly four ICD codes.

Without going into much detail,

these are codes used by hospitals, insurance companies, and other places where we need a “Dewey decimal of death and despair” for reporting. They are a mixture of alpha and numeric characters, with minimum punctuation. It should come as no surprise because that is the standard format for many ISO standards. Thanks to Unicode, this limited set of symbols can be used with any alphabet or symbol system allowed in Unicode. The following regex pattern would match 2021 ICD-10-CM codes:

^(?i:[A-TV-Z][0-9][0-9AB](?:\.[0-9A-KXZ](?:[0-9A-EXYZ](?:[0-9A-HX][0-59A-HJKMNP-S]?)?)?)?|U07(?:\.[01])?)$

In English, ICD-10-CM codes consist of 3 to 7 alpha-numeric characters (case-insensitive), and codes longer than three characters have a decimal point between the third and the fourth character [1], e.g., “E11.9” represents Type 2 diabetes mellitus without complications. A list of ICD-10-CM codes can be found in the file icd10cm_order_YEAR.txt available at the CDC ICD-10-CM web page (https://www.cdc.gov/nchs/icd/Comprehensive-Listing-of-ICD-10-CM-Files.htm). This file’s 7th to 13th characters (the 2nd column) correspond to the 1st to 7th characters of ICD-10-CM codes.

You do not need a complete understanding of regular expressions or ICD codes to follow this article, so don’t worry too much about it. The reason for posting the simplified regular expression was to scare you. My point was that this regular expression would be a pretty impressive CHECK constraint on this column. Shall we be honest? Despite the fact that we know the best programming practice is to detect an error as soon as possible, do you believe that the original poster wrote such a constraint for the concatenated list of ICD codes?

I’m willing to bet that any such validation is being done in an input tier by some poor lonely program, in an application language. Even more likely, it’s not being done at all.

First Normal Form (1NF) says that this concatenated string is a repeated group, and we need to replace it with a proper relational construct. However, instead of getting some help on how to do it right, people posted various bits of code to slice up the original string! This is akin to the old engineering joke, “Don’t force it!; Get a bigger hammer!” This attitude is wrong in so many ways.

Problem 1: Validation

I’ve already hit on the first problem of Non-First Normal Form (NFNF) data; it is simply complicated. In the SQL standards, a “field” is defined as a part of a column that has some meaning by itself but is not a complete attribute. The classic example is that a date breaks down into (year, month, day) fields, and a timestamp breaks down into (year, month, day, hour, minute, seconds). You get the fields out of temporal datatypes with a statement like EXTRACT(<field name> FROM <temporal expression>), or in SQL Server DATEPART. Unfortunately, if you’re going to split up a NFNF column, you have to do all the work yourself.

Problem 2: Permutations & Combinations

SQL and the relational model are based on sets and not sequences. This means that (A, B, C) should be the same as (C, A, B) or any of the other six possible permutations. The original problem, the poster wanted to require four ICD codes crammed in the column. That means we have 4! = 24 permutations. Suddenly, a simple, equi-join is growing exponentially. One way around this would be to sort the fields within each string. This adds a little extra overhead and requires any change to a string to be recomputed. Think about an algorithm for finding all patients with a particular diagnosis. It might not be their primary diagnosis, so we must do a little scanning. This is sliding us back into the world of COBOL string-oriented data processing.

This poster made things even worse. He wanted the most severe diagnosis to appear first in the list. This means we are dealing with a combination and not a permutation. Hence, (A, B, C) <> (C, A, B), which makes comparisons and ordering a good bit harder. Now we need a process for raising the diagnosis. Unfortunately, it’s going to be rather individualized. We can probably all agree that being sucked through a jet engine is a severe medical problem. After that, is mild diabetes more of a problem than high blood pressure? It depends on the patient and the associated medical history. Since we were supposed to come up with four diagnoses, what if the only thing wrong with the patient was being sucked through a jet engine? Do we make up three more conditions? Create a dummy diagnosis. Repeat the jet engine diagnosis three times?

Problem 3: Nesting Table Structures

Programming languages have had a formal basis, such as FORTRAN being based on Algebra, LISP on list processing, etc. Data and databases did not get “academic legitimacy” until Dr. Codd invented his relational algebra. It had everything academics love—a set of math symbols, including new ones that would drive the typesetters crazy. However, it also had axioms, thanks to Dr. Armstrong. (https://en.wikipedia.org/wiki/Armstrong%27s_axioms)

The immediate result was a burst of papers using Dr. Codd’s relational algebra. However, the next step for a modern academic is to change or drop one of the axioms to see that you can still have a consistent formal system. In geometry, change the parallel axiom (parallel lines never meet) to something else. For example, the replacement axiom is that two parallel lines (great circles) meet at two points on the surface of a sphere. Spheres are real. Furthermore, we could test the new geometry with a real-world model.

Since 1NF is the basis for RDBMS, it was the one academics played with first. And we have real multi-valued databases to see if it works. Most of the academic work was done by Jaeschke and Schek at IBM and Roth, Korth, and Silberschatz at the University of Texas, Austin. They added new operators to the relational algebra and calculus to handle “nested relations” while keeping the relational model’s abstract set-oriented nature. 1NF is inconvenient for handling data with complex internal structures, such as computer-aided design and manufacturing (CAD/CAM). These applications have to handle structured entities, while the 1NF table only allows atomic values for attributes.

Non-first normal form (NFNF) databases allow a column in a table to hold nested relations and break the rule about a column only containing scalar values drawn from a known domain. In addition to NFNF, these databases are also called 2NF, NF2, and ¬NF in the literature. Since they are not part of the ANSI/ISO standards, you will find different proprietary implementations and academic notations for their operations.

Consider a simple example of employees and their children. On a normalized schema, the employees would be in one table, and their children would be in a second table that references the parent:

CREATE TABLE Personnel
(emp_name VARCHAR(20) NOT NULL PRIMARY KEY,
..);

CREATE TABLE Dependents
(dependent_name VARCHAR(20) NOT NULL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL
REFERENCES Personnel(emp_name)--- DRI actions
ON UPDATE CASCADE
ON DELETE CASCADE,
..);

But in an NFNF schema, the dependents would be in a column with a table type, perhaps something like this:

CREATE NF TABLE Personnel
(emp_name VARCHAR(20) NOT NULL PRIMARY KEY,

dependents TABLE
(dependent_name VARCHAR(20) NOT NULL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
..),
..);

Which would make for a set of data that you might diagram as:

image

We can naturally extend the basic set operators UNION, INTERSECTION, and DIFFERENCE and subsets. Extending the relational operations is also relatively easy for PROJECTION and SELECTION. The JOIN operators are a bit harder, but limiting your algebra to the natural or equijoin makes life easier. The important characteristic is that when these extended relational operations are used on flat tables, they behave like the original relational operations.

To transform this NFNF table back into a 1NF schema, you would use an UNNEST operator. The unnesting, in this case, would make Dependents into its own table and remove it from Personnel. Although UNNEST is the mathematical inverse to NEST, the operator NEST is not always the mathematical inverse of UNNEST operations. Let’s start with a simple, abstract nested table:

Image

The UNNEST(<subtable>) operation will “flatten” a sub-table up one level in the nesting:

Image

The NEST operation requires a new table name and its columns as a parameter. This is the extended SQL declaration:

NEST (G1, G2(F3, F4))

Image

It fails when we try to “re-nest” this step back to the original table.

There is also the question of how to order the nesting. We put the dependents inside the Personnel table in the first example. Children are weak entities; they have to have a parent (a strong entity) to exist. However, we could have nested the parents inside the dependents. The problem is that NEST() does not commute. An operation is commutative when (A ☉ B) = (B ☉ A), if you forgot your high school algebra. Let’s start with a simple flat table:

Image

Now, we do two nestlings to create sub-tables G2, which is the F3 column and G3, which is the F4 column. First in this order:

NEST(NEST (G1, G2(F3)), G3(F4))

Image

Now in the opposite order:

NEST(NEST (G1, G3(F3)), G2(F4))

Image

The next question is how to handle missing data. What if Herb’s daughter Mary is lactose-intolerant and has no favorite ice cream flavor in the Personnel table example? The usual NFNF model will require explicit markers instead of a generic missing value.

Another constraint required is for the operators to be objective, which is covered by the partitioned normal form (PNF). This normal form cannot have empty sub-tables and operations have to be reversible. A relation in PNF is such that its atomic attributes are a super-key of the relation and that any non-atomic component of a tuple of the relation is also in PNF.

Why This Occurs

OCCURS is literally the reason this occurs. COBOL has a clause in its DATA DIVISION with that keyword which indicates a sub-record. I will assume most of the readers do not know COBOL. The super-quick explanation is that the COBOL DATA DIVISION is like the DDL in SQL, but the data is kept in strings that have a picture (PIC) clause that shows their display format. In COBOL, display and storage formats are the same. The records and fields in COBOL are very physical, unlike the rows and columns of SQL, which are abstract and virtual.

Records are made of a hierarchy of fields, and the nesting level is shown as an integer at the start of each declaration (numbers increase with depth; the convention is to step by fives). Suppose you wanted to store your monthly sales figures for the year. You could define 12 fields, one for each month, like this:

05 MONTHLY-SALES-1 PIC S9(5)V99.
05 MONTHLY-SALES-2 PIC S9(5)V99.
05 MONTHLY-SALES-3 PIC S9(5)V99.
...
05 MONTHLY-SALES-12 PIC S9(5)V99.

The dash is like an SQL underscore, a period is like a semicolon in SQL, and the picture tells us that each sales amount has a sign, up to five digits for dollars and two digits for cents. You can specify the field once and declare that it repeats 12 times with the simple OCCURS clause, like this:

05 MONTHLY-SALES OCCURS 12 TIMES PIC S9(5)V99.

The individual fields are referenced in COBOL by using subscripts, such as MONTHLYSALES(1). The OCCURS can also be at the group level, and this is its most useful application. For example, all 25-line items on an invoice (75 fields) could be held in this group:

05 LINE-ITEMS OCCURS 25 TIMES.
10 ITEM-QUANTITY PIC 9999.
10 ITEM-DESCRIPTION PIC X(30).
10 UNIT-PRICE PIC S9(5)V99.

Notice the OCCURS is listed at the group level, so the entire group occurs 25 times.

There can be nested OCCURS. Suppose we stock ten products and we want to keep a record of the monthly sales of each product for the past 12 months:

01 INVENTORY-RECORD.
05 INVENTORY-ITEM OCCURS 10 TIMES.
10 MONTHLY-SALES OCCURS 12 TIMES PIC 999.

In this case, INVENTORY-ITEM is a group composed only of MONTHLY-SALES, which occurs 12 times for each occurrence of an inventory item. This gives an array of 10 × 12 fields. The only information in this record is the 120 monthly sales figures—12 months for each of 10 items.

Notice that OCCURS defines an array of known size. However, because COBOL is a file system language, it reads fields in records from left to right. Since there is no NULL, inserting future values that are not yet known requires some coding tricks. The language has the OCCURS DEPENDING ON option. The computer reads an integer control field and then expects to find that many occurrences of a sub-record following at runtime. Yes, this can get messy and complicated, but look at this simple patient medical treatment history record to get an idea of the possibilities:

01 PATIENT-TREATMENTS.
05 PATIENT-NAME PIC X(30).
05 PATIENT-NUMBER PIC 9(9).
05 TREATMENT-COUNT PIC 99 COMP-3.
05 TREATMENT-HISTORY OCCURS 0 TO 50 TIMES
DEPENDING ON TREATMENT-COUNT
INDEXED BY TREATMENT-POINTER.
10 TREATMENT-DATE.
15 TREATMENT-YEAR PIC 9999.
15 TREATMENT-MONTH PIC 99.
15 TREATMENT-DAY PIC 99.
10 TREATING-PHYSICIAN-NAME PIC X(30).
10 TREATMENT-CODE PIC 999.

The TREATMENT-COUNT has to be handled in the applications to correctly describe the TREATMENT-HISTORY subrecords. I will not explain COMP-3 (a data type for computations) or the INDEXED BY clause (array index), since they are not important to my point.

My point is that we had been thinking of data in arrays of nested structures before the relational model. We just had not separated data from computations and presentation layers, nor were we looking for an abstract model of computing yet.

SIDEBAR:

When I described ICD codes as the Dewey decimal of death and despair, I probably should’ve also mentioned that they can be absurd. Here’s a list of the 20 strangest codes in the system. The phrase “subsequent encounter” means it has happened more than once to the patient. I am not sure how you can be sucked into jet engine more than once, but we have a code for it.

1. W220.2XD: Walked into lamppost, subsequent encounter

2. W61.33: Pecked by a chicken

3. W61.62XD: Struck by duck, subsequent encounter

4. W55.41XA: Bitten by pig, initial encounter

5. W59.22XA: Struck By turtle

6. R46.1: Bizarre personal appearance

7. Z63.1: Problems in relationship with in-laws

8. V97.33XD: Sucked into jet engine, subsequent encounter

9. R15.2: Fecal urgency

10.Y92.253: Opera house as the place of occurrence of the external cause

11.V9135XA: Hit or struck by falling object due to accident to canoe or kayak

12. X52: Prolonged stay in weightless environment

13.V94810: Civilian watercraft involved in water transport accident with military watercraft

14.Y92241: Hurt at the library

15. Y92.146: Swimming-pool of prison as the place of occurrence of the external cause

16. Y93.D1: Stabbed while crocheting

17. S10.87XA: Other superficial bite of other specified part of neck, initial encounter

18. Y93.D: V91.07XD: Burn due to water-skis on fire, subsequent encounter

19. V00.01XD: Pedestrian on foot injured in collision with roller-skater, subsequent encounter

20. W22.02XD: V95.43XS: Spacecraft collision injuring occupant

 

The post First Normal Form Gets No Respect appeared first on Simple Talk.



from Simple Talk https://ift.tt/jZYvrDa
via

Wednesday, October 4, 2023

Fabric: My Content compilation

Microsoft Fabric was first announced during Microsoft BUILD. Since then, I’m publishing content about Microsoft Fabric, creating an interesting sequence of content.

In this blog, I’m summarizing the content I published about Fabric, helping you to navigate on this content.

Main Articles

As you may have noticed, there is one article about concepts, one about Data Warehouse and one about Lakehouse, all of them with more than this simple description could explain.

Fabric Blogs

The blog posts are published every Wednesday, always advancing on the Fabric content

Technical Session Recordings

These are recordings of technical sessions about Microsoft Fabric

MS Build After Party – MMDPU : Light Speed Data with Microsoft Fabric

Fabric Monday Videos

Fabric Monday Videos are published every Monday, always short videos covering a subject about Microsoft Fabric

 

The post Fabric: My Content compilation appeared first on Simple Talk.



from Simple Talk https://ift.tt/rdeo2bG
via

Tuesday, October 3, 2023

SQL Server Row Level Security Deep Dive. Part 6 – RLS Attack Mitigations

As seen in the previous section, there are several ways bad actors can attempt to bypass RLS. Attacks range from removing RLS, getting data from other systems or straight brute-force methods using side-channel attacks. Mechanisms exist for each potential attack that allow you to avoid the attack or monitor for the attack when avoidance isn’t possible. This section covers those mitigations.

RLS can be bypassed or attacked using several broad categories. These include direct attacks, where RLS is modified in a malicious fashion or disabled, indirect attacks where information can be gathered without modifying the underlying RLS, and side-channel attacks that use specially crafted queries to derive data from RLS protected tables. Refer to the previous section of this series, RLS Attacks, for a full explanation of each attack type.

Excessive errors

Side channel attacks, with the current vulnerability, rely on forcing runtime errors. The exact error is 8134, divide by zero. This is an error that can be captured, allowing administrators to monitor for possible attacks. There are several ways to capture these errors and the method you employ will depend on your environment, standards, and preferences. When taking advantage of the divide by zero attack, thousands or even hundreds of thousands of errors are generated, leaving a large attack footprint. This makes it easier to see the attack – it really sticks out in the logs if you are checking for it.

SQL Trace

The error raised by divide-by-zero attacks is 8134. If a trace is created, the preferred method is using sp_trace_create. Using SQL Profiler is a good way to prototype a trace, but there are some disadvantages. Profiler sends all data over the network, increasing the performance cost. It also runs on a client machine, which must be on and connected all the time (or Profiler must be running on the server, which takes additional server resources) and can’t be configured to run automatically. Profiler is just a front-end for trace, so it’s a good idea to learn how to create a simple trace.

The following shows how this can be configured in profiler.

This example uses TSQL to create a trace capturing exceptions. As noted above, profiler also creates a trace, but it is more resource heavy.

DECLARE                @ReturnCode                     int
                        ,@TraceID                       int
                        ,@maxfilesize           bigint          = 128
                        ,@traceoptions          int                     = 2
                        ,@stoptime                      datetime
                        ,@FileName                      nvarchar(256)
SELECT @FileName = 'C:\Temp\RLSExceptions_' + replace(replace(replace(replace(convert(varchar(50),getdate(),100),'-',''),' ','_'),':',''),'__','_')
-- Create the trace with the name of the output file - .trc extension is added to filename
EXEC @ReturnCode = sp_trace_create @TraceID OUTPUT, 2, @FileName, @maxfilesize, NULL 
if (@ReturnCode <> 0) GOTO error
-- Set the events
declare @on bit
set @on = 1
-- 33   Exception       Indicates that an exception has occurred in SQL Server
EXEC sp_trace_setevent @TraceID, 33, 3, @on             --DatabaseID
EXEC sp_trace_setevent @TraceID, 33, 6, @on             --NTUserName
EXEC sp_trace_setevent @TraceID, 33, 8, @on             --HostName
EXEC sp_trace_setevent @TraceID, 33, 9, @on             --ClientProcessID
EXEC sp_trace_setevent @TraceID, 33, 10, @on    --ApplicationName
EXEC sp_trace_setevent @TraceID, 33, 11, @on    --LoginName
EXEC sp_trace_setevent @TraceID, 33, 12, @on    --SPID
EXEC sp_trace_setevent @TraceID, 33, 13, @on    --Duration
EXEC sp_trace_setevent @TraceID, 33, 14, @on    --StartTime
EXEC sp_trace_setevent @TraceID, 33, 15, @on    --EndTime
EXEC sp_trace_setevent @TraceID, 33, 27, @on    --Event Class
EXEC sp_trace_setevent @TraceID, 33, 31, @on    --Error
-- Ordinarily filters would be set here.
-- Capturing only the exception doesn't require any filters
-- unless known errors are regularly encountered
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT
        TraceID                 = @TraceID
        ,TraceName              = @FileName + '.trc'
GOTO FINISH
ERROR:
SELECT ErrorCode=@ReturnCode
FINISH: 
PRINT 'TRACE ' + convert(varchar(5),@TraceID) + ' created successfully'
PRINT 'TRACE name: ' + @FileName + '.trc'
GO

Output using a query to pull the results is shown below.

DECLARE 
        @curr_tracefilename                     VARCHAR(500)
        ,@base_tracefilename            VARCHAR(500)
        ,@indx                                          INT
--Note that the trace ID may vary if other traces are running
--Use the ID output during trace creation
SELECT @base_tracefilename = path
FROM sys.traces ST
WHERE ST.id = 2
SELECT
        ClientProcessID
        ,ApplicationName
        ,SPID
        ,StartTime
        ,EventClass
        ,Error
FROM::fn_trace_gettable(@base_tracefilename, DEFAULT) GT

Sample output from the trace when a brute force attack is running

SQL Trace Deprecation

Microsoft documentation warns that trace functionality will be removed in a future version of SQL Server. Take this into consideration when selecting your method for monitoring potential RLS attacks.

Extended events

In Azure databases, as well as all other SQL instances, extended events can be used to capture error 8134. I find extended events harder to work with and slower to query, but once configured they work well. They also only run on the server which helps performance. When they are setup in the GUI, they can be configured to show live results, but this wouldn’t be the method for active attack monitoring. The following shows how to setup an extended event to capture errors .

DROP EVENT SESSION RLSErrors ON SERVER
GO
--Extended even targeting errors
CREATE EVENT SESSION RLSErrors ON SERVER
ADD EVENT sqlserver.error_reported(
        ACTION(
                sqlserver.client_hostname
                ,sqlserver.database_id
                ,sqlserver.database_name
                ,sqlserver.nt_username
                ,sqlserver.sql_text
                ,sqlserver.tsql_stack
                ,sqlserver.username
)
) --Location to store data. Several options are available, check documentation for the best fit
ADD TARGET package0.event_file(SET filename=N'C:\Temp\RLSErrors.xel')
WITH (
        MAX_MEMORY=4096 KB
        ,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS
        ,MAX_DISPATCH_LATENCY=1 SECONDS
        ,MAX_EVENT_SIZE=0 KB
        ,MEMORY_PARTITION_MODE=NONE
        ,TRACK_CAUSALITY=OFF
        ,STARTUP_STATE=OFF
)
GO
 
ALTER EVENT SESSION RLSErrors ON SERVER
STATE = START;
GO

For the above extended event, this will pull the errors. I didn’t limit the query to error 8134 alone, but it would be reasonable to do so.

;
WITH XE_CTE AS (
        SELECT
                CONVERT(XML,event_data) EventData
        FROM sys.fn_xe_file_target_read_file('C:\Temp\RLSErrors*.xel',NULL,NULL,NULL)
)
,DECODED_CTE AS (
        SELECT
                EventData.value('(/event/data[@name=''error_number'']/value)[1]','varchar(max)')                                ErrorNumber
        FROM XE_CTE
)
SELECT 
        ErrorNumber
        ,COUNT(*)                       ErrorCount
FROM DECODED_CTE
WHERE ErrorNumber               = 8134
GROUP BY ErrorNumber

Results from the above query. This was generated by testing the brute-force side-channel attack script with 100 rows. As seen below, over 156 thousand errors are produced for this relatively small dataset. If you see numbers like this, you should dig deeper into what is happening in your system and rule out an RLS attack.

Server or Database Audits

SQL Server instances can use server and database audits to log queries against the server and databases on that server. Auditing for Azure SQL can be used to do the same thing against Azure databases and Azure Synapse. This can be useful for forensic analysis after an attack or even to determine that an attack has occurred. It can capture all queries against a database and it can also capture DBCC commands against a database if that vulnerability is a concern.

A benefit of using audits is that they aren’t limited to monitoring for RLS attacks. They can be used for a wide range of monitoring functions. Reports can be created for audit records making it a simple process to search for issues. If you are already using audits this is a good method to check for RLS related attacks.

SQL Advanced Threat Protection

SQL Advanced Threat Protection is an automated, relatively hands-off method to watch for attacks against SQL Server in Azure. The documentation lists SQL injections specifically, and general suspicious activity and vulnerabilities as target threats. It may catch RLS attacks in the future or certain RLS attacks, but in my testing, I didn’t get any warnings via this tool. It looks like a very useful method for capturing potential security issues, but you will want to supplement this with your own audits for now if RLS is part of your environment.

Performance Changes

Brute force attacks are not considerate of performance. This can potentially be used to detect an attack in action. The primary method to check is CPU usage. In lower-level Azure tiers, DTU usage can also be monitored.

Excessive CPU Usage

Brute force attacks cause CPU usage to spike. Depending on your configuration, it may be noticeable or it may blend into the background. In my test Azure SQL database environment, with the minimum DTUs, it was very noticeable. The DTU overview showed 100% utilization during an unregulated side-channel attack.

The CPU metric on the monitoring page also showed 100% utilization for this low tier SQL database.

My local workstation with SQL Server 2022 Developer Edition showed an increase in CPU usage, but not something I would probably notice or be concerned about. If RLS attacks are a concern on your system, you will want to see how your configuration performs when under attack.

This is still not a foolproof method for detecting these attacks. If an attacker is patient, they would just put a WAITFOR command in their attack to limit the impact of their script. It will still throw the 8134 (divide by zero) error, but the errors would be spread out over time. This is very similar to network port scans. An attacker will wait between scanning each port or even change IP addresses during the scan. Firewalls have evolved to detect these attacks. Your methods will need to evolve too and you will need to be aware of this method when protecting your systems.

--Sleep for 1 second
--Will reduce CPU usage and space out errors
--at the expense of attack speed
WAITFOR DELAY '00:00:01'

This limits the CPU impact of the attack almost entirely, but in the low performance tier, it still spikes the CPU to 100% during the initial processing of the primary key. After that processing, the impact is negligible, but the process is limited to 1 character discovered every second (or whatever interval is selected). Given that there were 156,521 errors generated, it would take almost 2 full days to reveal the protected data in the first 100 rows. Making the delay 50 milliseconds provided the data in a more reasonable time and the CPU was still below 20%. The following graph shows the impact of a 1 second delay.

Attack monitoring

The above tactics can be used to capture attacks. To alert administrators, a few methods can be used. Each method should look for excessive numbers of error 8134. Depending on your system and needs, you may also look for excessive CPU usage, specific DBCC commands, disabling RLS, or even data changes to RLS configuration tables. You will need to incorporate the scripts appropriate for your environment and perceived threat surface and create alerts for each of these.

SQL Agent Job

If a SQL Trace is used, SQL Agent is a potential method to monitor for attacks. If the error count exceeds a defined threshold during a time period, administrators can be notified. It can also be used against extended events, provided the server is on premises or a managed instance.

Agent jobs can also run queries looking for changes to your RLS tables or anything else that can be queried. RLS tables are a good use case for temporal tables. If the RLS configuration is a temporal table, a simple query will show any changes during a given time period. As mentioned previously, RLS is not prescriptive, so you will need to create scripts based on your implementation.

Elastic Job

In a pure Azure environment, Elastic Jobs can be used in a similar manner to SQL Agent jobs. This could be used to query extended events or RLS changes. Since SQL Trace is not available in Azure database, you wouldn’t likely use it for that.

Elastic Job Warning

At the time of this writing, Elastic Jobs are still in development. They have been available for quite a while and appear to be part of the ongoing Azure offerings, but there is no guarantee they will be implemented officially.

External Scheduler / Custom Code

Any external scheduler or custom code that can run SQL queries and send email or other notifications to administrators can be used to monitor for attacks. This will be specific to your environment and the exact method doesn’t matter. My general recommendation is to follow your existing patterns, taking into account future needs. Don’t build a custom solution if you have an existing scheduler or if you have SQL Agent available.

Code repo with history

All modern code repositories maintain history and can be used to show when changes happened and who made the changes. This won’t stop attacks by itself, but it will help with a post-attack analysis. It is also a deterrent to direct code attacks via an automated pipeline. If security is misconfigured, a developer / attacker could remove history in the repo. This is another case in the enterprise for minimum viable security.

Code reviews

Code reviews are a standard practice and a good method to find errors in access predicates, both intentional and unintentional. Microsoft documentation mentions taking care with users configured as policy managers. They can change and bypass RLS configurations. The same is true for developers, especially for automated or non-monitored deployments. Carefully review access predicates and security policy changes. Malicious changes are a risk, but a more likely scenario is code misconfigurations. Either case results in the potential for unauthorized access.

Audit for changes to security objects

Existing access predicates and security policies should be regularly audited and validated. This is to ensure they are still enabled and that the code hasn’t changed. The more sensitive the system and data, the more frequently it should be confirmed. It is useful, and I would say required, to know the expected configuration. Audits can be managed in several ways and some options are detailed below.

Tables with RLS applied can be verified using SQL metadata queries. Tables with RLS and tables without RLS applied, predicate definitions, and columns used for RLS can all be checked very easily. The same types of queries can also examine security policies and access predicates.

Depending on your code repository, you can also compare the committed code to your deployed database objects. There should be a very limited set of accounts that can change objects in a production environment, but it can be worth auditing. This would also check for changes in addition to RLS objects making it even more useful.

SQL metadata can also be used to look for changes to RLS related objects. This is easier if you use standard naming conventions and a separate schema. The modify_date will show when RLS objects have changed. It will also show if an object is dropped and recreated with the same name. There are also trace and extended events that can check for changes to objects. The following example query shows all objects in the RLS schema changed within the last day.

SELECT
        SS.name                 SchemaName
        ,SO.name                ObjectName
        ,SO.create_date
        ,SO.modify_date
        ,DATEADD(dd,-10,SO.modify_date)
        ,DATEDIFF(dd,SO.modify_date,GETDATE())
FROM sys.schemas SS
        INNER JOIN sys.objects SO
                ON SS.schema_id         = SO.schema_id
WHERE SS.name                           = 'RLS'
        AND SO.type                             IN ('IF','SP')          --IF = SQL Inline Table Valued Function, SP = Security Policy
        AND DATEDIFF(dd,SO.modify_date,GETDATE()) <= 1               --RLS objects changed in the last day. Change to a time interval and value appropriate for your environment
ORDER BY SO.modify_date DESC

Azure Alerts

Azure alerts can be used to check for performance issues and alert administrators. It usually won’t be an indication of an ongoing attack, but that is one possible reason for major changes in CPU or disk performance. During my testing I saw changes to CPU. This would be an area of interest for any administrator, even if an attack isn’t suspected.

RLS Support and Administration Scripts

 RLS is easy to validate using metadata scripts. The following samples show a few ways to check your RLS configuration.

Objects with RLS applied

The following shows all security policies and access predicate details. This is useful for reviewing security at a database level.

SET NOCOUNT ON
;
WITH ACCESS_PREDICATES AS (
        SELECT
                SP.object_id
                ,SP.target_object_id
                ,SS.value                               AccessPredicateName
        FROM sys.security_predicates SP
                CROSS APPLY string_split(SP.predicate_definition,N'(') SS
        WHERE SS.value <> ''
                AND SS.value NOT LIKE '%))'
)
SELECT
        SS.name                                                         SchemaName
        ,SO.name                                                        TableName
        ,SSSP.name                                                      SecurityPolicySchema
        ,SSP.name                                                       SecurityPolicyName
        ,SSP.type_desc                                          SecurityPolicyDescription
        ,SSP.is_enabled                                         SecurityPolicyIsEnabled
        ,AP.AccessPredicateName
        ,SP.predicate_definition                        AccessPredicateDefinition
        ,SP.operation_desc
        ,SP.predicate_type_desc
FROM sys.schemas SS
        INNER JOIN sys.objects SO
                ON SS.schema_id                 = SO.schema_id
        INNER JOIN sys.security_predicates SP
                ON SO.object_id                 = SP.target_object_id
        INNER JOIN sys.security_policies SSP
                ON SP.object_id                 = SSP.object_id
        INNER JOIN sys.schemas SSSP
                ON SSP.schema_id                = SSSP.schema_id
        LEFT JOIN ACCESS_PREDICATES AP
                ON SP.object_id                 = AP.object_id
                AND SP.target_object_id = AP.target_object_id
ORDER BY
        SS.name
        ,SO.name
        ,SSP.name
GO

Tables without RLS

The following can be used to show all tables in a database without a security predicate assigned. This is useful for finding new tables that were not properly assigned RLS and also for finding tables where RLS has been removed.

SET NOCOUNT ON
DECLARE @Exclusions TABLE (
        SchemaName                                                      varchar(255)
)
--Optionally - exclude the following schemas from the search
--The RLS schema would not have a security predicate assigned
--rather, regular users would not be able to access it at all.
INSERT INTO @Exclusions (
        SchemaName
)
VALUES 
        ('etl')
        ,('RLS')
        ,('ssrs')
SELECT
        SS.name                                                         SchemaName
        ,SO.name                                                        TableName
FROM sys.schemas SS
        INNER JOIN sys.objects SO
                ON SS.schema_id                 = SO.schema_id
        LEFT JOIN sys.security_predicates SP
                ON SO.object_id                 = SP.target_object_id
        LEFT JOIN @Exclusions EX
                ON SS.name                              = EX.SchemaName
WHERE SO.type                                   = 'u'
        AND EX.SchemaName                       IS NULL
        AND SP.object_id                        IS NULL
ORDER BY
        SS.name
        ,SO.name
GO

Specific column without RLS

Sometimes you only want to look for a specific column without RLS applied. In larger systems, this can save time and also allows team members without a full understanding of RLS to help run pointed audits. It is useful to perform these checks in production environments with many team members or rapidly shifting teams. It is easy for tables to be inserted into production without RLS in these scenarios. This script is a modification of the previous with additional parameters added to limit the columns searched.

SET NOCOUNT ON
DECLARE @Exclusions TABLE (
        SchemaName                                                      varchar(255)
)
--Limit the columns
--ExactMatch = 1 matches the full name
--ExactMatch = 0 performs a wildcard search
DECLARE @RLSColumn              varchar(255)    = 'SupplierID'
        ,@ExactMatch            bit                             = 1
--Optionally - exclude the following schemas from the search
--The RLS schema would not have a security predicate assigned
--rather, regular users would not be able to access it at all.
INSERT INTO @Exclusions (
        SchemaName
)
VALUES 
        ('etl')
        ,('RLS')
        ,('ssrs')
SELECT
        SS.name                                         SchemaName
        ,SO.name                                        TableName
        ,SC.name                                        ColumnName
FROM sys.schemas SS
        INNER JOIN sys.objects SO
                ON SS.schema_id                 = SO.schema_id
        INNER JOIN sys.columns SC
                ON SO.object_id                 = SC.object_id
        LEFT JOIN sys.security_predicates SP
                ON SO.object_id                 = SP.target_object_id
        LEFT JOIN @Exclusions EX
                ON SS.name                              = EX.SchemaName
WHERE SO.type                                   = 'u'
        AND EX.SchemaName                       IS NULL
        AND SP.object_id                        IS NULL
        AND SC.name                                     LIKE CASE WHEN @ExactMatch = 1 THEN @RLSColumn ELSE '%' +  @RLSColumn + '%' END
ORDER BY
        SS.name
        ,SO.name
GO

Summary and analysis

RLS is a great way to add a layer of protection to a SQL Server database. It is not the most secure option but it is relatively easy to implement, provides a method to greatly simplify reporting and query needs, and may be secure enough, depending on requirements. The ease of implementation makes is very enticing for internal reporting solutions. It can greatly decrease the number and complexity of reports required.

Layer of security

RLS is an additional layer of security that can be added to a solution. It is not a replacement for other authorization methods, but an enhancement that can be very useful in reporting and warehousing scenarios. Configured correctly, it can also work in transactional applications when used with SESSION_CONTEXT as a means of limiting data seen by particular users.

Hire trusted admins

System administrators and developers must be trusted to some degree. It is still possible, and sometimes required, to audit their activities, but the relationship with administrators must begin with trust. If users with elevated privileges are not trusted, they should not be in the system. Period. But that trust must be earned over time and reinforced with good decisions and successful projects. Administrators and developers have access to logon scripts, backups, monitoring, object code, change requests, and many other methods to directly impact databases and secure data. It is very difficult to prevent bad actors in these categories from gaining extra or unauthorized access. That is where audits can help fill the gap for security requirements.

Consider alternatives if the use case fits

There are several alternatives to RLS. These alternatives were the only way to segregate data by user before RLS was added to SQL Server. Some project use cases fit better with these custom solutions than with RLS. Alternatives include separate views or stored procedures for different users, separate reports, or other custom code.

Locking the data down excessively can potentially encourage data exports, decreasing overall security

One of the expectations of a database system is keeping the data secure. As mentioned, RLS is one of the methods that can enhance security on a system. Keep in mind that if users find a system too restrictive or cumbersome to perform their daily tasks, they will often find a way around those obstacles. Security in SQL Server is not different. Extracting data via service accounts, grabbing data in less secure systems, or using unexpected methods to grab data are possible side effects of unwieldy security structures. The only real solution to this is to work with the business every step of the design and ensure they agree with the security requirements. They are partners in the design and should be the decision makers regarding how secure to make the database. If the users (or their leaders) are requesting the security, they will be much less likely to circumvent those measures.

Current attacks that work may change with updates. It is important to keep up-to-date on patches and monitor systems.

The attack paths shown in this series work with the current versions and patches of SQL Server and SQL Database. There are likely additional attacks that I haven’t considered. Administrators and developers should watch for unusual activity on all databases, not only those secured with RLS. Especially sensitive data should be scrutinized closely and auditing features of SQL should be implemented. All software systems, databases included, are dynamic to some degree and changing. Regular security audits and system reviews can help ensure everything is still functioning as initially designed.

It is also possible that the current vulnerabilities will be addressed in a future version or patch. This would render the demonstrated attack method obsolete, but different attack vectors are possible.

Direct access to data always has the potential to inadvertently expose sensitive data

The security examples show that direct access to data presents more challenges to securing data. In addition to side-channel attacks, misconfigurations can potentially allow access to sensitive data. If access predicates are not applied to new tables in a system, if they are applied incorrectly, or if users are added incorrectly with elevated privileges, data security can be subverted. Be sure everyone on the team knows the proper methods to grant access to the database and the proper way to add new tables to the system.

Training and documentation

RLS can be confusing at first, even for administrators and developers very familiar with SQL Server. If you aren’t expecting RLS in the design, it can cause extra troubleshooting steps when diagnosing potential issues. Having zero records return when the table shows as full via administrative queries is a good indicator, but having RLS on a table shouldn’t be a surprise for the team supporting the database.

Developers, administrators and support teams should be given guidelines on the usage of RLS and the design patterns used in the local implementation. Even end users should have a basic understanding of the requirements if they are allowed to hit the database directly. After the architecture has been designed, the team should be trained and relevant diagrams and documents created.

Be sure to include administrators and service accounts in RLS, tables or session context, if they will be running queries. If service accounts are rotated, be sure to include all relevant accounts. If all relevant service accounts aren’t included, the best-case scenario is errors or missing data during connections. The worst-case scenario is extra deletes (during an anti-join) or duplicates when key columns don’t match. Consider allowing admins access via IS_MEMBER() so any organizational changes don’t impact your design.

It is useful to create a decision chart for support teams to understand what to check when diagnosing connectivity issues. The exact flow will differ by environment, but should start with the account they are using, making sure it is not locked out, that it is in the proper AD groups, the group or user has server access, it has database access, it is in the proper database roles, that RLS is applied correctly, and anything else for your environment. Decision charts like this are also useful for onboarding new developers, presenting to other teams, and during architectural reviews.

During the initial stages of development, templates should be created and shared with developers. This should include tables with the RLS column embedded and tables that require a join to apply RLS. The second category is especially important due to the performance impact it can have. It is also useful to have a set of queries used to manage RLS and check for tables without RLS applied. Development should also include performance tuning to ensure RLS performs as expected and changing the design doesn’t require too much refactoring.

Summary

RLS can be a useful addition to a database solution if it is implemented correctly and the limitations are understood. I didn’t realize the impact of side channel attacks until I dove in and tried to see how much data could be exposed. If users are allowed direct access to the data, errors should be monitored and especially-sensitive data and multi-tenant systems should limit the users allowed to directly query the data. RLS can greatly reduce the complexity of reporting and the number of queries required, just understand the security impact and include all the regular security layers you would in any other database solution.

Regular audits of the system, active monitoring and code reviews should be part of the process. Performance tuning and table design also needs to be considered carefully when adding RLS. Document how the system works and what is needed for RLS to work for an individual user.

If there is no room for error, consider encrypting data. For very sensitive data, double check that end users should be allowed to dynamically query the data. A stored procedure layer over tables with RLS will prevent the side-channel attacks shown in this series but allow most of the benefits. This limits how the data can be used, but it gives an extra layer of protection.

Be sure to discuss the benefits and potential pitfalls of implementing RLS with the business if you are considering it for your solution. As with most solutions, RLS involves compromises. It is usually up to the business to decide what risks they can tolerate. The advantages of RLS are likely to outweigh the downside in many cases, but the discussion is needed given that there are some vulnerabilities.

As seen in the previous section, there are several ways bad actors can attempt to bypass RLS. Attacks range from removing RLS, getting data from other systems or straight brute methods using side channel attacks. Each method has mechanisms to avoid the attack or monitor for the attack when avoidance isn’t possible.

RLS section Links:

Part 1 – Introduction and Use Cases

Part 2 – Setup and Examples

Part 3 – Performance and Troubleshooting

Part 4 – Integration, Antipatterns, and Alternatives to RLS

Part 5 – RLS Attacks

Part 6 – Mitigations to RLS Attacks

The post SQL Server Row Level Security Deep Dive. Part 6 – RLS Attack Mitigations appeared first on Simple Talk.



from Simple Talk https://ift.tt/LXrVdkP
via

Monday, October 2, 2023

Normalize strings to optimize space and searches

While this article is specifically geared to SQL Server, the concepts apply to any relational database platform.

The Stack Exchange network logs a lot of web traffic – even compressed, we average well over a terabyte per month. And that is just a summarized cross-section of our overall raw log data, which we load into a database for downstream security and analytical purposes. Every month has its own table, allowing for partitioning-like sliding windows and selective indexes without the additional restrictions and management overhead. (Taryn Pratt talks about these tables in great detail in her post, Migrating a 40TB SQL Server Database.)

It’s no surprise that our log data is massive, but could it be smaller? Let’s take a look at a few typical rows. While these are not all of the columns or the exact column names, they should give an idea why 50 million visitors a month on Stack Overflow alone can add up quickly and punish our storage:

HostName                     Uri               QueryString     CreationDate    ...
  nvarchar(250)                nvarchar(2048)    nvarchar(2048)  datetime
  ---------------------------  ----------------  --------------  --------------
  math.stackexchange.com       /questions/show/                  2023-01-01 ...
  math.stackexchange.com       /users/56789/     ?tab=active     2023-01-01 ...
  meta.math.stackexchange.com  /q/98765/         ?x=124.54       2023-01-01 ...

Now, imagine analysts searching against that data – let’s say, looking for patterns on January 1st for Mathematics and Mathematics Meta. They will most likely write a WHERE clause like this:

WHERE CreationDate >= '20230101' 
    AND CreationDate <  '20230102'
    AND HostName LIKE N'%math.stackexchange.com';

These quite expectedly lead to awful execution plans with a residual predicate for the string match on every row in the date range – even if only three rows match. We can demonstrate this with a table and some data:

CREATE TABLE dbo.GinormaLog_OldWay
 (
   Id           int IDENTITY(1,1),
   HostName     nvarchar(250),
   Uri          nvarchar(2048),
   QueryString  nvarchar(2048),
   CreationDate datetime,
   /* other columns */
   CONSTRAINT PK_GinormaLog_OldWay PRIMARY KEY(Id)
 );

 /* populate a few rows to search for (the needles) */

 DECLARE @math nvarchar(250) = N'math.stackexchange.com';
 DECLARE @meta nvarchar(250) = N'meta.' + @math;

 INSERT dbo.GinormaLog_OldWay(HostName, Uri, QueryString, CreationDate)
 VALUES
   (@math, N'/questions/show/', N'',            '20230101 01:24'),
   (@math, N'/users/56789/',    N'?tab=active', '20230101 01:25'),
   (@meta, N'/q/98765/',        N'?x=124.54',   '20230101 01:26'); 
 GO

 /* put some realistic other traffic (the haystack) */

 INSERT dbo.GinormaLog_OldWay(HostName, Uri, QueryString, CreationDate)
 SELECT TOP (1000) N'stackoverflow.com', CONCAT(N'/q/', ABS(object_id % 10000)),
   CONCAT(N'?x=', name), DATEADD(minute, column_id, '20230101')
   FROM sys.all_columns;
 GO 10

 INSERT dbo.GinormaLog_OldWay(HostName, Uri, QueryString, CreationDate)
 SELECT TOP (1000) N'stackoverflow.com', CONCAT(N'/q/', ABS(object_id % 10000)),
   CONCAT(N'?x=', name), DATEADD(minute, -column_id, '20230125')
   FROM sys.all_columns;
 GO

 /* create an index to cater to many searches */

 CREATE INDEX DateRange
   ON dbo.GinormaLog_OldWay(CreationDate) 
   INCLUDE(HostName, Uri, QueryString);

Now, we run the following query (SELECT * for brevity):

SELECT * 
   FROM dbo.GinormaLog_OldWay
   WHERE CreationDate >= '20230101' 
     AND CreationDate <  '20230102'
     AND HostName LIKE N'%math.stackexchange.com';

And sure enough, we get a plan that looks okay, especially in Management Studio – it has an index seek and everything! But that is misleading, because that index seek is doing a lot more work than it should. And in the real world, those reads and other costs are much higher, both because there are more rows to ignore, and every row is a lot wider:

Plan and seek details for LIKE against original index

So, then, what if we create an index on CreationDate, HostName?

CREATE INDEX Date_HostName
   ON dbo.GinormaLog_OldWay(CreationDate, HostName) 
   INCLUDE(Uri, QueryString);

This doesn’t help. We still get the same plan, because whether or not the HostName shows up in the key, the bulk of the work is filtering the rows to the date range, and then checking the string is still just residual work against all the rows:

Plan and seek details for LIKE against different index

If we could coerce users to use more index-friendly queries without leading wildcards, like this, we may have more options:

WHERE CreationDate >= '20230101' 
   AND CreationDate <  '20230102'
   AND HostName IN 
   (
      N'math.stackexchange.com', 
      N'meta.math.stackexchange.com'
   );

In this case, you can see how an index (at least leading) on HostName might help:

CREATE INDEX HostName
   ON dbo.GinormaLog_OldWay(HostName, CreationDate) 
   INCLUDE(Uri, QueryString);

But this will only be effective if we can coerce users to change their habits. If they keep using LIKE, the query will still choose the older index (and results aren’t any better even if you force the new index). But with an index leading on HostName and a query that caters to that index, the results are drastically better:

Plan and seek details for IN against better index

This is not overly surprising, but I do empathize that it can be hard to persuade users to change their query habits and, even when you can, it can also be hard to justify creating additional indexes on already very large tables. This is especially true of log tables, which are write-heavy, append-only, and read-seldom.

When I can, I would rather focus on making those tables smaller in the first place.

Compression can help to an extent, and columnstore indexes, too (though they bring additional considerations). You may even be able to halve the size of this column by convincing your team that your host names don’t need to support Unicode.

For the adventurous, I recommend better normalization!

Think about it: If we log math.stackexchange.com a million times today, does it make sense to store 44 bytes, a million times? I would rather store that value in a lookup table once, the first time we see it, and then use a surrogate identifier that can be much smaller. In our case, since we know we won’t ever have over 32,000 Q&A sites, we can use smallint (2 bytes) – saving 22 bytes per row (or more, for longer host names). Picture this slightly different schema:

CREATE TABLE dbo.NormalizedHostNames
 (
   HostNameId smallint identity(1,1),
   HostName nvarchar(250) NOT NULL,
   CONSTRAINT PK_NormalizedHostNames PRIMARY KEY(HostNameId),
   CONSTRAINT UQ_NormalizedHostNames UNIQUE(HostName)
 );

 INSERT dbo.NormalizedHostNames(HostName)
 VALUES(N'math.stackexchange.com'),
       (N'meta.math.stackexchange.com'),
       (N'stackoverflow.com');

 CREATE TABLE dbo.GinormaLog_NewWay
 (
   Id           int identity(1,1) NOT NULL,
   HostNameId   smallint NOT NULL FOREIGN KEY 
                REFERENCES dbo.NormalizedHostNames(HostNameId),
   Uri          nvarchar(2048),
   QueryString  nvarchar(2048),
   CreationDate datetime,
   /* other columns */
   CONSTRAINT PK_GinormaLog_NewWay PRIMARY KEY(Id)
 );

 CREATE INDEX HostName
   ON dbo.GinormaLog_NewWay(HostNameId, CreationDate) 
   INCLUDE(Uri, QueryString);

Then the app maps the host name to its identifier (or inserts a row if it can’t find one), and inserts the id into the log table instead:

/* populate a few rows to search for (the needles) */

 INSERT dbo.GinormaLog_NewWay(HostNameId, Uri, QueryString, CreationDate)
 VALUES
   (1, N'/questions/show/', N'',            '20230101 01:24'),
   (1, N'/users/56789/',    N'?tab=active', '20230101 01:25'),
   (2, N'/q/98765/',        N'?x=124.54',   '20230101 01:26'); 
 GO

 /* put some realistic other traffic (the haystack) */

 INSERT dbo.GinormaLog_NewWay(HostNameId, Uri, QueryString, CreationDate)
 SELECT TOP (1000) 3, CONCAT(N'/q/', ABS(object_id % 10000)),
   CONCAT(N'?x=', name), DATEADD(minute, column_id, '20230101')
   FROM sys.all_columns;
 GO 10

 INSERT dbo.GinormaLog_NewWay(HostNameId, Uri, QueryString, CreationDate)
 SELECT TOP (1000) 3, CONCAT(N'/q/', ABS(object_id % 10000)),
   CONCAT(N'?x=', name), DATEADD(minute, -column_id, '20230125')
   FROM sys.all_columns;

First, let’s compare the sizes:

Space details for more normalized design

That’s a 36% reduction in space. When we’re talking about terabytes of data, this can drastically reduce storage costs and significantly extend the utility and lifetime of existing hardware.

What about our queries?

The queries are slightly more complicated, because now you have to perform a join:

SELECT * 
 FROM dbo.GinormaLog_NewWay AS nw
 INNER JOIN dbo.NormalizedHostNames AS hn
   ON nw.HostNameId = hn.HostNameId
 WHERE nw.CreationDate >= '20230101' 
   AND nw.CreationDate <  '20230102'
   AND hn.HostName LIKE N'%math.stackexchange.com';
   /* -- or 
   AND (hn.HostName IN (N'math.stackexchange.com',
        N'meta.math.stackexchange.com')); */

In this case, using LIKE isn’t a disadvantage unless the host names table becomes massive. The plans are almost identical, except that the LIKE variation can’t perform a seek. (Both are a little more horrible at estimates, but I don’t see how being any worse off the mark here will ever pick any other plan.)

Plans against more normalized design

This is one scenario where the difference between a seek and a scan is such a small part of the plan it is unlikely to be significant.

This strategy still requires analysts and other end users to change their query habits a little, since they need to add the join compared to when all of the information was in a single table. But you can make this change relatively transparent to them by offering views that simplify the joins away:

CREATE VIEW dbo.vGinormaLog
 AS
   SELECT nw.*, hn.HostName
   FROM dbo.GinormaLog_NewWay AS nw
   INNER JOIN dbo.NormalizedHostNames AS hn
     ON nw.HostNameId = hn.HostNameId;

Never use SELECT * in a view. 🙂

Querying the view, like this, still leads to the same efficient plans, and lets your users use almost exactly the same query they’re used to:

SELECT * FROM dbo.vGinormaLog
   WHERE CreationDate >= '20230101' 
     AND CreationDate <  '20230102'
     AND HostName LIKE N'%math.stackexchange.com';

Existing infrastructure vs. greenfield

Even scheduling the maintenance to change the way the app writes to the log table – never mind actually making the change – can be a challenge. This is especially difficult if logs are merely bulk inserted or come from multiple sources. It may be sensible to use a new table (perhaps with partitioning) and start adding new data going forward in a slightly different format, and leaving the old data as is, using a view to bridge them.

That could be complex, and I don’t mean to trivialize this change. I wanted to post this more as a “lessons learned” kind of thing – if you are building a system where you will be logging a lot of redundant string information, think about a more normalized design from the start. It’s much easier to build it this way now than to shoehorn it later… when it may well be too big and too late to change. Just be careful to not treat all potentially repeating strings as repeating enough to make normalization pay off. For host names at Stack Overflow, we have a predictably finite number, but I wouldn’t consider this for user agent strings or referrer URLs, for example, since that data is much more volatile and the number of unique values will be a large percentage of the entire set.

The post Normalize strings to optimize space and searches appeared first on Simple Talk.



from Simple Talk https://ift.tt/PTrgmWY
via