Press "Enter" to skip to content

Category: Security

The Concepts of Data Control Language in SQL

Published 2024-11-12 by Kevin Feasel

Joe Celko talks about the lesser-known language in SQL (compared to DML and DDL):

But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.

The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.

Click through to learn more.

Comments closed

Analyzing Azure Network Security Group Flow Logs

Published 2024-11-08 by Kevin Feasel

Reitse Eskens says the bits must flow:

I had an interesting question lately where I was requested to show all the network traffic within an Azure landing zone. Specifically source and target IP, protocol and port. From the aspect of Zero Trust, it’s important to show both successful and failed connections in your network. To be able to answer this question I had prepared myself by enabling the so-called flow logs on the Network Security Groups (NSG). NSG’s are used to control traffic on the IP and port level between resources. There’s no packet inspection, just a check if IP 1 is allowed to connect to IP 2 on port 3. In this specific case, it also had to do with a migration to Azure Firewall where all the NSG rules had to be validated.

But getting the data is one thing, finding out what is in it is something else. In this blogpost I’ll drag you along the steps I took to get the raw JSON data into a SQL table and analyse the data.

Read on for the process and quite a bit of T-SQL code.

Comments closed

Security Risk Profile in AI-Generated Code

Published 2024-10-29 by Kevin Feasel

Jerome Robert reviews the papers:

As such, nowadays, almost all developers use some form of AI-generated code — and they absolutely should. AI tools make developers’ lives easier by leveraging the knowledge cultivated by the development community over time and across the globe to overcome obstacles that, while potentially new and challenging to them, have long been addressed. They can reasonably trust that code to perform the function they want to achieve — and can test it to be sure.

But can they trust that code to be secure? Absolutely not. With all that time and work spent committing functional code, just as much, if not more, is spent navigating the security backlog afterward.

Click through for a summary of two recent academic papers, as well as links to the papers themselves.

Comments closed

Don’t Trust TRUSTWORTHY

Published 2024-10-28 by Kevin Feasel

Chad Callihan talks about an untrustworthy setting:

TRUSTWORTHY is a database property change that can have far-reaching security consequences when turned ON. Let’s take a brief look at what the TRUSTWORTHY property is and if it’s worth turning on, even when it is a potential fix to your problems.

Chad links to a DBA Stack Exchange post from Solomon Rutzky concerning module signing, which is a good opportunity for me to plug Solomon’s modulesigning.info. This is the correct answer, not TRUSTWORTHY or any of its ilk (EXECUTE AS, cross-database ownership chaining, etc.).

Comments closed

Configuring the Fabric Service Principal to Support Storage APIs

Published 2024-10-17 by Kevin Feasel

Gilbert Quevauvilliers grants some permissions:

This blog post explains how to configure access for my Service Principal to interact with the Azure Storage API to use the API to get details for Microsoft Fabric Storage.

This is part of a blog post series where I am going to show you how to “View Total Storage consumed in Microsoft Fabric”

When I started this blog post I realized that I first need to explain how to configure the Service Principal authentication to interact with the Azure Storage API permissions. This is because in my notebook these steps are required for the notebook to run successfully.

Read on to find out how.

Comments closed

Simplifying Azure Authorization with Managed Identities

Published 2024-10-16 by Kevin Feasel

MIka Sutinen doesn’t need a password:


Managed Identities in Azure provide an efficient and secure way for managing credentials when accessing databases, or other Azure resources. In this post, we build on my recent post about using Service Principal for database authentication, further exploring how we can leverage Managed Identities to simplify the process.

While Service Principals can make some parts of the access management simpler in Azure, you’re still left with several responsibilities in their management.

These responsibilities include rotating secrets and managing the lifecycle of the Service Principal, to name maybe the two most crucial ones. And if you have multiple applications, each with a separate Service Principal, this can be complex to manage.

Click through to learn more about managed identities and service principals, and how they work to link together Azure resources behind the scenes.

Comments closed

Querying Audit Log (.xel) Files in Azure SQL DB

Published 2024-10-14 by Kevin Feasel

Tanayankar Chakraborty reads an audit log:

A recent issue was brought to our attention that customers could not query .xel log files in an Azure SQL DB using t-sql command. The customers complained that when they ran the command, they received column headers but no content whereas they know that there is content in the logs because they were able to open them with SSMS using Merge Extended Event Files. Here was the T-sql command used by the customer:

select * from sys.fn_get_audit_file (‘https://mydbastorage.blob.core.windows.net/sqldbauditlogs/servername/dbname/SqlDbAuditing_Audit_NoRetention/*.xel’, NULL, NULL);

Click through for the solution, which came down to two separate issues.

Comments closed

Permissions and BACPAC Files in Azure SQL DB

Published 2024-10-10 by Kevin Feasel

Roberto Yonekawa diagnoses an error:

We had a support request where the customer was getting an error when trying to export his Azure SQL Database to a bacpac file using the SqlPackage command-line utility.

Error message:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2024-08-21T16:10:56 : Microsoft.SqlServer.Dac.DacServicesException: One or more unsupported elements were found in the schema used as part of a data package.

Error SQL71627: The element Permission has property Permission set to a value that is not supported in Microsoft Azure SQL Database v12.

Click through for the specific issue Roberto found. I’d imagine that there are other permission sets that are incompatible with Azure SQL Database and would cause this error message to pop up as well.

Comments closed

Data Masking in Snowflake

Published 2024-10-09 by Kevin Feasel

Kevin Wilkie puts on the mask:

The premise behind data masking is fairly simple – make it so that only certain people can see the data in a certain column. For example, social security numbers or credit card numbers probably shouldn’t be seen by everyone who has access to your database.

I like to set it up as follows: (Obviously, this can change depending on your business’ policies.)

  1. Admins have full access.
  2. Call Center Representatives have access to the last 4 digits of the social.
  3. All others see nothing useful.

Read on to see how. I’ll even save my normal “data masking isn’t really a security feature” rant. This time.

Comments closed