Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FE Security - options (A97/A2k)

    I have a FE that allows users to choose the BE that they wish to access. I would like to secure the FE. I currently have a login function (form & module) that is user specific, and places them into one of three groups. This works fine. However, more knowledgeable users could open a blank DB, import the FE, recreate the FE, and potentially cause problems. Is there a way to add a DB password that is required before a user can "leave the forms" and go to the background (queries, tables, etc.)? This password would also be required before someone could IMPORT anything from another DB. And, would not be required as a part of the startup / login to the database.

    Compiling the DB (mde file) gets me part way there, but I would like more. By the way, one instance of this application has users with A97. Another 'duplicate' application has users with A2k. Identical DB, different BE data files.

    TIA for your help.

    Jerry

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: FE Security - options (A97/A2k)

    I take it you didn't use Access User Security to handle the login function. If you had, it is possible for users to be prevented from getting to tables, forms or whatever unless they have the appropriate permissions. Unfortunately, I don't believe there is any way to set a password for getting access to the database container window. That is usually done by not exposing it and turning off the ability to pull up the window at all. Of course the shift key on startup will get around that. It is possible to prevent that as well through code, and then the usual trick is to put a hidden control on the splash form or main menu that can be clicked to let the designer and/or admin people do what they need to. Unfortunately the search facility is turned off at the moment, so I can't point you to threads where this has been discussed in detail. Post back if you want assistance, and one of us should be able to help you.
    Wendell

  3. #3
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FE Security - options (A97/A2k)

    I've been reading more about User Level Security. Lots of options, even more questions. However, it seems that a database password could meet my needs. But I still have a few questions. If I secure a database without requiring users to logon:
    1. Would this prevent someone from importing tables into another (blank) database? Specifically, would using this feature allow users to run the FE (without knowing / using the DB password), but require the DB password when trying to access the BE tables (Import, etc.)?
    2. Does this setup affect only this database application, or all MS Access applications?
    3. Is the Workgroup Information File stored locally (on each user's machine) or can it be accessed via the network (same location as the application)?
    4. Is it possible for a user to read / access the Workgroup Information File and change their group?
    5. What does each user need to change / modify to use this (if anything)?

    I would like to search the archives for more on this topic. Do you about when it was previously discussed? What keywords should I look for in the titles?

    On a related note, is there a quick and easy way to dis-Connect linked tables when a user exits the DB application?

    TIA, Jerry

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: FE Security - options (A97/A2k)

    The subject is a frequent one, but unfortunately, at the moment the search facility is disabled as the Lounge has been experiencing serious performance problems. That issue is being worked on, but we will likely be in this mode for a while longer, so I'll have a go at your questions.
    1. <LI>It would prevent people from importing tables into another database - they would need to know the password, and the password prompt is associated with the back-end, so if there were no linked tables the user wouldn't get a prompt until you linked to a specific BE.
      <LI>The database password only affects the specific .MDB file that has been given the password.
      <LI>The workgroup (.MDW) file can be stored on each local PC - that's how it is setup by default - or it can be located on a server. Using that approach can cause problems if a user crashes and manages to corrupt the file however - so if you have 50 or 100 users, you probably want to put it on the local hard drive, which creates a maintenance issue as users are added or changed.
      <LI>A user cannot change the info in the .MDW file unless they have administrative priviledges
      <LI>I assue you are referring to user security here - all that needs to be done is to point to a secured .MDW file (one that has a password for the Admin account). You can do that with the Workgroup Administrator tool, or you can put the workgroup path in the shortcut that is used to start the database.
    Finally, you can disconnect the linked tables when a user exits the DB application - the most common solution is to use a hidden form and trigger the disconnect when that form closes. A couple of cautions however - doing these kind of things require that you know a fair bit about manipulating the Access Object Model in code, and problems can occur when a user crashes (the links don't get dropped in that case). Hope this helps you sort things out a bit - post back with any further questions.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: FE Security - options (A97/A2k)

    Just to let you know, there is no way to search the archives right now. The search feature of the Lounge had to be temporarily disconnected.

    Database passwords only affect the database to which the password is applied. They are not very secure since there are readily available crackers all over the web that will break the password for you in a very short time. Database passwords and Access security using workgroups are entirely different things, and I'm not sure which one you're asking about.
    Charlotte

  6. #6
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FE Security - options (A97/A2k)

    I've been reviewing previous postings, and reading a lot about security. My application does not require extreme security. Our corporate network firewall protects our IT resources "well enough" from outside problems. Our 'system' requires users to logon. I can restrict access to directories based upon this UserID. My logon form also uses this UserID to recognize who is trying to access the DB. I place users into one of three groups based upon the function they perform within the DB. I have about 50 users that have access to each DB. However, typically only about 3 users access the DB simultaneously. I have seen this as high as 6 or 7, but this is rare since several of the users are overseas.

    I think that I want to walk through (increased) security in stages. I do not feel comfortable enough with User Level Security (administration, maintenance, setup, problem solving, etc.) to implement it yet. What I would like help with is the following:
    1. What is the code to disconnect table links when a user logs off? What are the potential issues? If I understand Wendell's comments correctly, a crash will leave the tables connected (but isn't that how they are left normally?). As a side note, after a user logs on, the first thing that they do is select a BE DB and link the tables. This additional measure should enable a 'decent' level of security (mde for code, DB passwords for BE's, my logon security administration, and this).
    2. Where is the "good discussion" regarding user level security? I looked through the previous postings back to late December, and didn't see anything. So, either I missed them, or I stopped too soon. I would like to know how to implement and maintain User Level Security, since it appears to work well for others. However, I want to be certain that I can document it well enough for someone else to support it. My database is being replicated at other sites, and I am working with "system admins in training". Some have no experience with Access (or any other DB). And, the MS Access FAQ seems to overlook (or assume knowledge) some of the details.

    Any additional thoughts or suggestions are welcome. TIA.

    Jerry

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: FE Security - options (A97/A2k)

    1. <LI>I was intending to point out that you will need to write code to disconnect the tables when the database is started up, given that they may already exist. In order to do what you are planning, you need to know a fair bit about DAO - have you had any experience with that? What you are proposing is a fairly common procedure, but it depends on how general you make it - for example, will all the table names always be the same, and will they always exist in each back-end?
      <LI>I think you are making User Security much more difficult than it really is. It is based on the User and Group model, and all users can have a unique password in addition to a unique identifer that is assigned to them and is encypted. In addition, every object in the target database is given a set of permissions for each User and each Group. The administration of this is handled through two menu items under Tools / Security. That's about it - most people don't seem to have much trouble with it once they tinker with it a bit and there's no programming involved. I've had secretaries do the administration with little difficulty.
      <LI>The fact that you are dealing with replicated databases worries me considerably. Replication is far more complicated than User Security, and in fact there is a problem with having a replicated database having a password assigned to it - see MS Replication FAQs for Access97. To make matters worse, there are some significant issues with replication and user security noted in the same document. In addition, if a user is going to make any updates to a database, then they have to have full read/write priviledges - so you can't control very much with OS file permissions.
    In summary, I have an uneasy feeling that you have a disaster in the making (I've been at this kind of work for ten years, and have been called in to fix some similar situations), but I don't know enough about the problem you are trying to solve. If possible, I would suggest you post more details about your project and your level of experience with Access and databases in general - then I or others may be able to make better suggestions as to how you should proceed.
    Wendell

  8. #8
    Lounger
    Join Date
    May 2003
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FE Security - options (A97/A2k)

    I don't understand why you define this DB as being 'replicated'. What I have done is copied the DB, renamed it, and created an entirely new area of data to focus upon (new data, etc.). There is no relationship between the databases, other than the fact that they utilize the same structure, reports, etc. So, I believe that this is not a concern.

    I have programmed / adopted the SQL code for a button on the main form on my FE that allows the user to browse and select any one of the BE's for analysis / reporting. There are no problems picking or re-selecting a new DB to access. My issue arises when a user exits the DB. The links established are still intact, allowing a path to import data tables (even though the BE uses a DB password).

    The code uses a "For each tdf in dbs.TableDefs" statement to loop through all the tables, and then uses a "connect" command to relink the tables. I currently use this function to connect to two different BE's. What I would like is a "dis-connect" command, but I can't seem to find one. I considered creating a blank BE and then linking to it, but I felt that there had to be a better way.

    I understand how to make this work, but I am uncertain that I could develop the entire program on my own. I have been working with relational databases for years (Ingres, Foxpro, Access), but each has its own unique way of making things work (behind the scenes). Database development is a hobby for me. It is a tool that I use to get a task done. I would consider myself to have advanced knowledge / expertise, but not to be a programming expert. I have a need, I research it, I play with different possible solutions, and learn by doing.

    To answer your other question, yes the BE's all look the same. The must be the same to perform the same function. Only the data within the tables is different.

    I hope this explains things better. Thanks for the help. Jerry

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: FE Security - options (A97/A2k)

    >>What I would like is a "dis-connect" command, but I can't seem to find one. <<

    Try the following:
    dbs.TableDefs.Delete sSourceTable

    This will delete the link of the table defined in sSourceTable.

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: FE Security - options (A97/A2k)

    Sorry - I took your prior post literally -
    <hr>My database is being replicated at other sites, . . .<hr>
    Replication is a feature in Access that lets users work with disconnected databases and keep the contents synchronized. So your situation is not as complex as I feared.

    Your concern about the links still being established on exit isn't too big an issue. When the front-end is opened again, the user will get a prompt for the back-end password. However, you might want to check that if the front-end is open and a user does File / Import that the user gets a password prompt if they specify the existing back-end. I don't have Access97 available at the moment, so I can't test that situation.

    The TableDefs collection you are using is part of the Access object model, and is manipulated using DAO. That subject is unfortunately beyond the scope of the Lounge, but Patt has given you the command used to delete tables - you of course need to be sure to only delete connected tables. And you need to either know how to determine if a given table exists so you can delete it, or you need to create error handling code so that when you attempt to delete a non-existant table that you trap the error. If you are going to dig into this in detail, I would suggest you get your hands on a VBA book - I like the Wrox "Beginning Access 97 VBA Programming" by Smith and Sussman.

    For what it's worth, IMHO Access is in many respects one of the most complex database development environments there is. The fact that it can interface to so many different data sources, the ability to automate other applications from it, and the powerful user interface development platform make it quite unique. Obviously some of the learning curve can be climbed by tinkering, but you miss an awful lot of the power in doing so - a good book or training course will broaden your horizons considerably. Sorry about the philosophy - sometimes I get carried away. Hope this helps put things in perspective.
    Wendell

Posting Permissions

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