February 6, 2011 at 6:45 pm
My $.02...That is a lot of work...I have to tell you that majority of that should have been provided by the developer that wrote the application, or the vendor. The length you go to secure your database is only as good as your network perimeter and access to the server itself. If you do all that work and lock the database down to the nearest object, then some hacker gets a hold of an admin account on your network...the time you spent securing your database does not mean squat.
I work as a contractor and secure database installations and databases to DoD standards. You can download those checklist from here: http://iase.disa.mil/stigs/checklist/%5B/url%5D. They include database, operating system, and application.
If you have a large number of accounts required to access the database then you probably should tighten down the access those accounts have. However it should not require that much work on your part. The vendor or application developer should know (if they documented their app) what accounts need what access. Much like the documentation you may find for say SharePoint. The document list each process, what it does, and what access it requires to the database.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 7, 2011 at 6:30 am
Shawn Melton (2/6/2011)
My $.02...That is a lot of work...I have to tell you that majority of that should have been provided by the developer that wrote the application, or the vendor
Thank you, that is good point. Fortunately the database and applications are developed by ourself. We are free to make changes to the applications and database schema. I'm also planning to talk to our developers to see if they can support me for this project. Most process are documented pretty well, but there are a large amount of applications (around 100).
The options I have in mind are the following and I need to make a decision.
1. Stick with the fixed database roles (db_datareader / db_datawriter etc.)
2. Choose to set permissions on the table level and make custom roles.
I'm not yet sure what to do because we have some many applications. The maintainability can be an issue if I choose for option 2.
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
February 8, 2011 at 5:38 am
I would go with your 2nd choice.
Additionally, if possible I would only want to give access to the tables via stored procedures in lieu of ad hoc queries by the application, but that is even more work and testing. It really depends on what you or your manager wants to set the scope of this project at. You probably would only want to have the application testers/developers go through the process of changing code and testing once.
Moving forward, I would also write a standards document outlying how "new development" should be done. For instance, you may state that all access to the database objects should be done via stored procedures. No more "db_owner" privileges for applications, no more "datareader/writer" privileges, use schemas, etc... This way everyone is on the same page and you have a more secure and manageable system moving forward.
Steve
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply