Category: Security

Tom Collins sets up column-level encryption:

How do I set up the SQL Server Column Level Encryption ? 

This is quite different from Always Encrypted—it’s the encryption process which we saw with SQL Server going back (at least) to 2000. It’s also (currently?) the only available way manually to encrypt columns in an Azure Synapse Analytics dedicated SQL pool. I’d say it’s not trivial to set up and use but neither is it a huge challenge.

John Cook lays out an important insight:

One of the Safe Harbor provisions under HIPAA is that data may not contain sparsely populated three-digit zip codes. Sometimes databases will replace sparse zip codes with nulls. But if the same database reports a person’s state, and the state only has one sparse zip code, then the data effectively lists all zip codes. Here the suppressed zip code is conspicuous by its absence. The null value itself didn’t reveal the zip code, nor did the state, but the combination did.

Read the whole thing. This also leads to a swath of security attacks based around unions of information in which each query may data only when X number of people are in it (to prevent us narrowing down to one person) but based on some information I know about the person, I can write a combination of queries to elicit more info about that person. As an example, if I know that a person is left-handed (1/9 of the population), has red hair (around 2% of people), etc., I can find ways to combine these traits to make sure no individual query returns fewer than X results but I can have reasonably high confidence that I can get the individual with enough queries.

Andreas Wolter has some new server roles for us to use:

The new server-roles that can be assigned to server logins to enable customers to assign and delegate job functions for server-wide metadata access and access to certain management commands without requiring Server Admin or AAD Admin privileges. This helps comply with the Principle of Least Privilege and implement role separation (sometimes also interchangeably referred to as Separation of Duties).

There are seven such roles for SQL Server 2022, though we saw three of them already in Azure SQL DB. Andreas takes us through the four new roles in detail.

Tom Zika enables cross-database access:

I’ve recently had to revisit this topic and spent a lot of time recalling the details. So I’m writing this blog post mainly as a reminder for myself.

The most helpful part will be the diagram detailing all the components and their relation and a comprehensive example anyone can follow.

I’m not going to cover Module Signing in general (I’ll leave that to Solomon Rutzky).
Nor will I cover other ways to achieve Cross DB access (like Cross DB Ownership chaining) because this is superior from the security standpoint.

Click through to see a good way of providing access to cross-database resources without explicitly granting rights to users.

Lukas Vileikis starts a series on security in MySQL:

Security is a critical part of any infrastructure – it’s even more so in the database world, where one step in the wrong direction can be the cause of system disruptions and downtime, customer dissatisfaction, and in the worst-case scenario – deaths.

As MySQL is one of the primary relational database management systems being used in the world today, it’s critical to understand how to go about properly the database infrastructure to keep data breaches away from the organization’s websites or, if they’ve happened already, to become a thing of the past.

The first article covers some of the basics of security in MySQL. The second promises to have more.