Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question Unanswered: linking tables via nw connections, not through drive letters?

    ref. ODBC connections, DSN and mapping to drive letters.
    - is it possible to - in access by vba code - link to tables in databases on other workstations and at the same time avoid that the users can see where the linked databases are? (ie. no drive letter assignment appearing in Explorer/My computer. I know how to hide existing drive letters , but that method is not secure.

    Example on the latter:
    - all you need is to either use the command line, or any application that does not use standard file open/file save dialogs to view the connection, or simply to try out typing the drive letter along with a colon in the file name box in the standard file open dialog, and you can access that drive if the currently logged-in windows user have access there (which access requires - write access on the containing folder).

    I am looking for the most practical way of hiding where the database tables are stored. Even if Access requires read access for the current windows user on that folder, it is sufficient that the users cannot see and do not know the name of the share where the lined tables db resides.

    My case just now is a simple workstation and serverless windows network. All PCs on that network is also connected directly to the internet, all using XP professional and Access 2002.

  2. #2
    Join Date
    Dec 2003
    Posts
    172
    Couple of thoughts:

    You can skip mapping a drive and use the server UNC name instead:
    //ServerName/FolderName/FolderName/etc

    You can use a data source name (DSN) in the ODBC/control panel applet and use any number of providers to reach the server data. You can also password-protect the connection.

    You can use any number of connections to other database data via any number of providers and you can lock down the back-end database with a password and then (via the connection string with password, data-aware controls, or password-embeded Access table links) hide where the actual data is stored.

    I think if you want to skip mapping the drive (for the linked table) just go to Network Neighborhood and then choose the database under the folder from there.

    Of course, you will still need to consider locking down the back-end database somehow with a password or the built-in Access security features. Anybody who knows how to use Windows Explorer shell program can get to the back-end database.

    Win2k, WinXP etc provide the ability to lock down the folders as well, keyed to user account settings and this will also give you additional security.

    joeg

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by JoeG
    ...use the server UNC name instead:
    //ServerName/FolderName/FolderName/etc
    You can use a data source name (DSN) in the ODBC/control panel applet

    you can lock down the back-end database with a password and then (via the connection string with password, data-aware controls, or password-embeded Access table links) hide where the actual data is stored.

    I think if you want to skip mapping the drive (for the linked table) just go to Network Neighborhood and then choose the database under the folder from there.

    Anybody who knows how to use Windows Explorer shell program can get to the back-end database.

    Win2k, WinXP etc provide the ability to lock down the folders as well, keyed to user account settings and this will also give you additional security.
    hm.ok. But if I use ODBC that applet is forcing me to assign a drive letter, it seems... or am I wrong?

    I would like to link directly using for example the UNC withing the vba code, and then of course lock down the db to protect it and the password/Ccnnection. What is the code for this?

    The target db with all the main/critical tables will be in a shared folder on a dedicated workstation using sharename$, so that it does not turn up in Network Neighborhood. I only want in addition to avoid mapping it to a drive letter - EVEN when access is in operation, connecting through vba, I do not want a new drive letter popping up in Explorer. Is this possible to avoid?

    What folder lock-down ability are you referring to in XP? I know about assigning permissions, setting folders private, but do not know how the functionality works that even blocks administrators out of private folders. Is this done by setting ownership and access permission solely to the user, using the basic security/sharing features? If so, then I know fairly enough about that.

    Serverless workgroup (not domain) network of XP pro workstations...:
    - what is the "best" way security-wise of sharing the access to the db folder?

    Using one login that all users share (used in the vba code in the locked db that exist on all users' pcs (front-end without main/critical tables), or registering all users on the "server" workstation giviing them group permissions on that folder? Then every user (5-20 users depending on which db/folder share we are talking about) will have to both have their own login to that workstation (not desireable, but perhaps necessary), and the passwords must be manuelly "synchronised" - changing it on a workstation means you need to have that person change it on the pc with the db-share as well...).

    I would really like to use a standard login for the connection, but still separate Access user logins for the database. Is this feasible/secure "enough"?

    The catch-22 - maybe impossible with real security?:

    I know for sure that there are many tools that easily break the so-called security of Access. The only think needed is to get hold of a copy of the MDB/MDE file. That is my main concern. Even if I need to share the folder with write permissions to everyone, I do not want them to see where the main db with the tables is stored.

    Since anyone who bothers can (through the internet tools) crack also the front-end, they can find out the name of the share, and the pw used...
    If I want to avoid that, by placing the db on the other machine, I cannot connect via vba and would have to set up a permanent nw connection through a drive letter...

    Unfortunately, one cannot link to a db on the network which in turn is linking to another db with a "trust" set up only between the two latter PC's. That could have resolved most of this.

  4. #4
    Join Date
    Dec 2003
    Posts
    172
    Get External Data -> Link Tables. Browse to the back end select all the tables but don't browse using a mapped drive. Use network places.
    If you have a password on the back-end, when you make the link, it will prompt you for that password. Once linked you won't have to enter a password.
    Make an MDE file to remove all the design functions. An MDE file is just the database without any of the editable code. It is much smaller and users can't get to any of the design portions in that database.
    As far as the front-end password, I would pass through the user's Windows login password. I wrote a function to get the current user name from Windows and have a table called tblUsers, which stores the user name list and has columns for various permissions.

    Again you can get more sophisticated than this. I am not a guru on security, mainly because most of my clients who use Access don't want to (or need to) invest in the time, cost and required skills to manage more sophisticated levels of Access and Windows security.

    But any more sophisticated solution can be had with some research and testing. For example, you can use ADO to bind a recordset to your forms. It makes the form linkless, it will run faster, and you can embed the password in the connection string to the back-end database. And if you use Access level security, make an MDE, or encrypt the database, you can pretty much lock it down.

    If you do create a link using network neighborhood the linked table manager will show you the UNC path. But, you can prevent people from getting into Access forever behind the database window if (in the startup properties) you turn off all the built in properties, hide the database window, and then set the bypass key to false. The bypass key allows anyone to bypass your startup form and go right to the database window! With this property disabled, nobody can get back in.

    WHICH IS WHY I EMPHASIZE MAKE A BACKUP OF YOUR FRONT AND BACK ENDS (MAKE SEVERAL BACKUPS) BEFORE DOING ANY OF THIS.

    If you have any other questions, let me know.

    Perhaps some of the more seasoned security-wise forum members might have some guidance and opinions for you in this area as well.

    Good luck.

    JoeG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •