User Permissions

[mjdemaris]

I would like some advice on creating a permissions table or tables.

Currently, I have a table like this:
UserID, Username, DepartmentID, Review, Approve.

So a user can create a request for materials. Then, another user, based on department and Review rights will review the request for invalid data. Then, a third user, based on department and Approve rights, will approve or deny the request. Lastly, another user will view each request, add a PO# and order the materials.

I use a Navigation form so everyone can see the current requests, based on what department they are in, and one form for creating the request, clicking Reviewed or Approved and entering Ordering info.

I came to the point where I need to add the permission for certain users to Order, and realized that I would have to add like 20 more records - one for each user authorized to order for each department.

I feel like there might be a better solution, but .

Thanks!

 

[GinaWhipp]

Perhaps you could post a Data Model of what you have so we can see *where you are*?

 

[The_Doc_Man]

Sounds to me like you are not missing permissions - you are missing a formalized workflow. This means you are still in a design stage and might not be asking the right questions yet.

 

[GohDiamond]

You could use a Shared ID, but that requires: Trust.

But I think Doc_man is on point. You're still thinking this through and there's more than one solution.

Just saying
Cheers!
Goh

 

[mjdemaris]

You are both correct, that I am still thinking this through. In Excel, we have a sheet with the Department names, and user names in three columns: Review, Approve, and Order - so this determines who can do what.

I am not sure how I am going to implement this in Access.

If I put in a request for a screwdriver, then I should not be able to review, approve, or order.
User2 reviews, User3 approves and User4 orders. Except for certain departments. In the Facilities Dept, for example, User3 can create the request, review and approve it. And User4 will order.

I used User3 as a manager in this example - hence the permissions.

I am thinking that three tables may be the better option: Review, Approve and Order.

 

[mjdemaris]

Gina: I don't have a model, really. I could draw one up, but conceptualization has not been a strong point in my brain. I really have to work to model work/activity flow and use case stuff.

 

[GinaWhipp]

The Model IS the hardest part, until you can wrap your head around that everything else becomes mute. So, perhaps you could post what tables you *think* you need and let us take it from there...

 

[mjdemaris]

Well, Gina, I am going to take you up on that.

Attached is a png of the Relationships window. There are three tables on the right that I just created as an alternate to one table: tAuthorizations.

As for the PO table, it is not in use yet, but I think I am just going to use it to store PO numbers. Corporate takes care of the PO stuff other than us assigning the given PO number to an order.

Questions? Fire away!

 

[GinaWhipp]

Hmm,..

1. What's up with all the 1's after the Table names, i.e. tblUser1?
2. You've used some Reserved Words but we'll fix that...
http://allenbrowne.com/AppIssueBadWord.html
http://www.utteraccess.com/forum/Access-Reserved-Words-t539419.html
3. What were you planning to do with tAuthorizations?
4. Also you do not need 3 tables tAuth... just one with Authorization Type but we'll fix that also.
5. Are Items and Parts the same thing? If so we need to *coordinate* those names to avoid confusion down the road.

Once I have those answers I can mock up an example for you.

 

[mjdemaris]

As far as the 1's: I did that in case I changed the tables, so I could have a 2,3, etc.

I did not even see the reserved words...
The tAuthorizations table was serving as a way to provide permissions to various users when opening a form, so if I am in department 1, I cannot say that my request is good, and approve it and order it. It has to go through a hierarchy, up the chain so to speak.

The other three tables are as I mentioned in the post. I just created them this afternoon, planning on replacing the tAuthorizations table.

Yes, Items and Parts are the same thing.

Thank you and good night!

 

[GinaWhipp]

Okay, I'll mock up something tomorrow... got busy tonight.

 

[GinaWhipp]

Okay, have a look at the attached, should give you a better idea...

Note, *Code* and *User* are reserved.

 

[mjdemaris]

Gina:
So, looking at the mock up, if I want to say:
User1 can Review Dept1 requests
User2 can Approve Dept2 requests
User3 can Order Dept1 and Dept2 requests

How would this look?
If I see this right, I would have to list the Departments in the tblAuthorizations with the AuthorizationType Review, Approve or Order.

