|
**!!****!!****!!****!!****!!****!!****!!****!!****!!**
NOTE: There are hidden characters in this html
file for spacing and other purposes. Be wary about importing the code text
here directly into your VBA module.
First the bad news and some very good advice:
Be prepared for locking yourself out of a database at least once and
having to start over.
Use a non-critical database as a
test subject until your technique is flawless. Don't be satisfied with
just one success on one test database.
Back up a copy of the default workgroup file SYSTEM.MDW.
Never use it to set up security.
When you get around to using and important
database:
Back up an unsecured copy of the database in a safe
place.
Do not trust my code implicitly. There may be a typo
or somehow it could have been edited without my knowledge.
**!!****!!****!!****!!****!!****!!****!!****!!****!!**
This document contains the following:
Securing Access 2.0, 95, 97
Securing a back-end
Code to lock and unlock a secure database.
1. Securing Access 2.0,
95. 97: Return to Top
You have read up on access security in "Access [2000], [97],
[95], [20] Developers Handbook published by SYBEX. A great reference.
Be sure to read up on security first as what follows is only an outline.
BACK UP YOUR DATABASE FIRST IN A SAFE PLACE.
BACK UP A COPY OF THE DEFAULT WORKGROUP FILE SYSTEM.MDW
Be prepared for locking yourself out of a database at least once and
having to start over. I have done so. Use a non-critical database as a
test subject.
Study the outline below.
Make a copy of a "virgin" SYSTEM.MDW file (workgroup file)
and conveniently rename it. Log onto this renamed file using WRKGADM.EXE.
Open your test Access database as a member of this new workgroup. Your
user name is now "Admin." and you belong to the Admins group.
Anyone in the Admins group is "god" and has full permissions.
unless you remove permissions from the Admins group. (An unwise thing to
do unless you are very, very sure of what you are doing.)
Now use the menu to open the permissions dialog box. Note that there
are two security dialog options on the Tools\Security menu. One dialog box
is for setting permissions and ownership. The other is for managing users,
groups, and passwords.
Now for the trick. You create a new user and assign the new user to
the Admins group You will soon be that new user. You remove the default,
automatic user "Admin" from the Admins group. What! Yes, anyone
who logs on to an Access database without a password is assigned the user
name "Admin." Surprise -- out of the box, Access is secured but
the security is not active or transparent. When you log on to or create an Access
database, you do so as the user "Admin" with a blank or no
password whether you like it or not. So the first part of the trick is to remove the Admin from the Admins group. But Access insists there be one member in the Admins group.
Hence, the new user you create and put there.
Now, since you are still the Admin, give the Admin a password. The
current user can give himself a password. Now two users have full
permissions to open the database. The Admin can still do so because
"it" belongs to the "Users" group which we have not mentioned
until now to keep matters simple, and the new user you have created who
belongs the the Admins group and the Users group.
Close the test database and close access.
Reopen Access and your test database and presto, the security dialog
box appears asking for your logon name and password. Enter the logon name
of your new user. Your database should open. Return to the security dialog
box for assigning passwords and give yourself a password.
Close the dialog box, your database , and Access
Reopen Access and your test database. Enter your logon user name and
password. your database should open.
Try opening the database with the user name "Admin" and the
Admin's password.
If you can't get back in, read more and try again. Remember, you
only locked yourself out of a test, dummy database. Right?
Now for the second part of the trick. The nasty Users group. You can't
delete this group and every user automatically belongs to it. Worse, this
group has full permissions to everything in your
database by default. Let's see -- the group will always
exist and all users will always belong to it. BUT! you
can remove all the permissions is possesses. If it doesn't have any permissions, users who belong to it can't do a darn thing. We
can render it a toothless tiger in two ways.
The easiest is to use the security wizard and secure the database. This
wizard, in all Access versions up to 2002, will do several useful things.
In Access 2000 - 2002, it does more useful things. The wizard will remover the
Admin from the Admins group, but we have already done that. It will remove
all permissions from the Users group. Yay! It will make the current
user (you disguised as the new user you have created) the owner of the
database and all the objects in it. An important thing as you know from
reading the Access Developer's Handbook. Right?
The harder way is to use the information you have learned from reading
up on Access security and do all the things the wizard does yourself.
Close the database. The Admin can now open the database with its
password but can't do anything as he belongs to the toothless tiger, the
Users group. And, of course, only you will know the Admin's password. You
can open the database with your password and do anything because you
belong to the Admins group which has full permissions.
While logged onto the database as your new user, you can create new
groups and new users, assigning the users to groups as desired. I
frequently use the groups: Supers, Supports, and Readers. I assign Supers
permission to read, insert, delete, and update tables and queries. I
assign Supports a mix of these permissions, and I assign readers read only
permissions. I do not assign any users any permissions at all. Users get
their permissions by belonging to one of the groups. This strategy is easy
to manage. Read up on Users and Groups.
Note: If your database has make-table queries or code that changes the
design of forms or reports at runtime, or code that creates new forms or
reports at runtime, be sure to give the appropriate groups permissions to
modify the form or report design and\or permissions for using new tables
and queries. Read up on this topic also.
Note: If you do not give the groups you create administer permission,
members in the group cannot import database objects from this database
into a new one..
2. Securing a Back-End
Return to Top
Secure the front-end first using one of the above procedures.
Then, while logged on to your workgroup as the new user you have
created, create an empty database. Import into it the database objects in
your back-end. You become the owner of these objects and Admin loses
ownership of these objects. Use the security dialog box for permissions to
add and remove permissions for the objects in your back end. When done,
only you will have administer permissions for these objects. Other users
you previously created while securing the front-end will have a mix of
desired permissions. But probably none will have the administer
permission.
Now the front-end and the back-end belong to the same workgroup. You
can open the front-end and link to the tables in your secure back-end. The
table links in the front-end inherit the permissions assigned to the
matching tables in the back-end. To be dead certain, you can establish the
permissions for the links too that match the permissions in the back end.
However, if the link is broken and recreated,. the new link will
have full permissions. Some developers erroneously think that by securing
the front-end only and setting permissions on the links, they are securing
the back-end too, but that is not the case.
Finally, the code
Return to Top
The code is needed because you may want to cancel the shift key as a
way to open the database by circumnavigating the autoexec macro. You may
want to solidly hide the database window. You may want to include your own
menus so users cannot print anything or get to some of the other functions
on the standard toolbars. The code is dangerous because a mistake could
permanently lock-up the database. So be sure to use the code on a test
database with repeated successes before plunging in and using it on a
mission critical database.
You should put the macro and the code in both the front-end and the
back-end, if you have a back-end.
The code needs an autoexec macro to work. When you open an Access
database, the first thing that fires is the autoexec macro. However, note
that the autoexec macro in a back-end does not fire if you access the back
end using links from a front-end. You actually have to open the database
for the autoexec macro to fire.
The macro's name in the database window MUST BE "autoexec"
The action for the macro is "run code"
The function name is "OpenDb()"
You then create a new module and give it a suggestive name like "basOpenRun"
and copy the following code into it:
This code locks and unlocks the database. Most of it is based on code
found in the Microsoft VBA Help file and code found in the excellent Access
Developers Handbook published by SYBEX. Buy it!
'===========================================================
Dim mblnSpecialKeysAllowed As Boolean 'Scope of constant is
module-wide
'===========================================================
Function OpenDbADO()
On Error GoTo OpenDbADO_Err
' Note: Access 2000 on uses ADOX. This code will fail in earlier versions of
Access.
' Note See directly below for the same function that works in Access 2.0,
95, and 97.
If IsUserMemberADOX("Admins") Then
' The function TestDBProperty() is listed later on.
' It checks to see if the property is set to true or to false.
' Also note that the lines below only execute
' if the CurrentUser() is a member of the Admins group.
If Not TestDBProperty("AllowSpecialKeys") Then
If vbYes = MsgBox("Do you want to UnLock the database?",
vbYesNo + vbQuestion + vbDefaultButton2) Then
If UnLockDb() Then
MsgBox "I will close the database" & vbLf & "so you can open it unlocked."
Application.CloseCurrentDatabase
End If
End If
Else
If vbYes = MsgBox("Do you want to Lock the database?", vbYesNo + vbQuestion + vbDefaultButton2) Then
If LockDB() Then
MsgBox "I will close the database" & vbLf & "so you can open it locked."
Application.CloseCurrentDatabase
End If
End If
End If
End If
OpenDbADO_Exit:
Exit Function
OpenDbADO_Err:
' Insert your error trapping here.
Resume OpenDbADO_Exit
End Function
'===========================================================
Function OpenDbDAO()
On Error GoTo OpenDbADO_Err
' Note This function works in Access 2.0, 95, and 97.
If IsUserMemberDAO("Admins") Then
' The function TestDBProperty() is listed later on.
' It checks to see if the property is set to true or to false.
' Also note that the lines below only execute
' if the CurrentUser() is a member of the Admins group.
If Not TestDBProperty("AllowSpecialKeys") Then
If vbYes = MsgBox("Do you want to UnLock the database?", vbYesNo + vbQuestion + vbDefaultButton2) Then
If UnLockDb() Then
MsgBox "I will close the database" & vbLf & "so you can open it unlocked."
Application.CloseCurrentDatabase
End If
End If
Else
If vbYes = MsgBox("Do you want to Lock the database?", vbYesNo + vbQuestion + vbDefaultButton2) Then
If LockDB() Then
MsgBox "I will close the database" & vbLf & "so you can open it locked."
Application.CloseCurrentDatabase
End If
End If
End If
End If
OpenDbADO_Exit:
Exit Function
OpenDbADO_Err:
' Insert your error trapping here.
Resume OpenDbADO_Exit
End Function
' ================================================================
' This function works in all versions of Access
Function LockDB() As Boolean
On Error GoTo LockDB_Err
' Note: These constants simply make the code below more readable.
Const DB_Text As Long = 10
Const DB_Boolean As Long = 1
' Note: You can optionally create your own menus and toolbars to replace
the default ones
' The menu statements need to be remarked out until you create your own
menus.
' Call ChangeProperty("StartupMenuBar", DB_Text, "Your users Menu
")
' Call ChangeProperty("StartupShortcutMenuBar", DB_Text, "Your
users Shortcut
Menu Bar")
Call ChangeProperty("StartupShowDBWindow", DB_Boolean, False)
Call ChangeProperty("StartupShowStatusBar", DB_Boolean, True)
Call ChangeProperty("AllowBuiltinToolbars", DB_Boolean, False)
Call ChangeProperty("AllowFullMenus", DB_Boolean, False)
Call ChangeProperty("AllowBreakIntoCode", DB_Boolean, False)
Call ChangeProperty("AllowSpecialKeys", DB_Boolean, False)
Call ChangeProperty("AllowBypassKey", DB_Boolean, False)
Call ChangeProperty("AllowShortcutMenus", DB_Boolean, False)
Call ChangeProperty("AllowToolbarChanges", DB_Boolean, False)
LockDB = True
LockDB_Exit:
Exit Function
LockDB_Err:
' Insert your error trapping here.
Resume LockDB_Exit
End Function
' ========================================================
' This function works in all versions of Access
Function UnLockDb() As Boolean
' Note: You can optionally create your own menus and toolbars to replace
the default ones
' The menu statements need to be remarked out until you create your own
menus.
' Call ChangeProperty("StartupMenuBar", DB_Text, "Admins Menu")
' Call ChangeProperty("StartupShortcutMenuBar", DB_Text, "Admins Menu
Bar")
Call ChangeProperty("StartUpShowDBWindow", DB_Boolean, True)
Call ChangeProperty("StartupShowStatusBar", DB_Boolean, True)
Call ChangeProperty("AllowBuiltInToolbars", DB_Boolean, True)
Call ChangeProperty("AllowFullMenus", DB_Boolean, True)
Call ChangeProperty("AllowBreakIntoCode", DB_Boolean, True)
Call ChangeProperty("AllowSpecialKeys", DB_Boolean, True)
Call ChangeProperty("AllowBypassKey", DB_Boolean, True)
Call ChangeProperty("AllowToolbarChanges", DB_Boolean, True)
Call ChangeProperty("AllowShortcutMenus", DB_Boolean, True)
UnLockDb = True
UnLockDb_Exit:
Exit Function
UnLockDb_Err:
' Insert your error trapping here.
Resume UnLockDb_Exit
End Function
' ===========================================================
' This function works in all versions of Access
Function ChangeProperty(strProperty As String, varType As Variant,
varValue As Variant) As Integer
On Error GoTo ChangeProperty_Err
Dim db As Object
Dim prp As Variant
Const PropertyNotfound = 3270
Set db = CurrentDb
On Error GoTo ChangeProperty_Err
db.Properties(strProperty) = varValue
ChangeProperty = True
ChangeProperty_Exit:
On Error Resume Next
Set db = Nothing
Err = 0
On Error GoTo 0
Exit Function
ChangeProperty_Err:
' Note: If your code generates an error because the property does not yet
exist.
' the lines below create the property. A neat solution for which I am in
debt to the
' authors of the Access 2000 Developers Handbook, Getz,
Litwin, and Gilbert.
If Err = PropertyNotfound Then ' Property not found.
Set prp = db.CreateProperty(strProperty, varType, varValue)
db.Properties.Append prp
Resume Next
Else
' insert normal error trapping here
End IF
End Function
' ===================================================
' This function works in all versions of Access
' if you use the correct Dim statement for creating the database object
variable
Function TestDBProperty(strProperty As String)
On Error GoTo TestDBProperty_Err
' Note: the next Dim statement works in Access 2000
' without any reference to the DAO 3.6 library.
Dim db As Object
' Note: In pre-2000 versions of Access, you can use the statement
' Dim db as Database
Dim prpNew As Property
Dim strMsg As String
Const PropertyNotFoundError = 3270
Set db = CurrentDb()
' If the property exists and is set to True, TestDBProperty = True.
' If the property does not exist or is set to False, TestDBProperty remains False.
TestDBProperty = db.Properties(strProperty)
TestDBProperty_Exit:
On Error Resume Next
Set db = Nothing
Err = 0
On Error GoTo 0
Exit Function
TestDBProperty_Err:
If Err = PropertyNotFoundError Then '
Err equals 3270, property not found.
strMsg = "Property tested does not yet exist." & vbLf & vbLf
strMsg = strMsg & "You will be asked if you want to unlock the database." & vbLf & vbLf
strMsg = strMsg & "Click YES." & vbLf & vbLf
strMsg = strMsg & "The property will then be created." & vbLf
strMsg = strMsg & "The database will announce it is
closing." & vbLf & vbLf
strMsg = strMsg & "When you reopen the
database," & vbLf
strMsg = strMsg & "it will be set up for
locking and unlocking."
MsgBox strMsg, vbOKOnly + vbInformation
Else
' Insert you normal error trapping here.
End If
Resume TestDBProperty_Exit
End Function
' ===================================================
Function IsUserMemberADOX(pGroup As String, Optional pUser As Variant) As Boolean
' Note: This code only works in Access 2000 on
as it uses ADO and ADOX.
' See down
below for the code for prior versions of Access that use DAO
' You may need to
set a reference to MSADOX.dill
' While having a VBA
module open, click on Tools\References.
' Look in the
list for "Microsoft ADO Ext 2.5 for DDL and Security"
' Make sure it
is checked. The usual path is
' C:\Program Files\Common
Files\System\ADO\msadox.dll
' Note: You should review the Optional argument for user-defined functions
On Error GoTo IsUserMemberADOX_Err
Dim cat As ADOX.Catalog
Dim cnn as ADODB.Connection
Dim strGroup As String
Dim varUser As Variant
strGroup = pGroup
varUser = pUser
cat.ActiveConnection = cnn
Set cat = New ADOX.Catalog
If IsMissing(varUser) Then
varUser = CurrentUser()
End If
IsUserMemberADOX = (cat.Groups(strGroup).Users(varUser).Name = varUser)
IsUserMemberADOX_Exit:
On Error Resume Next
Set cat = Nothing
Set cnn = Nothing
Err = 0
On Error GoTo 0
Exit Function
IsUserMemberADOX_Err:
' Insert your error trapping here.
Resume IsUserMemberADOX_Exit
End Function
' ===================================================
Function IsUserMemberDAO(strGroup As String, Optional varUser As Variant) As Boolean
' This function works in pre-2000 versions of Access
On Error GoTo IsUserMemberDAO_Err
Dim wrk As Workspace
Dim usr As User
Dim grp As Group
Dim varGroup As Variant
Set wrk = DBEngine.Workspaces(0)
wrk.Users.Refresh
wrk.Groups.Refresh
If IsMissing(varUser) Then
varUser = CurrentUser()
End if
Set usr = wrk.Users(varUser)
Set grp = wrk.Groups(strGroup)
varGroup = usr.Groups(strGroup).Name
If Not IsEmpty(varGroup) Then
IsUserMemberDAO = True
End If
IsUserMemberDAO_Exit:
On Error Resume Next
Set wrk = Nothing
Set usr = Nothing
Set grp= Nothing
Err = 0
On Error GoTo 0
Exit Function
IsUserMemberDAO_Err:
' Insert your error trapping here
Resume IsUserMemberDAO_Exit
End Function
|
