Import objects and Access System Tables security issue

[alfalf]

Hello all.

I have:

1. Users & premissions setup;
2. mde app for front-end;
3. pwd protected server db;
4. front-end app have linked tables to this pwd protected server db.
5. Every user have login to front-end app.

The security leak is in this trick:

As user knows his own pwd to access front-end app., he/she can do following to compromise my pwd protected server db:

1. make a new blank database;
2. choose unhide 'System Objects' from tools/options dialog;
3. chose import menu option
4. chose front-end app mde file
5. will be prompted for username/password
6. he/she will confirm that
7. import MSysObjects System table from front-end mde app into blank db
8. Open it and simple read password for table liks to server db and therefore have password to access server database
9. This is ugly.

This leak bothers me for a long time.

Has anyone knew about this? What's the solution?

It is not so much that I'm considered that someone will alter design of my server db tables, but surely can access data with this pwd.

Also, there's no way to set premissions for system tables via User and Group premissions built-in Access tool.

So, any tip for solution of this mess will be helpfull.

Thanks.

 

[RickSpr]

My compliments on your analysis. I never would have thought of this apparent hole in Access security.

I take exception to your statement "there's no way to set permissions for system tables via User and Group permissions". If system objects are visible when you run the User and Group Permissions dialog, the system tables are listed in the dialog and their permissions can be set. At least this is true in Access 97 and Access 2000; I don't know about later versions.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[alfalf]

Thanks RickSpr.

My advice is to all: Never link tables to an PWD protected database. Because of upper reasons. Rather use code to access tables in an external database.

Although, Access authors offers solutions for this (Users and Premissions(ownership) instead of pwd protected database), that complicates things a lot.

DB

 

[RickSpr]

Good advice, DB.

Let me add some advice about accessing external databases from code: Consider using a global variable to keep a connection with the database. Open the database (DAO) or connect to it (ADO) as soon as possible after your application starts, and keep it active until you're done accessing it for a while, or until your application ends.

If you don't do this, a connection to the external database must be established each time you execute a query against it. This involves numerous transactions across the network to locate the database file, do file locking, etc. This may noticeably slow your application.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[alfalf]

Thanks RickSpr.

Talking about good advice (connection as global var)!

Thanks

 

[alfalf]

Hello RickSpr.

Again me on this security issue.

Recently I run into two mda files on Internet (will not mention names), free to download from legal site (will not mention name).

Those "Add-ins" are: one for viewing database password and other for viewing user's passwords from system.mdw file.

And, really, when I used "add in" for viewing user's pwds from system.mdw file -> VOILA! All usernames & passwords have been revealed!

Situation with other "add-in" (that reads database password) is different, as I use Acc2000 & my dbs are encrypted, the add-in couldn't correctly read my db password for any of selected db.

Therefore conclusion -> so much about security going on in Access. I posted three threads concerning security of Access databases (with wich I work since Version 1.0), and must say am completely devastated with my latest findings.

LITERALY, ANYONE CAN SEE USERNAMES & PASSWORDS.

But, here's very important conclusion concerning security to all access & VBA programmers:

1. USE ONLY PASSWORD FOR PROTECTION OF DATABASE
(As db passwords cannot be properly read with any of tested "add-in", and therefore CANNOT BE missused)
2. DO NOT USE INTEGRATED USER & PREMISSION SECURITY TOOL!!!
(Do not make user accounts and give them passwords as those can be EASILY viewed by any in Your company or wherever)
3. BECAUSE YOU'LL USE ONLY DB PASSWORD TO PROTECT DATA, DO NOT LINK TABLES FROM PWD PROTECTED DB!
(As from my previous thread about import/security, it's obvious that anyone can compromise that too!)
4. BUILD YOUR OWN SECURITY SYSTEM AND CUSTOMIZED LOGIN
(Make an additional pwd protected db, store pwds there, and use Code to reach that db and pwds stored).
5. ENCRYPT.

DO NOT DOUBT ANY MINUTE ABOUT THIS ISSUE.


For more help ask.
DB

 

[RickSpr]

DB, while I agree that Access security isn't tough enough for important secrets, building your own security system is worse.

Any security logic implemented in VBA is easily circumvented simply by creating a new database and linking in the tables from the supposedly secure database. The only way to block this is to use the Jet permissions to keep unauthorized users from opening a table. And that, of course, is what Access user security does.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Checking]

Hi folks; had the same problem in securing Front-End MDE.
My Back-end is secured with a Password I only know. Within the Front-End mde, you can activate a code to protect the FE from Shift-button opening it (separate issue/other thread) but also Hide all of your tables(including systemtables)

