Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Apr 2007
    Posts
    42

    Unanswered: ADP & user level security

    I am in the middle of converting from Microsoft Access 2003 to 2007, and will be converting to a SQL Server back-end (currently Jet db) later this year. Could someone please advise me on the following:

    (1) Does the 2007 ADP/ADE support user level security?

    (2) If yes, will the windows authentication from SQL Server enforce user level security (i.e. read-only users can only read the info on the screens, while update users can update the info on the screens)?

    (3) Am I better to remain in the MDB/MDE format or convert to the ACCDB/ACCDE format? My users are willing to temporarily go without read-only abilities until I convert to SQL Server, but I need to make sure it'll be there in the ADP project.

    Thanks for whatever assistance you can provide me.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Be careful with ADP and evaluate before converting your current application. You'll lose several functionalities of an Access database (queries for instance) and you could not gain as much as you hope towards SQL Server: depending on the version of Access and the version of SQL Server, some "native SQL features" of an ADP project will not work. In your case (Access 2007) you can use these features with SQL Server 2005, but not with SQL Server 2008. Moreover, there is nothing you can do with an ADP that you cannot do with a MDB, functionally.

    As far as security is concerned, it is (and must be) handled by the SQL Server. SQL Server does not care about any security features you implement in your application, everything must be defined at the server level.

    About the ACCDB/ACCDE formats, you'll lose the compatibility with the previous versions of Access. Try to evaluate (again) what you'll gain in the change.

    Do not try to switch to Access 2007 AND to the Access FE/SQL Server BE model at the same time: it would be very difficult to determine whether a problem is caused by the change of version or by the new FE/BE model. Personnally, I'd change my application to work with SQL Server first (remaining in a well known Access 2003 environment while learning how to interact with SQL Server), then migrate to Access 2007, but I'm not in your shoes.

    Three changes at the same time: from Access 2003 to Access 2007, From MDB to ADP, and from Access Jet to SQL Server is the recipe of a possible disaster.
    Have a nice day!

  3. #3
    Join Date
    Apr 2007
    Posts
    42
    Thanks for the response. I'm already into the conversion to 2007, but don't know whether to stay in the MDB format or move forward to the ACCDB format.

    The MDB format enforces user-level security, which I'm using; and the ACCDB format does not. Does ADP enforce user-level security, although maintained at the SQL Server level? I didn't see the icon in 2007 for users & permissions (like I do when I'm in a 2007 MDB format database).

    Don't I have to convert to an ADP when I convert to SQL Server? I've never heard of an ADP front-end with a Jet database back-end? Seems like those 2 have to be done at the same time.

    My big question, though, is whether or not SQL Server and an ADP will allow me to specify read-only for some users and update for others, and will that get enforced in my ADP screens?

    Thanks,

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It depends on what kind of security you're talking about. As far as accessing and manipulating data is concerned, everything has to be defined at the SQL Server level. At the application level, the only security that you can effectively design concerns the right to open a form, to print a report, etc. At the server level:

    1. You must explicitly create Users that are allowed to connect to the server (you can define users at the SQL Server level or work with users and groups defined in the Active directory of the domain, the choice depends on how the server is configured).

    2. Then you have to assign specific permissions for each users (or groups if you use A.D.) you defined in (1), at least at the database level and possibly for each object (table, view, stored procedures, etc.). Usually you use Schemas and Roles to define specific permission scenarios, then make a user member of one (sometimes more than one) Schema and/or Role. The security model can be very complex and if you're highly concerned about security questions in SQL Server 2005 or 2008, you should try to find a book specifically dedicated to this topic.

    Nothing prevents you from using an Access MDB type with SQL Server. You can go from a very simple schema where the application is connected to the back-end through attached (aka linked) tables, to a more complex (and more secure) model where almost everything is done with stored procedures. The main advantage of ADPs is that you can (theorically) modify SQL Server objects in Access. However, this is only true when you work with a specific version of Access connected to a specific version of SQL Server (e.g., you cannot do that with Access 2003 connected to a SQL Server 2005).

    If your application tries to use a server object without permission (as defined here above), it will generate an error that you'll need to manage in Access. Your Access application will not inherit (as in "be aware of") the security features defined at the server level, except through errors returned to it.
    Have a nice day!

  5. #5
    Join Date
    Apr 2007
    Posts
    42
    Thanks for your reply. I understand what you're saying about setting up user groups and permissions on the SQL Server level. Each user group will have access to the tables for read-only or update, depending how I set it up.

    But then in my ADP, will a user with read-only access to the tables used by that form default to read-only access on the form? And likewise a user with update access to the tables used by that form, default to update access on the form?

    Also, does anything prevent me from using an Access ADDCB type with SQL Server?

    Thanks so much for your help. I'm at a critical decision making point, and trying to understand all that I can.

    Thanks, Terri

  6. #6
    Join Date
    Apr 2007
    Posts
    42
    Oops, I meant to ask if anything prevents me from using an Access ACCDB type with SQL Server - fingers typing too fast ...

  7. #7
    Join Date
    Apr 2007
    Posts
    42
    To all,
    My bottom line concern is whether or not I have to re-code my navigation within my forms. Presently, I am using a listbox and bookmark logic to identify which record is viewed on the form. When I try to change the form to read-only via code, this logic no longer works.
    With 2003 MDB, the form was read-only or updatable based upon which user group the user was in. I setup security enforcing who could update and who could not. Now, with no user-level security in the 2007 ACCDB, I am concerned how it will work in an ADP project.
    Any comments would be helpful. Thanks so much everyone.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Personally I would abandon the ADP project and stick with an accdb/mdb (and create a compiled accde/mde). Unless there's a specific reason (which I've never found) for creating an ADP, your development (and maintaining) time is doubled/tripled.

    I usually control permissions via this example: http://www.dbforums.com/6332819-post68.html (or http://www.dbforums.com/6274790-post20.html) Both examples show how to return the user's loginID securely and the 1st example shows how to setup a permission level system (which is easy to do with only a couple lines of coding.)

    Which works very well. Keep in mind that an ADP is very time-consuming to develop (again, about 3 times longer) than an accdb/mdb will be. There really is no significant gain to creating an ADP/ADE (I was returning over 5 million records using an mdb and the unbound forms technique). After creating several ADP's, I abandoned creating them and use accdb/mdb and simply link SQL Server tables into the mdb. That way I can take advantage of queries/reports without having to hard-code everything to return data (which is a big plus over designing views/stored procedures for data returns.)

    I would also (personally) avoid using MSAccess security. Again, using the example posted in the link above, I meet all security requirements (ie. HIPAA) that our company requires and any other IT audit requriements (I occassionally audit other IT departments on their security).

    Although security is something that should not be taken lightly, there's no reason to change the development platform (ie. ADP) to implement security properly. Too often I see a developer think they 'must' create an ADP for security purpose only.

    If you look at some of the other examples in the code bank, you'll notice there's one which will automatically create/refresh the linked tables so you don't have to worry about creating an ODBC for every user: http://www.dbforums.com/6274793-post22.html. This little piece of coding automatically creates/refreshes the ODBC DSN when the user opens the accdb/mdb.
    Last edited by pkstormy; 04-05-10 at 22:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Note also - regardless if you use a security type table as the example shows in the previous post, you can open a form in read-only mode for users. You do this in code behind the button to open the form.

    ie.
    Private Sub cmdOpenMyFormName_Click()
    Docmd.openform "MyFormName"
    Forms!MyFormName.AllowEdits = false (make form read-only after opening it)
    end sub

    or
    Docmd.openform "MyFormName"
    if isadmin(getuser()) = false then <- function in the example which returns security using the getuser() routine.
    Forms!MyFormName.AllowEdits = false <- after the form is opened, allowedits property is set to false if no permissions.
    else
    Forms!MyFormName.AllowEdits = true <- otherwise set allowedits to true.
    end if


    Note also:

    You can also do things such as:
    docmd.openform "MyFormName"
    Forms!MyFormName.navigationbuttons = false
    Forms!MyFormName.scrollbars = false
    Forms!MyFormName.AllowAdditions = false

    etc...etc..

    (you can basically set any form property after the docmd.openform "MyFormName" - the key is to set the form's properties in the code after the docmd.openform....)

    If you're intent on designing an ADP, then (since all ADP forms are unbound), you basically control permissions to write the data to the table in your code behind the buttons to save the data on the forms in your ADP (or you can control permissions to the table in SQL Server which complicates things if users need to update that table in some situations). You can just as easily make your accdb/mdb forms unbound (which basically gives you the same thing as an ADP (including fast data read/write speed to/from the form) except with an ADP you lose the functionality of using queries in the accdb/mdb and instead must design views/stored procedures.) Again, plan on 2-3 times the development time for an ADP.

    I personally like controlling permissions to writing to the tables at the accdb/mdb form level versus on SQL Server. I don't let users get behind the scenes to the tables directly on my accdb/mdb projects. If SQL Server security is a must, consider designing a 'reporting' only table similar to a datawarehouse scenario.
    Last edited by pkstormy; 04-05-10 at 22:38.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Apr 2007
    Posts
    42

    Thanks

    Thanks so much ... let me dig into all that to understand what you're saying.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Isn't ADP now deprecated?
    if so thats another good reason not to develop an ADP project in my books, y dopn't know when Microsoft will drop ADp support from future versions. It'd be a shame to develop the project in ADP and then find it was no longer supported.
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Apr 2007
    Posts
    42
    I found a Microsoft article from June 2006 that says ADPs will continue to be supported in Office Access 2007, but who knows if they have changed since then.

    When I started this SQL conversion a year ago (stopped because it was such a large project), I tried have a MDB front-end with another back-end (don't remember if I had it as an ADP or MDB) linked to SQL Server, but had a lot of problems doing that. I couldn't ever get it to work. Is there something written up that explains how to use a MDB or ACCDB with SQL Server?

    Seems like that is the general advice - to not use the ADP anymore. Which then leaves me to decide whether to stay MDB or ACCDB. We need user level security (i.e. read-only users and update users by form or report), regardless of how it gets enforced.

    thanks,

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ADP was deprecated some 2 or three years ago, what that means in practice is that it will be supported for a few more years, whether that few more years is 2 or 3, 10 or 20 or forever is anybodies guess. its still in 2007, it may be in the next version it may not.

    if you are serious about developing for a server backend then do yourself a favour get a good book on design. sadly I don't think Sybex have done an Access Developers Handbook for 2007, to 2003/XP version has a complete book covering those aspects. if you can't find a 2007 version then Id strongly suggest you buy the 2003 version and adapt it to 2007. its not cheap, but quality rarely is.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Apr 2007
    Posts
    42
    I'll look into that book. My problem is that I can't find a book that talks about both SQL Server AND Microsoft Access, and how to make them work together. I have a couple of books on SQL Server (the Sybex one may be better), but my problem (before) was getting my forms and reports to do what I had them doing before with SQL Server as the back end. I'm realizing that a big part of that problem was ADP, so maybe it'll go smoother later this year.
    Thanks !

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you can still find it, because it's an old book, or if you can find a more recent edition, I strongly recommend the "Hitchhickers Guide to Visual Basicv & SQL Server" written by William R. Vaughn (in the edition I have: Microsoft Press 1997, ISBN 1-57231-567-9). More than 75&#37; of its contents is still accurate today and everything is very clearly explained in details.

    It's not specific to Acces but about the interactions between a VB (here VBA) application and a SQL Server. It gives the general idea on how to have such an application work with a SQL Server but also details the communication processes that occurs. Many obscure or "hidden" features of such interactions are revealed, up to the DLL level.

    More Access-oriented, the "Access Developers Handbook" (Sybex) is indeed a true goldmine.
    Have a nice day!

Posting Permissions

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