May 26, 2020 at 7:28 am
I am trying to understand how I can use the db server authentication - authorization mechanisms and user related tables, but I cant find any resource simple enough to grasp the whole concept. What I am trying to do is to not have my own tables for users, roles etc. but use the platform's syslogins, server-roles and database users.
My question concerns a tree-tier architecture, where all client requests go through an application server and then to the DB server.
Any related information is appreciated but I will also try to ask some specific questions:
May 26, 2020 at 1:53 pm
To really get a grasp on SQL Server security, I'd suggest Denny Cherry's book on the topic. It's a little bit old at this point, but 90%+ of the material will be completely applicable.
In general, I always try to approach security as follows. I'm assuming Active Directory, but a SQL login would still be the same. First, always give as little access as humanly possible. This is called the least privilege principle. No, your average application doesn't need 'sa' privs. No, your average app doesn't need 'dbo'. Depending on the app and how it's built, it may only need execution permission on stored procedures. Maybe one or two tables it needs read privileges. Etc., etc. First, determine the least amount of access you can possibly maintain for the app. Then, create a role on the database (not the server) and grant that role those permissions. Now, on the server, create an AD group. In that AD group, you'll place logins. Associate the AD group with the database role. You're done.
Now, management of access is only, ever, through the AD group. You shouldn't have to manage this at the server or database level on a regular basis. However, what you'll find is that you have different applications and different purposes for the apps. As that develops, follow the same approach. Create a database role. Create an AD group. Associate the AD group to the role. That will manage permissions.
However, through all this, one issue will come up. Individual logins. Especially when dealing with something like a web app, the likelihood is the app has permissions to your database, not the individual using the app. So, how do you track which person did what? There, you may have to add the people connected and performing actions as a part of the app itself, so that you can store the data in your database. No, it's not a way of managing security. You're still managing that as described above. However, you also need data tracking. That's what I'm talking about here.
There are tons of permutations around all this. However, if you find you're down at some very granular level coding EXECUTE AS to do stuff, step back and reassess. That's usually never required under standard circumstances.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply