Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How secure is it if I let the other company's IT department execute stored procedures on our database server? They would have to know the database name, address, procedure name and of course I could set them a new login. My worries are, are there "easy" ways of bypassing security and accessing other data in the database?

share|improve this question
1  
Is the need for this data temporary for the duration of the project, or permanent? –  Jon Seigel Mar 3 at 17:46
1  
Why don't you just give them a copy of your database? –  RBarryYoung Mar 3 at 18:45
2  
@RBarryYoung, how would "providing DB copy/backup" will help OP when some one is concern about the unauthorized data access? –  Anup Shah Mar 3 at 18:49
    
@AnupShah Oops, you are right, I misread the question. –  RBarryYoung Mar 3 at 18:56

2 Answers 2

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis.

As you stated this is going to be on a regular basis I would setup an SSIS package to export the information to a file. Then either have the package email the file (if not to large) or put it on a UNC directory for them to come and download themselves. If they want to determine when the data is pulled down setup a script for them to execute outside of SQL Server that simply runs the package.

share|improve this answer
    
Hi there...direct access is needed because there are going to be some data transactions from our database to theirs and vice versa. The data will be 50-100 thousand rows which needs to be updated and/or inserted. –  AArmin Mar 5 at 9:27
    
Unless it requires manual intervention SSIS package can take care of that for them. –  Shawn Melton Mar 5 at 15:02

I have been to in this situations before where for a small projects (yet for good amount of period) we need to work with some out sourced team and we also need to make sure the client data is safe. for every day work we can not keep an eye on who is accessing what. on the other side we also want to make sure they can access information they really need as smoothly as possible. in this cases, I would suggest to create specific USER_ROLE and assign appropriate permissions to that role. not only that also Deny the Other permissions explicitly. Add the specific/needed user from that team as member of this role. of course you will make sure that the Role doesn't have any sysadmin/serveradmin/dbowner/securityAdmin/BackupCreator etc... kind of permissions.

a single person can manage the adding or updating the permissions on db objects as needed/requested.

Granting/revoking/Denying permissions on different objects can be manged by automated scripts to reduce daily overhead.

by this way at least your team will be aware of what has been given access to at any point in time.

share|improve this answer
    
That's a nice thought but there is one thing though...When I give the other party access I don't want them to see the db or any tables listed in the db. They should login to the db via remote connection file (that isn't a problem which i set up) and pull info I granted to pull. Because there are tables which change every few minutes I cant generate on every change a new VIEW of 200.000 rows –  AArmin Mar 5 at 9:33
    
I didn't get this point. Why would you change/create new view for every update/insert in same table. When you said the Table is changing every few minutes, you mean there are insert/update/deletes happening on that table. lets say you created Role "RemoteUser". GRANT SELECT/INSERT/UPDATE/DELETE ON "Table1" TO "Anup". Now When I am login-in with as remote user and try to Access "Table1" I can access it all time. I am not restricted to selected rows. When any new Objects Gets Created/Dropped then I need to make sure the remote user "Anup" gets right permission if he supposed to see that. –  Anup Shah Mar 5 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.