Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Join Date
    Apr 2008
    Posts
    18

    Unanswered: Suggestions for securing a database.

    Okay, so I've finally completed my first database and I want to share it with my co-workers. Now, I had previously created a "test" database that I had enabled security on before attempting to share with my co-workers and it ended in chaos. I had put a password on the database, encrypted it and also created users and user groups.

    Problem was, when I moved the database to one of my co-workers computers, I couldn't access it, even after inputting the main database password and the user's password. I think that, now, in retrospect, it probably had to do with my not replacing the workgroup file on the user's computers with the one that I created on my computer (you know, the new "Security" workgroup file that is created when you enable user level security).

    But, I'm not sure and really don't want to have to start from square one again, like I had to that time.

    Now, the main problem I encountered with that test database is that, although I did not select the option of making my new workgroup file the default workgroup file, it still became the default. I know that Access creates a shortcut on the desktop that is supposed to open the secured database using the new workgroup file that you created for said database.

    Now, my issue is, what if I want to share this database that has to be accessed through this specific shortcut? As far as the book that I used to get most of my Access guidance from (Microsoft Office Access 2003 All-In-One Desk Reference for Dummies) says, the shortcut will contain the workgroup file needed to open the database.

    So, do I have to move the shortcut, along with the database and the workgroup file, to the new users' computers. I'm guessing moving the shortcut is kind of asinine, but, I have no idea of how to create another shortcut that will do all of the things that the original did.

    Thus far, I have been to get my database to work on one of my co-workers computers by moving the database (both front end and back end) to a shared folder on our network, then moving the front end and workgroup files to the user's computer and re-linking the tables. Wa La. Working database.

    But, I feel I NEED some form of security on the database, just to sleep good at night.

    Tell me. Am I asking for too much?

    P.S. I also had an issue with the Admin user whenever I moved the secured database to a user's computer. Because they are the admin of their computer, it seemed as if that null and voided all security measures I put in place.

    As you can tell, I'm kind of lost here, so, any help would be great. Thank you.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    naïve question: what is the threat that you want to secure against?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Right click on the shortcut and choose properties. Take a look at the 'Target' field to see what it is doing.

    Copy (don't move) your FE, BE, shorcut and workgroup file to a network drive.

    Change the target of the shortcut on the network to use the network db and workgroup files.

    Copy the shortcut from the network to another user's PC and test it.


    A few notes:
    When opening an mdb file directly (not through a shortcut), Access uses the system workgroup file and logs the user in as admin. If you don't want users to be able to open the mdb file directly (bypassing security), remove all rights from the amin user (and remove the admin user from any groups) in the new workgroup file when you set up the other users. Do not do this in the system.mdw file.

    Depending on your situation, you may want to distribute your FE to each user. If you don't need to do this, users can copy the shortcut from the network to the desktop.

    Before playing with security, make sure to back up your mdb and your system.mdw file. A hosed system.mdw file can be quite unpleasant.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Rogue has made some great comments but...
    Quote Originally Posted by izyrider
    naïve question: what is the threat that you want to secure against?
    Not naive (how do you do the dotty i thing???//) - awesome question!

    I like to flatter myself as an above average Access user but Access security scares the bejesus out of me. Access (or perhaps JET) is just, IME and IMHO, not made for implementing serious security. The fact that there are entire books on the subject is proof enough for me.

    To expand on Izy's question- is this for a regululatory requirement, to discourage the "inquisitive", to repel the malevolent etc?

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    I believe that pkstormy uploaded a great security portion to add to any database. It worked form the one that I did at the last job. I actually am putting in with all of the ones that I set up.

    It's better than Access ULS, which is what you used. The only thing that I'd add to it, is the 'diable shift bypass'. But if your users don't know much about access, it won't matter.

    The one that I had from pkstormy actually pulls the OS username from the computer and checks to see if they have permission to log into the DB. If they don't, it tells them and then automatically shuts the DB down.

    **Edit**
    Unless you're just looking at prying eyes, then I'd do the above, just put it on a network drive and then disable "everyone" from it and only add in those who you want to access it.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Just to follow up on Grafixx01 and the security type mdb I posted as an example, it's designed so that developers can click the close button (providing their security is high enough in the tables), and let's them simply close everything while others can't do that. It locks users from gaining access to a lot of the upper main items and really gives the developer full control on how she/he wants to make the flow of the program work. If you get a chance, it's worth a look: http://www.dbforums.com/showpost.php...9&postcount=68
    Read the items in the post and see if that's the kind of stuff you want to do. But give it a try. It will automatically register your user loginname when you first use it with full permissions but there are some dummy user names with limited control (ie. seeing buttons, buttons disabled, etc..) Nice thing is that you can change user names so you can then see what they would see when they log on.

    I might still impliment the mdw security which gives you more indepth control over read/write permissions to actual tables for certain users. Once you've established an mdw file relating to the mdb you're working on, it's a matter of adding users to the mdw file, setting their permission levels, and then always making sure you open the mdb using /wrkgrp switch (ex: "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" /wrkgrp "C:\Windows\System\Secure.mdw" ) Making sure the mdw file is accessable to the user.
    Last edited by pkstormy; 06-29-08 at 01:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Not one mention of MDEs? MDEs with Shift Bypass Override is a good place to start.

    However, I am largely with IZY here. You need to specify exactly what scenarios you are trying to secure against before getting specific and clear advice.

    Just make sure you back up your database fully and your system.mdw file BEFORE you start messing with user-level security.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not fond of the Shift Bypass override routine. I don't like having to exit a database and get back in to start coding. I'd rather push a button and be right there ready to write code without exiting the mdb and having to jump back in once you've disabled the shift bypass override. I bounce in and out of mdb's quite frequently and this just doesn't seem efficient to do that. Just an extra couple of steps to finally get to the coding and maybe do a quick fix or there.

    Now if I wanted to protect my code from other coders (and not the local joe's that use the program), I might consider using the shift bypass protection method as my impression is really that other developers is who it's designed to protect from (mde's keep the local users from messing in the code.) Most of the local joes that use an mde/mdb don't really know the shift trick. If they do, I have a few tricks where I still track who it is getting into the mdb, what time and date they were messing around with it, and I can usually spot anything they might have done to it.

    I also clone the mdb so even if they do get into it, they're getting into their copy which will get regenerated the next time they run the script. They can play all they want but when they launch the script to clone and open the mdb next, their code is vamished, gone, nowheres land and my source mdb I used for cloning is still untouched.
    Last edited by pkstormy; 06-29-08 at 03:02.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't like having to exit a database and get back in to start coding.
    Who has to do that? I only set the override bypass once the MDE is made
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    But if you need to get into the mdb and make some quick coding changes, don't you need to first open the mdb, enter the bypass code to allow using the shift key, exit, and then re-open the mdb again using the shift key?

    I think I get what you're saying though in that you wait until making the mde before disabling the shift key but if you're jumping into an mdb several times a day making a new mde 2-3 times a day, you would need to disable it every time unless I've misunderstood. I'm constantly tweaking my mdb's and recreating new mde's throughout the day (I'll recreate a new mde even I'm just fixing a small alignment problem.) I may have 5-6 different mdb's I jump in and out of throughout the day, working on one mdb for an hour or so, make an mde and copy to the server, then work on another mdb, etc...
    Last edited by pkstormy; 06-29-08 at 17:35.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    one the i do is HIDE the the object if they can't see it they can't delete it

    also when you Shift open I place the hand over the keyboad and use my little finger and press the shift key user them don't know what other key has been press.
    Ill seen a lot of user look at me funny as when they doulbe it run right but I run it we see the database window

    also and other thing I do is in the Menu bar put =1 there that hide the
    file edit .... menu
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  12. #12
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I caught some of that myle but I think a good post in the code bank on an mdb with some of your tricks would be worth a thousand words.

    It might even be nice to collobarate the different methods and give users one thread where they could download 1,2, or 3 different techniques to see which one works best for them.

    I always hide the tables, make a fairly secure mde file, and control where the user wants to go, not letting them get to behind the scenes or other places they shouldn't be unless they have the right permissions. Yet I still want that 1 admin button on the form which when clicked, checks to see if the user has admin security and if so, immediately closes all objects and lets the true coder do their thing. Without being in the table with admin rights, the button is useless for others. Here's the code if you want to see it: http://www.dbforums.com/showpost.php...9&postcount=68. Again, it's worth a look. It can be expanded upon but it let's the developer's "control" over how his application is used by others that is the key point here.
    Last edited by pkstormy; 06-29-08 at 20:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pkstormy
    But if you need to get into the mdb and make some quick coding changes, don't you need to first open the mdb, enter the bypass code to allow using the shift key, exit, and then re-open the mdb again using the shift key?

    I think I get what you're saying though in that you wait until making the mde before disabling the shift key but if you're jumping into an mdb several times a day making a new mde 2-3 times a day, you would need to disable it every time unless I've misunderstood. I'm constantly tweaking my mdb's and recreating new mde's throughout the day (I'll recreate a new mde even I'm just fixing a small alignment problem.) I may have 5-6 different mdb's I jump in and out of throughout the day, working on one mdb for an hour or so, make an mde and copy to the server, then work on another mdb, etc...
    I think you have misunderstood.

    I open the MDB, work on it. Make the MDE, open the MDE and disable shift (using a secret interface command) and then copy to server.... and repeat. All too easy imo.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Jun 2008
    Posts
    163
    I'm following this thread because while it isn't my concern at the moment, I know that eventually, it will most definitely be.

    If I understand correctly, as of post #6, a decent security measure is one that allows or disallows users access to the DB according to which windows user login they possess. Is there an alternative with this same level of security? (I saw Graffix's #33 on codebank)

    I agree that the code bank would be even more helpful if the submissions could be grouped. There are lots of awesome and really great references there and chances are, people looking for those things might easily overlook it. =D
    Last edited by coffeecat; 06-29-08 at 23:56.

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thing is that there are all kinds of security.

    * Security levels for users to control what they can do.
    * Security levels for users to control where they can go.
    * Security against users making design changes accidentally.
    * Security against malicious users deliberately making design changes.
    * Security against malicious users deliberately making damaging data changes.
    * Security against theft of intellectual property.

    And there's probably more....

    Each might require a different approach.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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