SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Securing Databases


Securing Databases

Author
Message
Matt Minnis
Matt Minnis
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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
Steve Cullen
Steve Cullen
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1363 Visits: 1226
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.


Matt Minnis
Matt Minnis
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
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.
homebrew01
homebrew01
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12562 Visits: 9222
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.



Matt Minnis
Matt Minnis
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 146
Windows AD Groups
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search