Well here is a brief and hopefully newbie-friendly guide to SQL Server security.
Firstly the 'why'. Many programmers appear to take the lazy route when setting up security, you take the path of least resistance and highest privilege so that your application simply works and doesn't hassle you with security errors. What is wrong with that approach? Well if you are the only person who works on the system and it is on a private network, that might be the most practical solution causing the least work but what if your system starts being used by more people? What if you want to expose it to the www? You can retro-fit security, but it is much more time consuming than thinking about it at the start. The 'why' in the bigger sense comes down to the following:
- You do not want to accidentally change data in the DB
- You do not want somebody to do more (i.e. higher privilege) than they are supposed to do either accidentally or deliberately
- You do not want anauthorised people to do anything in your system
For 1. you might have some configuration data for your system stored in a table. Although you are strictly allowed to change it, you generally do not want to and do not want to accidentally change it. You could set the security to be 'select' only. For 2. you might have a table of 'items' which must only be updated by an elevated user and selected by normal users, you would need to set 'select' for a normal user and perhaps 'select, update, delete, insert' for an elevated user. For 3. you need to ensure that everyone who accesses the database is authenticated and no-one who is not can therefore do anything. This might include people who work for your company but are not allowed into this specific database.
The first design you need to undertake is to decide what schemas you want to create. A schema is a logical way to group objects (tables, views, procs) so that you can set permissions on the whole group rather than on individual items. The definition of a schema is a group that does or might need different access permissions depending on the user/user role of the currently logged in user. Do not create too many since this can make maintenance messy. I have used schemas like admin (select for everyone, CRUD for admin users), writeonly (for error logs and the like, only allow select and insert), general (anyone can have CRUD access), readonly (items pulled in from elsewhere and which are not updated in this DB also for views) locked (admin tables that are important and unlikely to have rows deleted so have no delete permission) and cache (tables that are dropped and recreated for caching things). If you notice, each of this has a unique set of permissions applied both for system resilience and for security.
Once I have designed my set of schemas, I need to create them for the database under
I now have to consider the sort of database roles for my users. This is NOT an authorisation system for a web application so I don't need to distinguish at too low a level (e.g. user A can access table A and B but user B can only access table B) otherwise I end up with a lot of maintenance for no benefit, the authorisation at page level should be done in the web app. What I might have however are roles for admin, general, readonly and perhaps a couple of specials like finance who might be able to read stuff that most people can't. These are created under
Everybody who logs into the database needs a login. You can have a single login and get your application to login as this single user. The problem is then you lose the ability to make the most of roles and permissions because everybody appears as one 'person' to the DB which will then need access to everything for all users and which defeats the purpose. Ideally you should use windows integrated security if you can and remove the anonymous login ability on the web server. You will then need to create a login for each user on the database server under
In order to use these logins in your roles, you need to create a user IN EACH DATABASE to link to the login. The database user can then be added to a role when it is created. Under
So for user BrinerL you might have:
server login: MYDOMAIN\brinerl
database user: brinerl -> MYDOMAIN\brinerl
belongs to role: admin
admin has permissions: Insert/Update/Select/Delete/Execute on all objects.
It's quite straight-forward really. You can automate the adding of the logins with a stored proc if required so that perhaps the first time somebody logs into the web site, it creates them a login and user and gives them the basic readonly role. If they want more then a DB admin would need to either move their roles in Management Studio or log in to a page that can carry out the necessary work from some sort of front end.