SQL Server Security 101 (1 Viewer)

Thales750

Formerly Jsanders
Local time
Yesterday, 22:09
Joined
Dec 20, 2007
Messages
2,061
What is to stop a would be industrial spy from creating a new database, connecting to the "Machine ODBC" and inserting the data into new local Access tables.

All that would be left to do is email the file, copy it or upload it to the cloud.

Any ideas how to foul their attempt?

I think you can create a new Access Database even if you don't have Local Admin Rights.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
If the spy has access to the data, it doesn’t really matter if they can create a file on the computer or not. They could take a picture of it or write it down. If they can create files on the computer, it doesn’t have to be Access. They can copy and paste the data into a text file, or they can also connect to the data using other apps like Word or Excel.
 

Thales750

Formerly Jsanders
Local time
Yesterday, 22:09
Joined
Dec 20, 2007
Messages
2,061
There is a difference between letting them have access to their own accounts, and letting them copy the entire database.

Granted, this is still better than a file with everything in it, but only one layer better.

Does SQL Server have a way of limiting records for different SQL users?
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 13:09
Joined
Jan 20, 2009
Messages
12,849
Does SQL Server have a way of limiting records for different SQL users?

SQL Server has very fine grained security on all objects right down to individual table columns with control over allowed actions (Update, Insert etc).

If you want to protect particular records in a table then limit the records returned on Views and adjust the permissions to limit users to the appropriate Views and block them from the tables.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:09
Joined
Oct 29, 2018
Messages
21,358
There is a difference between letting them have access to their own accounts, and letting them copy the entire database.

Granted, this is still better than a file with everything in it, but only one layer better.

Does SQL Server have a way of limiting records for different SQL users?
Understood. But like I said though, if your only concern was to prevent the spy from creating a new Access database to connect to the backend, then I was trying to make you aware, Access is not the only tool a spy can use to steal your data. Other apps like Excel can also connect to your database. And as Galaxiom said, if the user has legitimate access to the data, then make sure their permission is limited to the data they're supposed to see, which can be done within SQL Server.
 

Thales750

Formerly Jsanders
Local time
Yesterday, 22:09
Joined
Dec 20, 2007
Messages
2,061
Yes I was aware there are many ways to get the data. It's just that Access provides a wholesale approach

Just make a local copy with data,

It seems that making this view thing might get a little burdensome. If an organization has 200 sales people and you are wanting to keep them out of everyone else's customers that may prove to be unattainable.

Maybe the idea would be to not use Directory Services for authentication. I'll do some reading and report back.
 

isladogs

MVP / VIP
Local time
Today, 02:09
Joined
Jan 14, 2017
Messages
18,186
It easy enough to limit access to selected data to specific users. If you remove the ribbon and navigation pane so that all interaction is via forms then it will also be difficult for users to make local copies of the tables from your SQL backend.

If still not secure enough for your liking, the BE could be encrypted. In addition you can remove all 'hard' links and just connect tables as record sources in code when form or reports are loaded. That approach is demonstrated in this example Encrypted No Strings Split Database .
Doing that would make the issue you are describing almost impossible...though it is of course still possible to take screenshots/photos of the unencrypted data in the forms
 
Last edited:

sonic8

AWF VIP
Local time
Today, 03:09
Joined
Oct 27, 2015
Messages
998
It seems that making this view thing might get a little burdensome. If an organization has 200 sales people and you are wanting to keep them out of everyone else's customers that may prove to be unattainable.
Why would that be burdensome or even unattainable? Most likely, you need to filter the data by the responsible sales person for usability purposes anyway. Put that logic into a view or stored procedure on the server (recommended for performance anyway!) and you've got the basis for secure data access already with next to no additional effort.


Maybe the idea would be to not use Directory Services for authentication. I'll do some reading and report back.
Some people will recommend that approach. - I disagree. Fundamentally.



Maybe you would consider including my text on this matter in your reading research. -> Authentication Mechanisms for Access + SQL-Server-Applications
 

Users who are viewing this thread

Top Bottom