About Robert Verrel
Robert is a SQL Server Professional working in the Nashville, TN area where he is active in the PASS community. He is originally from Mississippi and is a big Mississippi State fan.
Transcription: Principle of Least Privilege
Carlos L Chacon: Welcome to the SQL Data Partners Podcast. My name is Carlos L Chacon, your host. This is episode five.
Today, we’re talking about roles and responsibilities in the “Principle of Least Privilege,” with my guest Robert Verell. Super excited to have Robert on today. He’s been able to do some pretty interesting things in his environment. I’m glad that we’re going to be able to share that with you today.
As always, you can check out our show notes at sqldatapartners.com/podcast for today’s information, and for previous podcasts episodes. We are on iTunes, and if you like today’s episode, I invite you to give us some feedback. Rate our program, and let us know how we’re doing. If there’s something you’d like to hear about, we’d like to get that information as well.
If you feel so inclined, we invite you to subscribe to our channel so that you’re always getting the latest information, the latest podcast that we’re putting out. With that, let’s get in to it and welcome to the show.
SQL Data Partners[music]
Carlos: Compañeros! Thanks for tuning in today.I would like to introduce our guest, Robert Verell. Robert’s a DBA for Cigna-HealthSpring, where he has worked as a senior database administrator for seven or eight years now. He’s a Microsoft certified IT professional. He hails from Nashville, Tennessee. Robert, welcome to the program.
Robert Verell: Hi, Carlos. Thank you for having me.
Carlos: So Robert, tell us what you’re doing these days and where people can connect with you.
Robert: Well, I’m speaking at SQL Saturdays. That’s always a great time. Also I’m pretty active on Twitter. This year I’ve launched my blog and you can go to that on sqlcowbell.com. That’s also my handle, if you will, on Twitter. It’s @sqlcowbell. It’s S-Q-L cowbell. So I’m real easy to find.Usually if you send me some kind of message or anything like that, I’m fairly quick to respond.
What I’m currently working on is I’m working for Cigna-HealthSpring. On the Cigna side, we’re developing an internal cloud. They’re doing database as a service on that end.
There’s a lot of challenges to that. There’s no elevated rights for anyone, so we had to figure out how to develop certain ways of doing a request. Since you don’t have elevated rights at the server level, you’re not able to create logins. So how do you do that?
You have an interface, obviously, that someone can go to and say, “I would like to have this user set up as a login in my database and give them these rights.” Basically, they pass in some sort of [inaudible 03:13] directory name, and that gets sent over to a procedure that I’ve written, and it’s set all that up for them.
Carlos: That’s one of the reasons that we wanted to have you on the show today, Robert, is that principle of least privilege. How simple server roles can help us attain that.I know in the past, you’ve given a presentation about these roles and I know that in your current position you’re working a lot with this. I thought you’d be perfect to come in and chat with us about some of the nuances or difficulties that we might have. Also, because you’ve been able to be successful, how folks can actually get over the hump and implement this.
When we talk about the principle of least privilege, I guess we’re talking about giving users only the rights that we need.
Robert: That’s correct, and Brian Kelly, if you’re familiar with him, he had a blog post back, several months ago that I read. It really hit home with me. As far as giving privileges to people on a database, if you follow the principle of least privilege, you should go all the way down to the column level. If someone does not need to be able to see certain columns in a table, then they should not have rights to it.That means going all the way down and getting granular and going that deep with it if you want to strictly follow the principle of least privilege. Now, I’m not telling anybody to go out and follow that to the letter, because I can’t even imagine trying to administrate that. [laughs]
Carlos: Yeah, that’s a…Lots of overhead there.
Robert: There is a good bit of overhead there, yes. At the same time, you want to make sure that you have a secure environment. You want to make sure that people have rights to things that they need, but not things that they don’t need. In case of any kind of malicious breach or something along those lines.If someone has access to sensitive data that they really shouldn’t or don’t really need to have access to, and their credentials were compromised. Now you’re talking about your organization having to send out apologies to millions of people, and losing money and a stock drop, and things like that. You just don’t want that on your conscience or on your permanent record, as it were.
Carlos: Sure. And I think, while there may be folks kind of gunning for that, the column-level security, ultimately what we’re talking about today is much, much higher than that. Providing at least some initial environment where you can begin to — I don’t want to say, “lock things down.” Only give users what they need to make, your environment a little bit more secure.As we’ll talk about a little bit later, potentially avoid an opportunity where you may be unable to provide services that you thought you were going to be able to provide.
Robert: That’s correct. Absolutely. If…
Carlos: So…I apologize. Let’s go ahead and jump in to some of the justification that users might request or things that our database administrators are getting requested with. Why they might default to those elevated privileges. Because that being the administrator role or in the case of the database and SQL Server database, the db_owner role. Right?One of the ones that kind of jumps out to me is creating databases. I think we’ve all been in that environment where we have a third party application that wants to install something, and it needs to create a database.
Robert: Sure. There’s a common misconception that in order to be able to create databases, that you need sysadmin rights. That’s a fallacy.If you give someone the db_creator role at the server level, then they will be able to create databases as they need to.
Carlos: Right. That’s a very good example of saying, “Well, here’s what they need to do. There is a level of functionality a little more granular that will give them that opportunity to do that.
Robert: That’s correct. Yes.
Carlos: I think another one that you point out is create logins.
Robert: Sure. In order to create logins, you don’t have to have the sysadmin rights in order to create a login on a server. If you give someone the security admin server role, then they’ll be able to do that as well.With the security admin role at the server level, there is a little bit of a loophole here and Microsoft admits this as well. It’s that if you have someone who is in the security admin role, they’re able to give someone sysadmin rights. So you could potentially set someone up with security admin and then they could turn around and just give themselves sysadmin rights.[laughter]
Robert: From that perspective, I’m not going to say give that widespread, because you really shouldn’t. But if you do have to give someone that, make sure you audit. There’s something along those lines. Make sure you’re looking over that or have some sort of policy based management or alert set up that makes sure that that’s monitored. To where you can know if someone is making server side changes on roles or database roles.
Carlos: Sure. That kind of goes into then keeping up with your environment, so that you’re aware of what’s happened and changes that are going on in the system.
Carlos: One of your ideas was that we should not be giving anybody db_owner ever.
Robert: Ever, ever. I will take that to the bank. I will take anybody. Any challengers. Any and all on that. [laughs] The reason why is…The biggest thing with db_owner is you have explicit and implicit permissions.When you give someone a database role, such as db_datareader, that implies that they will be able to read any table or view. Basically run a select statement against any object in the database.
With db_owner, the implied permissions are astronomical. They’re beyond anything that anybody really has to have if they’re doing development work. The biggest implicit permission that comes with db_owner is the alter database permission.
This brings in a whole list of things that you can do that from an administrator perspective, you don’t want anybody else working on. It gives someone with the db_owner permission the ability to add data files.
Let’s say you have all of your data files are nice and neat. You have a dedicated drive for them. But the developer comes in and they run out of space for whatever reason while they’re running something at nine o’clock at night. And they decide, “Well, we need more space.”
“I know that there’s space on the C drive, so I’ll just put a new data file out on the C drive and we’ll just let it grow forever. We’ll let it grow as long as it will go.” Eventually, that will of course fill up and then you’ll have a C drive that’s full. You may have a server that goes down. [laughs]From production to dev, that’s never a good thing, because that still halts development work even if it is a development server. Another thing that the alter database permission gives is the ability to change things like the recovery model.
Let’s say in a non-prod, but higher environment, such as a UAT. Or maybe you have an integrated testing environment where you’re wanting to test things very much like production. You have transaction log backups. You want that kind of similarity to production.
If they’re able to change that recovery model or maybe you have it in simple. OK?
Robert: And you’re not taking transaction log backups. They could to in and change that recovery model from simple to full. Now all of a sudden, two hours later, now you’ve got a full transaction log on one of your testing databases because you’re not taking transaction log backups. [laughs]
Carlos: That’s right, and the application grinds to a halt.
Robert: Sure. On the flip side of that, if they switch from full to simple, now you’re trying to take transaction log backups. Those jobs are going to fail. You’re going to lose any kind of restore chain that you potentially have, because you got to change from simple back into full. And then take a full backup before you can start taking transaction log backups.
Carlos: Yeah. This was the big one for me. I thought that idea of breaking the backup chain…That’s that example of, so it’s database administrators, right? We always think of job number one as being able to make the data available. The source of that or the core is in those backups.If a change is made there or even if a full backup were to be taken and it breaks up our backup chain. We then put ourselves in jeopardy of being unable to provide that service to our users that they’re expecting. Things can get complicated very, fairly quickly.
Robert: Absolutely. The last one, and this was always the biggest kicker for me. I know you said “the restore chain” is big for you, but the alter database permission allows a user to take a database and take it offline.The biggest thing isn’t just that. It’s that you can take it offline and then you’re not able to bring it back online, because you have to be in the sysadmin role to be able to do that.
Carlos: There you go.[laughter]
Carlos: So they’re clicking around in the GUI and oops!
Carlos: And there you go. Just between you and me, Robert, have you ever had anyone do that?
Robert: Yes, I have.[laughter]
Carlos: Ooh! Well, there you go. I guess I consider myself fortunate now that that has not happened to me.We’ve just discussed some of the reasons why we don’t want to be giving the db_owner role to our users. We have this concept of role or kind of dividing and conquering some of the permissions that are available. We’ve already talked about the db_creator role and what that means.
We’ve also discussed the security admin as well.
Those are probably the two big ones or at least that I’ve more used. Any of the other roles that you want to talk about? The default SQL Server roles that you use on a regular basis?
Robert: At the server level, there’s really only one. I’m of the opinion that a lot of the server roles are…I don’t want to call them “useless.” I can see where there would be certain times when you’d want to give that role versus giving someone sysadmin rights.But I believe they’re really specific to maybe some special environment of some sort. Like disk_admin, which allows you to manage disk space and manage data files and log files. That’s something that you would assume a DBA would do, not someone who’s going to…You would assume that your DBAs are going to have sysadmin rights.
Carlos: Sure. Yeah, potentially if maybe your [inaudible 15:31] person, maybe they didn’t have a DBA and I don’t know. The disk people wanted to get in there.
Robert: Sure. And they wanted to manage their own disk space for whatever purposes. That would be a good example, sure.
Carlos: OK. Ultimately, we talk a little bit about rolling our own and other applications that use these. Even in some of the more familiar ones, at least in my mind, within SQL Server include the role in MSDB. Right? Like database mail user role? If you create a mail profile and you want that user to be able to send mail, you grant them rights to the database mail user role. Then they can send email all day long.
Robert: Sure. Absolutely. There’s a lot of roles in MSDB. I’ll be perfectly honest. I don’t know what all of them do! [laughs]From an implicit standpoint…I don’t know what every single one of them does, but I can say that I know that one that I use pretty regularly is the operator role, the SQL agent operator role as well as the SQL agent reader role.
The reader role is a real big one, because it allows users to be able to view job history. A lot of times, developers will have automated processes. They want to see run times and things like that. And compare. That’s good. We want our developers doing that. As an administrator, you want them looking at those kind of numbers.
If you give them “view job history,” they’ll be able to view all that stuff.
The operator role is good if you have jobs that you’re wanting people to be able to execute as much as you want them to be able to. [laughs] That was kind of long winded on how I said that. Basically, if you have a team, and all you want them to do is to be able to execute jobs, then you can give them that role. And they’ll be able to execute any job on the entire server.
The reader role will allow you to do that as well, but you have to specifically own the job in order to do that.
The operator role also allows you to be able to create jobs. But if you do not have them set up with any other kind of permissions in any databases or anything like that, then they won’t be able to run any kind of TSQL. If you don’t have any proxies set up for them, then they won’t be able to run SSIS packages or anything like that.
Carlos: Compañeros, one of the things we’re trying to do is to get some information out there. I think what Robert just said was huge. Giving users the reader role to your jobs is one that I see in a lot of different places, particularly your SSIS packages and other things that might be there. There is a way to give them that privilege without giving them the keys to the kingdom.I think that that’s something will be of value to a lot of folks.
Robert: Sure. That’s good.
Carlos: It’s a right there kind of within our grasp. We just need to execute and grant that role. Another one I was going to talk about was the RS.exe role, which is both in MSDB and the Master DB. That’s for, obviously, reporting services. So for those who’re going to be creating subscriptions or publishing reports, we make sure that that role is available to them so that they can have that privilege.Most of the time I think that comes through the application when you’re adding them into recording services. But I have seen from time to time, mostly with my migrations, it doesn’t quite get set. It’s just another example of specifying roles that a user would get.
Robert: Yes, I agree, that’s actually a great example, yes.
Carlos: We’ve painted the picture, we shouldn’t be giving folks DB owner role. We’ve talked about why. Some of the other things, but now they’re saying, “Wait a second, I don’t want to be bothered every time a user needs to create a database, but maybe I don’t want to give them that DB creator role or there’s other things like they want to do traces.”Another one that haven’t talked about is viewing dynamic management views. So that if they want to be doing a little tuning or performance and you don’t want that person to be coming back to you all the time requesting these little one-off permissions.
Robert: Sure, understandable. I can certainly respect the need to want to see that kind of data. Because, as stated earlier, we want our developers to be able to go in and view those kinds of things. So that they can tune their queries and we have a good, stable, fast running, environment.When developers and DBAs get along then your end users are very happy as well and your organization is happy as a whole. As far as the DMBs…
Carlos: I think we can also do the view database state or view server state, if they wanted to give them the whole server levels.
Robert: That’s right, yes.
Carlos: Would be another way to do that.
Robert: Yes, there’s many ways to do that, that’s correct. [laughs]
Carlos: Another part that I wanted to get to was your development and putting together of these roles or some of these permissions into a script or into a role that you’ve called DB developer.
Robert: That’s correct. I’m really big on this role, it’s something that I use in my organization and our shared development environments that we have. It gives developers the ability to develop in a shared environment without having the DB owner role.There’s no risk from my perspective about having any of the things that we talked about earlier. We’re saying, “Well we don’t want to mess with any kind of restore chains” or, “We don’t want someone accidentally dropping a database” or, “We don’t want someone changing a symmetric key that we don’t want changed.” [laughs] Just as some examples.
It gives you the ability to do all those things that you want your developers to work on, which includes stuff like being able to script out objects so you can see the code behind things. There’s not a role that gives you that except for the DB owner role.
My DB developer has that permission in it, where you can still do those things.
Carlos: Robert’s been very generous with us compañeros, and he’s going to make that script available for us on his site. We’ll have the link in the podcast notes, we hope that you’ll check that out and start playing with that.Another way that we can help manage some of these permissions, and this is more from an administration perspective and I think we’ve been headed in this direction for a long, long time. That is in the using groups for permissions.
Robert: Yes. I strongly recommend using groups. It’s recommended by Microsoft to do so. Basically the way that my environment is set up is, we have groups set up for anything that we think needs to have read only access. If someone needs that access we simply take their user and we put them into that directory group.Instead of going into the server and creating a login and creating a database user and then putting them into a database role. The reason why is whenever they change roles or if they leave the organization, it’s very easy to remove them out of those permissions.
If we’re wanting to remove them by using AD groups, all we do is take them out of the active directory group, that’s very easy to do. Whereas if we create a SQL login for them, then we have to login into the server, we have to delete the user and hope they don’t have any objects in the schema.
Then we have to delete the login. [laughs] There’s a decent amount more work that goes behind that.
Carlos: That’s a great point there. We can be assured that they’re not going to own any of those objects and it’s much easier to put them in or take them out. We even use that for our DBAs, creating that group and then adding them.That way, we know who has elevated privileges on a server, based on those groups.
Robert: That’s correct. That’s also another place where principle of least privilege can really come in. Let’s just take a really easy example. Let’s say that you have a server that was manually restarted. You know for a fact that on the Windows side that in order to be able to restart a server, you have to have administrator rights.You go into the server and you say, “OK, who has admin rights?” That basically narrows your list down of, “OK, well one of these people had to have been the person who did it.”
You can apply the same thing at the SQL server level. If a table is dropped, you can say, “Well I know that these are the people in this database that have the permission to drop these tables. It has to be somebody in this list.”
Carlos: Right. Making auditing a little bit easier there.
Carlos: I think that is going to wrap up our discussion on privilege of least permission. Is there anything else that you think we should hit on before we change gears?
Robert: No I think that’s good.
Carlos: OK. Robert thanks for this discussion about roles and permissions, I think it’s been valuable. I definitely think everyone should be checking out your DB developer role. I think it will be very, very valuable. As I’m always trying to create value for folks listening, I’d like to share another way that they can learn about SQL server.[music]
Carlos: Hello there compañeros, I want to tell you about a unique training opportunity that is unlike anything you’ve encountered. As a listener of this podcast you’re entitled to a special offer. SQL Cruise is a premier training experience set in the most unlikely of places, a cruise ship.Tim and Amy Ford have created a wonderful culture on SQL Cruise. With five or six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea. While you’re in port, you can enjoy the wonderful world we live in either by yourself or with the group.
The beauty of SQL Cruise is the unique relationships you will form with other SQL cruisers. Worried about leaving your spouse? Bring them along. I did and she had a great time with the group. In fact, I have been on SQL Cruise twice and I’m looking to go a third time.
You may be wondering if this is serious training and I can assure you it is as good as any conference you will attend on land. It never crossed my mind that I could be talking about SQL server with several Microsoft MVPs on the beaches of St Johns.
I know you must have other questions so I suggest you head over to SQLCruise.com and check out the site. Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material led you to us?
You enter SQL Data Partners and Tim will ensure you get $100 off the training registration. This is a great offer and I hope this $100 savings will entice you to check out the site. I did and went on the Caribbean cruise and had a great experience and I know you will too.
Maybe I’ll see you on board. So head on over to SQLCruise.com and use the code SQL Data Partners to save $100.
As we’re wrapping up here Robert, we have a couple last questions we’d like to have you share a little bit more about yourself. What’s your favorite SQL tool? It can be a paid tool, free tool, but what tool do you like and why do you use it?
Robert: That’s a really tough question. There’s a lot of tools that I use that I like. SSMS is my favorite tool.[laughter]
Robert: I’m kidding, I’ve got a better tool than that. If I had to give a really good tool to use, I would say probably Spotlight by Dell. That is probably the one that I really enjoy. It give a lot of really good data and the visualization of it, to me, is what really, really does it for me.I love other products by other organizations out there and I actually use them. I’m fortunate in my environment to have a lot of toys, as it were. In my opinion, Spotlight is something that I’m into every single day. I’m looking at stuff every day in there and evaluating things in there.
Carlos: OK. The Dell tool, that’s paid. It is interesting actually, I went to a session by Adam Machanic once and he talked about monitoring. To me, Adam Machanic is one of the people writing that knows so much about SQL server.
Robert: He’s forgotten more in the last day then I’ve learned in the last year.[laughter]
Carlos: That’s right. One of those people. He made the comment, he’s like, “Oh well, I would put a monitoring solution in all of my environments.” Interesting that, while yes, you could homegrown some of your own, that ability to have a product that is stable, is consistent along all of your environments and then be able to give you some of that history. I thought that was an interesting comment.We’d like to hear about an instance or experience that you’ve had that helps you remember why it is that you enjoy being a database administrator.
Robert: That’s really tough, do I have to limit it to one?[laughter]
Carlos: We are looking for just that one.
Robert: OK. If I had to give stories, I could mention SQL Saturdays, those are always great. Another one that I’ve done is, I’ve done a couple data center migrations and those are fantastic. You learn a lot during those, but I would say, probably one of the best DBA stories I had is something that I actually made a blogpost about.It was where I basically implemented compression earlier this year. Simply by compressing a partition in a single table, a nightly load increased by probably 20%. I was simply amazed by that. That was incredible for me, that’s the one thing that sticks out to me.
The reason why it was so big is because it was something that I had wanted to do for a long time. I had partially implemented it back six months ago, or something like that. Then two or three months ago when I got around to finishing it up and getting it completely done, the day that I got it completed, they came through. It ended up being one partition in one table that I compressed that you really did a lot of good work from it.
It was just a really good feeling to be able to implement something from an administrative side. Developers all the way down to end users saw a great benefit from it. They felt that it was a really good thing and they wanted to know where my magic wand was.[laughter]
Robert: When you do things like that, it really does make you feel good and it makes you feel very proud. That’s the best I got.
Carlos: Sure, very good, very good. Any time we can add value there to the business, polish our credentials a little bit if you will. It makes us feel good. Very good. Before we get to the last question, want to remind folks if they want to connect with you via Twitter, they can do so at SQLCowbell. You have your new blog at sqlcowbell.com.For our last question Robert, if you could have one superhero power, what would it be, and why would you want it?
Robert: I’m going to go with…That’s a really tough question.[laughter]
Robert: I’m going to go with the ability to see the future.
Carlos: OK. There you go, divination, whatever that…
Robert: Yes, that’s a great word for it, yes.
Carlos: Very good, very good. Well Robert, thanks again for being on the show. I had a great time. I think the folks who are listening will be able to learn a lot.
Robert: That’s great, and Carlos, thank you for having me. It’s been a pleasure. As always, I enjoy talking with you. I’d love to be back on if you ever wanted to have me. I’m an open book. If you reach out to me on Twitter, I am more than happy to answer any questions. I’ve even answered questions to people who have written in Japanese kanji before.[laughter]
Carlos: Nice, man of many talents.
Carlos: Thanks again Robert. We’ll see if we can have you on again. Compañeros, thanks for tuning in. It’s been another great show. We’ll see you on the SQL trip.[background music]SQL Data Partners.