February 3, 2014 at 10:29 pm
Comments posted to this topic are about the item Database User Superfluousness In SQL Server
February 4, 2014 at 3:04 am
Microsoft do not seem able to get the user authentication/authorisation security model right. It is fragmented, complex, duplicated in parts and still incomplete, which means mistakes and loopholes highly likely to be left lying around due to human error. The etiology of the problem is architectural in that multiple SQLServer databases attach to a single instance, so we need instance as well as db authentication. Compare this to ORACLE's model of one instance...one db - much easier to manage. I never thought the Microsoft model should be flattened. There is instance authentication (logins) with server level permissions and then DB User access and DB user authentications. Tidy. As long as I can and where it is appropriate to do so, I shall try to manage 2012/2014 server and db object access on this basis.
February 4, 2014 at 5:10 am
Comparing to Oracle is a bit overkill. I work with both and the "one instance = one db" which exists until 11g does more harm than good: if you want to run more than one application on a single server, you may run into troubles keeping the data isolated. this also applies to the user/login management, where "one user = one schema" drives me to madness whenever I need to find one specific schema in the middle of hundreds... just because we have hundreds of logins (they are all empty, but needed because of the app design).
I believe a more balanced approach would be better for MS-SQL, but we could run into some risks: if we restore a DB to a distinct server, how can we handle the permissions if we get rid of "users"? I mean, if all we have is a server-wide principal the permissions won't come over on the restore - unless we script it all out, more or less like scripting the SIDs.
also, if we enable encryption on something inside the DB, we need to be sure that a 3rd party can not gain access to this information by restoring or simply copying the DB somehow. Those logins/certificates/confusion are needed in this scenario.
don't get me wrong: I don't think this is the BEST way. not at all. I hate messing around with SIDs and the ancient Linked-Server authentication process is pretty much ridiculous.
February 4, 2014 at 5:37 am
mauriciorpp (2/4/2014)
..if we restore a DB to a distinct server, how can we handle the permissions if we get rid of "users"? I mean, if all we have is a server-wide principal the permissions won't come over on the restore - unless we script it all out, more or less like scripting the SIDs...
That was my immediate thought as well. I don't know how many times I have made use of the fact that a database restore restores user permissions. It does that even if the server login doesn't exist at the time of the restore. I suppose that might be a security issue as well, but in terms of getting things back up and running quickly (or, more often, cloning production to development) it is at least a useful one.
February 4, 2014 at 5:47 am
@megan, in that situation I usually take advantage of the SID property: I copy the login from Prod to Dev servers, changing the password on Dev. Whenever we need to refresh the environment a simple restore is done, and all login/users are working. But if you don't keep the same SID, you have to map the login to the user everytime a restore is done.
February 4, 2014 at 11:36 am
mauriciorpp (2/4/2014)
@Megan, in that situation I usually take advantage of the SID property: I copy the login from Prod to Dev servers, changing the password on Dev. Whenever we need to refresh the environment a simple restore is done, and all login/users are working. But if you don't keep the same SID, you have to map the login to the user everytime a restore is done.
I have usually been able to dispense with SQL Server authentication and use only Active Directory logins with their global SIDs. I guess it depends on the kind and scale of environment that you are working in. I generally have worked with smaller organizations (or departments of a large corporation) running little or no enterprise-class software.
It seems to me as though support for legacy software may be what drives Microsoft's decisions around cross-database security. When I have developed my own multi-database applications, I have used Windows authentication throughout, with access to the databases being granted, as a rule, only to the applications, as needed. Each database has its own set of roles, and the appropriate role in each DB is assigned to each app identity. Cross-database ownership chaining is disabled. That works well.
The trouble starts when I have a "legacy" application to deal with, one that, typically, uses SQL Server authentication and, like as not, doesn't provide useful roles. If I need to tap in to that data then I may resort to using cross-database chaining to avoid having to make any significant changes to the vendor's database, such as creating and assigning roles to schema or table access. The last time I did this it was easy because the vendor made everything readable by Public! (It turned out that it was publicly readable over the Web, too! -- by default.)
I have sympathy for Microsoft here because they have to figure out how to maintain backward compatibility with applications that are written this way, and my sense is that there are a lot of applications, originally developed in an era when security was not much of a consideration, that do bad things and that very possibly are not going to be cleaned up or obsoleted in my lifetime.
Still, it would be nice to have something better to move forward with in new designs, and contained databases don't seem to be it, at least not for some classes of applications. Maybe "flat" would be a way to go.
February 7, 2014 at 6:06 pm
Thank you all for reading, and for your comments.
For sismb8 & others:
Perhaps I should have emphasised this more: the existing security architecture in SQL Server is "dual-level" in name & feel only. Remember from the first section of my article that there is a 1-to-1 mapping between logins and users. So they are effectively (or logically) the same entities (except for the new "contained" DB users). In fact, a DB user's SID in the DB is the same as the SID of the login to which it's mapped.
And here's something I sort-of edited out of the DB-User History "note" for brevity ...
In SQL Server 2000, there was the option to include an alias to be used in the database for a login when issuing the sp_grantdbaccess command. Without it, the "user" name was the same as the login name. But either way, the username was just a label in the DB for the login. So it was the same as in 2005+, under the hood. The only difference between then and now is that the command set for managing this has changed. The alias, e.g., is now the user_name parameter of the CREATE USER command.
For mauriciorpp, Megan, others:
I think all would agree that the one-instance-one-database concept is silly.
But I think it would be helpful to remember that data and principals are separate... Do what's best to contain data, and do what's best to secure them. But do (and think of) them independently, because they're so different.
(... Sismb8, having multiple DBs in an instance doesn't require dual-level authentication, neither in theory nor practice. See MySQL for a working example. Also re-check my article sections on Security Methods & Security Summary for theoretical detail...)
And I think this difference is where you lost track with my article. I don't propose that permissions disappear from a database just because users do. Under the hood, there would still be the same/similar permissions tables in user (and system) databases. It's just that the SIDs recorded in them wouldn't reference DB users.
As for restores & attaches, that's where a robust security tool, which I mentioned in the article, comes into play. The GUI should have a tool that apportions most of the workload where it belongs--in the software:
You: Restore a DB.
Tool: "Ok. I see you have a bunch of SIDs (for server logins) in that DB that aren't on this server. Would you like me to create them for you (I'll need your help a little)?"
You: "Why yes, thank you. But some are for Windows Authentication logins from a different domain. Here are credentials for fetching those. I'll have the sysadmin create them in this domain in a minute."
Tool: "Ok. I'll see what I can find. One moment..."
. . . etc.
The lack of such a tool is a huge complaint of mine. Look at how sophisticated the tools are for clustering or replication, or file synchronization on any platform. So why are security tools treated like such an afterthought, or the runt of the litter? It just puts the load on 100,000 DBAs, year after year, instead of on a couple of programmers at Microsoft for a few months.
A flat userspace wouldn't alter the handling of encrypted data. You would still need the password/key/certificate to decrypt it, regardless of how it's transported.
Backward compatibility is a good point, and one I didn't address. But I think this would be a small factor in the grand scheme of things. A flat userspace wouldn't change an application's use of (SQL Authentication) application roles. And it wouldn't affect Windows Authentication, by definition. The work would come in refactoring apps that get in and muck about with database users and rely on ownership chaining, the TRUSTWORTHY property, etc. They'd have to point to different principals sometimes. But it's never wise to hang on to bad design for the sake of intransigence. And the corollary--hanging on to good (enough) design--hasn't stopped Microsoft from axing things before ;-).
- Thomas
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply