April 16, 2021 at 3:30 pm
Granting Connect,View any database and view any definition in UAT and PROD to Developers. Do you see any issues?
April 16, 2021 at 3:51 pm
The only issue I see is if developers don't need to see the definition of an object or the name of a database due to proprietary knowledge in or about it OR if the information they can access would be interesting to bad actors (hackers, disgruntled employees, etc.).
What I mean is if an account gets compromised that has access to this information, would the information be helpful/useful to hackers? OR is there information that could be useful to a disgruntled employee looking to take the company down?
An example would be if you had a stored procedure that had dynamic SQL and took in a parameter that was used in the dynamic SQL. Seeing the definition of that stored procedure makes it a LOT easier to target and potentially destroy your system.
Alternately, if I saw a database called "EmpData" and I was a hacker, knowing the name of it could help me in targeting it. If all I could see was "DevEnv01" (for example), I would not be as interested.
Same thing with the disgruntled employee approach. If I can see a database name that looks interesting but I have no access to it, it is more tempting to try to find a way in. If I don't know it is there, I can't be tempted. Security by obscurity.
My recommendation is to follow the "least privilege" model of security. If you don't need access to it, you don't have access to it. This way, in the event of a data breach due to compromised credentials, the impact is much lower. Now, I don't recommend restricting it to the point where people cannot do their jobs as that is not productive or helpful to anyone, and in the end will just make more work for you as you set permissions.
The above is just my 2 cents.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 16, 2021 at 4:07 pm
I agree. In particular, I would never do this for "ANY" db. And definitely not in production. Prod data, particularly, should always be as tightly restricted as possible.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 16, 2021 at 5:09 pm
Thanks for you're input's. So you're saying specific to View any database and view any definition is not recommended? How about read and Granting Connect access?
April 16, 2021 at 5:33 pm
Sorry. Granting view permission meaning is connect access right?
April 16, 2021 at 5:37 pm
Granting connect is safe and required if you want a user to be able to connect to the SQL instance.
Granting read is again, something I try not to give away too liberally. For example, lets say developer A has read access on the database EmpData. They do a SELECT on the table SSNInfo and now have a list of social security numbers (or SIN - social insurance numbers). or Addresses. Then you get a data breach on developer A's account and a whole bunch of PII data is leaked.
That is an extreme example, but it is why I like to follow the least privilege model. Now, if the information is something that is either public knowledge OR non-confidential, then granting public access to read it MAY be ok. But as soon as you start granting someone the ability to read all data in a database, you are making them a potential target for hackers or bad actors. If I have restricted access but I know developer A has access to data, I may be tempted to use their account (with or without their permission) to access data. Or you can get into weird permissions like I have no access to read table SSNInfo but I was given permission to impersonate developer A who does have permission to read ALL tables. That could be a REALLY bad scenario (unlikely scenario, but would not surprise me if it existed in some systems).
I strongly encourage you to work with a "least privilege" model and in general, avoid giving permissions to a user - create a role for the permissions and you can put multiple people in the role. The advantage to this is if Developer A changes roles to be a manager, you take them out of the database role and their permissions are gone. They hire a replacement or a secondary for Developer A, you put them in the role and BAM - permissions are set up on the database with minimal effort on your part!
Also - VIEW and CONNECT are 2 completely different permission sets.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 16, 2021 at 5:43 pm
I think probably what will help you the most is something like this:
https://aka.ms/sql-permissions-poster
It is a rather large poster, but it gives you all SQL related permissions, what they mean and what they give you access to.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply