September 14, 2015 at 9:19 am
Hi,
Our sql server 2012 contains 10+ databases. I need to let a developer work only with one specific and tempdb databases, and nothing else. I don't want them to see any of the other databases. When they login, they should only see tempdb and their assigned database. If I were to login with sa, then all databases should be available.
I created a "Test" user the developer will use for their work. I tried several settings, but could not get the desires results. I was able to get the "Test" user to login and ALL databases were hidden, including the one that the user has perms to.
Can anyone point me to resources that discusses this type of configuration? Or maybe you know...
Thanks,
Stanley
September 14, 2015 at 9:21 am
Create them a login. Give that login a user in the database they should be allowed to access. Give the user whatever permissions it should have in the database. Give the login NO other permissions.
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
September 14, 2015 at 9:39 am
When you say the user shouldnt see the other databases i assume you dont want them to have the databases listed in the tree view on SSMS ,this wontbe possbile since the user needs public access in order to access master database and validate the login. The only way to achieve the result would be if the database is a contained database and the user is a contained user.
Else if you just want them to be able to work on only thier databases then you can simple allow grant the user and login dbo access on the desired database while ignorning the rest.
the below video should help get the basics in place.Video here
September 14, 2015 at 10:11 am
>> When you say the user shouldn't see the other databases i assume you don't want them to have the databases listed in the tree view on SSMS
Yes, that is best as the other databases does not need to be advertising themselves.
So, does this mean that Gail's suggestion will not achieve this?
Thanks,
Stanley
September 14, 2015 at 10:20 am
Correct. Public (I believe) by default has the 'View any database' permission, so all logins get that by default (all logins are a member of public). You can deny that to the login, but if you do they won't be able to see any databases in Object Explorer. They can still use any database they have access to, but that'll require that they know the names and type all the commands.
Is just seeing DB names really a big deal? They won't be able to do anything else.
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
September 14, 2015 at 10:35 am
Hi Gail,
It would matter greatly if the server was hosting highly sought after data, as 1st, it would reveal/advertise that it is hosting such data which makes it a target by default. So, I'd like to learn how to really decrease the attack surface.
Also, at this point, we really need to minimize our disclosures, of which most are covered by a non-disclosure agreement. Our databases and tables can easily tell prying eyes what we are up to, what our next product or service will be, and etc, and that we must protect at all costs. Hope our reasoning makes sense...
Thanks,
Stanley
September 14, 2015 at 10:54 am
Public is a server-wide role. As previously mentioned, you can add people to individual databases without the role, but unless they know what to type / look at, they won't see anything.
September 14, 2015 at 11:07 am
Brandie Tarvin (9/14/2015)
Public is a server-wide role. As previously mentioned, you can add people to individual databases without the role, but unless they know what to type / look at, they won't see anything.
Brandie - I thought all logins get created with the public server role as the default. Don't you also have to deny view access to public?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 14, 2015 at 11:34 am
Matt Miller (#4) (9/14/2015)
Brandie Tarvin (9/14/2015)
Public is a server-wide role. As previously mentioned, you can add people to individual databases without the role, but unless they know what to type / look at, they won't see anything.Brandie - I thought all logins get created with the public server role as the default. Don't you also have to deny view access to public?
I think just revoking it will do the job, but to be honest, I haven't tested that in a while. I do know that I had an issue a few months ago with one database login not being able to access the db because it WASN'T a member of Public. Which is why denying that role is chancy at best.
September 15, 2015 at 2:50 am
stanlyn (9/14/2015)
Our databases and tables can easily tell prying eyes what we are up to, what our next product or service will be, and etc, and that we must protect at all costs. Hope our reasoning makes sense...
No, not really. You're talking about allowing a developer, I assume an internal developer (because if you have data that sensitive external people shouldn't be allowed within a mile of the server). Internal staff should be under the same NDAs as the ops people, and all that's revealed is the database name. The developer will be able to see the DB names, not open the databases, not see the tables, not query the data.
As I said, you can DENY VIEW ALL DATABASES to the developer's login and then the developer won't be able to see any databases, but he'll have to work entirely with script, he won't be able to use object explorer at all.
Or, just spin up a VM and give the developer a copy of the database on a VM that contains nothing else.
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
September 15, 2015 at 4:47 am
GilaMonster (9/15/2015)
Or, just spin up a VM and give the developer a copy of the database on a VM that contains nothing else.
I couldn't agree more with Gail there. Personally I do not like the idea of allowing developers (including myself here) to stomp about in live, it's just an unnecessary risk.
At our place the approach is changes in dev - script these to update a copy of live on UAT, diff to make sure the updated UAT matches dev, that script is then used to update live. This has prevented a lot of grief
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 15, 2015 at 4:56 am
Another option besides the VM is to log ship or Stand-By restore to another instance. Even if it's on the same server, a stand-alone instance won't show anything but what is on it.
I am curious, though, who's idea is it to lock down the databases? Yours or a boss's? If this comes down from on-high, you might want to find out what is prompting this sudden concern over secrecy. Because it won't be long before they are trying to lock the DBAs out of the server too.
September 15, 2015 at 5:37 am
If merely being able to see the name of a database is a breach of confidentiality then surely the easiest way to avoid that is to use codenames for new projects, maybe even create a few dummy ones from time to time if you are paranoid.
As Gail has already said by granting the developer only the database roles they require and no server roles they won't be able to see anything else, using codenames would then mean that what they can see is meaningless.
You would also want to lock them out of the filesystem of the hosting server.
tbh if you are that concerned then you need to consider what will be visible in tempdb.
September 15, 2015 at 5:13 pm
>> I am curious, though, who's idea is it to lock down the databases? Yours or a boss's?
Well, you can say both, as I am the boss and sys admin and have had our ideas stolen, only to later find out that you have to be rich to enforce a non-disclosure agreement. So, the best way is don't give anyone ideas, hence the secrecy.
At the moment, I'm attempting to learn Alpha Anywhere which is an application development suite for desktop, browser, and mobile applications with sql backends. I've been doing some sql work for our VFP frontends for several years now.
Currently whatever we (the Alpha developer and I) build is on a live server with many other databases, but I only want to expose a single db via a connection string to a 3rd part developer that is acting as a mentor and probably far better at this stuff than I am. Problem is, I cannot take a chance. I also don't need to make an overly complicated setup, as there is only the two of us. I don't have a huge budget for teams... I want it to run on the live server so performance testing can be done without setting up a special test env. The developer does not need ssms, however they wil be seeing the tables via Alpha's database tools. Remenber, the app he is helping me develop and mentoring me on is not advertised to the public, until we are confident to release it.
Initially, all I wanted to do is 1) create a project user account that allows full database functions on a single database and 2) hide all others. That is still the most ideal solution. Seems like (from everyone's comments) it is not that simple to setup?? Different servers and/or VMs just adds another layer of complexity and learning curve that we really don't have the time for.
I really appreciate everyone's input,
Stanley
September 15, 2015 at 5:14 pm
Thanks for the CodeNames idea, as that can serve us well and its simple..
Stanley
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply