Granular Permissions in SQL Server 2005 and 2008
I love security. Not necessarily being secure in life--life itself is a risk, after all--but computer security is interesting. One bit of proof of that is that I always push for a dedicated security chapter in any courseware I develop for AppDev. (Although I have to admit that I messed up with the SQL Server 2008 Integration Services course I'm updating right now...security will still be part of the Package Management chapter, not a separate chapter. Next time I'll rectify that!)
If you're into SQL Server security, Microsoftie Laurentiu Cristofor's blog is a must for your RSS feed. Laurentiu doesn't write as much about security these days, but the archives are full of great stuff.
Laurentiu recently wrote an interesting post, Basic SQL Server Security concepts: ownership, CONTROL, TAKE OWNERSHIP. In it he talks about permissions related to object ownership. I have to admit that a few things were of interest to me, either because I hadn't encountered them yet in the wild, hadn't really thought about them, or it's always good to be reminded of them:
- The owner of an object ... most importantly, cannot be denied those permissions while he continues to be an owner.
- The CONTROL permission [is] the next best thing after ownership of the entity, but it's not quite as powerful as ownership. The main difference is that a grantee of CONTROL can still be denied some other permissions on the entity. [I love his example: I can have CONTROL of a table, but can be denied SELECT permission on it. When would I ever use that??? :-)]
- TAKE OWNERSHIP [can be] used to selectively grant someone the ability to willingly become the owner of an object. [Laurentiu's emphasis. I had never worded this as willingly, but that's a good way to think of it.]
And best of all:
- Side effect of ownership change: A potentially surprising side effect of changing ownership of an object is that all permissions granted on that object will be lost. [Again, Laurentiu's emphasis. This is something I've never explored nor, to my knowledge, ever had a problem with. I'll have to watch this for side effects though, since I suspect they could be nasty if I weren't aware of this.
Read the whole post; it isn't long.
Practice safe computing!