[AccessD] Transactions

Drew,

    It needs to be examined whether user level security, applied at table level, can handle conditional rights at record level. Let us consider the following situation, where a finer control is needed as compared to conventional permissions for table as a whole.

    Given:
        Common data table where all executive level officers in the organization (including the top boss) enter / save / edit periodical performance notes for employees under their respective chains of command. Powers bestowed upon various executives are governed by the assigned level. For example, assigned level for top boss (say president) is 1, while that for each of the vice presidents (two or more) it is 2, for senior managers it is 3 and so on. There is no limit to the number of levels that could possibly exist in an organization.

    Objectives:
        (a) Each executive can enter and save periodical notes for all employees under his direct chain of command.
        (b) Each executive can edit the notes already entered & saved by him/her, provided the given record has not yet become history.
        (c) A record becomes history when the note is marked as having been sent. Once a record becomes history, it can not be edited and can not be reset as active record (by marking as not sent).
        (d) Each executive can view the notes recorded by all those below him in his direct chain of command. For example if Vice President 1 (VP1) has Mark as  a senior manager directly under his control, he can view the notes recorded by Mark as well as by all those under Mark's direct chain of command. However VP1 can not view the notes recorded by Mary and those under her chain of command, if  Mary is a senior manager working under VP2. Of course VP1 can not view the notes recorded by VP2 (and those below him) and vice-versa (being at same level).
        (e) Though in a position to view, no senior can alter the notes recorded by his juniors. Only the person who recorded the original note, can edit it (if not yet history). As supplementary action, the senior can record his own notes as required.
        (f) Once a note pertaining to a particular employee becomes history, no fresh back dated note for this particular combination of employee & noting official can be recorded.

    In view of what you have stated, could you kindly examine whether you are in a position to suggest a solution meeting the listed objectives, based purely upon user level security as applied to the above table. In this case, all users have to be able to selectively read/write/edit the said table.

Best wishes,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: A.D.TEJPAL 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, September 06, 2007 23:25
  Subject: Re: [AccessD] Transactions


      Thanks Drew! I agree. This being a sample db, full access is available to all objects. For regular use, it will have to be converted to mde along with safeguards preventing direct user access to tables.

  Best wishes,
  A.D.Tejpal
  --------------

    ----- Original Message ----- 
    From: Drew Wutka 
    To: Access Developers discussion and problem solving 
    Sent: Thursday, September 06, 2007 20:48
    Subject: Re: [AccessD] Transactions


    A.D., I took a look at your sample.  It's nice, but the 'rules' you
    mentioned are only enforced at the interface level.  Hitting F11 let's
    you go make any change to the table that you want.  You could prevent
    that with User Level security on the tables.

    Drew

    -----Original Message-----
    From: accessd-bounces at databaseadvisors.com
    [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
    Sent: Thursday, September 06, 2007 12:03 AM
    To: Access Developers discussion and problem solving
    Cc: adtejpal at gmail.com
    Subject: Re: [AccessD] Transactions

    Arthur,

        My sample db named NotesHierarchical might be of interest to you. It
    is available at Rogers Access Library (other developers library). Link -
    http://www.rogersaccesslibrary.com/OtherLibraries.asp#Tejpal,A.D.
     
        In this db, meant for recording employee's performance notes, the
    notes table, has a Yes/No field named Sent. Once Sent is set to true,
    the note in question can no longer be edited, even by the person who
    recorded it originally. More-over, once set to True, it can no longer be
    re-set to False and no back dated note can be created.

        The sample is in Access 2K file format. Reference:  DAO 3.6
     
    Best wishes,
    A.D.Tejpal
    ---------------