What Do You
Need to Know to Use the APSM?
To use the APSM you must know
the basics of Microsoft Access Projects. This includes understanding
Access objects and how they are used. You also need to know the basics
about MSDE/SQL Server security. You do not need to know how to program in
VBA (Visual Basic for Applications), but it is helpful if you know how to
add VBA code on the OnOpen event of a form or report.
The Access Project Security
Manager consists of a set of tables, forms, reports, and a module that you
include in your application. The tables are stored in the MSDE/SQL Server
database, while the forms, reports and module are
stored within your Access Project.
Manager is a stand alone form that administrators can use to maintain user
memberships, groups, group permissions, object permissions, monitor
security system status, and print security reports. There are several tabs
on the form that are used to select the various functions. On the Users
tab, as shown below, there is a combo box that contains a list of users
for the current database. This list of users comes from the user system
table within MSDE/SQL Server. You can choose a user from MSDE/SQL
Server security, or add a new user by just typing in the name and then
assigning a group to the user. The new user must then be added to MSDE/SQL
Server Security. The user must be setup in MSDE/SQL Server security for
the APSM to work for that user. Deleting a user from the APSM is
done by simply removing all groups for a user.
Users belong to groups, and
the Groups tab provides the interface to add new groups, and delete groups
(see the following figure). In addition, a list of groups is shown and
selecting a group will show a list of users that belong to that group.
a group has been established, the administrator can set the permissions
for the group. This includes setting permissions for forms, form controls,
reports, and report controls. This
is where the APSM goes beyond MSDE and SQL Server security.
Imagine, using the same form or report for all users.
You can limit who can open the form or report.
Control what each user can do on the form, and what data fields
they have access to. Use the
same report for all users, restricting sensitive data based on who is
running the report.
forms include: No Access, Read Only, Add Only, Edit Only, Edit and Add,
Edit and Delete, Edit/Add/Delete. Permissions
for form controls vary depending on the control and include such types as:
No Access, Read Only – Enable, Read Only - Disable, Read/Write, Not
Visible, and Visible. Reports and report controls have the same types of
permissions: No Access, and Print. Even
the permissions are table driven so the above lists can be modified to
suit an application’s specific needs.
The checkbox Auto
add code to objects when checked, will add the security VBA code to
the appropriate form or report if it is not there.
There are a few lines of code needed on the form open and report
open event to check for security when they are opened. If the security function determines a user does not have
permission to open a form or report, then that object is prevented from
loading with a message displayed that the object cannot be opened due to
group permissions have been established, you can view the permissions for
reports and forms on the Object Permissions tab. Just
choose the type of object and select the object by name from the pull-down
list. The control name and
type is displayed along with the group names and permissions assigned.
security system status, listing of security tables, and various buttons to
perform other functions is available on the Status
tab of the Security Manager (see the following figure).
Pressing the Database
Security button pops up the SQL Server security dialog box. The Change
Connection button, when pressed, displays the Data Link Properties
dialog window so you can change the server, database, as well as the logon
user id. The Update Status button will refresh the tab on the form
with the system status and security tables. The Check Code button
will go through all the forms and reports that have security permissions
established and make sure that the appropriate VBA code is in place to
call the security functions when the object is opened.
These reports provide
documentation for the security system groups, users and permissions.
Sample reports are included with the Access Project Security Manager. You
can modify these reports or create your own based on your needs.