Explaining SQL Server Security, Part 3: Permissions

In an earlier couple of posts, I had described three critical elements of SQL Server security: pieces, people and permissions.

For a non-technical person (such as, perhaps, an auditor) to understand SQL Server security, they need to have a grasp of all three elements.

You can read more about the four pieces of SQL Server as well as the different types of people (i.e. logins).

In this post, I’m going to cover the third essential element: Permissions.

Part III: Permissions

Now we have the pieces and the people. But how can they do anything?

In other words, what can the people (the logins/users) do to the pieces (the databases/schemas and views)? For our purposes, we care about their abilities to add or change data in the database.

We can grant that ability in two ways:

  1. Through a direct permission

In a database, someone has a permission when they can something to something. For example, they can insert (i.e. add a new) record to the General Ledger Transaction table. Or they can update (i.e. change) records in that table or delete them.

Given the above definitions, we can grant permissions to people to Insert, Update and Delete data in a couple of ways:

  • We can grant a user or group permission on an individual table. For example, we can give NH\ajacobson the ability to update the general ledger transaction table.
  • We can grant a given user or group permission to an entire schema. Let’s say we want to give an entire group access to a given application that uses a certain schema. Rather than go table by table, we can give rights at the schema level. For example, let’s say that in addition to dbo, we had another schema called custom. We could let users in the NH\r3grp update the custom schema.
  1. Through server or database roles

Of course, just as it would be tedious to handle permission for every user, it would be equally tedious to handle every permission on a table-by-table (or other object) basis.

Therefore, SQL Server has what’s called roles. A role gives certain overarching rights (such as read data on an entire database). Roles exist at both the database level and instance level.

Roles can be assigned at the database level and at the server level.

Roles that we care about for our immediate purpose at the database level include:

  • db_owner —can do anything to the database, (including update data)
  • db-datawriter —can write data to any table on the database.

At the instance level, we care most about sysadmin, which gives rights to anyone to do anything. It essentially makes them the equivalent of sa. And we don’t want to give this permission to many people.

In Summary

In these three posts, I’ve tried to give an overview of SQL Server security for folks with minimal understanding of it, which can include auditors.

Let me recap by highlighting the key terms:

  • Instance
  • Database
  • Schema
  • Table
  • Login
  • User
  • Role

If you know these terms, you’ve got a place to start.

Of course, tracking all these permissions can be tedious. The good news is that all of this is stored in SQL Server meta data. And I have queries (which I’m working on cleaning up) that report on all this.

If you’d like the queries, please let me know.

 

Explaining SQL Server Security, Part 2: People

In an earlier post, I explained three essential elements of SQL Server security: pieces, people and permissions. Your auditor (or any other non-technical person) will need to have an understanding of all three parts to understand how security works in SQL Server.

In this post, I’m going to explain the second element: People.

Part II: The People

While a database with no access would be secure, it wouldn’t be all that useful. So how do we tell SQL Server who gets access to the system?

We start at the instance level and look at logins:

Logins:

A login is a way for a person or program to gain access to SQL Server.

Let’s analyze a few of the logins I’ve set up here.

Single Windows User Login

In the above example, NH\ajacobson is a single windows user login.

When you see a \ within a domain, you’re seeing a login that first existed in Windows.  Almost everyone reading this post uses a Windows login when they come to work each day and starts up their PC.

Generally, the single user (in this example NH\ajacobson) also has login rights to Windows. We know it’s a single user because of the icon which represents one person…

as opposed to the icon for a group,  which kind of looks like multiple people (discussed below):

It’s almost always preferable to use this kind of login. With it, you don’t have to worry about passwords expiring within the scope of the accounting system and database because all that’s taken care of by your Windows admin.

Windows Group Login

NH\R3Grp is an example of a Windows group login.

As you can imagine, it wouldn’t be efficient to set up EVERY user as a login to your SQL Server. Generally, (especially for accounting applications), if a single user needs access to the database, many other users in the organization will need similar access. Therefore, Windows allows you to create a group.

So, let’s say you have 20 folks in accounting that will all be running reports over the accounting database. You can have your Windows administrator set up a group (in this case R3Grp) and include all the accounting users in that group. This way you can maintain security for the group and not for the individual. (Of course, your administrator will need to produce a list of everyone in a given group for your auditors.)

