How secure SQL Server is? (1 Viewer)

GPGeorge

Grover Park George
Local time
Today, 09:46
Joined
Nov 25, 2004
Messages
676
Yep, "people" is a dirty word though it IS bigger than four letters. For me the four-letter word is "user" and is spoken with appropriate vehemence. Or,... USER - can't live with them, don't have a job without them. What are you gonna do?
Perhaps I over-generalized too much. I ALWAYS do that.
 

OuterApply

New member
Local time
Today, 09:46
Joined
Mar 10, 2022
Messages
15
I think this is where the confusion lies. If you intended to give a user permission to access the data, then it's not a problem, is it? I think the problem you're talking about lies outside of SQL Server or Access - the problem is with the user. If you cannot trust the user to not manipulate the data against company rules, then why give them permission at all. Things like this are more of an HR problem than an IT one.
In all fairness to the original poster of this thread, I think this is a bit of an oversimplification-in-the-interest-of-dismissing the legitimate concern.

Security is a legitimate concern, and the answer to your question is: "YES", absolutely, security-against-user-malusage is completely, 100% a legitimate and normal concern.

We have clients who use a portal. do I "trust" those clients to manipulate my database and data outside of the front end app we've given them? Of course not, that wouldn't be at all normal, and this doesn't mean it's an HR problem. It's a normal reason to create apps.

Secondly, trust isn't necessarily what's at issue. It's accuracy.

I agree with OP that the whole reason you want to have a FE to begin with is somewhat diluted if you can't guarantee that your FE is the only tool doing the data manipulation.

I understand the point dbGuy was making, I just think it went a little too far.

Geico loves to provide me an app for the purpose of self-service on my automobile insurance policy, but that trust and desire does not go quite far enough to giving me ODBC access and saying "have away", does it??

@KitaYama
I may be crucified for this suggestion, but I'll make it anyway. Here is one approach: Create a SQL Auth user account for the sql server. Create a front end which IS, in its own right, very well locked down, using all the available tools you can learn, including and most especially probably the Runtime or something similar. Now create code in the front end that links ALL tables on-the-fly when the db is open, using the SQL Auth account AND password. (Yes, that may leave a pw plain text in your connection string, I didn't say this was a solution without a problem). (But, if Doug Steele has done much with his DSNless connections code over the years, there may even be a way to avoid this). Now your FE is equipped to make all data updates using the FE only! When the database quits, un-link all tables. Utilize various methods to ensure that they must use your button to close the DB (easy). People may point out various flaws in this plan, and that's fine, BUT - if your main thing that you want to eliminate is the user having the ability to connect to the tables you had to give them permissions to via any other app, then this solution may get rid of that, unless their hacking intentions are SO strong & able that they hack into your FE and see the table links. Now that would be a scenario I'd justify & agree with using dbGuy's logic on to dismiss, as it's much more extreme.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:46
Joined
Oct 29, 2018
Messages
18,989
I understand the point dbGuy was making, I just think it went a little too far.
Hi @OuterApply. Thanks for your response. I do tend to go to the extreme sometimes, but I was just trying to explain a situation in the context of private company data, and the user is an employee of said company.
Geico loves to provide me an app for the purpose of self-service on my automobile insurance policy, but that trust and desire does not go quite far enough to giving me ODBC access and saying "have away", does it??
In contrast, this scenario is a little different, in that you are not part of the company but only a customer. I believe, you can correct me if I am wrong, the data you see in your app is probably mostly "read-only," correct? If so, then that example is a little different than what I was trying to discuss earlier.

To the point @GPGeorge was making, the Geico app is probably a perfect example of an "application role/permission," so the user cannot access the data directly without using the provided/authorized app.
 

OuterApply

New member
Local time
Today, 09:46
Joined
Mar 10, 2022
Messages
15
To the point @GPGeorge was making, the Geico app is probably a perfect example of an "application role/permission," so the user cannot access the data directly without using the provided/authorized app.

Yes, agree whole heartedly. The best solution is some sort of service account concept, which my crude suggested solution was a nod to, and probably would be "the" perfect solution if the plain text password in the tables' connection strings could be solved for, or dang near close.

I worked for a company from 2012 to 2016. The last couple years I was there, a guy came in who was an expert in VB/C#.net, T-SQL, SSIS. He was going to build a 'better' app than our Access apps. Honestly, I have to admit he was way up there, expertise-wise. What interested me was that he spent most of his time solving for this very problem. Precisely how and under what context (security, user) the App was going to execute things in sql server without sacrificing any permissions.

Frankly I didn't understand everything he was doing by a long shot, but I know ultimately his solution was going to involve entire extra LAYERS of applications - also involving Windows Services and Messaging - and there were layers of SPROC's upon SPROC's upon SPROC's to determine who the user was, where it was, and who was making the command and actually make it totally unhackable. Definitely no windows user actually had any permissions, other than to the FE of the app. Learned a lot of t-sql SPROC-related commands I'd never seen before too, then forgot them all.

It seemed like a dream come true, but it required 2-3 years of a guy who we paid about $120/hr (all included) to contract for us, and when I left it was only about half done. Guess that's the downside :LOL:
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:46
Joined
Feb 19, 2002
Messages
36,344
@OuterApply I guess you didn't see my earlier suggestion to not give the user his password. This method uses the relink each time as you mentioned except that it uses user credentials rather than the developer's credentials.

Why is this relevant? Because it is the only way you and the DBA can develop accurate statistics by user. If all users get lumped together, then you can only generate statics for the database itself. You can't see what individual users are doing.
 

Users who are viewing this thread

Top Bottom