Call HideTables 'Put this code in a password protected button in a (system) form.
--------------
Public Sub HideTables()'put this code in a module
Dim daoDB As DAO.Database
Dim daoTDF As DAO.TableDef
Set daoDB = CurrentDb()
For Each daoTDF In daoDB.TableDefs
daoTDF.Attributes = dbHiddenObject
Next
End Sub
--------------
I found this code somewhere (don't know anymore) as I'm not an expert in programming, but it works.
If you protect your FE & BE with a 'shift-key-opening' prevention, I think security will be higher.
For max security, I'm afraid migration to SQL-server or others are a must.

B. Rgds to all of you.
PS. 5 stars for TEK-TIPS where I learned more the 5 last months searching tek-tips than the last 5 years without!!

 

[nashman]

Hi All,

I have run into the same issues where importing the MSysObject table into a blank database provided all of the information required to access my data.

There are a number of things you can do to help prevent unauthorized access. Because of the many add-ins available that will provide the password information to any mdw secured database, I have never used MS Access user security settings. Instead I do the following...

1.
Make an MDE. This will prevent your code and form design from being tampered with.

2.
Secure login with commandline access. I found this little bit of code here in Tek-Tips ( my apologies but don't remember who it was from ).

Create a new module and enter this code...

Function CheckCommandLine() As Boolean
    Dim db As DataBase
    Dim prp As Property
    Dim strProperty As String
    Dim strMSG, BOX As String
    Dim ysnAllow As Boolean

    Const conPropertyNotFoundError = 3270


    On Error GoTo CheckError

    Set db = CurrentDb()
    strProperty = "AllowBypassKey"

        ' Check value returned by Command startup function
    Select Case LCase(Command)
        Case "grantadmin"
            ysnAllow = True
            CheckCommandLine = False

            strMSG = "The Access bypass key has been reactivated." & Chr(13) & Chr(13)
            strMSG = strMSG & "The database is UNLOCKED!" & Chr(13) & Chr(13)
            strMSG = strMSG & "Please close the database and open again normally."
            DoCmd.Close acForm, "frm_DETECT_IDLE_TIME"

        Case "lock"
            ysnAllow = False
            CheckCommandLine = False

            strMSG = "The Access bypass key has now been deactivated." & Chr(13) & Chr(13)
            strMSG = strMSG & "The database is LOCKED!" & Chr(13) & Chr(13)
            strMSG = strMSG & "Please close the database and open again normally."
            
            Case "ok"
            ysnAllow = False
            CheckCommandLine = False
            DoCmd.OpenForm "frm_DETECT_IDLE_TIME", acNormal, , , acFormReadOnly, acHidden
            
        
        Case Else
            If LCase(CurrentUser) = "admin" Then
                strMSG = "This database is LOCKED!" & Chr(13) & Chr(13)
                strMSG = strMSG & "Please discontinue attempting to bypass security !"
                
                CheckCommandLine = False
                
            BOX = MsgBox("Access denied!@You do not have administrative priviledges to this database.@Please discontinue attempting to bypass security.", vbCritical, "Security alert!")
            DoCmd.Quit

            Else
                CheckCommandLine = True
            End If
    End Select

    db.Properties(strProperty) = ysnAllow

    If strMSG <> "" Then
        MsgBox strMSG, vbCritical, "MS Access Security"
    End If
     
    Exit Function


CheckError:
    Select Case Err.Number
        Case conPropertyNotFoundError
            Set prp = db.CreateProperty(strProperty, dbBoolean, ysnAllow)
            db.Properties.Append prp
            Resume Next

        Case Else
            CheckCommandLine = True
            Exit Function
    End Select
End Function

Next, either create an AutoExec macro and call the function or from your START UP form, on the OnOpen event, enter this code...

Private Sub Form_Open(Cancel As Integer)
Call CheckCommandLine
End Sub

Next create a shortcut to your database on your desktop. From the properties of this shortcut, enter the following line in the "Target"...( this is entered as a single line of text )

"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "enter_your_database_full_path_and_name_here" /cmd"ok"

From the code above, you now have 3 options to enter in the command line. To lock your database, open it with the new shortcut but change the /cmd"ok" to /cmd"lock".

This will display a message stating that your database is locked.

To test this, go to your database file and try opening with the Shift key. You will get a message asking you to stop "... attempting to bypass security".

Provide your users with the same shortcut but make sure you replace the commandline to /cmd"ok". They will be able to log in with your own security login page ( which you should also have created ).

If you need to access the database with admin priviledges, change the commandline to /cmd"grantadmin". You will see a message stating the database is now unlocked. You must shut down the database and again open with the same shortcut and cmmandline of /cmd"grantadmin".

You will now have admin access to your database.

Remember that all of your access must be done through this shortcut with the appropriate cmmandline code. Also remember that you MUST again lock your database once you have finished your work as admin. To do this, change your commandline to /cmd"lock". If you don't do this lock step, anyone can have access to your database with the Shift key.

3.
Do not create persistant links to any backend. Instead, create the links dynamically only once the user successfully logs in with your own custom login form. If you created an MDE like you are supposed to, no one can see your code to link to the tables in the backend.

4.
Add a process to detect an idle user, and force them out of the database after a few minutes of non-use.

5.
Provide a process to force your users out of your database whenever you need to change something. This can be done through a hidden form with a timer event that check a value in a backend table. If this value tells it to shut down, provide a warning to user and open another hidden form. This other hidden form's timer event will take care of closing your application after X minutes.

6.
For distribution, I force my users to copy the database on their local C drive. This prevents any other user from accessing their copy of the database and attempting unauthorized access.

7.
Save yourself some headaches and provide an automatic check to a MASTER copy of the database ( MDE file with no persistent links ), that the user's local copy can check against at login time. This provides a method for updates that does not require you to go to each individual PC. It will advise the user that a new version is available and copy this new version on their C drive. Then advise them that the copy process is complete, that the database will close, and they must again launch it. This time they will launch the new version.

8.
Since you don't want any persistent links, you must also prevent the user from closing your database by using the red X, or by right-clicking the taskbar icon and selecting "Close". This is accomplished through your main menu form. You must provide the user with a method of closing the database. Use this "Close" command button to remove the backend table links.

You will also want to track any unauthorized access through a VIOLATIONS table that can track the NETWORK ID, DATE_TIME, MACHINE_NAME, USER_NAME, and PASSWORD that anyone uses to attempt access. You can even automate a network message to yourself if this ever occurs.


With a little work, you can secure your database to a pretty good level. Nothing is ever completely hack-proof, but it will certainly keep out the rif-raf !

To date, I have not have any unauthorized access.

Happy coding !!!

 

[alfalf]

Hello All!

I see that my thread is building on, and must add something that (clearly logical) cannot be hacked in Acc 97/2000. This what nashman (good work!) talks about is simmilire solution I use, but together with this steps described bellow offers complete level of security on remote mashines. Combination of customized login, non-persistant links (use coded) to pwd protected db and steps taken below.

This steps below are common sence of it's author and adds additional security to deployed mde applications. Applying those steps bellow is a must.

Steps bellow describe simple security logics -> make user accounts & premissions for any DB (especially that one wich serves as server!) on Your PC, and then distribute it together with mde app. to others. Your system.mdw (on Yur PC) will retain passwords/prermissions, while others will not be able to hack them as they have their own system.mdw (wich is different!), and therefore cannot compromise them.

I used those steps below, and as a result I couldn't access any table in server db or mde application (wich contains same tables for my customized synchronization) on another mashine (message was that I have no premissions to access). Applications (my mde distributions) worked smooth with coded link access to server db and my customized users administration. No matter that one can determine db password. It'll have no use of it since all premissions were left on my PC! Therefore I am completely satisfyed, as I am sure that noone (I'm selling software) can do anything to compromise me, my design, their data and our business. I do sleep much much better now.

Finnaly: if You use one mdb as server database and one mde as application on network in Your distribution package, remember to protect them both with below steps.

Security without a Login - Avoiding built-in Login Dialog, in order to use Your own.
You can do this provided that you have only two security groups needed - you as owner/administrator and a single group for all users.
If you are using Access 97/2000, follow the steps outlined on this page, except for the 'Not Done Yet' section. Do not create any new groups. Instead assign the permissions you want your users to have to the built-in Users Group. When you're done, just ship the database without the workgroup file. Users will use their standard system.mdw workgroup and be restricted by the permissions you put on the Users Group. If you need to make design changes, log in using the workgroup you used to secure the database.
If you are using 2002/2003, then follow the steps outlined on this page. The wizard offers you the ability to assign permissions to the Users Group (step 24). Do not create any new groups, or assign permissions to the groups offered by the wizard.
Note that if you have any audit procedure in your database, you can't rely on the CurrentUser() function, since these users will all be 'Admin'. You can instead use the function at

http://www.mvps.org/access

to retrieve the user's network login name and use that in your audit trail


Access 97/2000 Security Step by Step

1. Go to Start, Run and type wrkgadm.exe This will open the workgroup administrator and show you the workgroup you are currently joined to by default (write down the path; you’ll need it later).
2. Click on Create and enter a Name, Organization, and Workgroup ID. Write down the exact strings you use; you’ll need these should you ever need to recreate the workgroup.
3. Choose a suitable location and name for your new workgroup file. Do not give it the same name as the standard workgroup (system.mdw). Once you click on OK, you’ll be presented with a dialog to confirm the information.
4. Once you’ve confirmed the information, the workgroup will be created and you’ll be joined by default to this new workgroup. Click Exit.
5. Open Access, canceling the opening dialog.
6. Go to Tools, Security, User and Group Accounts.
7. On the Users tab click on New to create a new user (one that will own all objects and have full permissions on your database). Enter the name and PID for this user (suggest you write down this information).
8. Add the Admins Group to this user.
9. Choose the Admin user in the dropdown list and remove them from the Admins Group.
10. Go to the Change Logon Password tab and enter a password for the Admin user. Click on OK.
11. Close Access.
12. Open Access and log in as the user you created in step 7 (the password will be blank). Cancel the opening dialog.
13. Go to Tools, Security, User and Group Accounts. Click on the Change Logon Password tab and enter a password for this user. Click on OK.

At this point, you can either use the security wizard or secure it manually. If you are using Access 97, use the wizard. If you are using version 2000, do not use the security wizard - secure it manually.

Using the Wizard:
14. Open the database you want to secure. Go to Tools, Security, User Level Security Wizard.
15. Choose all objects and click on OK.
16. Choose a suitable location and name for your secure mdb and click on Save.
17. The wizard will create the new secure mdb, and your original mdb will not be changed. You’ll get a confirmation message when it is completed. Your new user will be the owner of the secure mdb and all its objects. The Users Group will have no permission on anything.
18. When the wizard is completed, close the database window. Open your new secure mdb.
19. Go to Tools, Security, User and Group Permissions.
20. Click on the List Groups option, and then select the Users Group in the list.
21. Beside Object Type choose Database, and uncheck the Open/Run permission, and click OK.
22. Proceed to Final Steps, below.

Secure it manually:
14. Create a new database, choosing a suitable location and name.
15. Go to File, Get External Data, Import
16. Locate your database. Click on each tab and click Select All. If your database has custom menus and/or import/export specs, click on Options and ensure you include those. Click OK.
17. Go to Tools, Security, User and Group Permissions.
18. Click on the List Groups options, and then select the Users Group in the list.
19. Beside Object Type choose Database, and uncheck all permissions.
20. Choose Object Type Tables, select all items under Object Name. Click on Read Design once to remove all permissions and click on Apply. Repeat for the Object Type Queries.
21. Choose Object Type Forms, select all items under Object Name. Click on Read Design and on Open/Run to remove all permissions and click on Apply. Repeat for the Object Type Reports.
22. Choose Object Type Macros, select all items under Object Name. Click on Open/Run once to remove all permissions and click on Apply.
23. Choose Object Type Modules, select all items under Object Name. Click on Read Design once to remove all permissions and click on Apply.
24. Proceed to Final Steps, below.

Final Steps
1. Close Access.
2. Go to Start, Run and type wrkgadm.exe This will open the workgroup administrator and show that you are still joined by default to your secure mdw.
3. Click on Join and locate the original workgroup you were joined to (you wrote down the path).
4. Create a desktop shortcut that has the following as the target:
“path to msaccess.exe” “path to mdb” /wrkgrp “path to secure mdw”
This will ensure that you are joined to the standard system.mdw for all sessions of Access. When you want to use your secure database, you’ll start it using the desktop shortcut.

As a final test, open Windows Explorer, locate your secure mdb and double-click it. You should not be able to open the database at all.

Not Done Yet
So far you have ensured that only by using the secured mdw can someone open your secure database. You haven’t created any groups or users other than the one.
1. Open your database using the shortcut, and login with the username/password you created. Go to Tools, Security, User and Group Accounts.
2. Click on the Groups tab and create the groups you need. Be sure you write down the names and PIDs you enter.
3. Close the dialog.
4. Go to Tools, Security, User and Group Permissions.
5. Click on the Groups option beside List.
6. Select a group that you want to apply permissions to.
7. Go through all the objects in your database assigning the appropriate permissions to this group. Repeat for other groups you have created. Ensure that you don’t assign any permissions to the Users Group.
8. You do not need to assign permissions to individual users. Although it’s possible, it isn’t necessary and will make administration harder.
9. Go to Tools, Security, User and Group Accounts.
10. Click on the Users tab and create users.
11. Once you've created a user, you can assign them membership in one or more groups. That user will automatically inherit the permissions assigned to the group.