SQL Server Login

fransales is an example of a SQL Server login.

When you don’t see the backslash character in a login, the login hasn’t been set up in Windows. That means no one is going to login to their PC as fransales. However, sometimes people will use an application that uses SQL Server, and they’ll be asked for this user name and password.

In general, we want to avoid using these kinds of logins. Using them means maintaining password security in SQL Server. Also, these kinds of logins (and their passwords!) wind up getting hardcoded into spreadsheets. Not that I would ever do something like that.

A Special SQL Server Login

Before I finish with logins, I want to talk about one special login that exists on every SQL Server: sa. Which stands for system administrator.

The sa account can do ANYTHING to EVERYTHING on the server. How to secure this login requires another blog post. But the point is, whatever else happens on your server, if someone has access to this account they can do anything.

Database Users

Logins alone aren’t enough. You need to “attach” the logins to individual databases. In effect, you have to say not only can this guy use this server, but he can also use this database.

For example, I have a custom database that I want the NH\R3Grp to use. So, I’ve added them as a user to that database:

If we hadn’t added R3Grp to this database, users in this group would have no access to it.

So far, we’ve discussed the pieces of SQL Server and the people with access to SQL Server. In my next post, I’ll talk about permissions—allowing people to do stuff with the pieces.

 

Explaining SQL Server Security, Part 1: Pieces

I get calls from clients to answer auditor questions on a somewhat regular basis. These questions usually start with the accounting software and then drill down to the database level. Then, I often get this entirely reasonable question: Who can change data on SQL Server?

This is, as I said, an entirely reasonable question. Unfortunately, sometimes the person asking the question knows next to nothing about SQL Server. Which makes it difficult to answer this question without launching into a lengthy explanation of SQL Server Security 101.

(It’s disheartening when this happens. You would think that a person working for a large national firm sent to audit a system would have some knowledge of the database. It’s not like we’re using dbase or internally described files on the AS400. Yet, in talking to other folks, this is far from an uncommon problem.)

So, to save you the trouble, I’m going to walk through SQL Server Security 101 (or, more accurately, 000, or maybe -101). You can use it to explain to non-IT people how SQL Server security works.

To capture all this in a short series of posts is going to be a challenge—and I’m sure I’ll have to oversimplify.

The situation reminds me of one of my college professors. When I argued that a question on a western civilization test was way too big for a test, she stated that every question has a five-minute answer, an hour answer, a term paper answer and a thesis answer. And you have to learn to give the appropriate answer for the time given.

Here, I’ll be giving you the five-minute answer.

You’ll note that I’ve divided this series into three posts:

  1. Pieces—The pieces that make up SQL Server
  2. People—The people or groups that get access to SQL Server
  3. Permissions—What the people can do to the pieces.

I’m purposely going to start by using language that’s not special to SQL Server but can be understood by anybody.

Again, the goal here isn’t to describe how to do each thing. It’s to describe how to explain each thing to an auditor who will probably never use SQL Server.

Part I: The Pieces

SQL Server is complex software. Sadly, it doesn’t have a simple switch you can flick on and off to denote “keep end users from doing anything that’s going to cause a problem.” Instead, it’s made of up a variety of pieces that combine to make more precise distinctions.

So, to understand SQL Server security, you need to understand the pieces.

To start, you need to know four things:

  • Instance
  • Database
  • Schema

Below is a visual representation of SQL Server. (In this case, I’m using Dynamics NAV, but it could be any software.) In it, I’ve circled examples of these four things:

Let’s start at the top.

The Instance

In the example, the first line is the name of the instance. (I’ve greyed out the actual company name for privacy.)

Essentially, an instance is one installation of SQL Server, meaning that someone took the software and ran the install program. Mostly (but not always), you’ll find one instance of SQL Server running on one physical server or virtual machine.

The instance is important because every user who has access to the system will need to be set up at the instance level. (We’ll get there later in this series.)

The Database

The database in this example is Cronus.

A database is really just a collection of data about a given topic. In the world of accounting software, you may have separate databases for production and testing of a given software. You may also need, as in Dynamics GP, a separate database for each company that is setup.

From an audit perspective, you want to focus initially on the databases which hold production data.

Databases are important because you can grant users permission to do things at the database level and these permissions will give them rights to the next lower levels.

The Schema

In this example, the schema is dbo.

A schema is a way to organize the various tables and other objects that make up the database that store the data.

In most accounting systems I’ve worked with, only one schema exists per database, and it’s called dbo. However, you can have multiple schemas. Again, this is important because you can grant access to a schema and that access will apply to lower levels.

The Tables

Tables are where the actual data is stored. For example, in Dynamics NAV General Ledger, transactions for a given company are stored in a table called Company$G_L Entries. In Dynamics GP, General Ledger Transactions are stored in GL20000 and GL30000.

(Of course, databases contain other objects besides tables (e.g. views and stored procedures) but today, I’m going to focus on tables.)

Tables are the lowest piece to which you can assign security in SQL Server. (At least, in all accounting software I’ve seen.)*

So, to review, we have four pieces in SQL server:

Instance->Database->Schema->Table

And security settings at higher levels affect security settings at lower levels.

In the next post in this series, I’ll discuss the second part of the security puzzle: People.

*Theoretically, there’s a feature in SQL Server called row-based security that allows you to assign security to parts of a given table. But I know of no accounting software that uses this feature.

 

 

Azure SQL Database vs. SQL Server on an Azure Virtual Machine

I’m working on my first paid project using Azure. Previously, I’d set up test databases in Azure but nothing focuses the mind like having to get something working for a real client. I am hardly an Azure expert, but most of my clients are interested in or using some sort of cloud application, of which Azure SQL database is one. So, part of my motivation for writing this post is to get my thoughts clear.

Why Azure SQL Database Should Work for Me and My Clients

In the SQL server universe, I spend most of my time writing queries and doing integration development on SQL Server. I’m not a production DBA. I only learned about the back end to help various clients survive SOX and other financially related orders. So things like backups and redundancy are areas where neither my team nor I have in depth experience. Most of our clients have someone else (whether an ERP VAR or an in house IT group) who’s responsible for keeping the infrastructure up and running.

For me, this is the big selling point of Azure. If I don’t have to worry about the back end stuff, it makes it easier to create our solutions.

Also in Azure’s favor: developing on Azure SQL Database is much the same experience as any other database—whether on-premise or in a hosted environment like Rackspace. Indeed, we do the vast majority of our work remotely. So, while there are some small annoyances (as long as you stay within one database), it feels no different.

Why Azure SQL Database Doesn’t Work for Me or My Clients

There are two big reasons why Azure SQL database doesn’t work for me:

  • Lack of SSRS (SQL Server Reporting Services)
  • Cross database functionality.

Lack of SSRS

Azure SQL Database is well named. It is SQL Database, not SQL Server. SSRS is not part of the package. (Other parts of SQL Server aren’t included either, but we don’t use them as frequently).

Therefore, if you want to use SSRS (which we do. Always.), you’ll be spinning up another server. And this means you’ll end up responsible for much of the maintenance you’d hoped to avoid with Azure SQL Database. Granted, the scope of maintenance can be much less. But you still have to deal with it.

Cross Database Functionality

In standard SQL Server, accessing another database is as simple as changing your table reference from dbo.mytable to mydatabase.dbo.mytable.

In Azure SQL Database, it’s not nearly as simple. You can’t even switch databases in SSMS without opening a new query.

There are ways to access other databases, but they’re much more cumbersome. (I won’t describe the procedure here. But if you’re interested, I recommend these posts by Paris Polyzos and Torsten Grabs.)

In my business, I work across multiple databases all the time. It’s part and parcel of our commitment to provide “the reports you need from the software you already have.”

And I’ve written posts about how to manage issues of cross database code. (For example, this one on a Dynamics GP integration.)

Given the multi-database nature of our work, Azure SQL Database’s restrictions on accessing other databases would require a major rethink. This may be worth it. But not right now.

Conclusion

Azure SQL Database can make a strong case for some uses. But considering how my team and I spend most of our time, for now we’ll be sticking with SQL Server in a virtual machine.

 

SQL for Super Users Part 7: Date vs. Date-Time

If you haven’t read all the other parts in this series, you’ll want to start with the introductory post.

I was initially unsure whether to include the topic of dates and date times in an introductory series. It tends to confuse the heck out of people.

But given how often mistakes are made, it’s an important topic. So let’s give it a go.

We’ll start with an example. Let’s go back to the Order table:

You’ll notice the OrderDate column has more than just dates—it also has times. (I find this incredibly aggravating. If you don’t need time, don’t include time! But I digress.)

Since the time is always zero, we get the same results whether or not we include time in the WHERE clause. (Advanced users know that running the query without time is less efficient, but that’s a discussion for another day.)

Here’s a query without the time:

And here’s a query with the time explicitly set to zero.

But what if every row doesn’t have the time set to zero? (This happens often in inventory and point of sale tables. In these tables, it’s not enough to know the day something happened. You need to know the time something happened.)

Unfortunately, the sample Northwind database doesn’t have an example of such data. So I’m going to update one record by setting the time ahead one second:

Now, watch what happens if I don’t include the time:

We only get seven rows back.

But, if we fix the query to select the entire day, we get back all eight rows:

And we can see the time in the last row is set to one minute after midnight.

So remember, when you have a date/time column, you need to consider whether time plays into it.

As a final step, let’s clean up the data:

By the way, be careful when you use UPDATE. Hopefully, if you’re working on your company’s data, your system administrator only gave you select privileges. Otherwise, a few wrong keystrokes can cause a lot of damage.

I hope you’ve enjoyed this six part series on SQL for super users!

As mentioned in the intro post, if you’d like copies of sample documents that include the tables I created for some of these examples, let me know.

I’m also planning to create examples using sample data in NAV and GP. Again, let me know if you’d like a copy when they’re ready.

 

SQL for Super Users Part 6: Aggregates and Having vs. Where

New to this series? Start with our intro post.

So far in this series, every query we’ve built has returned details, i.e. one row of output for every row selected in the database.

But sometimes, we want totals only, not details. Or maybe we want the biggest (maximum) or smallest (minimum) value of a column from the rows selected. These are called aggregates.

The Ins and Outs of Aggregates

In this post, we’ll focus on the “SUM” aggregate, which is just like SUM in Excel. It delivers the total amount of a column over a number of rows.

In the following example, we’ll develop a query to get the total dollar value of orders summarized by product for a given period in time.

We start with a basic order detail query:

As you can see, SQL Server returns 57 rows.

Before we proceed, let me make a few points about this query:

  1. Math on columns

In the above example, I have a calculated amount:

od.UnitPrice * od.Quantity = ExtendedAmt

The “*” performs multiplication, just like in Excel. SQL Server supports many math functions as long as columns are numeric.

  1. Alias for calculated amount

After I calculated the amount by multiplying Unit Price * Quantity, I assigned an alias to the column. I called it ExtendedAmt.

When creating a calculated column, you should always give it an alias. This makes it easy to reference in reporting and analysis tools.

  1. Brackets around Order Details

SQL Server, unlike most other databases, allows developers to put spaces and other special characters in table and column names. This is a bad idea. But as reporting people, we can’t do much about it. We just have to make sure to enclose any names within brackets.

  1. Date range

You’ll see in the output that OrderDate is a date/time (giving hours, minutes and seconds) while I’ve used only dates in my example. This can cause problems, but I’ll deal with that in my final output.

Now, back to our example.

We wanted sales by product. So, let’s trim the query. We need to add a GROUP BY clause and the aggregation SUM:

The GROUP BY clause states how we want to summarize the rows—in this case by ProductId.

Note that we still include an ORDER BY clause. Just because we group something does not mean the output will display in the same order.

This query returned 35 rows.

You might have noticed that we didn’t provide an alias for SUM(od.Quantity) so the TotalQty column has no name. We fix that here:

So we have a basic total. But it seems we’ve made things too simple—we don’t have the product description any longer.

Let’s add it back in:

Oops. That didn’t work. Let’s look at the error message:

Column ‘dbo.Products.ProductName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

That means just what it says. Once you use GROUP BY, every column in the select list must:

  • It must be included in the GROUP BY clause, or
  • It must be enclosed with an aggregate, such as SUM, MAX or MIN.

Let’s try it with both methods.

First, let’s add ProductName to the GROUP BY function:

That works just fine.

Second, let’s surround “prod.ProductName” with an aggregate.

(In this case I use MAX. I could just as easily used MIN. For a given product, every ProductName is the same.)

That covers the basics of aggregates. But before we finish up, there’s one more clause I want to cover. It’s called HAVING.

HAVING vs. WHERE

HAVING is similar to WHERE in that it filters rows. But it’s different in that it filters rows based upon the output of all the other statements.

Let’s look at an example. Say we only want to see products that have had more than $1000 in sales for the month. We could use HAVING for this:

The query only returned 9 rows.

But again, the filter was based on the Total Amount for the product, and it works on the aggregate.

Let’s try the same example using WHERE.

If I put the comparison in the WHERE clause, it just doesn’t work:

Let’s try it again but make another mistake—we remove the aggregate. The SQL works but we don’t get what we expect:

We only get seven rows back.

Why? Because SQL is only finding rows where the Extended Amount is greater than $1000 for the individual order line, not the total amount.

Therefore, we need to use HAVING, as in the first example.

 

SQL for Super Users Part 5: The Left Outer Join

If you’re new to this series, you should start by reading our introductory post.

In the previous posts in this series (on single table joins and multi-column joins) we had matching records in our two tables.

But sometimes, you may not have data in your second table. This kind of thing can come up when (for example):

  • All vendors don’t have a 1099 code
  • All employees aren’t assigned to a department
  • All GL accounts don’t have a sub account or cost center.

And this is where a left outer join comes in handy. We use it when we may or may not have a record in the second table, but we want all the data from the primary table.

To illustrate, let’s look at two tables: Customers (from the Northwind sample database) and Red3States (which I created).

As you can see, it has 91 rows. The region column is returning “NULL” in some rows. And in some places, it looks like it’s returning state abbreviations (e.g. “WA” for Customer LazyK and “CA” for Customer LETSS).

Let’s add the state name to the query:

Luckily, I have 50 rows in my state table.

Now, let’s put the two together:

So, we have state names that make sense. But only received 13 rows back because those were the only rows where the region was actually a state name.

Therefore, we need to change the join, using a left outer join instead:

Now, “OR” matches to “Oregon” and “SP” returns ‘NULL,” meaning no value was found. And we’re back to 91 rows, which is what we want.

The basic rule: If you’re adding a table to your query—and you don’t expect a match to always exist—use the left outer join.

 

SQL for Super Users Part 4: The Multi-Column Join

To read this series from the beginning, start with our intro post.

The previous post in this series gave an example of the simplest possible join—a join on a single column. But life isn’t always that easy. In real-world Dynamics tables, the key often includes multiple columns. (For example, when a customer has multiple ship-to addresses.)

Unfortunately, the Northwind sample database (which we’ve been using for this series) doesn’t have an example of this. So for the purposes of this post, I created two additional tables: ship-to address and order header.

How to Create a Multi-Column Join

I’ve titled the ship-to table, Red3Ship. And I’ve set up two customers, each with two ship-to addresses:

Now, let’s look at the Order table. We have one order for each CustomerID/ShipToID combination:

First, let’s add the customer name (i.e. CompanyName) to the query:

We still have four records.

Now, let’s add the ship-to name (i.e. ShipToName):

The good news is we have the ship-to name. The bad news is we’ve doubled the record count. We now have eight records.

You might guess (based on my example in the previous post) that the problem arose from not specifying the join properly. And you’d be right!

We can fix this in two steps.

First, in addition to the ShipToID from the Order table, we include the ShipToID from the Red3Ship table:

We can see here that while each order has one ship-to, our query doesn’t bother matching the ship-tos. It joins to both records.

Therefore, we need to fix the join by adding a join on ship-to, not just on customer:

And we’re back to four records.

The point is this: You need to know the complete key when you join. Otherwise, your query will return too many records.

In other words, you want “the key, the whole key and nothing less than the whole key.”

 

SQL for Super Users Part 3: Adding Another Table Using JOINs

Querying data from a single table doesn’t help much in the real world. We need to be able to query more than one. And to do that we have to use joins.

Joins are a big topic. Even without going into too much detail, I’m going to need three posts to give an overview.

Let’s start with a simple example. We want to add category name to the previous query we built.

Here’s the query against the Products table as it currently exists:

And here’s the Categories table:

If we look at the columns in SSMS, we see that CategoryID is the primary key:

And we can see that CategoryID is a foreign key:

As mentioned in Part 1 of this series, in most ERP systems, the foreign key is implicit. You can’t always rely on the handy visual cue of these little silver keys.

Let’s put the two tables together. (I’m going to intentionally make some mistakes because I still make these mistakes. And I’m sure you’ll make these mistakes too when you get started.)

First, we add the second table to the FROM clause and the CategoryName column to the SELECT statement:

As you can see, SQL Server returns an error message, saying that CategoryID is ambiguous.

This means that the identical column name exists in both tables. Consequently, SQL Server doesn’t know which one we’re referring to.

We run the query again, specifying which table we want:

We put the table name before the ambiguous column name, and the error message goes away. Even better, we now have CategoryName in our output.

But wait, we now have 96 rows in our output! Taking a closer look, we can see that ProductID 1 shows up eight times—one for every category in the table. We’ll address that in a minute.

But first, I’m not crazy about how the query looks. Repeating the full table name requires a lot of typing or cutting/pasting. And also, some columns have table names while others don’t. As we add other tables, we’ll probably have to go back and fix other columns to solve other ambiguities.

To solve this problem, we create a few aliases:

After each table we added a little abbreviation—“prod” for “dbo.Products” and “cat” for “dbo.Categories.” These are called aliases.

After we created the aliases, we then prefixed each of the column names, wherever they appeared in the query, with the appropriate alias.

This is an excellent practice for building queries. While many things change, one rule holds true: People ask for more data on every report you build. And therefore, your queries become more complex.

Creating comprehensible and consistent aliases makes life easy for you and anyone else who looks at your code. (And don’t get lazy and try to use aliases “a,” “b,” and “c.” Those won’t work for long.)

Now let’s get back to the issue of our query returning 96 rows. Why is that happening?

It’s happening because when we added the extra table, we didn’t tell SQL Server how to match records in our tables. This is what JOIN is for.

To fix this, we simply add a JOIN clause and tell SQL Server which columns should match:

Thanks to the added JOIN, we’re now back to 12 rows.

We continue the topic of joins in part 4 of this series, where we’ll cover the multi-column join.

 

SQL for Super Users Part 2: SELECT Statements

If you’re reading this series for the first time, you’ll want to start with our introductory post. It explains everything you need to know about this series.

In Part 1 of this series, we covered some basic terminology.

In today’s post, we move on to create a couple simple queries using SELECT statements.

But first, you need to know these four terms:

SELECT specifies the list of columns you want to include in your output.

FROM specifies the list of tables you need to access

WHERE limits the output based on the values you specify

ORDER determines how to sort the output.

A Simple Query

Let’s start with a very simple query:

SELECT * FROM dbo.Products

If we enter this query in SSRS, we get the following output:

This is the simplest possible query in SQL. But even so it contains several mistakes:

  1. We didn’t specify any columns for output

We asked for everything, and we should never do this in the real world. Database tables can and do change over time. When we use the wild card, the output will change when the table changes. And this can cause reports to blow up.

  1. We didn’t limit output with a WHERE clause

With no limiting WHERE clause, this query selects every record in the table. Which is fine when you only have 77 records (as in the Northwind Products table we’re using). But it’s not fine when you have several million records in your sales table.

  1. We didn’t sort the output with an ORDER clause

The output looks like it’s sorted on ProductID, so what’s the issue? Unless you state it, order is NOT Guaranteed. If you want a particular order, you must specify it.

So, let’s fix the mistakes. Let’s start by specifying four columns:

Now, let’s select records using a WHERE clause where CategoryID = 1.

In the bottom right hand corner, we can see that SQL Server only returned 12 rows. The first query returned 77 rows.

When exploring data for the first time, sometimes it’s hard to figure out how to limit output when you don’t know what the data contains. In that case, we have another option for limiting data: The TOP function.

Below, instead of using a WHERE clause, we specify the number of records we want to return with TOP.

Even with no WHERE clause, SQL server only returned five records.

For one final example of a simple select, let’s sort the records using ORDER by ProductName:

Now, ProductID 39 comes before ProductID 38 because “chartreuse” comes before “cote” in alphabetical order.

In our next post in this series, we’ll get acquainted with a single table join.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult