2017-03-21

The Mount Rushmore of Lazy SQL Security

Dave Mason - Mount Rushmore of Lazy SQL Server Security

SQL Server security can mean different things to different people. And rightly so. The first thing I always think of is authorization. And that is why I'm stepping onto my soapbox, blogging my frustration into the ether. I see four recurring security blunders that drive me nuts. The blundees (is that a word?) are seemingly oblivious. They react as if everything is fine and anyone that suggests otherwise is unreasonable. Allow me to immortalize their folly, National Memorial-style.

sa

The first face of poor security is the use of the [sa] login. If you are savvy enough to attempt a connection to SQL Server, you probably know (at least on some level) that connecting to any system as an administrator is wrong. It's overkill. Sure, [sa] can be renamed or disabled. And a SQL instance can also be configured for Windows Authentication logins only. But potential attacks are likely to try to exploit the [sa] login because it exists on every SQL Server instance, and most importantly, is all powerful. I don't want outsiders using [sa], and I wouldn't want you using it either.

sysadmin

The second face of poor security is membership in the sysadmin fixed server role. Like [sa], logins in the sysadmin role are all powerful. IMO, it's slightly less atrocious than using [sa]--at least there's the option of removing the login from the role, if not dropping it outright. But it's still overkill. You *really* want a login with sysadmin role membership? Then you have to be an on-call DBA too. And don't contact me in the middle of the night or on the weekend. You're on your own, cowboy!

dbo

The third face of poor security is ownership of a database. If you owned a database, there'd be little I could do to keep you from shooting yourself in the foot. You see, your login would connect as the [dbo] user. I couldn't DENY you any privilege within your database. You'd have free reign, when all you probably needed was read/write permissions. If you ask for ownership of a database, expect to get a stone cold "No!"

db_owner

The fourth face of poor security is membership in the db_owner fixed database role. This is the least egregious of the four. A DBA could DENY permissions to a database user that's a member of db_owner. But beware, DBCC commands can't be denied. In the end, db_owner membership is far more than what's required. You don't need it. Don't ask for it.


Closing Thoughts

You may be thinking us DBAs are as unyielding and rigid as a statue. But don't take us for granite. Understand that for the sake of your data, we must chisel away that which isn't necessary. Someone somewhere will be giving their best effort to make your data highly available and recoverable. They will attempt to protect it from corruption, loss, theft, hardware failure, software failure, human error, and acts of God. They may additionally have to meet compliance standards. When there are non-administrative entities with administrative privileges to SQL Server, DBAs can't meet these responsibilities. Don't put the DBA between a rock and a hard place. I beg of you. No more stonewalling! Figure out what permissions are needed. And ask for nothing more.


SHARE