Restricting Access to certain parts of a Database (1 Viewer)

[Boi]

Good afternoon,

How should I go about restricting access to my database as a whole and then awarding permission to view certain parts to a select group? :banghead:


Regards,

Boi

 

[isladogs]

To restrict access to your whole database, user a login form with user name & password
There are numerous examples on this forum and elsewhere online

For limiting access to selected items within your database, add a permissions table to include UserID & UserLevel (integer or text field)
Standard users assigned to level 1 (or User)
Selected users to level 2 (admin)
You could add other levels as required e.g. level 3 (Developer)

For items with restricted access, add code to the Open or Load event similar to this:

Private Sub Form_Load()

If DLookup("UserLevel","tblPermissions","UserID" = '" & GetCurrentUser & "'") <1 Then
  MsgBox "Sorry - you do not have permission to use this form"
  DoCmd.Close

NOTE: you need a function similar to GetCurrentUser in a standard module e.g.

Function GetCurrentUser()

GetCurrentUser=Environ("UserName")

End Function

HTH

 

[The_Doc_Man]

Colin's suggest is spot-on. I will only add this note:

If you have a wide-open internal network, you need a login form to capture the user's identity. HOWEVER, if your network is a well-managed domain that "locks down" some user abilities via a Group Policy template or other similar means, it is possible to get going by simply trusting the user to have logged in via the domain (Active Directory, in that case). Then the "GetCurrentUser()" function that Colin described will give you the user's domain name without you needing a special separate login form.

Colin would probably point out that a malicious and clever user would be ably to somehow circumvent this process, but if you know your user base well enough, you can trust them for the most part and just keep on improving your security as you go. Or you can know that you CAN'T trust them and work on bolstering your initial security and worry about other features as you proceed.

Once you know WHO the user is, you can look up what that user can do from whatever you use as a "Permissions" table. Or you might take this as a different approach - as a user "Roles" table. Then "Clerk" might have limited access, "Supervisor" might have more access, and "Manager" might have still more. That type of decision is up to you and is limited only by your imagination.

Crucial to this concept is that your users must NEVER see the internal structure of the DB. They must always see some kind of dispatcher or switchboard form where they NEVER see the Access navigation panel or the ribbon. The only way they should see a form is by clicking an appropriate button on the dispatcher form that would be the Opening Form for your database.

That means you have to do some reading on "Securing an Access Database" - which you can find here by using the SEARCH function of this forum. Look in the thin blue ribbon near the top of the page. On the left-most option you see "User CP" but what you want is SEARCH, which is 3rd from the right end of that ribbon.

There are many ways to do this once you have identified the user and that user's roles or permissions. The two most likely cases are

(a) User either CAN use a form or CANNOT. If so, then the "switchboard" form can test the user's ability when they click a button to launch a form. If the user is in the CANNOT category, you issue a Pop-Up message box that says "Not Allowed" or whatever else you want to say - and then just don't launch the form.

(b) User can use the form to either BROWSE data or UPDATE data. Here, you need to do some things in the form's OnLoad event to customize its abilities by perhaps disabling some of the controls or locking them or something when you have a restricted user.

There is also the ability to modify the form by setting the form's .AllowEdit to FALSE if you don't want people to use the form to edit data. Look at the various "Allow" flags that are properties of the form. You can make your security decisions in the OnLoad event, which fires BEFORE the user actually sees the form, so that way you get to control what that user does.