Then, in the tblUserAuthorizations, I would need to list each user in conjunction with each Authorization in tblAuthorizations.

Would it be simpler to have a table, instead of tblUserAuthorizations and tblAuthorizations:

tblUserAuthorizations and tblAuthorizationTypes, where:
tblUserAuthorizations ---
uaID
UserID
Department (ID)?
AuthorizationTypeID

Thoughts?

 

[GinaWhipp]

Users *permissions* would be set up by their Authorization Type. However, I see I for about the Department but that can be added with a tblDepartmentAuthorizations. So, a User assigned to a Department should inherit the permission of said Department and then the tblUserAuthorizations will assign the Users permissions.

However, will also need to add tblUserDepartments between tblUsers and tblDepartments because Users will be assigned permissions at the Department Level.

 

[GinaWhipp]

Forgot to add... you are going to need some VBA to do this maybe a Select Case statement but it's going to get a wee bit complicated as you want different levels of permission per User per Department.

 

[jdraw]

Further to Gina's comments, I recommend you have permissions assigned to Roles and People associated with Roles. I'm thinking it would be more maintainable when changing the permissions for "Jim" when he moves from "Operations" to "Management" etc. I think there would be fewer permission/people combinations.

Just my $.02

 

[GinaWhipp]

I concur with jdraw... Roles would be better and easier to maintain AND easier to code.

 

[mjdemaris]

Hmm. Can you expand on Roles for me?

Let's say Jim is the Maintenance manager. Tom, works for Jim. Tom creates a Request for tools. Don, who is in the Technical Service Dept, has to review the Request. Once Reviewed, then Jim has to Approve it. Once Approved, Doug, in the General Office Department, has to order the materials.

Tom - Maintenance - no permissions
Don - TSD - Reviews
Jim - Maintenance - Approves
Doug - General - Orders
And so on with other departments

There are two departments where the Manager reviews and approves their own departmental requests.

Finally: there are only two people with the authority to order these materials, one is a backup for the primary. (In reality, some managers go ahead and order things they need without going through the program!)

The whole purpose of this program is to create a paper trail for materials that come in. So when the freight guy, me, receives the stuff, I can find out who they go to and notify them via auto email. And the paper trail goes to the Finance people for processing with Corporate.

Well, I guess I just added all that extra for more clarity or someone's reading enjoyment.

 

[GinaWhipp]

Hmm, this is getting complicated maybe we have to leave Roles out of the picture and just assign Departments and Authorizations, though it sounds like the Role of Manager would give one approval authority, is that correct?

 

[jdraw]

The extra details are the things that let you build a model.
In fact I think you are dealing with 2 models;
-a data model showing the things involved and how they are related
-a process model to show the workflow/steps involved

By Role, I am suggesting as concept to consider:
- Read
- Edit(read, update/change)
- Create(new records, Edit)
- Delete (mark record as deleted--if this applies) Based on your requirement for permissions, I would expect you do not physically delete any records. You simply mark them as logically deleted by means of a boolean that your related processing logic must respect.

These roles have to be associated/assigned to each of the "things of importance in your database" such as forms/reports... assuming all interaction is via Forms.

If this level of control is required, you may also be looking to Transaction Processing and Audit Log.
You know what is required much better than readers.

Then you assign freight guys to Read role.

You will probably have some code in Form and/or report that will check the Role of the particular user against some list of roles/permissions you have assigned to that Form or Report.

For some users, some forms may not show all controls/options. They may be set to Not Visible or Not Enabled for certain Roles. Some aspect of a Role may be customized within a Form -your approver --may only be permitted to see and click a button/check a box to "approve", but not be allowed to modify other controls on that form. This is where your knowledge of the processes and the business fits.

From time to time posters discuss permissions and access control. You might find some sample code by searching with google or Bing, or the search options within various forums. Here's a recent post that may be useful. And the sample it references.

I'm sure there are forum members who may have some examples.

These are thoughts/ideas for consideration as you design your approach.
Good luck.

OOoops: I see Gina has responded. I agree with her that role based access control may be overkill. You know the big picture better than any
posters, so the complexity vs requirements is really your call.

Good luck with whatever approach you decide upon.