Voice of the DBA

Tag: SQLNewBlogger

Row-Level Security Basics–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I realized recently that I hadn’t really blogged about Row-Level Security, so this post covers some of the things I know at a high level.

What is Row-Level Security?

This was a feature added to SQL Server in SQL Server 2016 that makes it easy to grant access to rows of data based on some characteristic of a user. At a high level, this means:
- I have something that segregates rows of data, like a CustomerID as a column in an Orders table.
- I want a customer to only be able to view their orders, those associated with their customer ID.
- This has to work, even if they didn’t use a WHERE clause and did a SELECT *.
- In this case, a user for CustomerID 4 would only see Orders that had CustomerID=4 in those rows.
We used to be able to do this with views, but this was cumbersome, and it was obfuscation. There was no security mechanism that actually ensured a user logged in wouldn’t see other rows.

Row-Level Security

This was a first class security mechanism that uses security policies and functions to control access. The way this works is as follows.

We create a function that is a table-valued function which takes a parameter(s) from a column(s) and returns a 1 if the user should view a row. In this case, we would use a WHERE clause in the query in the function that looks for Orders.CustomerID = @CustomerID.

We bind this function in a security policy that binds the function to the table, and specifies the column (or columns) used as parameters to the function. We also specify the predicate involved. There are two types:
- Filter predicates – limit read access
- Blog predicates – limit write (insert/update/delete) access
We give permissions to the function to users.

Does it Work?

Yes. It works very well from a security standpoint. Since we are tying this to users or logins, the performance of determining if the user or login has access can be slow. The IS_ROLEMEMBER() and similar functions are not super efficient and you can have performance issues across millions of rows.

However, it works.

I’ll write more in the future on the details.

SQL New Blogger

I was watching a presentation recently on this topic. I’ve written about this for SQL Server Central, but when I checked, I hadn’t really done much blogging on it.

Here I’m re-using knowledge, but in a basic way. I took 15 minutes to write a high level description. I’ll do a few more posts that demo setting this up for reads, one for writes, maybe one to get around how this might have a hole for security purposes. At least 3 more posts.

You could learn this and blog 3-4 times about what you learn and how to set up it up situations.
November 8, 2021
Quick CSV Import with dbaTools – #SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Recently I was searching around for some sample data for a project. I stumbled on a CSV of data from the Internet Movie Database (IMDB). I wanted to quickly import this into a database to play with and query. I decided to use dbaTools since my csv python skills were a little rusty and I thought this would be quick.

This post looks at how easy this was.

Disconnected from the Internet

I had downloaded the CSV before getting on a plane, but once I was ready to import this, I had no access to the Internet. This meant I had no access to looking up commands or documentation. As a result, I decided PowerShell and dbatools would be the easiest way to do this.

I ran a quick Get-Help command and got the name of the command. Using wildcards, I could see the dbatools result below.

I then ran “Get-Help Import-DbaCsv” to get the syntax. Using the results below, I wanted to see how quickly this would run.

A Quick Experiment

With the docs above, I created a database and then entered this command.
```
$movie = Import-DbaCsv -SqlInstance localhost -Database IMDB -AutoCreateTable -Path "IMDB-Movie-Data.csv"
```
This was my experiment. Just try some values and see what happens here. This ran in less than a second, and from ADS, I could see the data had been imported.

When I checked my variable, I saw that things had just worked with the 1000 rows imported in less than half a second.

Success!

This wasn’t my main task, but using some tools and some past knowledge, I figured out how to accomplish this task quickly and get back to the process of writing SQL code to query the data.

SQLNewBlogger

My entire purpose here was to write some queries against this data, but I needed to import the data. Either ADS or SSMS have import wizards, but I’ve had various levels of success at times with them. I didn’t want to work through an ETL process. Once I saw how quick dbatools made this, I decided to write this post, based on the ease of getting something done.

You could easily duplicate this post, noting why you needed to do an import and how easy this was. You could compare this to SSMS or ADS, or even write about starting to use dbatools for this purpose.

This took me less than 10 minutes.
November 1, 2021
What’s my MAC Address in a Command Prompt?–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The other day I was working with a licensing issue and someone requested my MAC address to track something down. I know I could dig into the adapter properties, but that’s an annoying set of clicks in Windows. I decided to spend that time checking out how to get the MAC address from a command line.

Normal CMD

I tried “ipconfig” at the command line, which I always have open. I got my IP and DNS info, but no MAC. A quick search on Google showed me I needed more detail.
```
ipconfig /all
```
This gives me all the info, as you can see below:

PowerShell

I’d prefer PowerShell for many things like this, mostly because I could programmatically extract the values and use them elsewhere. I don’t need that here, but I might for something.

In this case, this is in the WMIObject, which is an ugly command. I’ll never remember this.
```
Get-WmiObject win32_networkadapterconfiguration | select description, macaddress
```
However, there’s an easier cmdlet.
```
Get-NetAdapter |select MacAddress, Name
```
This will return what I want. I get see which MAC is associated with which adapter.

Both quick ways to get some information.

SQLNewBlogger

I had to get some info, and I took a minute to learn something. This took me just 2-3 minutes to learn how to do this and practice. Then it was less than 10 minutes to do this post.

For reference, I used these links:
October 27, 2021
STRING_SPLIT Basics–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I saw a post on using STRING_SPLIT() with cross apply recently, and as I was reading, I realized that I hadn’t adopted this very often in code. Mostly it’s not something I need to deal with, but recently my wife did give me some data and asked to reformat it, and I used STRING_SPLIT(). I thought it was a nice easy problem, so I decided to write about it.

Conflated Data

The dataset that she gave me was formatted something like this:

What she wanted was a report that provided data more like this:

This was more complex in the past, and hard in T-SQL as string manipulation isn’t great. However, STRING_SPLIT makes this easier. Not great, but easier.

STRING_SPLIT

While STRING_SPLIT is a function, it is a table-valued function, meaning that it is used as the source for a data set. Rather than being in the SELECT column list, we would use this in the FROM clause as another table. Or anyplace a table can be used.

The basics of this would be:
```
SELECT ss.value
  FROM dbo.ClassSchedule AS cs
   CROSS APPLY STRING_SPLIT(students, ',') AS ss
```
Using this code, I would get a list of students.

The STRING_SPLIT returns this single column, value, which is the string passed in split by the separator. I could see this more simply with this code:
```
SELECT * 
  FROM STRING_SPLIT('alpha,beta,delta,gamma',',') AS ss
```
This returns these items:

A few more examples:

The important thing to note here is that this is a simple substring based on the separator, with no other processing. Spaces or any other character outside the separator is left in the result.

There also is no ordering of the results. While this appears to be in the same order in these simple examples, this is not guaranteed, meaning the ordering of the substrings might not match the original order.

This is a good way to easily split up data that you have stuffed into a large character field, but it has limitations, so use it carefully.

SQLNewBlogger

This post took me about 15 minutes to write after spending 5 minutes setting up some test data. I was using string_split to clean up some data and decided to make a few notes, then built a new table with some strings in it to help me explain how it works.

A good example of a post you could write, perhaps noting when this was released or comparing this to a way of splitting these strings with a more complex T-SQL query.
October 18, 2021