November 5, 2001 at 12:47 am
Hello.
I'm new to SQL Server, and find the quality and clarity of discussions on this site to be very impressive.
I am having difficulty getting my head wrapped around security issues. Sure would appreciate any help anyone can provide.
I have a VB vertical market app which currently uses an Access Db with user level security. When I open the db in code, I refer to a particular "workgroup information file" which contains all users, groups, and permissions in an encrypted format. Essentially, I need just one user since my app logs into the db using that user's name and pwd for everyone.
Using this strategy, I'm able to keep anyone at the user's site from accessing any part of my Access db. This is the same result I'd like to achieve with my SQL Server (7.0)version.
Is this possible? I'm getting the impression that there's no way to prevent the sysadmin from having access to a proprietary db running on a user's server. Hope I'm just missing something.
Many thanks,
Bob
November 5, 2001 at 5:00 am
You're not. Double edged sword. You can easily keep all BUT sys admins out. Think of it much like NT/Win2K on a network, you need someone who is all powerful for those times when something goes wrong.
You really have to to evaluate why you want to keep them out. If you're concerned about the DBA modifying structures, stored procedures, etc - you're going in the wrong direction. It's that exact ability that makes SQL such a powerful platform.
On the other hand, if you're concerned about the DBA seeing the data, you have two choices. The first (and I recommend) is to get over it. Just like with a network admin/domain admin type person, you have to trust your DBA a LOT. The second option is to encrypt the data using whatever scheme you come up with.
I work with a 3rd party app that uses SQL, but to remain platform independent it makes NO use of stored procedures. So when I have performance issues or need to tweak it's behavior, I'm very limited in what I can do - I can't recompile the app!
One other note: it is possible to encrypt stored procedures which may contain sensitive business logic, but this encryption is not uncrackable. It's better than nothing in some cases, but use caution depending on what you're protecting.
Andy
November 5, 2001 at 5:27 am
We had a similar issue, and tackled it using two solutions together:
(a) encrypted sensitive info such as passwords etc prior to entering it into sql server (we used crypto.dll: free from ms)
(b) had the client sign a sort of disclaimer - you are not prepared to support a database if they have 'fiddled' with it. (Not surprisingly this meant they left well alone!)
Paul Ibison
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
November 5, 2001 at 11:54 am
I agree with the two above. A couple items to think about.
1. As of right now, I have the decrpytion algorithm to decrpyt any v6.5 view/sproc/trigger. I also have 2 separate apps to do the same for v7/2000.
2. Encrpyting data using ENCRYPT() works if you need a one way hash. This can be used to "verify" info (like passwords), but not get back the original text.
3. BE VERY CAREFUL about encrypting data. You need to be able to always get the data back and people will forget passwords/keys, machines will crash and lose keys, etc. If you provide this capability for users, and they lose data, you may be liable. I'd avoid this.
You can't keep the sysadmin out. BTW, access is even less secure, despite what you think.
I'd recommend you use MSDE, not provide the tools and write your contract to disclaim support if they alter the structure of the app.
Steve Jones
November 6, 2001 at 12:49 am
Thanks very much Andy, Paul and Steve. You've given me a lot to think about.
For the record, the reason I hadn't thought of actually "trusting" the sysadmin (must be a horrible thought to all you dbas) is that our customers are typically companies with under 30 users, and almost none of them has such a thing as a sysadmin. So we wind up playing that role on line. At least that's how it works using Access, and in our legacy dos app.
Another reason I want to keep the db as secure as possible is, frankly, to keep it and its schema out of the hands of competitors and would be competitors. Guess I can't do much about table structures and fields, because encrypting the data is scary. But I'll have to think carefully about encrypting sprocs. I've got dozens of them, and it's been quite a chore converting them from Access.
Steve, I didn't understand what you mean by:
>I'd recommend you use MSDE, not provide the tools...<
Thanks again, very much.
Bob
November 6, 2001 at 10:34 am
Glad we could help. Rather than full-blown SQL Server, if this is a small app, use MSDE. Basically SQL Server light and do not provide any of the client tools. This way non-tehcnical people won't mess with the system.
Good luck
Steve Jones
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply