Solved User Permission Control

[smtazulislam]

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:

UserRoles	Add	Edit	Delete	View	ChangePass	GoToUtilityBoard
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]

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.

 

[Isaac]

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]

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]

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]

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]

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...

 

[smtazulislam]

any suggest

 

[arnelgp]

i will use Switchboard on this demo.
login Tazul (admin), admin menu will appear.

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

 

[smtazulislam]

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,

 

[smtazulislam]

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]

I have attached "User Role".

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.

 

[Gasman]

Crossposted today https://www.accessforums.net/showthread.php?t=83909

 

[vhung]

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

 

[smtazulislam]

Maybe your log-in and main Form has same table source if possible

tbluser JOIN with LEFT JOIN tblUserRole
can possible or Not ?

 

[smtazulislam]

First I all of the Dashboard filed create Disable.
then I tried to use in the Dashboard Dlookup Like :

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

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]

Here you go, please review the attached file.
Cheers,

 

[smtazulislam]

Ma-Sha-Allah, Great Work ! I really appreciated it.

 

[smtazulislam]

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]

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)?