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 Databases Expand / Collapse
Author
Message
Posted Tuesday, July 6, 2010 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 10:37 AM
Points: 45, Visits: 145
We are coming from a single tennant system and merging database servers to a multi tennant system to control costs, however that gives the tennants access to each other's databases.

How do I setup security for groups on my databases so that:
Admin can access all DB's (as it seems to be now, i just don't want to break this trying to secure things).
Users in Group A can only read/write/alter database A and no others.
Users in Group B can only read/write/alter database B and no others.

Also, how do I do this so that future DB's get created with proper permissions?

I think I know how to do this manually with deny and so forth, but as I envision that, it gets ugly as groups/db's get added in future. I presume I am seeing it wrong and making it harder than it needs to be....

Thanks,
Matt
Post #948044
Posted Tuesday, July 6, 2010 11:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
SQL Server uses a role based security model. Your databases get created already without granting access to users on the server. You should set up database roles with whatever permissions you wish, then make your users members of those roles as required.

You should however make sure that users on a server do not have admin priviledges at the OS level, or they will get access to all databases on the server.


Converting oxygen into carbon dioxide, since 1955.

Post #948057
Posted Tuesday, July 6, 2010 11:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 10:37 AM
Points: 45, Visits: 145
Steve Cullen (7/6/2010)
SQL Server uses a role based security model. Your databases get created already without granting access to users on the server. You should set up database roles with whatever permissions you wish, then make your users members of those roles as required.

Ok I think this is where I am fuzzy, on how to set up these roles properly to do this.

So I would create roles at the server level like:
AccessDB-A
AccessDB-B
(I'd use less stupid names on server, just want to keep this simple and clear.)
Then apply the AccessDB-A role on the Group A login group?

You should however make sure that users on a server do not have admin priviledges at the OS level, or they will get access to all databases on the server.

They do not, and any of my users who do give them such should be flogged.
Post #948068
Posted Tuesday, July 6, 2010 12:33 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:19 AM
Points: 2,826, Visits: 8,462
Are you working with SQL Logins or Windows accounts ?

For Windows accounts, I create Active Directory Groups, such as "DatabaseA_ReadWrite", then put Windows accounts such as MyDomain\MikeSmith into that group. Then give the A/D group "DatabaseA_ReadWrite" database permissions to Database A.



Post #948122
Posted Tuesday, July 6, 2010 12:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 24, 2014 10:37 AM
Points: 45, Visits: 145
Windows AD Groups
Post #948124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse