Friday, March 23, 2012

Help with Security Model

Hi all,

I need some sugestions from all of you about setting up security model in
our SQL2000 box.

The server was setup using Mixed mode. However, all the applications
(web and MS access) access the server using "sa" userid.

There are several databases in our server. Ex: (DB1,DB2,DB3,DB4 and DB5)

Application 1: need read/write access to DB1,DB2 and DB3
Application 2: need read/write access to DB5
Application 3: need read/write access to DB4 and DB3

Should I set up three userids and give them the dbo access to those
database that they need to use?

Does that make any sense to you?

Thank you for all your suggestionThe 'sa' account should NEVER be used by ANY application or user (other than the DBA, and then carefully.)

Use three different UID/Pwd for the applications and GRANT permissions that way.|||In addition, unless they really NEED dbo, just GRANT the minimum they need.|||I'm a firm believer in the "principle of least privlege", meaning you create as many accounts as you need and only give each one the privleges that it needs to do its job. Check out the predefined database roles (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_help_5omd.asp) to see if your accounts even need dbo, or if a combination of db_datareader and db_datawriter would do.

-PatP|||I take it a bit further than data_reader/writer and grant Select/Insert/Update/Delete permissions as required. For instance, a financial application allows Inserts but NO updates. Once a record has been inserted, that's the way it stays. Corrections are made by inserting another record to adjust the transaction (along with an explanation.) Auditors seem to prefer this for following the money trail. Good thing I'm sa... :)|||Thank you for all your suggestions.

No comments:

Post a Comment