A Fine Slice Of SQL Server
Athar Ishteyaque enables an extension:
The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL.
The goal of a PostgreSQL audit is to provide the tools needed to produce audit logs. These logs are often required to pass certain government, financial, or ISO certification audits.
I am kind of curious what the performance impact of this extension is.
Comments closedSteven Vaughan-Nichols gives us the story:
The Python Package Index (PyPI), is the most popular Python programming language software repository. It’s also a mess. Earlier this year, the FortiGuard team discovered zero-day malware in three PyPI packages called “colorslib,” “httpslib,” and “libhttps.” Before that, 2022 closed with PyTorch-nightly on Linux being poisoned with a fake dependency. More recently, PyPI had to stop new user registrations and project creations because of a flood of malicious users. PyPI isn’t the only one to notice the user trouble. The Python Software Foundation (PSF) received three subpoenas for PyPI user data. What is going on here!?
Read on to learn more about what’s happening with the most popular Python repository.
Comments closedQuoth Chad Callihan, “Occasionally more”:
QUOTENAME can be used to make sure database objects are valid in your query. Most of the time, objects like table names only contain valid characters, so there’s nothing to worry about. But nobody’s perfect. Let’s look at an example of what can happen when somebody creates a table with a forward slash in the name and see how QUOTENAME can be used to query against it.
QUOTENAME is also a good way of preventing SQL injection, though I still prefer appropriate use of exec sp_executesql in any case in which it’s possible to use.
Matthew McGiffen joins two great flavors:
In SQL Server you can also compress your encrypted backups. Unlike TDE this has been possible with Backup Encryption since the feature was first made available, and there have been no issues that have required fixing – though as always you should still test that restores work correctly. As mentioned in my post about compression with TDE, compressing backups has benefits not just in terms of file size but potentially also in reduced backup times as the time taken to write to disk is smaller.
Read on for more information. Microsoft did the right thing: they compress first and then encrypt; otherwise, you’re not getting any benefit from the compression.
Comments closedChad Callihan gives us the primer:
I was setting up a dev environment for a new application recently. All seemed to be well until I went to actually run the application. I was getting a vague error in the application and still didn’t know the exact cause. I fired up an XEvents session to find the query causing the issue and found a query failing with the following error:
Msg 6263, Level 16, State 1, Procedure dbo.MyProc, Line 60 [Batch Start Line 14]
Execution of user code in the .NET Framework is disabled. Enable “clr enabled” configuration option.
Incidentally, my single biggest gripe around CLR integration in SQL Server (and something I complain about almost every time someone brings up the feature) is that they really messed up in picking names. CLR has Safe and Unsafe modes.
What far too many DBAs interpreted it as: “I don’t want unsafe code running in my systems! Ban CLR!”
This fundamental misunderstanding of terms killed a smart integration and capability for extremely fast functions—for example, the fastest way to split a string in T-SQL is via CLR, and there’s a lot of impressive functionality you can get at near-native speeds (i.e., if the SQL Server team wrote the code as a function instead of you, allowing them to optimize the database engine for that function) with a few lines of code. But the leads at most places saw the word “Unsafe” and nope’d out.
Comments closedMatthew McGiffen runs some tests:
Unlike TDE, there is some extra CPU overhead when you take an encrypted backup as the data has to be encrypted before being written to disk – whereas with TDE the data is already encrypted. Backup times however are unlikely to be affected significantly as the bottleneck is usually going to be the time it takes to physically write the data to disk. The CPU processing should take a fraction of that time.
Matthew’s tests are on a ~9GB database, which is large enough to estimate differences without having us wait all day to compare.
Comments closedRyan Booz limits database access:
We discussed how this can be done manually with a
GRANTcommand each time an object is created, however, that is time consuming to manage and easy to miss a detail.Instead, PostgreSQL provides a method for setting default privileges which are granted on behalf of the object owner as database objects are created. Using default privileges, a role can prepare the database ahead of time to ensure that consistent access privileges are applied while easing the management burden over time.
But how do you go about creating a set of roles and default privileges that will provide the right level of control and access? Let’s dig a little deeper.
RBAC on groups is definitely the way to go, and Ryan shows us how.
Comments closedMatthew McGiffen lays out the requirements:
When we talk about protecting our at-rest data, the item that we are likely to be most concerned about is the security of our backups. Backups are generally – and should be – stored off the server itself, and often we will ship copies offsite to a third party where we don’t have control over who can access the data, even if we trust that that will be well managed.
From SQL Server 2014 the product has included the ability to encrypt data while creating a backup. This feature is available in both the standard and enterprise editions of SQL Server, so it is something you can use even when TDE may not be a feature that is available to you.
Click through for a primer on the topic.
Comments closedMatthew McGiffen shares some advice:
Database backups continue to work without change when you have TDE enabled. The only difference is that the backups contain encrypted data that cannot be read without the certificate and private key. There are a couple of points that are worth discussing though.
Click through for several notes, including a warning to those still on SQL Server 2016 and woefully under-patched.
Comments closedAaron Bertrand notes some changes:
In 2015, during the SQL Server 2016 beta, I explored a new feature is this article, Always Encrypted. This feature finally allowed us to encrypt data at rest and on the wire, and I showed how beneficial this was and how much more secure your data could be. I also explained that, as a new feature, some limitations made it difficult to use and, sometimes, impossible to adopt.
Several major versions of SQL Server later, how has this feature evolved, and is it easier to use today?
Read on for the answer. Aaron also covers secure enclaves, a big topic for Always Encrypted users.
Comments closed