September 15, 2015 at 5:30 pm
On second thought, the CodeNames can only help moving forward and can do nothing for us on our existing databases, without rewriting a lot of code.
tempBD was mentioned... Does that mean that all these efforts can be easily defeated by an accomplished dba? If so, what should I be considering?
Can Gail's solution be an effective solution if I implemented the CodeNames? Would it be secure against tempDB as well?
I do need the ability to use ssms's gui on the database while (as I understand you) the developer can see the other databases, but cannot explore, drill down and/or execute them? Without the gui, productivity will suffer, at least for us.
Thanks,
Stanley
September 16, 2015 at 1:56 am
stanlyn (9/15/2015)
tempBD was mentioned... Does that mean that all these efforts can be easily defeated by an accomplished dba? If so, what should I be considering?
Thanks,
Stanley
If the dba has the permissions required to to his job then he doesn't need to defeat them - he will be able to see everything anyway. If you cannot trust your DBA then you have bigger problems.
September 16, 2015 at 3:03 am
stanlyn (9/15/2015)
the developer can see the other databases, but cannot explore, drill down and/or execute them? Without the gui, productivity will suffer, at least for us.
He can see the other DBs, but since he has no permissions on them, cannot open them and hence cannot see anything inside.
Your question about a DBA defeating these tricks is pointless. A DBA needs high-level permissions to do their job, they don't need to defeat the tricks. Same goes for a server admin. To do their job they have to have enough permissions that they can do anything on the instance/server.
If you don't trust your admins, fire them. If you don't trust your developers, don't let them access the production server.
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 16, 2015 at 4:36 am
stanlyn (9/15/2015)
Brandie Tarvin (9/15/2015)
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.
GilaMonster (9/16/2015)
stanlyn (9/15/2015)
the developer can see the other databases, but cannot explore, drill down and/or execute them? Without the gui, productivity will suffer, at least for us.He can see the other DBs, but since he has no permissions on them, cannot open them and hence cannot see anything inside.
Your question about a DBA defeating these tricks is pointless. A DBA needs high-level permissions to do their job, they don't need to defeat the tricks. Same goes for a server admin. To do their job they have to have enough permissions that they can do anything on the instance/server.
If you don't trust your admins, fire them. If you don't trust your developers, don't let them access the production server.
Totally agree with Gail here. If you are having trouble with your mentor stealing your ideas, then you have other issues that are far more severe. First of all, you have the wrong mentor. Also, it's possible this mentor stole ideas from previous clients. So you should consider getting together with the people he's worked for before and check with them. Also, consider reporting these people to law enforcement. There are other options than lawsuits. If you're in the U.S., report the thieves to the FBI / Justice Department. They can investigate the mentor (or previous employees) for corporate espionage and other crimes.
stanlyn (9/15/2015)
I want it to run on the live server so performance testing can be done without setting up a special test env.
OUCH! That's "fingers on a chalkboard" to a DBA. Never ever ever run anything against your production server if it's not production ready. You could screw so many things up. I know you don't want a special test environment, but think about how much this is costing you in stolen intellectual property and how much it will cost you in business losses if your production server goes down because of a test that hosed the entire server. Seriously, Stanley, don't do this. It's not worth losing everything just to avoid a little bit of work on a test environment.
stanlyn (9/15/2015)
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.
If the Alpha app is giving the developer all the information anyway, then nothing you do on SQL Server will matter (as far as restrictions go). Server loads can be simulated by replaying trace files (windows and SQL) against a test machine. There are tools that can help with creating test data (I'm pretty sure several people here have written articles on how to create test loads with T-SQL). Install a separate instance on a cheap server / desktop. Put only what you need on it. Test there, not production.
And remember, code names will have to be rewritten in your code before the final push if you're using linked servers in your code. Something to take into consideration.
September 16, 2015 at 9:42 am
Once you run DENY VIEW ANY DATABASE for the Developers user id, The developer will not be able to see any databases in SQL Server Management Studio unless they are a SYSADMIN or their login is the owner of a database(s).
Would you be happy to make the developer a DBO?
September 16, 2015 at 1:55 pm
>> If you are having trouble with your mentor stealing your ideas
I have always trusted my people, until they prove otherwise... Sometimes it was too late before we found out that they could not be trusted. I said earlier that I've been stolen from before and I learned a lesson from that, therefore I'd like to not repeat the lesson, instead secure it, and don't tempt anyone with what our instances contains, which is the root question of this thread.
After a lot of googling last night, it appears that I am not alone in finding an answer to this, as it is a very, very popular question. Before I asked the question, I would have never imagined this was not doable in SQL Server, instead I expected this to be a very simple 101 issue. Installations where multi-tenant servers are all seeking this solution too, as they are getting pricing resistance from some customers that are seeing how many customers databases that the instance is hosting, as well as CustomerA is complaining that CustomerB can see their databases and hence their business.
I did find one article discussing 2012's "Contained Database" as a possible solution. Any suggestions on this?
Another article described this as a bug...
Check out this old and revealing article on this issue: https://connect.microsoft.com/SQLServer/feedback/details/273830/need-view-definition-permissions-per-database
Does anyone know if any newer version MSSQL has addressed this?
Does MySql also suffer from this as well?
Thanks,
Stanley
September 17, 2015 at 1:47 am
Your other option would be to set up more instances as you can control access to each seperately. Ultimately you can go to the extent of one instance per database.
Set up development and test instances as well whilst you are at it - idealy on a seperate server. Actually do this bit whatever path you choose to go with for security so that you don't need to test on a production server.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply