Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 How to set limited access to certain database tabl

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.
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 23:57:40
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 - 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
Go to Top of Page

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
Go to Top of Page

Andywin
Starting Member

3 Posts

Posted - 2013-04-05 : 04:44:31
unspammed
Go to Top of Page
   

- Advertisement -