Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Putting security on back end database (Access 2000)

    Hi. I have a front-end database running on all the users c drive. This has user-level security.
    The back-end however only has a database password. This is causing problems, as each time I want to make structural changes to the back end, the users have to log out.

    What is the protocol for applying user-level security to the back end? Should I use the same security file, if so how do I do that?

    Any help will be much appreciated.

    Thanks

    Amanda

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

    Re: Putting security on back end database (Access 2000)

    I think you're mixing up a couple of things. You cannot make design changes in Access 2000 or later databases unless you open the database exclusively. That has nothing to do with user security or passwords and it cannot be bypassed or turned off.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    Exactly. I don't want to keep asking people to log off the system when I want to make changes. Therefore I would like to apply user-level security.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Putting security on back end database (Access 2000)

    As Charlotte wrote, even if you apply user-level security to the backend database, you still wouldn't be able to make changes as long as other users are active in the database, so you'd still have to ask them to log off. In Access 97 and before, it was possible to change the design of database objects while other users were active, but this was a potential cause of corruption, so starting with Access 2000, you can only do so if you are the only active user.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    I think you misunderstood Charlotte.
    Even if you implement user-level security, you will not be able to make any modification to the back-end if you have not open the database exclusively.
    Francois

  6. #6
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    OK, got it. Charlotte was right.

    So is there a suggested workaround? Do I make a copy of the back end and then import the changes.

    thanks

    Amanda

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    What do you mean by "and then import the changes" ?
    Import the changes of the database structure ? You can't do this.
    Import the changes of the data ? How will you determine wich record have been changed ?
    I think you are stuck to ask your users to get out of the db, make the modifications and allow the users use the db again.
    Francois

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Putting security on back end database (Access 2000)

    See the thread starting at <post#=262704>post 262704</post#> for a way to log out users automatically.

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

    Re: Putting security on back end database (Access 2000)

    Others have pretty well laid out the situation, so let me focus on ways to do what you need to do. I also presume that all you have in your back-end database is tables and relationships. (Our Database Splitting Tutorial may be helpful if you aren't that comfortable with the subject.)

    It is true that you cannot make changes to forms, reports, macros or queries in Access 2000 and up. You can however run action queries and create new tables, and that sort of thing. However, trying to make a change to a table design while others are using the database is like playing Russian roulette. Suppose you start to change a field type from Integer to Double Precision. Meanwhile Joe opens that table open in a form, then you save the table, and he tries to make an update to that field. Not a pretty picture, and you can create dozens of other similar scenarios. So how do you deal with structure changes when you need to?

    First, you want to make sure you have a solid table structure design before you ever go live. The idea is that you want to minimize table design changes in a production environment. But of course they will almost certainly always be required. Once you've gone live, create a test backup that you can do development and testing in, and group changes so you don't have to kick users out frequently. Make your design changes and test extensively to make sure you have what you want. Then kick the users out, make the changes, test to make sure you got them right - the test database serves as a template at this point - and then relink each of the front-ends so the front-end knows about any new or changed tables, and let the users back in.

    Hope this helps give you a feel for the situation. SQL Server is a bit more forgiving if you use that as a back-end, but you still have the issue of not changing fields while the database is being used. If you want more background or some advice on tools to help manage things, please post back.
    Wendell

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    Thanks Hans,

    That is helpful

  11. #11
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    Thanks Wendell

    That is just the kind of advice I was looking for. It makes sense that you can't change the design of tables if users are writing to them.

    One of my problems was that I was trying to write modules that weren't being saved. I guess I'll just have to write them on a test copy of the database and then copy them over when I've finished them. I suppose SourceSafe, or something like that might help that procedure?

    Thanks again

    Amanda Battye

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

    Re: Putting security on back end database (Access 2000)

    We do recommend SourceSafe or some other source management system when you have three or more developers working on a project. With two working on a project, it gets a bit dicey - the overhead of the source management system just about balances out the savings in problems and two people changing the same thing. With one, unless you have a long history you want to keep, we don't normally use SourceSafe, but we do frequent backups, and deploy in small increments. And actually importing objects from a test database is usually pretty painless. Besides most of that goes on in the FrontEnd, not the BackEnd. I should add that we are now working on a product that will be deployed at several locations with minor variations at each location. I think we will probably use it in that case so we can support multiple builds of the same product.
    Wendell

  13. #13
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    Cool

    Thanks for your advice

    Amanda

  14. #14
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Putting security on back end database (Access 2000)

    Wendell

    Hi. I'm still having problems with not having user-level security on my back-end database. I need to track which users are currently logged on to the system. Their copy of the front end resides on their c drive, so I'm assuming the only way to do this is to look and see who is logged on the back end. But to do this I need user-level security on the back end, right?

    If that is the case, how do I use the same secured.mdw file for both databases.

    Many thanks

    Amanda

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

    Re: Putting security on back end database (Access 2000)

    Each users should ideally have their own copy of the System.mdw (or whatever you chose to call it (secured.mdw I guess) when you secured your database). We typically download it into the same directory that the front-end database is placed. It does cause some grief if you have to make changes to users or groups, as you need to deploy it each time - we have an automated deployment tool we use.

    The bottom line here is that a given session of Access uses one and only one security file, so if you have a front-end and a back-end, both are using the same security settings. As long as you remove all permissions from the Admin user in your database, and remove Admin from the Administrators group, and make sure Admin is not the database owner, you've done most of what needs to be done to make sure your database is secure. Does this help?
    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
  •