Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Securing user account in production db Expand / Collapse
Author
Message
Posted Thursday, March 6, 2014 6:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:10 AM
Points: 100, Visits: 311
We have an application and accompanying database that are about to be moved into production, and I want to ensure that things are locked down as much as possible. What are the best practices etc for securing user accounts in the database? Can they be limited to only run specific stored procedures etc?
Post #1548250
Posted Thursday, March 6, 2014 7:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Yes. It can be locked down.

Make sure whichever account is used by the application has appropriate access, exclude anyone else?

For stored-procedures, you can user
USE MASTER; GRANT EXECUTE ON [schema].[StoredProcedure] TO [<Account>]

If you need more detailed information, please provide a more detailed description


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1548295
Posted Thursday, March 6, 2014 7:58 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 15,558, Visits: 27,932
Just backing up what's been said. You can get extremely granular on the security, and I'd get as granular as possible. If you can, limit all access to the data to stored procedures. You can even go further and make all logins belong to different schema than the other objects in the database to further isolate their access, in case you miss something. Also, make the default database for the login the database you want them to access. By default it'll be master, but people don't need access to that, even if they can't do anything.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1548301
Posted Thursday, March 6, 2014 8:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:10 AM
Points: 100, Visits: 311
All data access is done through stored procedures, so my thought was to limit the account access to the sp's that it needs. Some of those stored procedures make calls to an another db on the same server, though. Will that be a problem?
Post #1548319
Posted Thursday, March 6, 2014 8:46 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, July 14, 2014 2:06 PM
Points: 3,865, Visits: 7,130
Access will need to be granted to the other databases for the account(s) that are accessing the tables via the stored-procedure. Just check to make sure what the procedure is actually doing to the other database tables and grant SELECT, INSERT, UPDATE, or DELETE based upon what the scripts are actually doing. And yes, you would need to add that same user to the other databases it accesses.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1548324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse