Solved User Permission Control (1 Viewer)

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
Any help will be appreciated.

Hello, I have a database where I want to create user permission. I have User Control 3 tables tblUsers, tblUserRoles, tblUserActivity.
I have 36 forms, And 47 report.
Here Before we used 2 database (separately). 1) Employee Management 2) Payroll.
Now we add one Database.

Full Database create FORM name is LIKE that First 5 characters
* Admin can use - frmYourName & rptYourReportName & LfrmYourName & pfrmYourName & pRptYourRoprtName & pLfrmYourName
* SuperVisor can use only - frmYourName & rptYourReportName & LfrmYourName
* PayRoll can use only - pfrmYourName & pRptYourRoprtName & pLfrmYourName
* Guest can View - frmYourName & rptYourReportName & LfrmYourName & pfrmYourName & pRptYourRoprtName & pLfrmYourName

tblUserRoles:
UserRolesAddEditDeleteViewChangePassGoToUtilityBoard
Admin
Yes​
Yes​
Yes​
Yes​
Yes​
Yes​
Guest
No​
No​
No​
Yes​
Yes​
No​
PayRoll
Yes​
Yes​
Yes​
Yes​
Yes​
No​
SuperVisor
Yes​
Yes​
Yes​
Yes​
Yes​
No​
NOTE : UserName create administrator then user can choice Password when he first times open form.

Any idea how can I control this user permission. Advanced Thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:14
Joined
May 7, 2009
Messages
19,175
your table users must add field that defines each user's role.
you need a Login screen to collect these information.
enable/disable the appropriate menu (ribbon or switchboard).

we have similar implementation.
the only difference is i am using ribbon and enabling/disabling
the ribbon button.

see this demo.

username: arnel
password: puzon

this will give you Admin users and all buttons are enabled.
see "User Manager" on the ribbon.

username: common
password: common

some buttons (esp admin) are disabled.
 

Attachments

  • TR2Materials.accdb
    5.1 MB · Views: 151

Isaac

Lifelong Learner
Local time
Today, 03:14
Joined
Mar 14, 2017
Messages
8,738
Any time you can, skip the internal username/password stuff and just go with the user's logged-in network username, which is easily determined in VBA. Let your I.T. dept do what it does best - be responsible for all those network logins, and the legitimacy and currency of them all. Rather than reinventing the wheel with your own internal usernames.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:14
Joined
May 7, 2009
Messages
19,175
Rather than reinventing the wheel with your own internal usernames
I think it is best to have user table.
You dont want just anyone in the network to have access to your db, right?
It is meant for a particular, specific users only.
If anyone want to have access, they need to ask.
Your db involve personal info as well as financial.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:14
Joined
Feb 28, 2001
Messages
27,001
I was with the U.S. Navy for over 25 years as a contractor. Part of that time, I was a security-related DB admin as a secondary duty. We used the local domain's security and used API calls to determine the user's domain ID. That became the primary key for the lookup of the user table that held user role. Permissions and other special actions or limits were child tables of the user table.

Nobody EVER saw anything but forms and reports. The Opening Form's Form_Open routine looked up the user info from the network and determined whether to allow the user in at all. (Form_Open supports the Cancel operator.) If the user was going to be allowed in, then the rest of that form stored user role, permissions, and other special data in a public area that every other form could see and immediately test. Every form that needed to test something did so in the individual Form_Open routines if the object was to restrict the form, or they enabled and disabled controls in the Form_Load routine if something was going to be selective. And we populated a local table that listed the projects to which that user had admin rights.

Net result? We had pretty decent security that kept users from EASILY tapping into someone else's projects, and the truly administrative forms were not available to folks who were not truly administrators.

As to overall DB access, we put the BE and the master copy of the FE (that got downloaded to the user's preferred area) in a shared folder. That folder had a "DENY ALL" for EVERYONE - but that was the last entry in the Access Control List. We had a special group identifier that was used only for the authorized users of the DB. A second group ID was for the DB administrators - me and two others. The DB User identifier granted MODIFY to the shared folder and files. The DB Admin identifier granted FULL CONTROL and also owned the folder. I.E. at the highest level we used group privileges so that all we needed to do was make a person a member of the group - or NOT a member of the group - as needed. The Navy liked it like that, and you can imagine how picky the U.S. Navy might be for protecting data about their servers.
 

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
your table users must add field that defines each user's role.
you need a Login screen to collect these information.
enable/disable the appropriate menu (ribbon or switchboard).

this will give you Admin users and all buttons are enabled.
see "User Manager" on the ribbon.
Thank you so much for your reply.
I apologize for the late reply. Because here is our holiday.

It's a nice example. appreciated!
How can I control Ribbon? can you provide me a sample of one or two buttons that can possibly edit?
 

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
I think it is best to have user table.
You dont want just anyone in the network to have access to your db, right?
It is meant for a particular, specific users only.
If anyone want to have access, they need to ask.
Your db involve personal info as well as financial.
Please review
Here I uploaded a sample.
EDIT:
I want to manage the users system...
 

Attachments

  • UserSecure V.1.accdb
    1 MB · Views: 129

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:14
Joined
May 7, 2009
Messages
19,175
i will use Switchboard on this demo.
login Tazul (admin), admin menu will appear.

login Islam (not admin), User menu will appear.
 

Attachments

  • UserSecure V.1.accdb
    1.9 MB · Views: 132

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
i will use Switchboard on this demo.
login Tazul (admin), admin menu will appear.

login Islam (not admin), User menu will appear.
Its really appreciated the work.
It is run well. Sorry to say I don't understand how to work it and how I edit the next work. I tried Micro to converts it VBA. But it's very hard for me to read. some of the criteria I don't get why use there.

can you please as the previous dB for manage User, I create a table the same as that.
Please visit frmDashboard. need your re-touches ONE or TWO Button into any two users.

And I also try to understand your sample dB code. but it's very hard for me to read, But there I have learned about the ribbon.
Thank you so much,
 

Attachments

  • UserSecure V.1.accdb
    960 KB · Views: 122

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
Hello, @arnelgp have times to possibility to re-touches... Your previous sample I can't edit,
I want to checkbox to easily and quick manage the users.
 

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
I have attached "User Role".
Capture.PNG

How can I display it my Dashboard.
display for "user Name" I used, TempVars!UserName and When I used TempVars!URID
its nothing display . I also try Dlookup function , Nothing result.
How can I solve this.
 

vhung

Member
Local time
Today, 03:14
Joined
Jul 8, 2020
Messages
235
Quite difficult;
If some ways do…

1. Create a Form for user registration where you can edit user details
2. Log-In Form
On “Form” a combo select for username is been created,
The User ID number is undetermined to call for user name,
Rather type a name or select on combo the registered user name,
Then enter a password if matches, it view the specified form

3. Then next the restriction Form for users base on your user role table
The permitted access of a user could be determined
On the main form could have, {Button’s on enable/disable mode (buttons: Forms, Reports, tables, query)}

4. Could be your main Form connects to your Log-In and Restriction Table
Maybe your log-in and main Form has same table source if possible
So that on your main Form the User Role/ details be seen if necessary

noted: I already made this concept before... Only the design differs
 
Last edited:

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
First I all of the Dashboard filed create Disable.
then I tried to use in the Dashboard Dlookup Like :
Code:
Private Sub Form_Current()

Dim db As DAO.Database
Dim rs As DAO.Recordset


Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("tblUsers", dbOpenSnapshot, dbReadOnly)

If rs!UserRoleID = DLookup("UserRoleID", "tblUsers", "UserRoleID = 1") Then
    Me.cmdFile.Enabled = True
    Me.cmdAdministrator.Enabled = True
    Me.cmdSuperVisor.Enabled = True
    Me.cmdchnPassword.Enabled = True
End If

If rs!UserRoleID = DLookup("UserRoleID", "tblUsers", "UserRoleID = 2") Then
    Me.cmdFile.Enabled = True
    Me.cmdSuperVisor.Enabled = True
    Me.cmdchnPassword.Enabled = True
    
End If
If rs!UserRoleID = DLookup("UserRoleID", "tblUsers", "UserRoleID = 3") Then
    Me.cmdFile.Enabled = True
    Me.cmdPayRoll.Enabled = True
    
End If
End Sub

Here also not Disable as I called the fields.
Only work in the First criteria
Code:
If rs!UserRoleID = DLookup("UserRoleID", "tblUsers", "UserRoleID = 1") Then
    Me.cmdFile.Enabled = True
    Me.cmdAdministrator.Enabled = True
    Me.cmdSuperVisor.Enabled = True
    Me.cmdchnPassword.Enabled = True
End If
Others Criteria is not work.
Have any Idea why it is not working !
 

bastanu

AWF VIP
Local time
Today, 03:14
Joined
Apr 13, 2010
Messages
1,401
Here you go, please review the attached file.
Cheers,
 

Attachments

  • UserSecure V.1_Vlad.accdb
    952 KB · Views: 133

smtazulislam

Member
Local time
Today, 13:14
Joined
Mar 27, 2020
Messages
806
Kindly can you answer me one more question ?
it is just my other idea to know.

If any user did not log on to the form frmLogin then any form doesn't open.
How and where I put criteira ?
 

bastanu

AWF VIP
Local time
Today, 03:14
Joined
Apr 13, 2010
Messages
1,401
Not sure I understand your question, it looks like you handle missing login or password. Maybe this is what you want (prevent all forms from opening if login was not activated)?
 

Attachments

  • UserSecure V.2_Vlad.accdb
    1,020 KB · Views: 136

Users who are viewing this thread

Top Bottom