| Author |
Topic |
|
ostinoh
Yak Posting Veteran
66 Posts |
 Posted - 2013-02-13 : 09:48:38
|
| Hello - Developers at the company I work for want to access one of our databases to create an iPad app for entering your timecard. I have a copy of this database on another server off my production server. The database has many tables some with private company info and I only want to grant them rights to 6 tables that they can access and will help them create this app.What is best approcah to allow them only that access to those tables?Regards,David |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-13 : 10:26:39
|
I try to avoid putting sensitive information on development servers as much as possible. So my suggestion would be to restore a copy of the database, remove all the sensitive information and give them the pruned database with full access. The other alternative would be to give them full privileges to the specific tables. That can be done, but there may be many things a developer wants to do to test their code that would require elevated permissions. So you may need to add additional permissions. All of that can be done, but each time you will have to verify that the sensitive information is protected from them.If you want to follow the second approach, give them public access on the database - you do that in the server level security under logins, User mapping tab. Preferably give this to an AD group for developers on this project. Then, grant select on the tables they should be allowed to see.GRANT SELECT ON dbo.ATableName TO [YourDomain\DevelopersAdGroup] You also need to make sure that none of the users individually or the AD group does not have sysadmin privileges on the server. Considering all of these various things you need to look at I want bring back and present my first solution again as the preferred solution. |
 |
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2013-02-13 : 16:54:41
|
James - Thank you for the reply. I agree with you 100% about not letting them have that access but it came down from the CFO.  If I grant them the select permission on those tables if they have SSMS installed would they be able to query any tables besides they ones a granted from within SSMS?Regards,David |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-13 : 23:55:45
|
quote:
Originally posted by ostinoh
James - Thank you for the reply. I agree with you 100% about not letting them have that access but it came down from the CFO. If I grant them the select permission on those tables if they have SSMS installed would they be able to query any tables besides they ones a granted from within SSMS?Regards,David
the best thing you can do in that case is to copy the tables from production and run some scrambling algorithm to scramble sensitive data. The tables can still be accessed by dev team but they wont be able to get hold of actual data.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
ostinoh
Yak Posting Veteran
66 Posts |
Posted - 2013-02-14 : 09:42:43
|
| Thank you for all the replies. I have some fun time ahead of me to work this out. I will update if I can get it to work right.Thanks again,David |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-02-22 : 18:47:18
|
| You could also issue a DENY on the sensitive table; maybe DENY INSERT, UPDATE, and DELETE, also. Assumin the developers are in some AD Group or SQL Role:[CODE]DENY SELECT ON dbo.SensitiveTable TO [YourDomain\DevelopersAdGroup][/CODE]Now, even if they are sysadmin via some other security GRANT, they won't be able to see the data.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
|
Andywin
Starting Member
3 Posts |
Posted - 2013-04-05 : 04:44:31
|
| unspammed |
|
|
|
|
How to set limited access to certain database tabl