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:
- Pieces—The pieces that make up SQL Server
- People—The people or groups that get access to SQL Server
- 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:
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.
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 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.
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.
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:
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.