SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to set limited access to certain database tabl
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ostinoh
Yak Posting Veteran

66 Posts

Posted - 02/13/2013 :  09:48:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 02/13/2013 :  10:26:39  Show Profile  Reply with Quote
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.
Go to Top of Page

ostinoh
Yak Posting Veteran

66 Posts

Posted - 02/13/2013 :  16:54:41  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  23:55:45  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  23:57:40  Show Profile  Reply with Quote
see this thread for methods to do it

http://www.simple-talk.com/sql/database-administration/obfuscating-your-sql-server-data/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ostinoh
Yak Posting Veteran

66 Posts

Posted - 02/14/2013 :  09:42:43  Show Profile  Reply with Quote
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
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1693 Posts

Posted - 02/22/2013 :  18:47:18  Show Profile  Reply with Quote
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:
DENY SELECT ON dbo.SensitiveTable TO [YourDomain\DevelopersAdGroup]
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
Go to Top of Page

Andywin
Starting Member

USA
3 Posts

Posted - 04/05/2013 :  04:44:31  Show Profile  Reply with Quote
unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000