Press "Enter" to skip to content

Category: Security

Row Level Security Anti-Patterns and Alternatives

Published 2023-09-18 by Kevin Feasel

Ben Johnston tells us why we might not want to use row level security in SQL Server:

One of the primary reasons to implement RLS is to facilitate reporting and ease the administrative burden. This section covers some considerations for using RLS with the primary Microsoft reporting engines and gives you an idea of things to look for in your reporting engine. Some anti patterns and alternatives to RLS are also examined.

This article goes a long way toward explaining why I find row level security so rare in the wild and never implemented it myself: most databases I’ve worked with are either transactional or hybrid OLTP/OLAP, they’re mostly multi-tenant, and they’re accessed through service accounts. That’s just a no-go across the board.

Comments closed

Adding a Service Principal to a Fabric Workspace via API

Published 2023-09-14 by Kevin Feasel

Marc Lelijveld tackles a challenge:

Lately, I found myself struggling using the Power BI REST API to add a service principal to the (Fabric) workspace. After I engaged with some other folks, I managed to succeed. In this blog I will elaborate on the mistake I made and how I got it to work. For some this might be a less useful blog. Though, I still wanted to blog this even if it is for my own memory on how to do this.

Read on for the story.

Comments closed

A SQL Server Security Checklist

Published 2023-09-11 by Kevin Feasel

Hemantgiri Goswami has a list and checks it twice:

Last week, in my previous article on How to Secure SQL Server I have discussed a few points that can help you secure SQL Server. In this post, as promised I will share a SQL Server Security Checklist that I have used for many of my clients to help them achieve PCI compliance.

As you are aware, PCI is global payment security standard council. Following their standards help an organization achieve a compliance certificate that all the card data that is processed, store and transmit are maintained in secure environment.

The good news is that you can use the dbachecks suite to check many of these items.

Comments closed

Row-Level Security Performance and Troubleshooting

Published 2023-09-11 by Kevin Feasel

Ben Johnston digs into row-level security:

There are two main areas where RLS can impact performance. The first is the user or authentication lookup. Some kind of lookup must be performed in the access predicate to determine either the user name, group membership, or specific values in the session context. Considering that RLS is non-prescriptive, the lookup isn’t confined to these methods, but they are very easy methods to use and implement and are standard based on implementations I’ve seen.

The second area is the authorization lookup. The authorization lookup, checking if a user has access to particular rows, can have a much bigger impact on performance. This is also in the access predicate. Following the basic rules for performance and keeping lookups simple goes a long way to minimizing the impact of RLS on performance. The goal is to keep performance levels as close as possible to a table without RLS. If indexes and predicates are correct, RLS can improve performance in some situations due to the automatic filtering that happens.

Read on for Ben’s thoughts on the topic.

Comments closed

Don’t Try These with SQL MI and Private Endpoints

Published 2023-09-08 by Kevin Feasel

Zoran Rilak wraps up a series on Azure SQL Managed Instance and its support for private endpoints:

The first two installments of this mini-series discussed a couple of basic and advanced scenarios involving private endpoints. Today we’ll look at some ways private endpoints cannot be used to implement scenarios where one might expect otherwise.

Read on for four of these in total, laying out things you cannot do via private endpoint to a SQL Managed Instance. In fairness, Zoran also provides what I would consider reasonable work-arounds for each of those: have a VM jumpbox in the same virtual network for DAC connections, peer your virtual networks for replication, and so on.

Comments closed

Automatic SSL Certificate Updates for SQL Server

Published 2023-09-06 by Kevin Feasel

Jamie Wick doesn’t want to waste time clicking mouse buttons:

In a previous post (How Secure Are Your Client Connections?) I covered using SSL certificates to encrypt client connections to SQL Server. One part of the process that has always been annoyingly repetitive is the need to regularly renew/update the SSL certificate(s) and reconfigure SQL Server to use them. In the past our SSL certs were good for 3 years. Then in 2020 Google, Microsoft, Apple & others announced that they were reducing their certificate validity period to a maximum of 398 days. Recently, there have been proposals to further reduce the validity period to 90 days. When this happens (and I’m certain it is a when, and not an if situation), manually replacing SSL certs on multiple SQL servers will not be a viable option.

Read on to see what Jamie’s solution to the problem is.

Comments closed

Row-Level Security Setup in SQL Server

Published 2023-09-05 by Kevin Feasel

Ben Johnston configures row-level security in SQL Server:

Implementing RLS is as much a business endeavour as technical. Without a business case to implement RLS, there is no reason for the extra effort and testing involved. This is where driving out business requirements and making sure the solution fits the problem is important. Non-technical members of the team or business partners likely won’t know what RLS is or if it should be used. Differential access to the same data, replacing systems (or proposed systems) with multiple reports based on user groups, and multi-tenant access are possible indicators that RLS may be a useful tool. There are always multiple ways to solve a problem. If RLS would simplify the design and make it more robust, that’s when I start to seriously consider it for a design. It does help if the business is aware of RLS and have used it in other projects or databases, but having the business essentially design the system is dangerous too. Use all of the information available during planning sessions and design the system that best fits the need of the business and the skills of the technical team.

Read the whole thing. I’m particularly interested in this series, as I’ve liked row-level security and didn’t find any trivial workarounds or exploits, but also hate how slow it is once you’re dealing with large datasets.

Comments closed

The Concept of Schema in Relational Databases

Published 2023-08-30 by Kevin Feasel

Adron Hall explains how different relational database management systems describe schemas:

From the viewpoint of someone familiar with the general idea of a schema, it can indeed seem unusual that databases like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL each interpret and implement schemas in slightly (or sometimes, vastly) different ways. While the core idea behind a schema as a structured container or namespace for database objects remains somewhat consistent, the exact nature, utility, and behavior of schemas vary across these systems.

Read on for an overview of these for four products, as well as what the ANSI standard indicates.

Comments closed

Managing Security Roles for Hierarchical Organizations in Power BI

Published 2023-08-29 by Kevin Feasel

Marco Russo and Alberto Ferrari are working for The Man:

The security model in Tabular used by Power BI can filter rows of a table based on a DAX expression. When security is applied to a hierarchical structure, every hierarchy level is represented by a different column in the table. This structure can make it challenging to define a dynamic security filter based on the name of a node in the hierarchy, because the DAX expression must filter the column corresponding to the hierarchical level in which that node exists. If the security needs to be maintained dynamically in a configuration table, the resulting code may end up being extremely complex and hard to maintain, as well as create possible performance issues.

Without describing the complexity of solutions based on a filter applied directly to the appropriate hierarchy level, we want to describe a solution that minimizes the effort required in maintaining a configuration table for the dynamic security rules, while also providing good performance at execution time by minimizing the processing overhead required to apply the dynamic security.

Click through for the scenario and how you can implement this kind of security model in Power BI.

Comments closed

Testing Row-Level Security in Power BI

Published 2023-08-25 by Kevin Feasel

Wolfgang Strasser puts on the Mission Impossible face replacement mask:

Long time, no Power BI blog post from my side. But today I found out, that the testing of your row-level-security (RLS) logic in the Power BI service changed “a little bit” since I last used it.

Whenever you want to test your RLS logic, you can do this in Power BI Desktop (Mange Roles for definition, “View as” for testing).

Click through for an example of how this works. I like this approach a lot because the people who are developing these reports usually have access to everything, so it’s hard to ensure that you got everything right until people start complaining.

Comments closed