Secured database lock out 1

[Principal]

I recently secured a database and was having some problems with passwords. I removed the admin password by clearing it. I also experienced a computer crash with an hour of clearing the password. Afterwards, when I attempted to get into the database I received the message that the user does not have the necessary permisions. THe database and secured file are on a network drive. Did I create this condition by removing the admin password? If so, what is the fix? The security report indicatesd that the system made an unsecured backup. Is this database truly unsecured? WHen I tried opening it last week I got the security login prompts. As fate would have it, our tape backup is down and the last copy of the db is over two weeks old so I would like to attempt fixing this. Thanks for any insight.

 

[psemianonymous]

Try putting the password back on, then log in as someone other than Admin. I assume you have a poweruser that has full permissions. Once you have access to all your database objects, OPEN A NEW MDB and import everything into the new MDB file. This *should* reset permissions to full permissions, unless you've messed with the default permissions.


Whatever you do, be careful to save a copy of the original database AND the workgroup file you're working with. If you don't know what workgroup file you're working with, chances are it's c:\winnt\system32\system.mdw or something along those lines.


Also, before you start again on security, read the manual disguised as a FAQ:

http://support.microsoft.com/support/access/content/secfaq.asp

 

[Principal]

Thanks for the reply. Stupid question time. How do I put the password back on? THanks.

 

[RickSpr]

Not a stupid question at all. Start Access without opening any database (or you can open an unsecured database). Then, on the Tools|Security menu, select User and Group Accounts. Click the Change Logon Password tab, and enter the new password. Then exit Access and start again. You'll get the logon prompt.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[BillPower]

Hi RickSpr,

Brilliantly expalained and such a nice approach. Well deserved * .

Bill

 

[Principal]

Thanks. I'll be in at work early tommorow to get this going. I appreciate the help.

Steve

 

[Principal]

As advertised, you fix worked great. I was able to backup the database and everything is working just fine. NOw, another question. Is is possible to programically get all menus on (for admin purposes) once you have disabled them via startup. I also want to disable the SHIFT key method. In my application it isn't feasible to distribute a mde file. I need to do ad-hoc work but would like the security on as well. Thanks again,

Steve

 

[paqguy]

You can turn menus back on by using VB Code. The database property is AllowFullMenus - never used it myself, so I'm not sure of the syntax.

Also, you CAN disable the SHIFT key, however, this can only be done with Code. The property you want is AllowByPassKey and does not exist by default. If you look up AllowByPassKey in the help file, you will find a piece of code that you can copy and paste into your application. Very handy, but make sure you give yourself a backdoor - a hidden button or some other way to get around a disabled bypass key.

Hope this helps, let me know if you can't find the code or need help implementing it.

paqguy - Shaun Beane
Instructor/Developer
sbeane@verizon.net

 

[qu1ncy]

Hi Steve,

The following is a procedure created by one of our team members and works like a charm. You will need to place a transparent label somewhere on your login form and paste this into the "double click" event of the label. Or.. paste it into the same event of a label that is currently present and visible. For MS Access 9.0.

You will need to change the label name_event (lblcss_DblClick) in the error handling lines to your label name.
-----------------------------------------------------------
On Error GoTo lblcss_DblClick_ERR

'The "Database" datatype requires the Microsoft DAO 3.6 Object Library
'be included. This can be done by clicking on Tools-References in the
'menu and then finding and checking off Microsoft DAO 3.6 Object Library
Dim db As DAO.Database
Dim prp As DAO.Property

Set db = CurrentDb()
If Application.CurrentDb.Properties("AllowBypassKey&quot.Value = True Then
CurrentDb.Properties("AllowBypassKey&quot = False
' Message box can be removed in production database code
MsgBox "SHIFT key is disabled."
Else
CurrentDb.Properties("AllowBypassKey&quot = True
' Message box can be removed in production database code
MsgBox "SHIFT key is enabled."
End If

lblcss_DblClick_ERR_exit:
Exit Sub

lblcss_DblClick_ERR:
'If the property AllowBypassKey does not already exist then this
'will create it and append it to the current databases properties
'so that you will be able to disable the shift key
If Err.Number = 3270 Then '3270 = Property Does Not Exist
Set prp = db.CreateProperty("AllowBypassKey", _
dbBoolean, False)
db.Properties.Append prp
End If
Resume lblcss_DblClick_ERR_exit

------------------------------------------------------------
If you use the transparent label on your login form, place the label in the same location for all of your MS Access Login forms. Have fun.

Q

 

[RickSpr]

(Other members got there before me, but since I had worked on this for a while and it's more general and more secure, I'm posting it anyway.)
----------------------------

I activate all the security and do the startup settings as the last thing before I do the final test and deployment. I always keep my unsecured copy of the front end for future development. It makes things easier.

You can't undo the lockdown from the Access window directly. (If you could, your users could, too.) However, you can do it via VBA code if you provide some kind of hidden button or keystroke/Autokeys macro, or a macro you can execute via the /x command-line parameter.

What you have to do is delete or modify the Access-added properties of the Database object. Here's a function that will do it for you. Just call this from your hidden button, macro, etc. After it has run, close the database and open it again. It should open to the Database Window, with all menus and toolbars available.

Public Function UndoLockdown(Optional Database As DAO.Database) As Boolean
' Unlocks an application database by deleting all the startup properties, so
' that it can be opened in the Access user interface with default startup
' options. Note that any AutoExec macro will still be run, unless you hold
' down the Shift key.
'
' In order to run this function successfully, you must be logged on as a user
' who is a member of the Admins group, and you must have all permissions on
' the database. You can alter these specifications by modifying the Const
' declarations below.
'
' The function returns True if successful, False if not

    ' CurrentUser must be member of this group in the current workgroup
    Const DBAdminsGroup = "Admins"
    ' CurrentUser (or a group he's in) must have these permissions on Database
    Const ReqdPermissions = dbSecDBOpen Or dbSecDBExclusive Or dbSecDBAdmin _
        Or dbSecWriteSec Or dbSecReadSec
    Dim prps As DAO.Properties
    Dim grp As DAO.Group
    Dim doc As DAO.Document
    Dim lngPermissions As Long
    
    On Error Resume Next
    
    ' Verify user is a member of the database administrators group
    Set grp = DBEngine.Workspaces(0).Users(CurrentUser).Groups(DBAdminsGroup)
    If grp Is Nothing Then
        MsgBox "You must be an administrator of this database to use this " _
            & "function", vbExclamation, "User Not Authorized"
        Exit Function
    End If
    Set grp = Nothing
    
    ' Verify user has required permissions on the database
    If Database Is Nothing Then Set Database = CurrentDb()
    Set doc = Database.Containers("Databases").Documents("MSysDb")
    lngPermissions = doc.AllPermissions
    If (lngPermissions And ReqdPermissions) <> ReqdPermissions Then
        MsgBox &quot;You don't have permission to use this function&quot;, _
            vbExclamation, &quot;User Not Authorized&quot;
        Exit Function
    End If
    Set doc = Nothing
    
    ' Delete the startup properties
    Set prps = CurrentDb().Properties
    With prps
        .Delete &quot;StartUpShowDBWindow&quot;
        .Delete &quot;StartUpShowStatusBar&quot;
        .Delete &quot;StartUpMenuBar&quot;
        .Delete &quot;StartUpShortcutMenuBar&quot;
        .Delete &quot;AllowShortcutMenus&quot;
        .Delete &quot;AllowFullMenus&quot;
        .Delete &quot;AllowBuiltInToolbars&quot;
        .Delete &quot;AllowToolbarChanges&quot;
        .Delete &quot;AllowBreakIntoCode&quot;
        .Delete &quot;AllowSpecialKeys&quot;
        .Delete &quot;AllowBypassKey&quot;
    End With
    Set prps = Nothing
    
    UndoLockdown = True
    MsgBox &quot;Startup properties have been cleared for this database&quot;, _
        vbInformation
End Function

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Principal]

Rick:

Thanks for the help. Your code worked very nicely. My job as a high school principal does not leave me the time I would like to study and experiment with coding techniques. I have programmed with applications using SQL and some of the early Dbase and Rbase packages. While Access is indeed very powerful I have always found it to be a bear to uncover certain information. This site has been invaluable. Again, thanks for the code. It hs been put to good use.

Steve

 

[RickSpr]

On rereading, I realize I had an error in this function. The line:

    Set prps = CurrentDb().Properties

should have been:

    Set prps = Database.Properties

As originally given, the Database argument is ignored and the clearing of database properties happens in whatever database is currently open, which is probably the one which contains this code. It was meant to allow you to specify the database to be affected.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein