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 ««1234»»»

Do Users based on logins in master have access to other databases? Expand / Collapse
Author
Message
Posted Sunday, March 17, 2013 3:06 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1432029
Posted Monday, March 18, 2013 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, 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.



Post #1432201
Posted Monday, March 18, 2013 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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?
Post #1432212
Posted Monday, March 18, 2013 8:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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?
Post #1432215
Posted Monday, March 18, 2013 9:32 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1432232
Posted Monday, March 18, 2013 12:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
lol!
Post #1432296
Posted Tuesday, March 19, 2013 8:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, 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.



Post #1432694
Posted Tuesday, March 19, 2013 8:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 42,445, Visits: 35,501
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 2008, MVP
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

Post #1432701
Posted Tuesday, March 19, 2013 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 9:02 PM
Points: 33, 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.



Post #1432702
Posted Tuesday, March 19, 2013 9:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:13 AM
Points: 307, Visits: 475
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!
Post #1432716
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse