Segregation of duties

[hassan.khalifeh10@outlook]

Kindly, What are the required steps to specify access for different users over a certain program and DB access mode?

 

[GaP42]

Perhaps first you might Google how to set up user access control in MS Access.
Investigate the many resources provided to see if that is what you need.
There are some samples on this site.

 

[theDBguy]

Hi. Welcome to AWF!

That's a very broad question. Consider providing more details about your situation.

 

[The_Doc_Man]

I believe you need to search this forum for topics including the phrase "role-based security". This question is also tied into "securing a database".

The overview - VERY high-altitude overview - is that ...

• You must set up forms to do everything for you, including a dispatcher or switchboard form that can launch other forms.
• You need a table of users and their roles (or their abilities) and a reasonably secure method of login. The structure required for this will highly depend on what you intend for each role to do (or not do), so I can't give you a structure to consider.
• Every form should be aware of the user roles being present and significant in deciding what to do (or not do).
• There can NEVER be a time when an ordinary user (specifically, not the developer) can see any structural elements like tables, queries, or form and report design view.
• NO DIRECT DATA ENTRY OR MODIFICATION TO TABLES!
• You have to do "evasion" things like hiding the ribbon and blocking off some of the shortcuts that would get you inside the architecture.

This is NOT a trivial process and you should expect it to take a while to implement. Many of us have published short articles on these subjects, but our member isladogs has several extensive articles on the subject. MajP has also offered some guidelines. I have written a few posts on these subjects myself, but the last time I dealt with this in detail was over 8 years ago, and I have retired since then so I'm a little bit rusty on the details.

One detail I am certain is still a requirement is that you MUST perform a detailed "requirements analysis" because otherwise you will end up having to go back and fix things A LOT. We call that "retrofitting" and it is the easiest way I know to break things you though were fixed already. Usually, retrofitting means you didn't get it right the first time.

There is a software engineering rule that says "If you didn't have enough time to do it right, how will you EVER find enough time to fix it?" My dad was a do-it-yourself carpenter and my father-in-law was a carpentry contractor. They both said that the rule for building ANYTHING had to be "Measure twice, cut once." If you plow into the implementation without having a good design document as a reference, ... it's like going on a trip without a road map. How will you ever know you have arrived if you weren't sure where you were going?

 

[Pat Hartman]

Here is a link to a sample with very simple security.

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk

Keep in mind that Access is by its nature insecure so this will help to guide honest users at best.

 

[hassan.khalifeh10@outlook]

Hi. Welcome to AWF!

That's a very broad question. Consider providing more details about your situation.

I need to specify access over a program in access (Reports, Forms, level of access in a specific form such as allow to edit or add or just view)

 

[hassan.khalifeh10@outlook]

I believe you need to search this forum for topics including the phrase "role-based security". This question is also tied into "securing a database".

The overview - VERY high-altitude overview - is that ...

• You must set up forms to do everything for you, including a dispatcher or switchboard form that can launch other forms.
• You need a table of users and their roles (or their abilities) and a reasonably secure method of login. The structure required for this will highly depend on what you intend for each role to do (or not do), so I can't give you a structure to consider.
• Every form should be aware of the user roles being present and significant in deciding what to do (or not do).
• There can NEVER be a time when an ordinary user (specifically, not the developer) can see any structural elements like tables, queries, or form and report design view.
• NO DIRECT DATA ENTRY OR MODIFICATION TO TABLES!
• You have to do "evasion" things like hiding the ribbon and blocking off some of the shortcuts that would get you inside the architecture.

This is NOT a trivial process and you should expect it to take a while to implement. Many of us have published short articles on these subjects, but our member isladogs has several extensive articles on the subject. MajP has also offered some guidelines. I have written a few posts on these subjects myself, but the last time I dealt with this in detail was over 8 years ago, and I have retired since then so I'm a little bit rusty on the details.

One detail I am certain is still a requirement is that you MUST perform a detailed "requirements analysis" because otherwise you will end up having to go back and fix things A LOT. We call that "retrofitting" and it is the easiest way I know to break things you though were fixed already. Usually, retrofitting means you didn't get it right the first time.

There is a software engineering rule that says "If you didn't have enough time to do it right, how will you EVER find enough time to fix it?" My dad was a do-it-yourself carpenter and my father-in-law was a carpentry contractor. They both said that the rule for building ANYTHING had to be "Measure twice, cut once." If you plow into the implementation without having a good design document as a reference, ... it's like going on a trip without a road map. How will you ever know you have arrived if you weren't sure where you were going?

thanks, I want to give access over some reports, & Forms. where a user can edit or export, while other user should not.
the question is (where can I give create Users? where can I give access over the form or the report? can I give access to show a command? or can I give access to run a command? can I give access Rules over group Of users or just for the user?
if there is a course I have to see to make it easy to understand, kindly share it to me?

 

[CJ_London]

Suggest before you go any further, respond to the link provided by Pat in post#5

 

[gemma-the-husky]

I need to specify access over a program in access (Reports, Forms, level of access in a specific form such as allow to edit or add or just view)

Even that distinction may be difficult. Setting a form to "allowed edits = false" may prevent you changing values in combo boxes for instance, and I'm sure that's not what you want.

 

[Pat Hartman]

Even that distinction may be difficult. Setting a form to "allowed edits = false" may prevent you changing values in combo boxes for instance, and I'm sure that's not what you want.

There are other ways that allow you to lock/unlock specific controls if you insist. Since I rely on the BeforeUpdate event to enforce security, I almost never actually lock forms. When I do, I use the tag property of the form to identify the controls that have a fixed property of locked or unlocked. For example, controls used for searching are always set to NOLOCK. Controls like the PK are always set to LOCK. Then you call the code passing in the form object and a lock or unlock command.

This procedure goes into a standard module so it can be used by ALL forms. Do NOT put it in the class module of a specific form.

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working ---- try adding acObjectFrame
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

 

[hassan.khalifeh10@outlook]

There are other ways that allow you to lock/unlock specific controls if you insist. Since I rely on the BeforeUpdate event to enforce security, I almost never actually lock forms. When I do, I use the tag property of the form to identify the controls that have a fixed property of locked or unlocked. For example, controls used for searching are always set to NOLOCK. Controls like the PK are always set to LOCK. Then you call the code passing in the form object and a lock or unlock command.

This procedure goes into a standard module so it can be used by ALL forms. Do NOT put it in the class module of a specific form.

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working ---- try adding acObjectFrame
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Thanks, but where to specify the access for a specific user?

 

[CJ_London]

I'm going to drop off this thread since it seems to me the OP wants someone to do it for them

 

[Pat Hartman]

Thanks, but where to specify the access for a specific user?

Did you look at my example? Did you see the table where users are assigned values for view, add, change, delete? Did you look at the code in the four relevant events (Current, BeforeUpdate, BeforeInsert, BeforeDeleteConfirm) where the user's security level value is compared to the security level value assigned to the form?

This is the simplest model you will find. If you want something more complex, there are other options out there. Some are not free. If you are only interested in a model that maps users to objects, make sure that it supports groups or you are creating a maintenance nightmare for yourself.

 

[The_Doc_Man]

Thanks, but where to specify the access for a specific user?

The problem with that question is that we have answered but you don't seem to recognize that.

You need a user table that lists the people who are to be your valid users, meaning that they are allowed to use at least some parts of the app. Your user login names must be known so that this lookup can succeed. Whether you have a secure domain-level login or you need something more localized will depend on the intended scope and environment of your app. We can't know that. You have to answer that for yourself.

The next step depends on whether you have a rule of one role per person or whether you can have many roles per person. If it is one role per person, your user table would include a field for the assigned role. If you have many roles per person, you would probably need a child table listing the person's roles. But that "scope of activities" is yours to decide, not ours.

Then you have to decide what kinds of access permissions you will need. For example, some forms might be totally off limits. Others might allow partial use for some folks, full use for others. Some might limit which company subdivision data can be used by employees from another division, i.e. not only structural limitations but also data isolation by department or corporate group. Every nuance of your "specify the access" question depends on what you actually mean by that question. We cannot answer that. You and ONLY YOU know that answer.

For different levels of access, the method of "how to restrict usage" cannot be answered until you enumerate your intended limits. Some things must be restricted by disallowing the form to open. Other things might require that each individual form must implement something in code to impose restrictions on specific actions or specific classes of data.

Until you have formally written down the detailed answers to the questions we are asking, we can't help you much. We are good at doing this, but only when we know the project's specific goals. It is your project, so the goals must be yours, not ours. And ... warning ... without specific goals, you will be going nowhere. This might sound somewhat harsh, but it is not intended that way. It is merely that sometimes the truth is harsh. And the truth is that without a solid plan formulated BEFORE you try to implement anything, you are doomed to failure. Particularly for something that is big enough and complex enough to require this kind of security. You are looking at a months-long project in all likelihood.

You asked about a course. I don't know that I have seen such a thing. However, others on this forum might be able to point you to various articles or videos. Search the web for "Securing an Access database" or "Role-based security in an Access database." See if any videos pop up.

 

[gemma-the-husky]

The easy way is just to allow certain users/user groups to open a form or not.

The hard way, needing painstaking coding, is to allow users to do different things on the same form.

Pat just showed a way to use the "tag" property, but then you have do decide all the special actions to want to allow/restrict, and then for every form, consider which controls you want to manage in this way. It's very laborious, and as soon as you do it, some users will request equally complicated amendments.

 

[Pat Hartman]

Pat just showed a way to use the "tag" property, but then you have do decide all the special actions to want to allow/restrict, and then for every form, consider which controls you want to manage in this way. It's very laborious, and as soon as you do it, some users will request equally complicated amendments.

We haven't heard from @hassan.khalifeh10@outlook in a week. He seems to have lost interest.

I'm not sure I would use that locking code as a way to manage individual users. It would require other tables to map field lists to forms to create groups and then those form groups to users. If you have to control access by column, I would not use ACE as the BE, I would use SQL Server.

So unless the OP comes back, consider this thread abandoned and don't waste your time.