April 26, 2012 at 3:21 pm
Hi - I have seen many articles on here about permissions and what to grant/deny - I'm a sole programmer (not a DBA) - is there a post here, that is favoured, that simply states for a normal web application (I still have the SA login, so can logon and do whatever I need to the tables/structure etc), where the web app has to read/write/delete/update records across the database, but restrict them from deleting/creating/modifying any tables/settings etc what to set in Sql Server, to at least give a good level of protection?
Sorry for such a basic question, but I don't want to give my web app the login for SA, in case someone does something stupid with it.
Thanks for any newbie guidance,
Mark
April 26, 2012 at 4:33 pm
Security is a big topic in SQL Server...but for a simple approach abide by the notion that "a database user should only be able to do what is needed to service the application's data needs, nothing more and nothing less". One blanket statemet: do not go for db_datareader or db_datawriter.
Will your application be using stored procedures, or does it interact with tables directly? Stored procedures are more secure than direct table-level access and generally much better for performance. If you are just starting out, try to use them before going directly to the tables.
Ideally, you will not only create your Server Logins and Database Users, but will also create Database Roles to add your Database Users into. Roles are just containers for User, but you can grant Roles permissions just like a User...you can think of them kind of like Windows Groups if you;re familiar with those. It is a Best Practice to only Grant permissions to Database Roles...and never to a Database User directly...
As I said, it's a big topic...those are some random thoughts for someone just starting out. Here is a great view of the whole picture, and it might be overwhelming. If you follow this document though you'll be way ahead of the game:
SQL Server 2008 R2 Security Best Practices - Operational and Administrative Tasks
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 2:07 am
Thank you - I really appreciate it.
I will try to use SPs as much as possible, and I'll also look at setting up a role, to just be able to execute (certain?) SPs, and to read/write/update/delete records from various tables.
I'll also have a thorough look through the link you provided.
Thank you once again,
Mark
April 27, 2012 at 7:35 am
mark.tait (4/27/2012)
Thank you - I really appreciate it.I will try to use SPs as much as possible, and I'll also look at setting up a role, to just be able to execute (certain?) SPs, and to read/write/update/delete records from various tables.
I'll also have a thorough look through the link you provided.
Thank you once again,
Mark
Here is the neat thing about prods, and one of their properties that makes them more secure....you only have to grant exec on them and do not always have to grant any access to the tables they interact with provided the proc and the table are owned by the same entity. That may sound like gibberish, but sticking to basics and using a real world, and very common example:
-Say we have a table named People that resides in the dbo schema, I.e. dbo.People
-Now say we make a proc named CreatePeople to create rows in that table also in the dbo schema, I.e. dbo.CreatePeople
-A schema has an owner, so, since the table and the proc both reside in dbo, and we did not explicitly change the owner of the table or proc (objects can be explicitly owned too) the de facto owner of the proc and table are the same.
-The good part: This means that if I grant exec to the proc to someone they can execute the proc, the proc can insert a row into the table on their behalf, but the person calling the proc cannot use an INSERT statement of their own to add rows to dbo.People directly. This is a feature known as Ownership Chaining, and is how we can present an interface to users via procs without ever granting direct access to the tables.
If you have any questions about that process just post back. I could not recommend anything more to someone just starting on a new system than to use stored procedures for all data access. The benefits beyond security, in the areas of performance as well as maintainability among others, are also major factors on why to use procs over direct table access.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 7:47 am
Once again, thank you - I can understand the reasoning - and I suppose granting rights to insert/delete table data, opens you up to sql injection (something else I've been looking at before beginning this project) - which if you were running a SPROC, would mean any injection attack, just wouldn't be run against the table/database.
Cheers, Mark
April 27, 2012 at 8:18 am
mark.tait (4/27/2012)
Once again, thank you - I can understand the reasoning - and I suppose granting rights to insert/delete table data, opens you up to sql injection (something else I've been looking at before beginning this project) - which if you were running a SPROC, would mean any injection attack, just wouldn't be run against the table/database.Cheers, Mark
You're very welcome!
SPROCS do help avoid injection attacks, but it depends on how you call them. What is your app language? Using procs do not solve all injection problems. If there is a formal construct to call SPROCS (e.g. ADO.NET SqlCommand) I recommend using it, as that will protect you from injection. Creating a string like this and pushing that to the database, while it uses a proc, is prone to injection:
variable = "exec dbo.procname @parm1= '" + parmVar + "'"
My second choice, if your language does not have a formal proc-calling construct, is to use Prepared SQL to call procs. Try to avoid issuing ad hoc SQL (I.e. just executing strings created as above) against your SQL server at all costs.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2012 at 8:57 am
Hi - it's ASP.Net - where you add paramters to the SPROC using Parameters.Add...... - not by "select * from my table where " & somenastycode
So think that's all good there...
cheers, Mark
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply