Welcome back to our last class this term on security in SQL Server here at SQL University. According to the syllabus, you should be picking up history next week with Jorge Segarra. SQL Server has a rich history. I know you'll enjoy the coming week. However, before we move on, let's close on some intermediate security topics. First let's talk about something I call "multiple paths."
"Hi, my name Is... What? My name is..."
Within Windows, a Windows user can be a member of numerous security groups. And the way permissions work, permissions can be assigned to any, all, or none of those security groups. For instance, when I was an infrastructure and security architect, I wore several hats. Each of those hats was mapped to a security group. So if you looked at my membership, here's what you would have seen:
It's entirely possible to allow multiple security groups to connect to SQL Server. For instance, on a particular SQL Server, Server Admins could be allowed in as well as DBAs. DBAs would have full rights over the SQL Server. Server Admins would have rights to use a couple of databases, but not all the databases on the SQL Server. So when I come into SQL Server, who am I? It looks like a complicated mess, but really it's not. I'm both a member of DBAs and a member of Server Admins. So as far as SQL Server is concerned, I come in as both. That's right, both. So that raises the next question: What are my permissions?
If you remember back to last class, I talked about how you could have multiple permission sets. We used the example of three sets, which I called sets A, B, and C. And we talked about how the permissions added up, unless there was a DENY involved. Well, what we were discussing then is possible based on the situation I'm presenting now. I'm authenticated by SQL Server against the login for Server Admins and against the login for DBAs. And since the DBAs have complete control over the SQL Server, I have complete control over the SQL Server.
Let's take another example. Assume I'm not longer in the DBAs security group. Let's say the Server Admins have access to a database called ServerInventory. The Incident Response Team Members have access to a database called IncidentResponse. And there's another database called DBAsOnly that only the DBAs can get into. Since I'm a member of Server Admins, I have access to ServerInventory. Since I'm a member of the Incident Response Team Members I also have access to IncidentResponse. But since I'm no longer a member of DBAs, I do not have access to DBAsOnly. Make sense?
Another way to think of it is imagine a row of lockers, like back in high school. Being a member of Server Admins gives me a key to one locker (database). Being a member of Incident Response Team Members gives me a key to a different locker. The school (SQL Server) looks and sees I'm a member of both groups. Because of this, it hands me both keys. But since I'm not a member of DBAs, I'm not handed the key to the locker for DBAsOnly. Hopefully that gives you a visual picture of how SQL Server handles who you are. This is done both at the server level as well as at the individual database level. And because I can hold multiple keys (logins), those can map to multiple permission sets, like what we covered on Wednesday. Really, this is no different than file and folder permissions at the operating system level. It works the same way in SQL Server as it does in Windows.
"Know your role!"
The Rock had many sayings when he was in World Wrestling Entertainment (formerly the World Wrestling Federation), but among them was, "Know your role!" When it comes to SQL Server, this saying is important. At the server level, it is possible to put a login into several pre-defined roles which we call fixed server roles. These give a blanket set of permissions. Take, for instance, the sysadmin fixed server role. That role allows you to have complete control over the SQL Server. If a login is a member of that role, it can do anything within that SQL Server. And just like at the server level, there are roles at the database level, too. There are fixed database roles which have preset permissions, just like server roles. There are also user-defined database roles. These are role you can create. They are just like Windows security groups in that they are designed to group together database users. Also, you can assign permissions against the role, just like with security groups. Therefore, at the database level, the best practice recommendation is straight-forward:
Ownership Chaining
Ownership chaining is a feature of SQL Server. Basically if you have two objects, and they have the same owner, they can form an ownership chain. For instance, let's say I have a table and a view. The view refers to the table. If both objects have the same owner, then I can have an ownership chain. Let's say I have the ability to issue a SELECT against the view. But I have no permissions against the table. If I try to issue a SELECT directly against the table, I'll get an Access Denied error. But if I issue a SELECT against the view, it works. It works even though the view hits the table. Why? Because SQL Server allows an ownership chain to form.
Basically, SQL Server sees that the same user owns both objects. So it assumes that if the owner has built one object to refer to another, like our view referring to our table, the owner meant for the reference to work. And as a result, when you go from the view to the table, SQL Server won't check permissions on the table. It will assume the owner knew what he or she was doing when the reference was created. We can use this to control access to the base tables in our databases. By controlling access through functions, stored procedures, and views, we can control how the data is displayed and how the data is manipulated. With ownership chaining, access to the base tables happens, but only through the methods we've explicitly built. So, for instance, this won't work:
DELETE FROM dbo.SomeVeryBigTable; GO
If an end user tries to execute this, that person will receive an Access Denied type of error. This is good because imagine if they were able to execute it. Then we're stuck in recovery mode and that's bad. Now imagine we built a stored procedure like the following:
CREATE PROC dbo.ControlledDelete @SomeID INT AS BEGIN DELETE FROM dbo.SomeVeryBigTable WHERE SomeID = @SomeID; END; GO
In this case the end user can still delete rows. But our stored procedure only allows the deletion of one row at a time, and you need to know the right ID. We now only have to grant EXECUTE rights against this stored procedure and everything will work as we'd like. We don't have to grant any sort of permissions against the table itself. All right, enough on the theory. Let's round out the week with a few demonstrations. Afterwards, look to our coach for some drills to help you get stronger in SQL Server security.
Practical Demonstrations:
It's good to see all your bright and chipper faces here at SQL University. Hopefully you've digest Monday's lesson on Authentication. Today we're going to discuss Authorization. Now Authorization can only take place after Authentication has been accomplished. If SQL Server doesn't know who you are, it can't determine what you have access to. Therefore, if you've not gotten the class notes and looked at the videos, it may help you to review before proceeding further. If you're caught up, let's continue. Authorization is simply the permissions a particular person has. With respect to SQL Server, SQL Server is basically saying, "Once I know who you are, I can tell you what you are allowed to do."
Permissions - A Game of "Simon Says"
If you've ever played the game of "Simon Says," you know that you only react to the instruction given when Simon says to do so. If you react to any other instruction, you're out of the game. If Simon says to do something and you don't, you're out, too. SQL Server works kind of like Simon says. If you tell SQL Server that a certain permission is to be given to a user, then it's like you played Simon says, only you are Simon. SQL Server will carry out that permission exactly as you said. So if you tell SQL Server that Jimbo has the ability to read data from a particular table (granting SELECT permissions on that table), then SQL Server will allow Jimbo to read data from that table whenever he asks for it. Likewise, SQL Server will not allow access if you do not give explicit permission. SQL Server is the ultimate Simon Says player. So if you don't tell SQL Server that Jimbo can read data from that table, SQL Server won't let him (with the exception of ownership chaining, which we'll look at on Friday).
Securables - A Whole Lot of Tupperware
When I was growing up, Tupperware was expensive. It's not like today when I can run down to the Dollar General and pick up disposable containers for a couple of bucks. In SQL Server, starting with SQL Server 2005, there is a new concept called securables. Securables, in a nutshell, are anything you can assign permissions against. There are also special types of securables called scopes, which are nothing more than securables that can contain other securables. Within SQL Server there are three scopes:
Servers have securables like logins and endpoints as well as the securables/scopes databases. Databases have securables like users and symmetric keys as well as the securables/scopes schemas. And schemas have the securables we normally think of like tables, views, and stored procedures. That's the hierarchy. Now, normally we grant permissions directly against tables, views, and stored procedures. So if I grant SELECT on a table, then the person can execute a SELECT query against the data in that table. If I think of Tupperware, my wife has given me permission to open up the Tupperware containing the salad. But the Tupperware containing the lasagna, well, that's off limits. So I have SELECT rights against the Salad Tupperware but I have no rights against the Lasagna Tupperware. SQL Server is my wife, but SQL Server is always on duty. So if I try and open up that lasagna, I'm going to immediately get an Access Denied!
Now let's take this a step further. If you do it right, you can put smaller Tupperware containers inside larger Tupperware containers. This is the concept of using scopes in SQL Server. Imagine that the lasagna container fits inside the salad container. My wife has given me permission to open up the salad container. Lapsing back to my past as an 8 year-old boy, I interpret her permission as saying, "You can eat anything in the salad container. So I take the lasagna container and put it inside the salad container, dumping any salad that prevents the true prize from fitting. I close up the salad container and then say to myself, "Well, time to eat what's in the salad container." And then I proceed to open both containers, first the salad one, then the lasagna one, and eat the lasagna. Sure, my adult self knows I'm in trouble as soon as my wife catches on, but we're talking lasagna! SQL Server operates as our eight year-old selves do. If you give the permission on the larger container, or scope, it applies to the securables contained within. For instance, if I give SELECT permission against a schema, all the tables and views contained in the schema that permission applies to as well. So we need to think about that when it comes to giving out permissions. We can either give it directly to the object (securable) in question or to the scope that contains it.
GRANT, DENY, and REVOKE - Understand the Interaction
GRANT gives the permission. So if I GRANT SELECT on a table, the person can read from the table. DENY blocks the permission. So if I DENY SELECT on a table, the person cannot read from the table. Also, DENY is a trump. If you've played the game of Spades, all spades cards trump cards of any other suit, regardless of numeric value (so a two of spades will trump a 10 of diamonds or an ace of clubs). When you've got spade against spade, it's all about numeric value. As a result, unless you're playing with house rules which include jokers, nothing trumps the Ace of spades. The DENY is like the Ace of spades. It doesn't matter what the other permissions are, if a particular person has a DENY, it will trump any GRANTs that the person may already have. And that bears explanation.
It is possible for multiple sets of permissions to apply to a particular person. We'll get into the how on Friday, but assume that three different sets of permissions apply to a particular person. Set A grants SELECT against a table. Set B grants INSERT and UPDATE against the same table. SQL Server will aggregate the permissions and give you the sum of all of them. So if we're just considering sets A and B, the person has SELECT, INSERT, and UPDATE on the table. If you've worked with permissions at the file and folder level on the operating system, you should be familiar with this type of behavior. Now let's look at set C. Set C has a DENY on UPDATE. Remember, DENY trumps everything. So the DENY on UPDATE in set C is going to trump the granted UPDATE in set B. So once we consider all 3 permission sets, the person really only has SELECT and INSERT.
By the way, you can replace one for the other. So, for instance, assume set C should have been a granted UPDATE instead of DENY. If you decide to replace DENY with the GRANT, you can do so simply by executing the GRANT permission. So when applied in the same manner (again, covered on Friday), they will replace each other. So if that's the case, why do we need REVOKE? REVOKE is a giant eraser. If the person has a particular permission, REVOKE removes it. It doesn't matter if it's GRANT or DENY. Instead of overwriting the permission, it simply gets rid of it. So if we go back to set C, and we didn't want to grant UPDATE permissions, but we wanted to get rid of the DENY, we could use REVOKE. If set C had permissions for other tables, we might not want to get rid of set C altogether. But we do need to correct the mistake without granting any additional permissions. In this case, REVOKE is ideal. It gets rids of the permission altogether. If the person doesn't have permission via another mechanism, we're back to that game of Simon Says again. Since Simon (or John or Marie or whoever you are) didn't say the person should have access, then the person doesn't gets access. If, however, the person had access through another means, such as set B, then the DENY is no longer there to block them.
Practical Application:
Okay, enough theory. Like last class, let's see some of this in action. Again, the videos are around 5 minutes or shorter.
Jorge Segarra (@SQLChicken) has put together an idea to have on-line basic lessons on SQL Server called SQL University. The idea is to present two to three lessons each week in a somewhat academic manner, one that will hopefully encourage learning. It is targeted at the new or "accidental" SQL Server DBA. In this endeavor he has asked around for others in the community to pitch in and help. I've volunteered to take on Security Week (sounds like Shark Week, a nod to my friend and fellow SQL Server MVP, Frank Kalis, who loves sharks).
Jorge has already gotten the first week of lessons up. In addition, he should be making an annoucement soon as to some of the other faculty members as well as the athetic director so stay tuned here:
SQL University
In this presentation we'll look at the use of triggers for added database security. We'll start with the typical DML triggers used to audit changes to data and best and worst practices with their implementation. Then we'll move into SQL Server 2005 and 2008 with the use of DDL and Logon Triggers which allow us to prevent changes to the database schema, to report on certain types of login behavior and to even block logins if we see them from unauthorized applications. In addition, we'll briefly consider the Audit object, a feature of SQL Server 2008 Enterprise Edition, which allows us to finally perform audits on SELECTs against critical tables without having to resort to SQL Server traces.
I had a need to export permissions to be run against the database once an older version was restored (but which didn't have the permissions yet). I'm sure there are 101 versions of the script out there, but here's mine:
SELECT CASE dp.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE dp.state_desc END + ' ' + dp.permission_name + ' ON ' + CASE dp.class WHEN 0 THEN 'DATABASE::[' + DB_NAME() + ']' WHEN 1 THEN 'OBJECT::[' + SCHEMA_NAME(o.schema_id) + '].[' + o.[name] + ']' WHEN 3 THEN 'SCHEMA::[' + SCHEMA_NAME(dp.major_id) + ']' END + ' TO [' + USER_NAME(grantee_principal_id) + ']' + CASE dp.state_desc WHEN 'GRANT_WITH_GRANT_OPTION' THEN ' WITH GRANT OPTION;' ELSE ';' END COLLATE DATABASE_DEFAULT FROM sys.database_permissions dp LEFT JOIN sys.all_objects o ON dp.major_id = o.OBJECT_ID WHERE dp.class < 4 AND major_id >= 0 AND grantee_principal_id <> 1;
It filters out anything other than database, object, or schema permissions, so if you're looking on exporting permissions on encryption keys, assemblies, etc., you'll need to expand it. It also ensures that permissions against dbo (which you can't do) are filtered out. If you're wondering about the COLLATE DATABASE_DEFAULT, I was having an issue where it was telling me that it couldn't determine the proper collation. That solves that. And I wanted to make sure the WITH GRANT OPTION was covered, so that added a couple of CASE statements.
I haven't tested it against all scenarios, but if you're looking basic permissions, it should grab and script those.
In a recent forum thread, someone asked to see who created/altered a particular object. Starting in SQL Server 2005, this information is contained in the default trace, provided that the default trace is enabled and the information hasn't rolled out of the trace files. The catch is how to report on the information easily. If you've used it before, you're likely thinking of the Schema Changes History report. The catch is that if you're using SQL Server Management Studio from SQL Server 2005, this functionality wasn't available until Service Pack 2. So if you have just installed the workstation tools and you've never applied a service pack, you won't see the option for the reports because it's not there. The same is true if you have a full SQL Server installation that's still RTM or SP1. The only way you see the change to SSMS is to apply SP2.
Which brings up a good point. You should be applying service packs to your client systems as well. This was a lesson we learned the hard way on SQL Server 7. It seemed like with every new SQL Server service pack, the definition of DTS packages changed. The only exception was if you encrypted your DTS packages with a password. That didn't change. And so what it meant was if you had different SP versions between your servers and your client systems creating said packages, you had a problem. Because ones with later service packs could read packages created with older service packs, but not vice versa. And as I'm writing this, Aaron Bertrand has just put out a blog post talking about an SSMS issue that is fixed with a hotfix, which you should take a look at, especially if you're dealing with mixed environments. So the point here is that in addition to keeping your SQL Servers up to date. you also need to plan on keeping your SQL Server client tools up-to-date as well. Not only are new features provided, but fixes are included with the updates, too.
Another SQL Quiz started by His Evilness, Chris Shaw. Okay, Chris isn't evil; I just wanted to say "evilness" and these quizzes are of value to the community. I was tagged by Jorge Segarra, better known as @SQLChicken. So here goes.
Do you feel that you have a reliable SAN Solution? If so what is the secret?
Yes, I do. The secrets aren't so secret:
Like I said, nothing in any of that is rocket science.
Explain Database Mirroring in layman’s terms
Let's say I've got two very well trained monkeys. They throw and catch pretty near perfect. However, they can only do one or the other at any given time, you just have to tell them which they are doing. If either monkey tries to both catch and throw, we're in for it. What do these monkeys toss around? These monkeys get bits of information in, process it, and then toss it over to the other monkey. And they do this blazingly fast. So at any given time both monkeys should have the same information. In asynchronous mode, that's not guaranteed. The monkey doing the tossing could take a break, go chase a banana, or what have you after he processes some information and forgets to make the throw. Then again, the receiving monkey could be on break himself and not catch a bit of information heading his way. You're in this for speed, not accuracy. But speaking of that, you're only allowed to talk to one monkey. That's one making the throws. You are providing him the info, and then he processes it and tosses it over to the other monkey. Should you try and talk to the other monkey, he's going to ignore you. Now, if the monkey you're talking to decides to wander off, as monkeys are known to do, you've got to go over and specifically tell the receiving monkey that he's up. He'll listen to those words, because he wants to be the thrower. And once he does, you can start talking to him and passing him info.
Now let's say you want to ensure that both monkeys have the same information all the time. This is synchronous mode and now we need another monkey. This monkey we'll call the witness, but he's effectively the ref because he's wearing the zebra shirt. He's carefully watching all the information coming in to the first monkey and ensuring that after it gets processed, that it gets thrown over to the second monkey and processed there, too. Should any of that fail, the witness is going to blow his whistle and call a foul. The first monkey must then take out that bit of processed information. Either both monkeys process it or no monkeys do. As you might expect, this is a bit slower than the other mode. But that's required for data consistency. Oh yeah, the witness can do one other thing if you want. If the witness notices the first monkey keel over, say it was trying to keep up with SQL Rockstar in viewing all 24 hours of PASS, then the ref, er, witness, can call a foul and tell the receiving monkey to start talking. In this scenario, the second monkey can start handling your data and will. However, it sets aside the information in hopes that the first monkey comes back on-line. If the first monkey stays down for the count and the second monkey keels over, too, then you potentially have some of your information lost.
Tagging:
The other day a colleague and I were shadowing a vendor who was installing their 3rd party application on a hosted server. The vendor went ahead and created an alias for the application but initially created it with Named Pipes. Needless to say, the alias didn't work. Given that this application is hitting against a SQL Server 2005 installation, the Named Pipes protocol is disabled by default for remote connections. Only TCP is enabled. The vendor originally went back and configured the alias to use TCP, which did work. But this got me to thinking, "Why did the vendor choose Named Pipes?"
If we look in Books Online, we see the following quote:
Generally, TCP/IP is preferred in a slow LAN, WAN, or dial-up network, whereas named pipes can be a better choice when network speed is not the issue, as it offers more functionality, ease of use, and configuration options.
This caused me to pause. Over the network I've seen issues with Named Pipes, such as unexpected disconnects that I've not seen with TCP/IP. I saw this last year, as a matter of fact, when an application would periodically disconnect from the database. The problem was that this application did not attempt to reconnect nor did it fail gracefully. It crashed out every time it lost the database connection. This application commited one of the fallacies of distributed computing: assuming the network is reliable. And this is on a modern switched LAN at typical LAN speeds.
So I'm not in agreement with the assessment given in Books Online. As a matter of fact, I would definitely disagree with respect to SQL Server 2005. In SQL Server 2005, only the TCP/IP protocol supports Kerberos authentication, which overall is a better security protocol than NTLM. Named Pipes has to use NTLM. Until SQL Server 2008, there is no option to use anything but. And even in SQL Server 2008, it means being aware of a potential additional SPN that's not needed for TCP/IP.
Which all leads to the question that's still nagging at me, "Why use Named Pipes? What's the benefit of doing so over the default of TCP/IP (or shared memory, for local connections)?"
I was playing around with the endpoint catalog views this afternoon just looking at ways to do poor man's configuration collection on SQL Server and the options avaliable. The endpoints naturally represent the way in to SQL Server and since TCP is the default network protocol for SQL Server 2005 and 2008, I was looking specifically at sys.tcp_endpoints. Basically, I was looking to execute the following:
SELECT [name] , [state_desc] , [port] , [is_dynamic_port] , [ip_address] FROM sys.[tcp_endpoints];
This query would seem to return a lot of useful information. It returns the name of the TCP endpoint, whether or not it's active, what IP address and port it is listening on, as well as whether or not that port was configured dynamically. However, what I got back was 0 for port, 1 for is_dynamic_port, and NULL for ip_address for several servers I hit. basically, these are the three columns in sys.tcp_endpoints that are not in the catalog view sys.endpoints. They represent TCP specific configuration information. But with one glance it's obvious that the information being returned isn't useable. So I went back to Books Online and checked, and sure enough, there was this proviso:
The information that is displayed regarding ports and IP addresses is not used to configure the protocols and may not match the actual protocol configuration. To view and configure protocols, use SQL Server Configuration Manager.
So if you're looking at trying to extract the IP address and port SQL Server is listening on from this catalog view, don't. It can't be relied upon. The guidance from Books Online says to use SQL Server Configuration Manager and that certainly works, but it's a GUI tool, and not useful for retrieving the information using an automated process. All of the information on TCP endpoints is in the registry, it's just a matter of parsing the information out. So I'll be looking at writing a quick script that does just that.
Note: Since there have been several comments on this, I'm using parameterization at the application layer in the security sense of using the CreateParameter method. I'm not talking about parameterized queries with respect to execution plans or the specific use of sp_executesql. I thought I made that clear with me saying "proper parameterization at the application layer" but since there have been several comments on that, it must not be.
One of the main defenses touted against SQL injection attacks is to use proper parameterization at the application layer. But while this gets most of the cases, there are clearly examples where this alone fails. For instance, consider the stored procedure:
CREATE PROC dbo.usp_ExecuteSQL @SQL NVARCHAR(4000) AS BEGIN EXEC(@SQL); END;
Now I know the natural response by most folks is, "I would never see that in a production application." Perhaps not. But I have. In one organization's main application I found this procedure not once, but twice (albeit with different names). There was a standard in place that all database access from the application had to be done through stored procedures. The standard was met. But naturally because of such access, you can imagine what the permissions looked like within the database. And you can imagine the abuse that could have been performed through these types of stored procedures.
I know this is an extreme example, but it reflects that just focusing on parameterization isn't enough. For this stored procedure, the parameter would be defined properly, but that wouldn't stop SQL code from running in the back-end. Now I know the argument is, "Well, it's because the stored procedure used dynamic SQL." True enough. However, there are lots of cases out there where dynamic SQL solutions are running in production. For instance, I've seen things like the following:
USE [AdventureWorks2008]; GO CREATE PROC dbo.usp_ReturnPeople @Sort NVARCHAR(100) WITH EXECUTE AS SELF AS BEGIN DECLARE @SQL NVARCHAR(MAX); SET @SQL = 'SELECT Title, FirstName, MiddleName, LastName, Suffix FROM person.Person ORDER BY ' + @Sort; EXEC (@SQL); END
This stored procedure falls prey to situations like where @Sort can be sent in as '1;DELETE FROM person.Person;' which would effectively delete all the data in that table. And since @Sort would pass the parameterization check, using parameters doesn't serve as an effective control to prevent the SQL injection. Now I know there are techniques involving XML or using the CASE statement in the ORDER BY clause, but these aren't immediately obvious. As a result, it is possible to see an example like I've given above than those solutions. And yes, one could pare down the size of the parameter and that would help greatly, but when folks are trying to be flexible, these things aren't necessarily on their radar. Nor would a consideration be made to use EXECUTE AS with a defined user account that only has SELECT permissions against the table. And unless there is stringent code review, these types of things may slip into production and once they are there, they have to stay there until there is time to do another build.
Therefore, if we rely strictly on parameterization as our defense, we can still be beaten, even without someone working maliciously on the back-end code. Both examples I've given were written by well-meaning people who were trying to develop a reasonable solution. And the solutions they came up with solved their problems and they moved on. They didn't consider the security ramifications. Maybe they weren't aware enough to them to code defensively against them. Or maybe they were under a stringent deadline and were trying to get the job done. Whatever the situation, the vulnerability is there. So how do we guard against that sort of thing? Two things come to mind:
Now none of this is new. In fact, David Litchfield talked about second order SQL injection attacks at BlueHat and has published work on lateral SQL injection attacks in Oracle. The crux of all of this is to say there isn't one single vaccine to cure SQL injection. Parameterization is effective, yes, but a cure all it's not. There can be structures in the database that permit SQL injection attacks despite parameterization. And therefore, we must not stop at just using parameterization, but we must investigate further to ensure that we catch those vulnerabilities.
Log File Sizes:
It's not unusual to see cases where database backups are taken from production and restored to a development or QA environment. Often times the database in production requires point-in-time recovery, meaning we set it up to use full recovery mode. However, when it goes to non-production, there is no need for that level of recovery. There are also cases when we create a new database and we don't check its recovery mode. By default, the database is going to be created with full recovery. In both of those cases we can see the transaction log grow very large. Sometimes, we can even see cases where the transaction log is larger than the size of the database files. We can see the size of the database files by the following simple query:
SELECT NAME, type_desc, (size / 128) AS [Size_in_MB] FROM sys.database_files;
The size column is the number of 8-KB pages allocated to the file. So in order to quickly determine the value in MB, we can multiply by 8 to get the KB and then divide by 1024 to get MB. That's the same as dividing by 128, hence the calculation on that column.
When running this against a particular sample database I created, here were the results.
With the data file size only being 20 MB and the log file size being 1024 MB (or 1 GB), we have an issue. Likely the database is in full recovery mode. Since it's non-production, it likely only needs to be in simple recovery mode. We can see what databases are not in simple recovery mode by executing:
SELECT [name], recovery_model_desc FROM sys.databases WHERE recovery_model_desc <> 'SIMPLE';
Once the databases are known, they can be changed by using the ALTER DATABASE statement with the RECOVERY option. For instance, for the sample database I cited above, I would issue the following statement:
ALTER DATABASE SampleDB SET RECOVERY SIMPLE;
Once that's done, the next step would be to shrink the files to an appropriate size. The command DBCC SHRINKFILE() can be used. For instance, to attempt to shrink the log file down to 5 MB, I would issue:
DBCC SHRINKFILE('SampleDB_log', 5);
The entire space may not be reclaimed. For instance, after running this command, the log file shrunk to 255 MB. That's not the 5MB I wanted, but it represents recovering about 3/4 of the space used,
Database Ownership:
When a database is created or restored, unless otherwise specified, the owning login for the database is whoever performed the operation. At my organization we prefer to ensure databases are not owned by a login that corresponds to an actual person. The reason being is that if that person is no longer a DBA but still has the ability to log on to the database, then the user will be able to implicitly do anything he or she wants within the database. That's because SQL Server automatically maps the login who owns the database to the dbo user within the database. That user bypasses all security checks within the database. To prevent against this, we periodically check database ownership and re-assign accordingly. To determine who owns each database, a query against sys.databases is all that is necessary:
SELECT NAME, SUSER_SNAME(owner_sid) [Owner] FROM sys.databases;
Once you determine which databases are owned by actual people, changing the ownership is simple. In SQL Server 2005/2008, you can use the ALTER AUTHORIZATION command. For instance, to change the ownership of SampleDB to be owned by sa, I'd execute the following:
ALTER AUTHORIZATION ON DATABASE::SampleDB TO sa;
And if I run the query again, I'll see that ownership has changed.
When it comes to logins to SQL Server, there are basically 3 types:
The latter two we have limited control. We can grant them the ability to connect to SQL Server and we can then make them users within the databases. But with respect to passwords and password expiration/strength, there's nothing we can do within SQL Server. Those accounts (or the accounts which are members of the group, in the case of a Windows group login) are controlled either by the operating system (an account local to the computer) or by Active Directory (domain account). So other than auditing for their existence on a SQL Server and their fixed server role memberships, there's not a whole lot more we do with respect to them at the server level. SQL Server-based logins are a different story.
With SQL Server-based logins, we can (as of SQL Server 2005) control whether or not they adhere to the password policies set at the operating system level (and which the computer may receive from the domain via a group policy). We can choose whether or not password complexity and lockout is enforced as well as whether or not password expiration is checked. In SQL Server 2005 and above, the key to audit this information is to use the catalog view sys.sql_logins. The reason for using this over sys.server_principals is that it only returns information on the SQL Server-based logins and it returns all the information we would want to check related to a SQL Server-based login. It also inherits all the columns from sys.server_principals, meaning we don't have to reference that catalog view as well.
Here are several simple queries that we can use to audit our SQL Server environment:
SQL Server Logins which are currently disabled:
SELECT name FROM [sys].[sql_logins] WHERE [is_disabled] = 1;
SQL Server Logins which do not adhere to the password policy:
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0;
SQL Server Logins which do not adhere to password expiration:
A note about this one. Password expiration is only checked if the password policy is enforced. If the password policy is not enforced, then SQL Server won't check for password expiration, meaning that SQL Server login effectively has a password that never expires. Therefore, we need to check both when there is no password policy enforcement as well as when there is password policy enforcement, but no password expiration enforcement.
SELECT name FROM [sys].[sql_logins] WHERE [is_policy_checked] = 0 OR ([is_policy_checked] = 1 AND [is_expiration_checked] = 0);
One of the videos I did for JumpStart TV is up on the front page:
SQL Server Authentication Modes
It is an introductory video to help understand the two types of authentication SQL Server can perform: Windows authentication only and Mixed Mode. It's primary purpose was to cover the two modes and how to switch SQL Server back and forth between the two. However, I did cover a bit about the SQL Server 2005 attack surface.
It turns out that in SQL Server 2005, you can use an SQL Server login connection attempt to determine whether or not the SQL Server is configured for Windows authentication only or for Mixed Mode. Pick a SQL Server login you don't believe will be a defined login for that SQL Server. Then attempt to login with it. I used TestAuthenticationMode. If the SQL Server is configured for Mixed Mode, you'll get the following error message back:
But if it's configured for Windows authentication only, you'll get a different error message:
Fortunately, this does not work for SQL Server 2008. In both cases, you get the error message indicating the login failed.
In a previous blog post on Detecting When a Login Has Implicit Access to a Database, I mentioned that having CONTROL SERVER rights means having implicit rights into the databases. Robert Davis posted a comment asking if there was a difference with respect to explicit permissions between being a member of the sysadmin fixed server role and having CONTROL SERVER rights. Members of the sysadmin fixed server role bypass security meaning even if you issue an explicit DENY, they still have access. The same as someone who maps in as dbo for a given database. You could DENY to public, which should encompass said user, but it doesn't matter. The DENY is ignored.
However, members of the db_owner fixed database role do NOT ignore DENYs. So that got me to testing to see if there was a difference between the sysadmin fixed server role and CONTROL SERVER rights and there is. Having CONTROL SERVER righs still means you can be blocked. An easy example is the following, we'll create a login, grant it CONTROL SERVER rights, the DENY CONNECT SQL for that login.
USE master; GO CREATE LOGIN TestControlServer WITH PASSWORD = 'AStr0ng0ne,Sort0f...'; GO GRANT CONTROL SERVER TO TestControlServer; GO DENY CONNECT SQL TO TestControlServer; GO
If you then attempt to connect, you'll get that the login failed. The explicit DENY is preventing the login, even though the login has CONTROL SERVER rights. But what happens if we make the login a member of the sysadmin fixed server role?
EXEC sp_addsrvrolemember 'TestControlServer', 'sysadmin'; GO
If you try it again, this time you should be able to connect. Therefore, having CONTROL SERVER rights may be the functional equivalent of having membership in the sysadmin fixed server, except that explicit DENYs do still affect the login.
One of the things that we have to re-learn when going from SQL Server 2000 to 2005/2008 is that objects no longer have owners. Rather, objects are contained in schema and schema have owners. And if you query sys.objects, you will see that this seems to hold true. While there is a principal_id column, normally the value is NULL for all objects. I was looking at it the other day and first wondering why there was a principal_id column. I took a close look at the text for sys.objects in Books Online and it says this:
ID of the individual owner, if different from the schema owner. By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.
In other words, objects can still be owned by a user. But the text "alternate owner" made me pause. What exactly did that mean? Well, let's create a test scenario. We'll need two objects in different schema. Those schema will need different owners. And of course, we're going to need a test subject, so a third user will be good. Let's get that set up:
/* Set up the users needed to show the issue. Do this in a "play" database. */ CREATE USER User1 WITHOUT LOGIN; GO CREATE USER User2 WITHOUT LOGIN; GO CREATE USER User3 WITHOUT LOGIN; GO /* Create the different schema and set them to be owned by different users. */ CREATE SCHEMA User1 AUTHORIZATION User1; GO CREATE SCHEMA User2 AUTHORIZATION User2; GO /* Verify the schema are owned by different users. */ SELECT schema_id, [name], USER_NAME(principal_id) [Owner] FROM sys.schemas WHERE LEFT([name], 4) = 'User'; GO
Now that we have the user and the schema, let's create a table and a stored procedure that refers to the table. They need to be in the separate schema we created. This should mean there is no ownership chain:
/* Create the table that will be reference in schema User1. */ CREATE TABLE User1.MyTable (Number INT); GO INSERT INTO User1.MyTable (Number) VALUES (1); INSERT INTO User1.MyTable (Number) VALUES (2); INSERT INTO User1.MyTable (Number) VALUES (3); GO /* Create the referencing stored procedure in a different schema, User2. Since the schema User1 and User2 are owned by different users, typically an ownership chain wouldn't be formed. */ CREATE PROC User2.MyProc AS BEGIN SELECT Number FROM User1.MyTable; END; GO /* Grant User3, our guinea pig user, the ability to execute the referencing stored procedure. User3 has no other rights, especially no rights on User1.MyTable. */ GRANT EXECUTE ON OBJECT::User2.MyProc TO User3; GO /* Verify the objects we just created and the schema and "owners." Note the Owner column shows "--No Owner--." */ SELECT OBJECT_ID, SCHEMA_NAME(schema_id)[Schema], [name], COALESCE(USER_NAME(principal_id),'--No Owner--') [Owner] FROM sys.objects WHERE LEFT([name], 2) = 'My'; GO
Now to test to see if User3 can successfully execute the stored procedure.
/* Execute the stored procedure in the context of User3. This should generate an error indicating User3 doesn't have SELECT rights against User1.MyTable. */ EXECUTE AS USER = 'User3'; GO EXEC User2.MyProc; GO REVERT; GO
The error tells us an ownership chain isn't forming. This is the expected behavior. But what if we use ALTER AUTHORIZATION against User1.MyTable? What then?
/* Now let's add an owner to User1.MyTable. That owner will be User2. */ ALTER AUTHORIZATION ON OBJECT::User1.MyTable TO User2; GO /* Re-query the schema to show ownership hasn't changed on either of them. */ SELECT schema_id, [name], USER_NAME(principal_id) [Owner] FROM sys.schemas WHERE LEFT([name],4)='User'; GO /* Re-query on the objects. Note the Owner now present for User1.MyTable. It's User2, meaning an ownership chain can form. */ SELECT OBJECT_ID, SCHEMA_NAME(schema_id)[Schema], [name], COALESCE(USER_NAME(principal_id), '--No Owner--') [Owner] FROM sys.objects WHERE LEFT([name], 2) = 'My'; GO
And when we execute the stored procedure, it now works. The ownership chain formed.
/* Re-execute the stored procedure as User3 and this time it works. The ownership chain did form. */ EXECUTE AS USER = 'User3'; GO EXEC User2.MyProc; GO REVERT; GO
But here's an interesting consideration: what about other objects in the User1 schema? Can they form an ownership chain with the table? The answer is no, as this script will demonstrate.
/* Let's now create a stored procedure in the User1 schema and see if we have an ownership chain. */ CREATE PROC User1.AnotherProc AS BEGIN SELECT Number FROM User1.MyTable; END; GO /* Grant the ability to execute the stored procedure to our guinea pig user again so we can test. */ GRANT EXECUTE ON OBJECT::User1.AnotherProc TO User3; GO /* And when we do execute the stored procedure, we get the SELECT permission was denied error again. */ EXECUTE AS USER = 'User3'; GO EXEC User1.AnotherProc; GO REVERT; GO
To be blunt, I don't like this capability at all. The ability to do this isn't really talked about a lot in Books Online and I know in the 2005 version there was a lot of confusion when object owner was referenced. When you consider the following, it makes sense: the object owner is, by default, the owner of the schema. If no owner is actually specified for the object, the schema owner is the assumed owner. But you can have an object owner.
This can lead to confusion where there shouldn't be any. What this means, from a security perspective, is we need to check at both the object and the schema level for ownership and consider ownership chaining from there. Also, it poses a real potential for issues, especially if someone does a "one-off" and implements a specific owner on an object trying to make an ownership chain work. I would have preferred that they just left this at the schema level and been done with it. But it's not that way, meaning we've got to be aware of that as we look at database security, especially with respect to ownership chains.
