There's the light, as this is the last class for Security week at SQL University. From the scenarios we specified in the introduction class on Monday, this is the last and easiest case we'll ever have with respect to imposing our security mindedness on, but it also can represent the most challenging situation because we have a blank slate. This is the case where the application is being built in-house and we're brought in at the very beginning to ensure that our input is processed and put into the design. Everything is still on the table, so let's talk about the three As (Authentication, Authorization, and Auditing) and how we apply them.

Authentication:

The mantra is Windows authentication unless it doesn't work (and the previous scenarios present cases where this is true, like yesterday's). Yesterday also covered the scenario where you're looking at a service account versus coming in as the end user. Certainly, if it is the latter case, Windows groups are the way to go. But is there anything else we should consider?

Of course! Since you're starting from scratch, you're going to want to determine what the different logical levels of access are. Now while what I just said refers to authorization, it affects authentication. If you have three different levels of access for the application, there may be three different groups required in Active Directory. And that means you'll need to add those as separate logins to SQL Server. But once you get past the Windows vs. SQL authentication and service account vs. context of the end user discussion, anything else related to authentication is going to depend on authorization, so let's dive into that.

Authorization:

Through functional specs, talking with the end users, discussions with the developers, and looking at an existing system being replaced (if that's the case), you can get an idea of the different levels of access that are needed. Now, if the application is going to use a service account and manage those levels within the application, there's not a whole lot for you to do on the SQL Server side. You need to be aware of what all the application does so you can honor the principle of least privilege, but other than that, your work is basically done once you give that superset of rights to the service account.

But what if it is the end users coming in? In that case you're going to want to create roles for each of the levels of access. Those roles should be well-named. And those roles should have the permissions needed for that access and nothing more. If it makes sense, you may even need to nest those roles. I've seen it done both ways. In the interest of keeping things simple, I've seen folks create the roles and assign permissions explicitly to each role, with no nesting of roles (or no making a group a member of multiple roles). I've also seen it done where nesting is in place.

An example of this is if you have a Customer Support Analyst role and Customer Support Manager role. If the Customer Support Manager should be able to do everything the Customer Support Analyst does and more, then you could treat it in several ways:

• Handle each role separately, meaning both roles will be granted the same permissions, with the Customer Support Manager being granted additional permissions.
• Grant the Customer Support Analyst its proper permissions. Grant the Customer Support Manager the permissions it needs which aren't covered by the Customer Support Analyst. Make the Customer Support Manager a member of the Customer Support role.
• Grant the Customer Support Analyst its proper permissions. Grant the Customer Support Manager the permissions it needs which aren't covered by the Customer Support Analyst. For the database user corresponding to the Customer Support Manager, make it a member of both roles.

My preference is the first. It tends to be the cleanest and easiest to understand at 3 AM, thereby passing the "3 AM test' for me. However, this will be an architecture decision you'll need to decide based on the requirements of the application, how your organization functions, etc.

Another thing to coach up, if necessary, is the use of stored procedures if you're talking a traditional application. I'm not going into the performance arguments. But stored procedures as opposed to direct access to tables and views means you carefully control data access. Combined with ownership chaining and you can build a tight data access model that ensures that folks and applications aren't doing things like an UPDATE SomeTable SET SomeColumn = SomeValue and leaving off the WHERE clause. From a security perspective, this is better handling of the C for Confidentiality and I for Integrity in the C-I-A triad.Now obviously, if you're talking about end users reporting off a warehouse, stored procedures are not what you're looking for here. In that case we're talking access to tables and views. So again, think about what is needed, how the system will be used, and don't immediately implement a "best practice" because it's a "best practice." Consider how it fits. If it doesn't fit, don't force it in.

Auditing:

The beautiful thing about auditing here is you can have it explicitly built in and instrumented throughout the code that acesses the database. This means you don't have to rely on triggers. If you're able to even control SELECT queries through stored procedures, your stored procedures can provide the audit information so you're not forced to a third party data protection product or the use of the SELECT object or extended events or a trace. Of course, all of those might still be a consideration because it can be easy to leave off such auditing in a stored procedure. And that's the strongest argument for the use of triggers that I've heard with respect to auditing. You expect them to fire, and you don't have to worry about a developer forgetting to include the proper calls for auditing in a stored procedure. So this causes me to quote Paul Randal (blog | twitter) and say, "It depends," if we're talking about how to build auditing in.

Certainly, if you're not familiar with the options, review Day 2, but realize you now have the flexibility to consider all of these potential solutions.