ULS final post

[zozew]

Hi all,

I've come to the point where Im looking at user levels and security for my DB and I've spent countless hours testing and finding the best way.

I've read many posts condemning MS for removing the ULS in 2007 and up and others praising it..

I've also tried several examples from users and most have some advantages and disadvantages...can a MVP or Access guru-overlord-darthvader type "dude" point One to a general example that has the best of "all" worlds..?

I have been testing and as a basic-med-VBA developer i just wish someone could point me out to "Thats an overall good security solution" thats not for the hardcorest of VBA developers..

So...ULS with levels and some type of DB window security think lock or hide..

I just think its such an integral part of a DB that there has to be some type of acceptable VBA Standard for it as in a type of module, type of settings etc...

If i could i would set up a post giving developers like me the ABC's of ULS...so you Guru's can point guys like me to that post and tell them to stop pestering the rest of us..

 

[Galaxiom]

If your data is in Access then it isn't secure. That is one reason why Microsoft dropped ULS. If you need security then put the data in a database server. A free version of MS SQL Server is available and the changeover is not difficult. With this you also move up from 2 GB to 10 GB of data.

 

[zozew]

If your data is in Access then it isn't secure. That is one reason why Microsoft dropped ULS. If you need security then put the data in a database server. A free version of MS SQL Server is available and the changeover is not difficult. With this you also move up from 2 GB to 10 GB of data.

Yes I came a cross that in my research....but....im at the stage where i can live with the "not" so secure status of access for the time being and migrate to a MS SQL later when ever its needed.

I was thinking of getting some summary advice/links on the GUI of access like managing users and their levels as in what they can and cant see/use.
Im thinking of it more of a way to help a user not mess up the system because he managed to somehow click away and delete the main data table in a drunken state a Friday night at the office after a sales party.

Im quite aware that anyone who really wants can get in quite easily...

thanks again

 

[speakers_86]

I think it is best to lock down access as best as possible for all users. You should provide all the features that are needed to all users. The access gui is just for developers.

If you need to duplicate ULS, it's not that hard, just more time consuming than anything. You would have a table that defines ther permissions for each user group, and then you would have to assign each user to a group. When a user tries a restricted operation, you look up their group and check to see if they are authorized for that.

 

[zozew]

Yeah sure I have seen a few examples on creating your own ULS with and without groups and so on. That part is kinda straight forward, I just wished someone with experience could advice on what not to do and what to do locking down the Access GUI as well as best practice to lock down the GUI for the users (forms etc). what methods to use and so...

 

[speakers_86]

Hide the navigation pane, create a custom ribbon with startFromScratch=true, disable default toolbars and shortcuts, disable the options button, disable shift bypass. You can also apply these steps to the backend, just use a blank ribbon instead so there is no user interface at all.

Here is what I use for a blank ribbon:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
 <commands>
       <command idMso="Help" enabled="false"/>
       <command idMso="WindowClose" enabled="false"/>
       <command idMso="WindowRestore" enabled="false"/>
       <command idMso="WindowMinimize" enabled="false"/>
       <command idMso="ApplicationOptionsDialog" enabled="false"/>
       <command idMso="FileExit" enabled="false"/>
       <command idMso="PrintDialogAccess" enabled="false" />
       <command idMso="FileSaveAs" enabled="false"/>
       <command idMso="FileNewDatabase" enabled="false"/>
       <command idMso="FileOpenDatabase" enabled="false"/>
       <command idMso="FileCloseDatabase" enabled="false"/>
   </commands>

  <ribbon startFromScratch="true">
    


  </ribbon>
</customUI>

 

[Galaxiom]

You would have a table that defines ther permissions for each user group, and then you would have to assign each user to a group. When a user tries a restricted operation, you look up their group and check to see if they are authorized for that.

Putting the permissions in a table is the Access equivalent of leaving the front door key under the mat.

 

[speakers_86]

Yeah, that's all we are doing. If you lock down the application and hide the navigation bar it is sufficient to keep the curious out. I guess if you wanted to be more obscure you could create custom database properties. That would keep out those that know enough to get in trouble, but it's not much different really.

 

[Galaxiom]

One of the most important steps is to distribute mde/accde rather than the mdb/accdb. Some protection is then available for security measures in the code.

This also stops users messing much with the code. Note however that strings are still visible as plain text in the file and can even be edited with a Hex editor so it is important to hash anything vulnerable such as passwords.

 

[zozew]

Hide the navigation pane, create a custom ribbon with startFromScratch=true, disable default toolbars and shortcuts, disable the options button, disable shift bypass. You can also apply these steps to the backend, just use a blank ribbon instead so there is no user interface at all.

Ok just to summeries a little...

Use this to create a table that lets you add custom RIBBON xml as above

Column Name    Data Type    Description
    
RibbonName    Text        Contains the name of the custom ribbon to be associated with this customization.

RibbonXML    Memo        Contains the Ribbon Extensibility XML (RibbonX) that defines the ribbon customization.

One of the most important steps is to distribute mde/accde rather than the mdb/accdb. Some protection is then available for security measures in the code.

This also stops users messing much with the code. Note however that strings are still visible as plain text in the file and can even be edited with a Hex editor so it is important to hash anything vulnerable such as passwords.

Absolutely mde/accde is a given.

Things to do:

1. Use mde/accde when finalizing your DB for distribution, (Done!Just choose it when you distribute it in the Access menu Save & Publish)
   
2. Use custom ribbons as above to limit actions mde/accde (Done! Use the above method)
3. Disable default toolbars and shortcuts, disable the options button, disable shift bypass (Done! Most of the options can be disable in the options and under current Database but for shift key look below)

How to disable SHIFT key (Taken from MS)

• Start Access.
• Create a new module, and then add the following two functions:

 Function ap_DisableShift()
    'This function disable the shift at startup. This action causes
    'the Autoexec macro and Startup properties to always be executed.

    On Error GoTo errDisableShift

    Dim db As DAO.Database
    Dim prop as DAO.Property
    Const conPropNotFound = 3270

    Set db = CurrentDb()

    'This next line disables the shift key on startup.
    db.Properties("AllowByPassKey") = False

    'The function is successful.
    Exit Function

    errDisableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, False)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If

    End Function

    Function ap_EnableShift()
    'This function enables the SHIFT key at startup. This action causes
    'the Autoexec macro and the Startup properties to be bypassed
    'if the user holds down the SHIFT key when the user opens the database.

    On Error GoTo errEnableShift

    Dim db as DAO.Database
    Dim prop as DAO.Property
    Const conPropNotFound = 3270

    Set db = CurrentDb()

    'This next line of code disables the SHIFT key on startup.
    db.Properties("AllowByPassKey") = True

    'function successful
    Exit Function

    errEnableShift:
    'The first part of this error routine creates the "AllowByPassKey
    'property if it does not exist.
    If Err = conPropNotFound Then
    Set prop = db.CreateProperty("AllowByPassKey", _
    dbBoolean, True)
    db.Properties.Append prop
    Resume Next
    Else
    MsgBox "Function 'ap_DisableShift' did not complete successfully."
    Exit Function
    End If

    End Function

• In the Visual Basic editor, click Immediate Window on the View menu.
• If you want to disable the SHIFT key, type ap_DisableShift in the Immediate window, and then press ENTER. If you want to enable the shift key, type ap_EnableShift in the Immediate window, and then press ENTER.

Please remember to make a button or something that enables the SHIFT key so you dont get locked out as it can be a bit messy to get back in if you have disabled everything

Ok thats kinda covered now for locking/hiding the GUI of access. How about a good ULS example with groups, login and password. What kind, style do you guys use? links to examples that are good?

 

[HiTechCoach]

zozew,

I've come to the point where Im looking at user levels and security for my DB and I've spent countless hours testing and finding the best way.

Hopefully that means you are just getting started and still at the table design phase. If you want any good Granular Role Based Security (ULS) then it must be part of the core design and implemented from the start.

I believe any type of ULS should be part of the core design of the application. The JET database engine used for a .mdb format database has User Level Security (ULS) built into the core and always running. Yes, you always used ULS with a .mdb. There is no way to turn it off.

It is possible to stick on a ULS at the end but it will take a lot of work and probably some redesign to make it very secure. Waiting till the end usually leave security holes. It takes lots of extra testing, redesign, testing, redesign, testing, redesign, (rinse and repeat) to bolt it on at the end. This comes from experience dong this for client's databases.

It is very difficult to give you any specific advice about ULS for your needs without knowing a lot more about your security requirements.

You may also what to check out:

LASsie (Click Here)

LASsie (Light Application Security) is a Microsoft® Access® add-on that enhances your Access application with a simplified user security system. Intended as an alternative to Access’ built-in security, LASsie offers a less complicated way to secure forms, reports, controls and certain records from unauthorized user access.

IMHO, for an ACE format (accdb/accde) front end that requires any type of ULS and/or copy protection the first step should be to upsizing the back end to MS SQL Express or higher.

Putting the permissions in a table is the Access equivalent of leaving the front door key under the mat.

I agree if the data is stored as plain text. I store my "permissions" in tables with the data encrypt.

Locking down the front end:

TIP 1: Only deploy the front end as a compiled database (.mde or .accde). Yes that means every database should be split.

TIP 2: Only lock down the mde/accde when it is ready for deployment. If you avoid locking down the mdb/accdb you won't lock yourself out.

Note: Even if you do ever lock yourself out, it is easy to unlock any database mdb/mde/accdb/accde).

 

[zozew]

Thanks HiTechCoach for a thorough explanation on the way to think. And ....yes...i am way past the table design part haha im actually almost done with the DB's core functions, form etc..so i guess i get a F in that department coming at this stage and asking for some advice on ULS. Although I have a feeling that I'm not the only one that after some developing starts to think "Wouldn't it be nice to hide/restrict the user so he doesn't mess the DB up"
So couldn't we (I mean you of course) make a sticky post somewhere where "we" explain what and how to think and what not to forget when starting a DB if the user has any intention of using it for more than your Book or CD collection...

Maybe there is but i haven't come across it anywhere..

For me in this stage i have a fairly simple DB a main table with inmate information a few related tables for keeping a history on where they have been and their status...ill try some ULS i have seen posted here and on other forums and then try to implement it and ill try to share the DB when it is done here

thanks again for all the advice...

 

[zozew]

Ok so i have gathered a few examples :

http://bytes.com/topic/access/insights/957166-how-create-user-permissions-customized-menus-ms-access

http://www.access-programmers.co.uk/forums/showthread.php?t=193131

http://www.databasedev.co.uk/login.html

None of the code is mine I just wanted to gather it in one place
The Zip contains 6 examples in different flavors, if you use any of them please give credit where credit is due

 

[HiTechCoach]

None of the code is mine I just wanted to gather it in one place
The Zip contains 6 examples in different flavors, if you use any of them please give credit where credit is due

In general you should only provide links to example code. This way if/when the code is updated you have the latest version.

Also be sure to check to see if you have permissions to re-post the code, especially along with other similar examples.

 

[speakers_86]

Here is something you might like. I will post it in the sample database eventually once I have used it some more. If you notice any errors let me know.

Use it to set all the properties to lock down your application. Ctl + Shift + K to open the form. Of course Access provides a GUI for this, but it is nice to be able to set the properties after you have disabled the options buttun.

 

[zozew]

Here is something you might like. I will post it in the sample database eventually once I have used it some more. If you notice any errors let me know.

Use it to set all the properties to lock down your application. Ctl + Shift + K to open the form. Of course Access provides a GUI for this, but it is nice to be able to set the properties after you have disabled the options buttun.

Looks absolutely great! just have to figure all it can do and how haha I don't know what many of the properties stand for or when they apply.. but i guess testing is the best way

 

[speakers_86]

All of the properties are built in. Most can be accessed from the Options > Current Database. Like I said, this form isn't necessary, I just think it is very convenient. It's very anoying to go finish up a project, make the mde, and then realize you still have shortcuts enabled.

 

[zozew]

Cant thank you enough its a great form for the properties im learning so much from it

 

[zozew]

All of the properties are built in. Most can be accessed from the Options > Current Database. Like I said, this form isn't necessary, I just think it is very convenient. It's very anoying to go finish up a project, make the mde, and then realize you still have shortcuts enabled.

I cant say again how easy and just convenient it is to have your form popping up with a shortcut ahah fan-freaking-tastic!!

But could you explain a little the Customs tab and the debug properties..?

 

[speakers_86]

It allows you to make your own properties and do with them what you will. The debug button will print all of the properties to the immediate window. To see that, enable special keys, restart access, press Alt+F11, and then I think it is Ctrl+G. If you are having issues, use the debug button to verify the property is set correctly.