April 4, 2009 at 2:41 pm
I have an application I will call App1 which the customer access it's own database in an instance of SQL server which we have over 200 identically customer databases (identical schemas). However, we use a single login via web services that has admin rights over the entire database. It is the same for all customer databases. I want to reduce risk by lowering rights. APP1 only accesses the customer database via stored procedures. We also have several function that we use which are called in the stored procedures. We do not use any views. We also created an ad hoc reporting app that I will call App2. App2 performs SELECT queries directly from the database tables. We do not update tables from App2. What is the simplest way to handle this. I would assume a role (I believe that would to be an application role) at the database level with execute permissions for App1. For App2, it would be the same except it would be SELECT ONLY, I would not allow it to have execute ability on the stored procedures. I think this is the best way but I would like some feedback before I go ahead and propose this change
April 4, 2009 at 2:50 pm
Shall we understand each one of you customers has access to any and all the "databases" of all your customers with dbo rights?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 4, 2009 at 3:23 pm
No. They don't have access to other custemers dbs due to other constraints in the code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply