February 6, 2011 at 9:20 am
At the moment I'm working on a big project to reduce excessive database rights and to minimize privileges. There are various types of applications involved.
- Console applications (mainly data import/export processes)
- Websites and webservices
- Windows Service applications
We already made a big step forward replacing SQL Logins by Active Directory logins using strong passwords. Every process / website now also uses a seperate AD account to access SQL Server.
Next step is to reduce access rights so the application can only read and write the data in databases it's supposed to.
I could do the following:
- Investigate for each application which database it needs to access
- Determine in which database the user is supposed to read and which database to write.
- Add the user to the specific fixed database roles (db_datareader and db_datareader)
- Determine if any stored procedures / functions etc. needs to be granted access to.
I could also do the following.
- Check which tables a process needs to read and write in
- Add custom database roles for each process
- Grant select/insert/update/delete permissions for these tables to the role
- Add the role to the database user
The last solution is quite complex and difficult to maintain, but I know a lot people would recommend this method.
I know it's possible to grant permissions to specific schemas. However at this moment there is only 1 schema dbo defined.
Of course this could be a reason to alter the data model and start using schemas.
I'm sure a lot of people run into these kind of situations. I wonder what your recommendations are. Thanks.
Best regards, Peter
__________________
MS-SQL / SSIS / SSRS junkie
Visit my blog at dba60k.net
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/[/url]. 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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply