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


Do Users based on logins in master have access to other databases?


Do Users based on logins in master have access to other databases?

Author
Message
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86879 Visits: 45263
clintonG (3/17/2013)
The SQLExpress database will be intsalled on a share of a Windows7 machine


Errrr....

SQL is not Access. It's not a file-based database that sits on a share. It's a service that other machines connect to.

With the SQL Service running on a Windows 7 machine, anyone who has administrative access to that Windows 7 machine can do what they like to your database (and that includes dropping it, uninstalling the service, etc).

You need to lock that machine down and treat it like a server if it's serving data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37
GilaMonster (3/17/2013)
clintonG (3/17/2013)
The SQLExpress database will be intsalled on a share of a Windows7 machine


Errrr....

SQL is not Access. It's not a file-based database that sits on a share. It's a service that other machines connect to.

With the SQL Service running on a Windows 7 machine, anyone who has administrative access to that Windows 7 machine can do what they like to your database (and that includes dropping it, uninstalling the service, etc).

You need to lock that machine down and treat it like a server if it's serving data.


I understand thank you. However doesn't Group Policy Editor remain accessible to any Windows 7 Administrator anyway?

So with your insights and those of others I've worked through creating a login, user, flexible role with DENY DELETE and the app does not allow deleting data when it is being used which is all I wanted to learn get done at the moment.



kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
When I look at the SSMS console for a database, I can see under the Permissions section that for a given user/group, DENY DELETE can be granted. Is that not what you are looking for?
kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
I have just created a role called "DENY DELETE", and assigned a user called "TESTUSER" to it after adding that user to new role. Is that sufficient?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86879 Visits: 45263
clintonG (3/18/2013)
However doesn't Group Policy Editor remain accessible to any Windows 7 Administrator anyway?


Err, huh?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
lol!
clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37
kevaburg (3/18/2013)
lol!


I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?

Furthermore, if you ever watch Law and Order its always the guy with the Windows Administrator account that runs the Group Polcy Editor that does all the whack job anyway and as far as I've been able to determine the only thing that is really laughable is a secured implementation of a Windows client OS.

My point is I know there are lots of ways to lock sh!t down but its not worth my time or trouble to try to learn or even spend the time to do so if I am not going to get paid for it and what I do can be subverted anyway which is why I always write a "good faith" clause in all work agreements.

Furthermore, as I said, the only way I know of doing so on a client machine is by using Group Policy Editor and as I've indicated there is no way to lock down Group Policy Editor that cannot be hacked by the Administrator account anyway.



GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86879 Visits: 45263
clintonG (3/19/2013)
kevaburg (3/18/2013)
lol!


I don't know what's supposed to be so funny. How else could somebody lock down a Windows 7 client OS functioning as a "server" than to use Group Policy Editor?


Start by making sure that 3/4 of the staff don't have the administrator password. Strong administrator password, limited user access with minimal permissions. Not talking about group policy, talking about restricting logins in the first place. Unless someone has a need to administer the particular machine, they should have no rights whatsoever to the machine.

Once you've limited the access to just the few people who need to administer the machine, then you can do a proper lock down and harden if necessary.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


clintonG
clintonG
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 37
kevaburg (3/18/2013)
When I look at the SSMS console for a database, I can see under the Permissions section that for a given user/group, DENY DELETE can be granted. Is that not what you are looking for?


I've got it done this way...

--// CREATE THE LOGIN
--// CREATE THE FLEXIBLE ROLE
--// GRANT, DENY, REVOKE PERMISSIONS FOR THE ROLE
--// ADD MEMBERS TO THE ROLE

It meets my objective to disallow a logged in user permission to delete any data while using a LightSwitch 2-tier app.



kevaburg
kevaburg
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1577 Visits: 988
Firstly, I wasn't laughing at you, I was laughing at Gails response!

Secondly, simply having Group Policy Editor is not the opportunity that people look for to hack into a system of any kind. It is the permission to edit the Group Policy itself that causes the problems and if you have Domains Admins that are likely to abuse that right then you problem is bigger than you think.

If you think Windows 7 security is laughable then perhaps a better understanding of Active Directory and Group Policy Management is necessary. Laughable for me is someone has decided to place a production (I assume) database on a laptop and the admins did nothing to stop it! It is a database service and deserves more respect than that.

Above all, use the concept of minimal privilege. It is true that if someone really wants to get in, they will. But that is not the get out clause for admins that distribute admin passwords to all and sundry and not the excuse to use for not understanding the (admittedly) complex security mechanisms that will protect your organisation.
The bottom line: Get a new box for the database, even if it is only a good PC!
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