June 6, 2007 at 10:36 am
In my environment have a Client Server application that performs Customer Service functions, requiring read/write access. I created a database role that grants db_reader, db_writer and execute permissions for specific stored procedures and functions that are essential to their job functions.
However, there are folks who need to perform QA functions that are asking for applications like TOAD or MS ACCESS in order to look at table data directly.
Allowing these domain users to install MS ACCESS would allow them to edit production data directly since they'd just be able to connect to the SQL Server with the same rights as they have to the CSM Application.
It is not an option to change the CSM application to use a seperate login so I can drop the permissions to the domain accounts.
Do I have any recourse here?
June 7, 2007 at 9:24 am
The people connecting to SQL Server using their domain logins will still only have the permissions that you grant them as users in the database. So, if they're members of db_datareader only, they won't be able to update the data even if they have those permissions in Access.
They might be able to import the data to Access and change it, but if they don't have insert or update permission in the SQL Server database, they won't be able to push their changes back to SQL Server.
Greg
Greg
June 7, 2007 at 1:40 pm
Yeah, that is part of my problem. For the client-server app they need db_writer, which would then spill over into other, non-validated and non-logged, applications.
June 7, 2007 at 3:00 pm
So, do they need db_datawriter when using the client-server app because it doesn't use stored procedures for everything it does? If so, maybe you could use a SQL Server login for the app and grant db_datawriter to it rather than to the domain logins.
Greg
Greg
June 9, 2007 at 1:01 pm
You may need an authentication proxy to control who can use TOAD or Access and from where.
If you can't change the application UID, then you may need to create a separate database account for accessing the database with read access only. You can configure the proxy to only allow those database accounts to be used from their workstations.
Any other attempt to connect using TOAD/Access from a different machine (regardless of the account) could be blocked.
There is only one authentication proxy I know of at this time - check pynlogic.com.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply