[AccessD] Presentation Level Security System

Fri Aug 20 21:56:02 CDT 2010

Guys,

I finally have a client specifically asking for my Presentation Level Security System (PLSS) so I am 
in active development.  I previously called PLSS my Lightweight Security System and you may remember 
other emails regarding that.

I am writing a document which discuses how I intend to implement the PLSS.  I am pasting it here to 
get comments on my ideas and have a discussion on how you might implement such a thing.  I know many 
people have "rolled your own".  Feedback is encouraged.

The idea is to have security information about form and control properties stored in tables (table 
driven) so that it can be easily setup and changed as the needs change, and reported in security 
reports.

Here is the document as it exists so far.

 >>>>>>>

Presentation Level Security System (PLSS)
Microsoft Access provides very little in the way of security for the applications that you develop. 
  I have always “rolled my own” security at the presentation level so that, for example, a specific 
form could only be opened by a specific group or groups of people.

Examples of how security might be used include:

Selectively allowing groups to open forms, and add / delete / modify records in forms.  For example 
one group might be denied the ability to even open a form.  Another two groups might be allowed to 
view the data in a form but not edit the data, add or delete records.  One specific group might be 
allowed to view, add, delete and modify the data.

In other cases you might want to control access to specific controls on a form.  Perhaps only one 
specific group is allowed to see a specific control on a form, for all other groups the control 
would be invisible.

In order to control access to data at the presentation level we need to define and implement code 
for specific objects in the database.  The objects that we will start with will be users, groups, 
forms and controls.  PLSS provides a means of specifying which groups contain which users.

Security Rules

The first question that has to be answered is where the security information is stored for a given 
application.  All code will be stored out in our mda library which can be turned into an mde if 
desired.  The problem is that the security information about the application’s forms and controls 
has to be available to the code that is trying to apply security.  Access (DAO) expects that the 
data in queries and tables be native to the application or linked from somewhere.  While we could 
place this information in tables in the mda (library), this causes immediate issues getting at the 
data, but more importantly it can cause issues with multiple applications using the same table etc. 
  If we store the data outside of the FE then we have the issue of getting at the data from the 
application.  If we link the data then it is available to be modified by a malicious user who 
manages to get at the table.

We have to put it somewhere however so we are going to start with the security information in usys 
tables in the application FE itself.  Any table with a usys prefix will not be displayed if the View 
System Objects property in the database is set to false.  Uncheck Options / View / System Objects to 
cause any such objects to not appear in the database window.  Likewise we will set the Hidden 
property to true for all of the usysPL objects.  In the database window right click the object and 
set Hidden attribute.  Then uncheck Options / View / Hidden options.  Obviously this implies that 
the user is not given access to the Tools menu in the database to prevent them from playing with 
these properties and getting to see these hidden usys objects.

The next question we have to answer is what the security rules are.  Do we allow functionality by 
default or do we deny functionality by default.  Because we intend to apply security to an existing 
working application, in general we need to begin allowing functionality.  As strange as that sounds, 
allowing functionality to one or more groups effectively denies functionality to all the other groups.

For example, by default all forms allow all users to do whatever they want.  They can open any form, 
can add, delete and modify records, and can see, enter and edit all controls.

In order to apply security to the application we would start by allowing some privilege to one or 
more groups.  Security is applied on a property by property basis, so as a form opens, the rules for 
that form are looked up.  If no security is found, then the form and control properties are left at 
whatever state they are currently in.  If specific security information is found for that form or 
control, that security is applied.

For example a form has no visible property.  We emulate that (kind of) by preventing the form from 
opening.  We have to specifically apply security in this case, and then check the security in the 
form’s OnOpen event.  If the form is not allowed to open, then a message is displayed and the form 
closes back down.

The form does have properties for AllowAdd, AllowDelete and AllowEdit.  In design view these 
properties can be set false (do not allow that action) or true (allow that action), and when the 
form opens, the form will follow the dictates of those properties.  Our security will specifically 
set the state to true (allow) for a given property for a given form / control for a specific set of 
groups.  So in order to allow a specific group to add records, you would set that AllowAdd property 
to false in design view of the form and then set security to allow specific groups to add records.

In fact, once security is set for a given property, the default for that property becomes false.  So 
whether or not you set the AllowAdd to false in design view, if you set the security system to allow 
a specific group to AllowAdd, all other groups would be denied add privileges.    On the other hand, 
if the AllowAdd property is set true by default in design view, then if no group is specifically 
given AllowAdd privilages, anyone could add records through that form.  Thus by setting the property 
to false in design view, AllowAdd privileges are denied by default to all users.

Users - usystblPLSUsers

Users are the people allowed to use an application.  There is a record in usystblPLSUsers for each 
person in the company that is allowed to use a specific application.  Having a record in the table 
does not allow or deny that person any specific rights in the application, it simply tells the 
system that the person exists.

Groups – usystblPLSGroups

Groups are the method of organizing people such that that entire group of people can do something. 
Groups have names, but they do not inherently have any privileges in the application.  A group might 
be a department such as “Accounting” or a title such as “supervisor” or manager, or even a 
functionality such as “car owner”.  Each company decides what their groups should be called based on 
how they need to group people to allow or deny access to data.

At some point, groups will be used to allow or deny access to some property of some form(s) or 
control(s).  Rules such as “the accounting group can open frmAccounting switchboard” will be created 
using a wizard in the PLS system.

UsersInGroups

Users are placed into and removed from groups using a wizard in the PLSS.  Belonging to a group is 
the only way to specifically set that user to have some specific right in PLSS.

Forms

It is important to remember that simply having a user in PLSS or having the user assigned to a group 
in PLSS does not give that user or that group privileges.

Forms have several properties used to control security for the form.  Whether the property is set 
False or True in design view, that setting applies to all users until PLSS specifically sets one or 
more groups to have that privilege, at which point all other groups no longer have that privilege.

Privileges are assigned to specific form or control objects using a PLSS wizard.  Once a group is 
given a specific right using that wizard, then and only then does that group have the right to 
perform that action, and even then only to the form or control objects initialized using the PLSS. 
Once any groups are given specific rights, all other groups not specifically assigned those rights 
are denied those rights.

Visible (can open)

There is no specific Visible property for a form, i.e. if a form can be opened, it is visible.  Thus 
by definition all forms can be opened by anyone until they are specifically allowed to be opened by 
one or more specific groups, at which time that form can no longer be opened by anyone other than 
the groups given that privilege.

AllowAdd

Set True, allows records to be added to the dataset (for bound forms).  For unbound, would just be a 
form property used by the unbound framework to allow / deny adding data.