May 12, 2011 at 8:16 am
I'm beginning work on a new project and I am trying to integrate security and flexibility into the design rather than it be an after-thought. Right now, I currently have this design rules:
1) There will be one SQL account that accesses the database or one SQL account per application
2) There will be one schema per application so that security and scope can simply be controlled easily
3) The database will be encrypted via SQL Server 2008's TDE feature
4) The database will be abstracted from the application so that database management/redesign/optimization will not impact application code and can be done at will. This will be done via Stored Procedures and Views.
5) All transactional tables will show who updated the record last and when (the user value will be passed into a sproc when altering the data)
6) All transactional tables will have historical tables populated via trigger.
When implementing new databases, what features in the design are important to you and why?
Is there anything missing from my plans so far?
Is there anything you think I should reconsider and why?
Thanks!
EDIT 1) Clarified 5's method and 1's verbiage
May 12, 2011 at 8:38 am
When following design best practices i usually try a normalise my database solution.
so basically i try and follow:
table should have an identifier
table should store only data for a single type of entity
try and avoid nullable columns
should not have repeating values or columns
May 12, 2011 at 10:30 am
I thing that you should reconsider the following:
How did you determine that there should be only one user? Is this a Windows application or a Web Application?
Whether you have one or more schemas should be based on the Business Requirements not on what makes it easier.
If you are going to have a trigger on every table consider the overhead before you do so.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 11:17 am
How did you determine that there should be only one user? Is this a Windows application or a Web Application?
Whether you have one or more schemas should be based on the Business Requirements not on what makes it easier.
If you are going to have a trigger on every table consider the overhead before you do so.
There would be multiple people that use the system, but the actual connections would be though either one user ID or one per application. By doing this, we do not need every user to be set up on the database as a user. Why does it matter if it is a Windows or web app?
The business people don't know anything about databases, so I'm not sure how they would know what schema should be used. What's wrong with having one schema per application?
I understand the overhead on the triggers, but unless someone turns the triggers off, we would always have an accurate history. It may be possible to write it into the stored procedures, but my concern (and history has proven me correct on this matter) is that people will forget to write histories when making data updates. How big of a hit is it really if I use set based logic on the inserted and deleted virtual tables?
May 12, 2011 at 11:31 am
I should also probably clarify the business a bit. This is not a high transaction environment. There are ~500 users and it's mostly reads (80% probably).
May 12, 2011 at 11:34 am
5) All transactional tables will show who updated the record last and when
if you only have a single userid being used to connect, how will this have any benefit? do you mean logging a username or id that is used by the application, and not what SQL server has available?
Lowell
May 12, 2011 at 11:36 am
Response: There would be multiple people that use the system, but the actual connections would be though either one user ID or one per application. By doing this, we do not need every user to be set up on the database as a user. Why does it matter if it is a Windows or web app?
Reply: Is this an ASP.Net application or a Windows application or something else. It does matter.
Response: The business people don't know anything about databases, so I'm not sure how they would know what schema should be used. What's wrong with having one schema per application?
Reply: The Users are not going to know what a schema is or if they exists but that is up to you to make the determination of how many schemas there should be.
Response: I understand the overhead on the triggers, but unless someone turns the triggers off, we would always have an accurate history. It may be possible to write it into the stored procedures, but my concern (and history has proven me correct on this matter) is that people will forget to write histories when making data updates. How big of a hit is it really if I use set based logic on the inserted and deleted virtual tables?
Reply: If you need to place an audit trail on every table and the triggers don't slow down your application then I would use then when appropriate.
You ask for an opinion and I gave you mine.
Regards,
Welsh
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 11:47 am
This requirement would mean that all insert, update, and delete sprocs would need to get the user id from the application or data layer. I won't be able to get the real user's name from the database as the connection will hide that.
@Welsh Corgi
I appreciate your responses and am not trying to be difficult; I just want to understand why it matters. There is a vast amount of knowledge and more importantly experience on this forum; I want to make sure I make use of that. It will probably be both with regards to web versus Windows. Ideally, a WCF service will act as the data access layer, but I'm not positive that will always be the case.
May 12, 2011 at 11:48 am
SpectralGhost (5/12/2011)
I'm beginning work on a new project and I am trying to integrate security and flexibility into the design rather than it be an after-thought. Right now, I currently have this design rules:1) There will only be one User ID that hits the database or one per application
2) There will be one schema per application so that security and scope can simply be controlled easily
3) The database will be encrypted via SQL Server 2008's TDE feature
4) The database will be abstracted from the application so that database management/redesign/optimization will not impact application code and can be done at will. This will be done via Stored Procedures and Views.
5) All transactional tables will show who updated the record last and when
6) All transactional tables will have historical tables populated via trigger.
When implementing new databases, what features in the design are important to you and why?
Is there anything missing from my plans so far?
Is there anything you think I should reconsider and why?
Thanks!
Your number 1 and your number 5 are incompatible unless each application can be used by only one user. Constraining every application to one user only is rather a heavy constraint (and, unless everyone who can see any data is allowed to update that data as well, it is a constraint on how applications are written as well as on the database - you can't secure the database internally, you have to police access permissions in the apps, and that is probably a wheel you don't want to be reinventing).
Tom
May 12, 2011 at 11:53 am
Clarification on the user id...
Each user has a domain account, but the connection to the database will not use their account. It will use a single SQL account to connect via the application. Maybe I'm not phrasing this correctly.
May 12, 2011 at 12:18 pm
SpectralGhost (5/12/2011)
Clarification on the user id...Each user has a domain account, but the connection to the database will not use their account. It will use a single SQL account to connect via the application. Maybe I'm not phrasing this correctly.
With your revised number 5 it is rather less of a problem - but it still assumes that everyone who uses a particular app has exactly the same database access permissions, unless the app is policing what users do, so either the security requirement is very simple (unusually so), or you have different apps for different groups of users who are allowed to do different things (so you can use platform access permissions to say which user can use which app and that translates into database access permissions because each app runs as a single database user with the appropriate permissions - this may have quite an impact on app design) or the app does the policing and you have exported DB security not into the platform's security but into the app code.
Tom
May 12, 2011 at 12:46 pm
@tom.Thomson
You are correct. The security permissions for users will likely be controlled via table entries that grant access to various features or controls. It seems like the least of all evils to me. We lose a bit of granular security at the database level, but gain a significant amount of security in that user's computers can't be programatically hijacked to make calls to the database. And user management becomes far simpler. I would rather worry about the user id and password becoming comprimised for 1 account than 500 accounts. At least, that is the way I see it right now. If I am missing something, please feel free to let me know.
May 12, 2011 at 12:57 pm
I was trying to find out if your security model was based on a web application or a windows application.
I had a reason for asking this question.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 1:04 pm
What reason? Everything is all in discussion at this point on my end, so I'm not really tied to either solution and neither is the business. External access is not a top priority.
May 12, 2011 at 1:07 pm
If its a Windows app, how about the following which is standard practice:
Set up a windows group and give this permission to your database. In turn you can add users to this group. This can be done by the windows admins thus saving you from having to maintain it.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply