I am struggling with which most efficient method to use to secure data in my database. I have an application split BE on server and FE on users workstations. I have already set up user level securit but users can still see the linked tables and queries from the FE and can also access tables on the server. Any ideas.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
database security 1
- Thread starter mok44
- Start date
mok,
If you've set up security properly, they won't have any other permissions than the ones you've granted them. It's quite common to miss a step or two in the process. I would try again.
Have you read the Security FAQ from MS? I have a copy of it on my website. But I also just released a paper on the topic of Access security and how to secure a database--it goes into a bit more detail than what's in the FAQ. All this is in the Developers' section of my website.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
If you've set up security properly, they won't have any other permissions than the ones you've granted them. It's quite common to miss a step or two in the process. I would try again.
Have you read the Security FAQ from MS? I have a copy of it on my website. But I also just released a paper on the topic of Access security and how to secure a database--it goes into a bit more detail than what's in the FAQ. All this is in the Developers' section of my website.
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Thread starter
- #3
Hi Jeremy
Great tips on your site!
I reviewed your security document. I think I am following the same process with the Access security wizard, however I am having to give some tables and query permissions to users otherwise they will not able to run action queries, insert/delete records etc...I do want to let users manipulate data, I just want them to do it via my forms and not be able to get to tables via the back end or by clicking on Windows>>Unhide.
Thanks again.
Great tips on your site!
I reviewed your security document. I think I am following the same process with the Access security wizard, however I am having to give some tables and query permissions to users otherwise they will not able to run action queries, insert/delete records etc...I do want to let users manipulate data, I just want them to do it via my forms and not be able to get to tables via the back end or by clicking on Windows>>Unhide.
Thanks again.
MOK,
Glad you found that info valuable. There are a lot of other things you can do to lock down an application. I'll have to do a full write-up of those and add that to my security section. For now, here are some things to check into:
-The Start-up options, which allow you to hide the database window.
-Disabling the Shift-Bypass of that effort.
-Releasing your database as an MDE
-Using the two-mdw system for securing a database.
Just please, please make backups before you investigate any of them, as some of them work quite well!
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Glad you found that info valuable. There are a lot of other things you can do to lock down an application. I'll have to do a full write-up of those and add that to my security section. For now, here are some things to check into:
-The Start-up options, which allow you to hide the database window.
-Disabling the Shift-Bypass of that effort.
-Releasing your database as an MDE
-Using the two-mdw system for securing a database.
Just please, please make backups before you investigate any of them, as some of them work quite well!
Jeremy
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
vbainchicago
Technical User
I did this for a client once, and it seemed to work well.
I ran the security wizard on my workstation only, and distributed an .mde of the front end to the users. While they don't need to type a password to work with the app, they can't change anything in the .mde file. If they try, they get a "you don't have rights" message. While this may not be the ideal solution in every case, it worked very well for this particular client.
VBAinChicago
Just for Fun -
I ran the security wizard on my workstation only, and distributed an .mde of the front end to the users. While they don't need to type a password to work with the app, they can't change anything in the .mde file. If they try, they get a "you don't have rights" message. While this may not be the ideal solution in every case, it worked very well for this particular client.
VBAinChicago
Just for Fun -
- Thread starter
- #7
I've tried several of the suggestions made here so far, I still cannot effectively prevent my users from seeing tables and queries, is there no way to lock the back end yet still allow tables to be linked? Same for seeing tables and queries on the front end. An .mde does not prevent the user from seeing the linked tables and queries. I tried unchecking the Display Database Window but that can be changed by anyone.
Is there no way to force access to the app through forms only?
Thanks for all the help.
Is there no way to force access to the app through forms only?
Thanks for all the help.
You can disable the shift bypass, which will make it so that you can't get to the database window. You also have to uncheck the "use special keys" in the tools window, otherwise hitting f11 will bring it up.
This page talks about how to do this:
If you do this, I would suggest creating a backdoor, so that you can re-enable it. I usually use some element of the form that doesn't look like it would "do" anything, such as an image or something like that, and put code in the doubleClick event that, if you're a member of the administrators group for that database, allows you to turn re-enable the shift bypass.
And again I'll emphasize the importance of making backups before getting into this.
Jeremy
PS: Also, my bad, in that I should have listed rwop with the other things. It crossed my mind, but for some reason it didn't get turned into action.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
This page talks about how to do this:
If you do this, I would suggest creating a backdoor, so that you can re-enable it. I usually use some element of the form that doesn't look like it would "do" anything, such as an image or something like that, and put code in the doubleClick event that, if you're a member of the administrators group for that database, allows you to turn re-enable the shift bypass.
And again I'll emphasize the importance of making backups before getting into this.
Jeremy
PS: Also, my bad, in that I should have listed rwop with the other things. It crossed my mind, but for some reason it didn't get turned into action.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
-
1
- #9
This will toggle the allow bypass property (or create it when it's run the first time). *Label controls have a double-click event you can use with a label that only has a space for a caption--giving you an invisible "image-map". Just don't forget where it is...
[tt]Public Const PROPERTY_NOT_FOUND As Long = 3270
Public Sub ByPassKeyToggle()
'This Code Disables/Enables the Shift Key
On Error GoTo Error_ByPass
Dim Prp As DAO.Property
Dim DB As DAO.Database
Dim blnToggle As Boolean
Set Db = CurrentDb()
blnToggle = Db.Properties("allowbypasskey"
blnToggle = Not blnToggle
Db.Properties("AllowByPassKey" = blnToggle
MsgBox "AllowByPass is set to " & blnToggle
Exit_Bypass:
Set Prp = Nothing
Set DB = Nothing
Exit Sub
Error_ByPass:
'Constant above
If err.Number = PROPERTY_NOT_FOUND Then
Set Prp = Db.CreateProperty("AllowByPassKey", dbBoolean, True)
Db.Properties.Append Prp
Db.Properties.Refresh
Resume Next
Else
MsgBox "Bypass routine failed due to " & Err.Number & ": " & Err.Description
End If
Exit Sub
End Sub[/tt]
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
[tt]Public Const PROPERTY_NOT_FOUND As Long = 3270
Public Sub ByPassKeyToggle()
'This Code Disables/Enables the Shift Key
On Error GoTo Error_ByPass
Dim Prp As DAO.Property
Dim DB As DAO.Database
Dim blnToggle As Boolean
Set Db = CurrentDb()
blnToggle = Db.Properties("allowbypasskey"
blnToggle = Not blnToggle
Db.Properties("AllowByPassKey"
= blnToggleMsgBox "AllowByPass is set to " & blnToggle
Exit_Bypass:
Set Prp = Nothing
Set DB = Nothing
Exit Sub
Error_ByPass:
'Constant above
If err.Number = PROPERTY_NOT_FOUND Then
Set Prp = Db.CreateProperty("AllowByPassKey", dbBoolean, True)
Db.Properties.Append Prp
Db.Properties.Refresh
Resume Next
Else
MsgBox "Bypass routine failed due to " & Err.Number & ": " & Err.Description
End If
Exit Sub
End Sub[/tt]
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Does this bypass key thing work with Access 2003?/Windows XP? I used to have my DB set up this way, but now that I have access 2003 I have to go to another PC that has older versions in order to get into the backend of my database. Any Suggestions?
Haven't got a clue personally. I've yet to get Access 2002 (I like 2000 and have never seen a compelling reason to change--don't need triggers or XML for Access just yet).
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development
- Thread starter
- #12
Put a db password on the backend file. Then you can write code to link/or refresh links in your front end using the password in the connect string (if MDE file users can't see encoded pwd). I've done this before but I can't find the code...
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development
Jeff Roberts
Insight Data Consulting
Access and SQL Server Development
Why are you not securing the back end? If you secure the back end you won't have these problems.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
Great thread everyone!
So far we've been banking on the ignorance of our users and just removing the "Window" selection from their Menu Bar in Access so they can't get to the "Unhide" option for our databases.
Not very good security, but its kept us safe so far (knock wood). Anyway, we'll be able to set up a some REAL secuity now thanks to your posts.
Nikgo
So far we've been banking on the ignorance of our users and just removing the "Window" selection from their Menu Bar in Access so they can't get to the "Unhide" option for our databases.
Not very good security, but its kept us safe so far (knock wood). Anyway, we'll be able to set up a some REAL secuity now thanks to your posts.
Nikgo
- Thread starter
- #16
Yes, implement use-level security on both the front end and the back end.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations
Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
