February 13, 2013 at 7:47 am
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
February 13, 2013 at 8:18 am
david.ostrander (2/13/2013)
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
Are you planning to allow the app to directly access the table? Ideally you would guide them into having the app access all data through stored procs.
For development purposes though, if you just want them to see those 6 tables you could:
1. create Server Logins for the developers on the instance (CREATE LOGIN)
2. create Database Users based on those Logins in the database (CREATE USER). By default Database Users are only a member of the public Fixed Database Role which will not have SELECT access to any tables unless someone granted additional permissions to the public Role (you may want to check).
3. create a new Database Role (CREATE ROLE) and add the Database Users you just created as members of the role (sys.sp_addrolemember)
4. grant the Database Role necessary access to those tables (GRANT INSERT, SELECT, UPDATE, DELETE ON [schemaname].[tablename] TO [RoleName];)
If you do go the route of having the app use stored procs for data access there is more you will need to do in order to allow the developers to develop procedures while still protecting your sensitive data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 13, 2013 at 2:05 pm
Thank you for the response...
Would they be able to run Select statment from SSMS on any of the tables? I just tested that and they could. Is there another way to stop that as well?
Regards,
David
February 13, 2013 at 2:41 pm
They will only be able to retrieve data from tables to which you grant them SELECT permissions.
If you are asking if there is a way to allow their database user to select data from a table using "the app" but not when using SSMS, no not really.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 14, 2013 at 12:48 am
You could look into using an application role for the app, but I guess even that wont prevent them from using ssms for queries.
February 14, 2013 at 1:31 am
Uninstall SSMS from systems they are using :w00t:
You can make user the windows users don't have administrator privilege and make the USER ACCOUNT CONTROL High under System and Security.
Hope this helps
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply