Press "Enter" to skip to content

Day: August 24, 2022

Securing Kafka Streams

Amani Newton gives us a primer on Apache Kafka security:

The largest companies in the world use Apache Kafka® for their real-time streaming data pipelines and applications. Kafka is the basis for the real-time fraud text alerts from your bank and the network-connected medical devices used in your local hospital. Securing customer or patient data as it flows through the Kafka system is crucial. However, out of the box, Kafka has relatively little security enabled. This blog post previews the free Confluent Developer course that teaches the basics of securing your Apache Kafka-based system.

Click through for the overview.

Comments closed

The Importance of Dev Environment Security

Kenneth Fisher shares an opinion:

I was asked a rather interesting question during an interview recently. It went something like this:

Your resume says you’ve implemented security standards. What do you do if a development team comes to you and says “We have to have a SQL Server Id in production (or other restricted permission) because that’s the way we tested it and we are going into production now.”?

And in general my response is “You give it to them. It’s too late to stop them at that point.”

Kenneth goes on to elaborate.

I prefer a compromise solution, which requires more than 2 environments. Dev is a sandbox. Developers don’t know what they need and generally need more space to work than the final product itself will need. A wide-open dev environment is something I don’t mind. In some organizations, a local dev environment may be sufficient for this task—that is, SQL Server (or whatever) installed on the developer’s personal machine or brought in via Docker container.

However, there must be at least one pre-production environment in which developers have prod-level rights. Ideally, it would not be the last step of integration prior to prod but instead something like a QA environment which is part of a CI/CD process.

I should also point out that module signing helps with this problem: stored procedures may contain rights that devs shouldn’t have but are necessary to the way an application functions. Access to execute those stored procedures is something the DBA team can control and put some limits around, so in Kenneth’s example, truncation could be handled via stored procedure. That procedure handles the truncation operation and opens the door to further checks, such as a list (or table) of tables which the app is allowed to truncate, logging of operations, etc.

Comments closed

Standby Mode in Log Shipping

Garry Bargsley will not simply stand by:

Did you know that log shipped databases do not have to sit there collecting dust and not being used? However, there are certain situations where replication or Availability Groups are not the answer to the question of separating your reporting traffic.

You don’t need to build an actual data warehouse, you don’t want the complexity of Windows Cluster for Availability Groups, and you cannot use replication because your tables do not have primary keys.

We can use the long trusted Log Shipping technology and tweak a few settings and get a read-only copy of a database.

Click through to see how you can make use of those log shipped databases.

Comments closed

Variance in Parallel Query Performance

Joe Obbish takes on parallelism:

You may have noticed large variations in elapsed time for parallel queries while performing query tuning or observing a production workload. This post reviews some of the possible explanations for those variations. I’m testing on SQL Server 2022 RC0 but this behavior can be observed on all currently supported versions of SQL Server.

Joe has an interesting example (and my guess of how bad the performance would be was not too far off, though I did underestimate the difference), as well as several possible causes and mitigation strategies for slow parallel queries.

Comments closed

When Trivial Plans Hide Relevant Information

Chad Callihan wants the optimizer to sweat a bit:

When you execute a query, the SQL Server query optimizer determines potential query plans and decides which plan is the cheapest. It’s possible for a query to be so simple that there’s no need to bother weighing out different plans before returning results. In this case, SQL Server may use a trivial plan. Let’s look at an example of this plan and one reason why simple may not always be the best.

Click through for an example.

Comments closed

Granular Permissions in SQL Server 2022

Andreas Wolter notes some permissions changes:

In the last 3 years, the SQL Security team has put more emphasis on enabling customers to use SQL Server while adhering to the Principle of least Privilege (PolP). As part of that effort, all new features in the next SQL Server release: SQL Server 2022, can be controlled with more granular permissions. SQL Ledger is a good example of such a new feature. It comes with separate permissions for creating (ENABLE LEDGER) vs dropping ledger tables (ALTER LEDGER); a separate permission to view ledger content (VIEW LEDGER CONTENT) for auditing purposes; and another independent permission to generate a ledger digest (GENERATE LEDGER DIGEST).

Besides new features, a lot of effort also went into improving the existing set of permissions, namely by making them more granular. This has happened in 3 distinguishable areas:

Check out the changes they’ve made.

Comments closed

SQL Server 2022 RC0 Availabile

Ajay Jagannathan has a big announcement:

Continuing with our release cadence, we’re excited to announce the release of SQL Server 2022 Release Candidate 0. Since the first public preview in May 2022, anyone can download SQL Server 2022 RC0 to try the new features in this release.

The changelist doesn’t look enormous for this release and as we get closer to RTM, that set usually drops off a bit as code stabilizes in preparation for release.

Comments closed