How To Create User Permissions and Customized Menus in MS Access

[twinnyfo]

Seldom does a Database Designer build a Database for themselves and themselves alone. It’s usually built around the requirements of a Customer. Many times, the Database will have multiple Users, and these various Users may (or may not) perform different functions within the Database.

MS Access offers a bit of security with passwords that can encrypt the Database (both the Front and/or Back End) to limit who can open a Database. However, we are still faced with the issue that once a User has access to the Database, they have access, since the password encryption unlocks the entire Database, not just certain parts.

One of the options for managing multiple users is to have a Switchboard or menu of activities that can be performed within the Database, and the Users will only select those items that apply to them, based on their responsibilitie s in the work center. However, the primary challenge with such an option is that you open yourself to a user “exploring” places of the Database they shouldn’t be exploring. In most situations, this is not really a problem, because the average employee doesn’t want to sabotage their place of employment. However, when it comes to personal, confidential or even medical information, it behooves the Database Administrator to build into their Database certain safeguards to ensure that restricted information is not fallen upon casually or incidentally.

Another solution would be to create separate front ends for each type of User. When one User requires access to a particular set of Forms and Reports, those objects could be loaded into one Front End, linked to the Back End. Another User requires a different set of Forms and Reports, so the Database Administrator builds another Front End that suits. However, the obvious challenge with this solution is the constant expansion of different FEs. This can also cause a drastic duplication of effort, as some different Users (or different types of Users) may require the use of the same Objects. Then, every time one of these Objects is changed, all FEs containing that Object must also be updated.

I find myself in a situation like that in my work center. We only have 10 employees who use the Database on a regular basis, with sometimes 10 more who have a very limited access for short periods of time. However, those 20 employees have vastly different roles in the Database. Additionally, since nearly everything we touch is Privacy Act Information (I work for the Federal Government), we want to control access as much as possible.

Over time, I have developed a method for limiting access to the Database, based on the User. The method for doing this is rather simple:

1. Create a Users Table
2. Establish Public Constants and Variables
3. Determine who has accessed the Database
4. Determine what to do with that User
5. Allow User-specific access to the Database

Creating a Users Table

Your Table may have different Fields, but my Table (tblUsers) has certain fields based on my requirements. Feel free to make your own list of requirements and build the Table appropriately. A description of my Table follows:

[B][U]Field Name      Data Type[/U][/B]
UserKey         Long (AutoNumber)
UserID          Text
UserName        Text
UserRights      Integer
UserMachine     Text
UserDirectory   Text
UserSigBlock    Memo

The UserID is the value returned from the OS environment that identifies who is currently logged onto the machine. All our computers require Smart Card Access, so security is double-authentication throughout. Only people who are verified users can access the Database.

The UserRights will be explained below, but this is a value indicating what level of access (or areas of access) the User will have. This comes into play in the final steps.

Since our IT Department often requests a list of our Users’ machine names, I have included that in my list, and this, too, is easily obtainable from the OS environment.

Occasionally, our Users will download reports or spreadsheets from the Database, and having a User-established Directory helps to make sure their items are always in one place.

Our office frequently sends out e-mail messages from the Database, so I have also included a User-customizable E-Mail signature block. This allows the Database to quickly add a signature block to outgoing E-Mails. This is very convenient.

Now that your Users Table is created, we need to figure out how to use it!

Establishing Public Constants and Variables

I’ve created a Module that holds my lists of Public Variables and Public Functions, called modSystem. Here is a list of what I use to limit User Access:

Option Compare Database
Option Explicit

'User Rights Constants
Public Const UserAdmin As Integer = 1
Public Const UserOIC As Integer = 2
Public Const UserPromo As Integer = 3
Public Const UserRecords As Integer = 4
Public Const UserSrRecorder As Integer = 5
Public Const UserRecorder As Integer = 6

'User Variables
Public glngCurrentUser As Long
Public gstrUserID As String
Public gstrCurrentUser As String
Public gintUserRights As Integer
Public gstrUserDirectory As String
Public gstrUserMachine As String

I have established constants for the various types of Users because it is easier to remember the Type of User than it is to remember the value of their User Rights. Additionally, if I use these Global Constants throughout the Database, and need to make any changes to the actual values of these Constants, I don’t need to find every instance of the User Rights. The Global Constant will take care of that.

To explain my Public Variables, I like to allow the Database to have certain data always at its finger tips. For example, the gstrUserID is the System User ID, returned from the Operating System. The variable gstrCurrentUser is the actual name of the User (e.g. “Egbert Schmuckatelli”) . This value is established when the Database Administrator sets up the User’s Account. The variable glngCurrentUser is the Primary Key of tblUsers. The other variables are self-explanatory.

I use these variables throughout the Database, whenever we want to identify a User as accomplishing a certain task or when User-specific information must be provided.

Determining Who Has Accessed the Database

This is the easiest step. When the Database opens, I use a Splash Form which automatically determines who logged into the Database, as well as what the Database will do with that User. In the OnOpen Event of the Form, I have the following:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String
    gstrUserID = Environ("USERNAME")
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblUsers " & _
        "WHERE UserID = '" & gstrUserID & "';"
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rst.EOF Then
        With rst
            .MoveFirst
            glngCurrentUser = !UserKey
            gstrCurrentUser = !UserName
            gintUserRights = !UserRights
            gstrUserMachine = !UserMachine
            gstrUserDirectory = !UserDirectory
        End With
    Else
        'This User does not exist in the Users Table
        'Determine what to do--whether to Quit or set up new account
    End If
    rst.Close
    db.Close
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
EH:
    MsgBox "There was an error initializing the Form!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Exit Sub
End Sub

So, at this point, the Database knows the User’s rights within the Database, but we just have to make the system direct them appropriately.

Determining What to do With a User

Since my Splash Form has no actionable objects on it, after it completes doing several other things in the background (driven by the OnTimer Event), the Code simply tells the Form to Close. In the OnClose Event, I have placed the following:

Private Sub Form_Close()
On Error GoTo EH
    Select Case gintUserRights
        Case UserAdmin
            DoCmd.OpenForm "frmAdministrator"
        Case UserPromo, UserOIC, UserSrRecorder
            DoCmd.OpenForm "frmSwitchboard"
        Case UserRecords
            DoCmd.OpenForm "frmCommandRecords"
        Case UserRecorder
            DoCmd.OpenForm "frmPRFReview"
    End Select
    Exit Sub
EH:
    MsgBox "There was an error Closing the Form!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Exit Sub
End Sub

You will notice that, based on the Rights of the User, a different Form opens. This is completely transparent to the User, as our “Records” staff only ever uses the Command Records Form. They don’t know any different. A Recorder never has need to access our Switchboard, but only performs duties on the PRF Review Form.

However, you may notice that three different types of Users (userPromo, UserOIC and UserSrRecorder) all open the Switchboard Form. This doesn’t seem to jibe with this entire Article! If you have established different levels of User Rights, how can they all access the same Form? Won’t they all have access to the same information?

Here is where I have made a small twist on a classic capability of MS Access.

Allowing User-Specific Access to the Database

Many years ago, Microsoft Access began introducing the Switchboard Manager. I have used that standard format as the basis for my Switchboard, but have made some changes to provide User-specific access to the Menu Items. Keep in mind that if you currently use MS Access’ built in Switchboard manager, this method will NOT work—nor will you be able to edit the Switchboards using the built in Switchboard Manager. This is an overhaul to the Tables, as well as the VBA code that runs the Switchboard, so you may want to start from scratch.

First, I’ve made some modifications to the underlying Table for the Switchboard. I have renamed my Table “tblSwitchboard s” (note the plural, as this Table does, in fact, hold data for multiple Switchboards). Here is my list of Fields:

[B][U]Field Name      Data Type[/U][/B]
User            Integer
SwitchboardID   Integer
ItemNumber      Integer
ItemText        Text
Command         Integer
Argument        Text

Aside from the first Field (“User”) I believe this Table is identical to the MS Acces Switchboard Table generated automatically. One thing I have done is Create a related Table, tblSwitchboard Commands, which is used as a Row Source for the Command Field. Its values are as follows:

[B][U]ID   Command[/U][/B]
0    Page Title
1    Switch Page
3    Open Form
4    Open Report
6    Quit

This just makes the Table more intuitive when looking at it in Datasheet View.

Additionally, I have modified the Argument Field to be a Lookup Field, in which the Row Source is the following Query:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (MSysObjects.Type=-32768 OR MSysObjects.Type=-32764)
ORDER BY MSysObjects.Name;

MSysObjects is a System Table within MS Access, so don’t go around trying to change it! This Query only returns the Names of Forms and Reports, which are the only objects my Users have access to (PERIOD!). With a little research you can expand this query to include any other objects you desire.

You may also notice that this Query will return SubForms and SubReports (as well as the Parent Forms and Reports). If you have a standard Naming Convention (as I know ALL of you do...) you can limit the Query further by changing the Where clause to:

WHERE (MSysObjects.Name Like "frm*" AND MSysObjects.Type=-32768) OR (MSysObjects.Name Like "rpt*" AND MSysObjects.Type=-32764)

You can experiment as much as necessary to get this Query to return the proper list of values.

It is very important that you set the “Limit To List” property of this Field to “No”. The reason for this will become apparent shortly.

Now that we have created this Switchboards Table, let’s populate it. Excuse the length of this list, but I wanted to illustrate more fully. Keep in mind that this list has been pared down significantly, but is used only for illustration (and please excuse the whacked columns, but I did not see a point in converting my tabs to spaces):

[B][U]User	ID	No.	Item	Cmd	Argument[/U][/B]
Administrator	1	0	ACC Officer Promotions	Page Title	Default
Administrator	1	1	Task Management	Switch Page	2
Administrator	1	2	Administrative Tasks	Switch Page	6
Administrator	1	3	Quit	Quit	
Administrator	2	0	Task Management Menu	Page Title	
Administrator	2	1	Review Open Tasks	Open Form	frmTasks
Administrator	2	2	Overdue Tasks Report	Open Report	rptOverdueTasks
Administrator	2	3	Upcoming Tasks Report	Open Report	rptUpcomingTasks
Administrator	2	4	Task Template	Open Form	frmTaskTemplate
Administrator	2	5	Print Task Template List	Open Report	rptTaskTemplateListing
Administrator	2	6	Print Continuity Book	Open Report	rptContinuityBook
Administrator	2	7	Manage Projects	Open Form	frmProjects
Administrator	2	8	Return to Main Menu	Switch Page	1
Administrator	6	0	Administrative Tasks	Page Title	
Administrator	6	1	Command Records	Switch Page	10
Administrator	6	2	Release Actions	Open Form	frmPromotionRelease
Administrator	6	3	Recorders	Switch Page	11
Administrator	6	4	Evaluation Timeliness	Open Form	frmEvaluations
Administrator	6	5	STEP Quotas	Open Form	frmSTEPQuotas
Administrator	6	6	Manage Users	Open Form	frmUsers
Administrator	6	7	Administrator Functions	Open Form	afrmAdministrator
Administrator	6	8	Return to Main Menu	Switch Page	1
Administrator	10	0	Command Records Menu	Page Title	
Administrator	10	1	Screening Progress	Open Form	frmRecordScreening
Administrator	10	2	Send Missing Evals	Open Form	frmSendMissingEvals
Administrator	10	3	Command Records Database	Open Form	frmCommandRecords
Administrator	10	4	Return to Previous Menu	Switch Page	6
Administrator	11	0	Recorder Actions	Page Title	
Administrator	11	1	Recorder Listing	Open Form	frmRecorders
Administrator	11	2	Recorder Scorecard	Open Form	frmRecorderScorecard
Administrator	11	3	Return to Previous Menu	Switch Page	6
OIC	1	0	ACC Officer Promotions	Page Title	Default
OIC	1	1	Task Management	Switch Page	2
OIC	1	2	Administrative Tasks	Switch Page	6
OIC	1	3	Quit	Quit	
OIC	2	0	Task Management Menu	Page Title	
OIC	2	1	Review Open Tasks	Open Form	frmTasks
OIC	2	2	Overdue Tasks Report	Open Report	rptOverdueTasks
OIC	2	3	Upcoming Tasks Report	Open Report	rptUpcomingTasks
OIC	2	4	Task Template	Open Form	frmTaskTemplate
OIC	2	5	Print Task Template List	Open Report	rptTaskTemplateListing
OIC	2	6	Return to Main Menu	Switch Page	1
OIC	6	0	Administrative Tasks	Page Title	
OIC	6	1	Command Records	Switch Page	10
OIC	6	2	Recorders	Switch Page	11
OIC	6	3	Evaluation Timeliness	Open Form	frmEvaluations
OIC	6	4	Return to Main Menu	Switch Page	1
OIC	10	0	Command Records Menu	Page Title	
OIC	10	1	Screening Progress	Open Form	frmRecordScreening
OIC	10	2	Send Missing Evals	Open Form	frmSendMissingEvals
OIC	10	3	Command Records Database	Open Form	frmCommandRecords
OIC	10	4	Return to Previous Menu	Switch Page	6
OIC	11	0	Recorder Actions	Page Title	
OIC	11	1	Recorder Listing	Open Form	frmRecorders
OIC	11	2	Recorder Scorecard	Open Form	frmRecorderScorecard
OIC	11	3	Return to Previous Menu	Switch Page	6
Officer Promotions	1	0	ACC Officer Promotions	Page Title	Default
Officer Promotions	1	1	Task Management	Switch Page	2
Officer Promotions	1	2	Quit	Quit	
Officer Promotions	2	0	Task Management Menu	Page Title	
Officer Promotions	2	1	Review Open Tasks	Open Form	frmTasks
Officer Promotions	2	2	Overdue Tasks Report	Open Report	rptOverdueTasks
Officer Promotions	2	3	Return to Main Menu	Switch Page	1
Senior Recorder	1	0	Senior Recorder Actions	Page Title	Default
Senior Recorder	1	1	PRF Review	Open Form	frmPRFReview
Senior Recorder	1	2	Recorder Scorecard	Open Form	frmRecorderScorecard
Senior Recorder	1	3	Quit	Quit

NB: The Argument Field can have one of four types of entries:

1. “Default” – this indicates that this is the Main Switchboard (Home page)
2. Blank – indicating that this is a Switchboard Page Title (also indicated by the Command Field) or indicates to Quit the Database
3. A number (this is saved as Text!!) and indicates the Switchboard page to which the User will be directed
4. The name of a Form or Report

Notice, also, how the Switchboard options are clearly different for each User Type. Now we just have to set up our Form to work with this Table.


[imgnothumb]http://bytes.com/attachment.php? attachmentid=76 85[/imgnothumb]
(Image 1)


As you may not be able to see from the picture attached, my Switchboard has ten Command Buttons and Labels. These Command Buttons and Labels follow a specific Naming Convention (because I know you ALL follow the same principles!) of “cmdOption1”, “cmdOption2”, etc. and “lblOption1”, “lblOption2”, etc. You can ignore some of the stuff on this Form, but you can see that it’s really just a modification of the old MS Access standard Switchboard. When we look under the hood, this is what we find:

The Form itself has the Record Source of tblSwitchboards . the Form's Properties of Allow Additions and Allow Deletions are both set to No. As the User navigates the Menu, the Form Filters the specific Record that it needs (one record and one record only is displayed). There are several procedures we have to put in place for this Form to Work with the Table:

NB: Some of my code is not displayed as it is irrelevant for this purpose.

Option Explicit
Option Compare Database

Private Const intButtons = 10

Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
    Me.Filter = "User = " & gintUserRights & _
    " AND ItemNumber = 0 AND Argument = 'Default'"
    Me.FilterOn = True
    Exit Sub
EH:
    MsgBox "There was an error initializing the Form!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Exit Sub
End Sub

We establish the Constant intButtons to indicate how many buttons this Form uses. It’s nice to know we can always expand if we need to.

Then, when the Form opens, we filter by the “Default” Switchboard Page for that User. Very simple, so far, right?

When we choose another Switchboard Page to move to, the Code will filter the Form by the Page Title Selected (according to the appropriate User), so when that Filter is complete, we need to show the Switchboard options for this Page:

Private Sub Form_Current()
On Error GoTo EH
    FillOptions
    Exit Sub
EH:
    Exit Sub
    MsgBox "There was an error moving to the current Record!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
End Sub

This must go in the OnCurrent Event of the Form, otherwise the initial Page will not populate properly.

Then, the associated Procedure to fill the Options for the Form:

Private Sub FillOptions()
On Error GoTo EH
    Dim dbOptions As Database
    Dim rstOptions As Recordset
    Dim strSQL As String
    Dim intOption As Integer
    Me.cmdOption1.SetFocus
    For intOption = 2 To intButtons
        Me("cmdOption" & intOption).Visible = False
        Me("lblOption" & intOption).Visible = False
    Next intOption
    Set dbOptions = CurrentDb()
    strSQL = "SELECT * FROM tblSwitchboards" & _
        " WHERE User = " & gintUserRights & _
        " AND ItemNumber > 0 AND SwitchboardID = " & Me.SwitchboardID & _
        " ORDER BY ItemNumber;"
    Set rstOptions = dbOptions.OpenRecordset(strSQL, dbOpenDynaset)
    If rstOptions.EOF Then
        Me.lblOption1.Caption = "There are no items for this Switchboard page"
    Else
        While Not rstOptions.EOF
            Me("cmdOption" & rstOptions!ItemNumber).Visible = True
            Me("lblOption" & rstOptions!ItemNumber).Visible = True
            Me("lblOption" & rstOptions!ItemNumber).Caption = rstOptions!ItemText
            rstOptions.MoveNext
        Wend
    End If
    rstOptions.Close
    dbOptions.Close
    Set rstOptions = Nothing
    Set dbOptions = Nothing
    Exit Sub
EH:
    MsgBox "There was an error listing the Options on the Form!  " & _
        "Please contact your Database Administrator.", vbOKOnly, "WARNING!"
    Exit Sub
End Sub

You may have noticed that the key clause for this code to work lies here:

" WHERE User = " & gintUserRights

Only the Switchboard Items that apply to that specific User will show up.

One last thing. How do we determine what to do when a Menu Item is clicked?

First, each Command Button and Option Label must have in its OnClick EvenT the following:

=SelectOption(x)

Where “x” is the specific number of the Command Button or Option Label (1-10). That will execute the following Function:

Private Function SelectOption(intOption As Integer)
On Error GoTo EH
    RestoreForm Me.Form.Name
    Const optSwitchboard = 1
    Const optFormAdd = 2
    Const optFormBrowse = 3
    Const optOpenReport = 4
    Const optExit = 6
    Const ErrCancelled = 2501
    Dim dbOption As Database
    Dim rstOption As Recordset
    Dim strSQL As String
    Set dbOption = CurrentDb()
    strSQL = "SELECT * FROM tblSwitchboards" & _
        " WHERE User = " & gintUserRights & _
        " AND SwitchboardID = " & Me.SwitchboardID & _
        "  AND ItemNumber=" & intOption & ";"
    Set rstOption = dbOption.OpenRecordset(strSQL, dbOpenDynaset)
    If Not rstOption.EOF Then
        Select Case rstOption!Command
            Case optSwitchboard
                Me.Filter = "User = " & gintUserRights & _
                    " AND ItemNumber = 0" & _
                    " AND SwitchboardID = " & rstOption!Argument
                Me.FilterOn = True
            Case optFormAdd
                DoCmd.Close acForm, Me.Form.Name
                DoCmd.OpenForm rstOption!Argument, , , , acAdd
                GoTo SelectOption_Exit
            Case optFormBrowse
                DoCmd.Close acForm, Me.Form.Name
                DoCmd.OpenForm rstOption!Argument
                GoTo SelectOption_Exit
            Case optOpenReport
                DoCmd.OpenReport rstOption!Argument, acPreview
                GoTo SelectOption_Exit
            Case optExit
                DoCmd.Quit
            Case Else
                MsgBox "Unknown option."
        End Select
    Else
        MsgBox "There was an error reading the Switchboards Table."
        GoTo SelectOption_Exit
    End If
SelectOption_Exit:
On Error Resume Next
    rstOption.Close
    dbOption.Close
    Set rstOption = Nothing
    Set dbOption = Nothing
    Exit Function
EH:
    If (Err = ErrCancelled) Then
        Resume Next
    Else
        MsgBox "There was an error executing the command.  " & _
            "Please contact your Database Administrator", vbCritical
        Resume SelectOption_Exit
    End If
End Function

If you have your Switchboard's Table set up properly, you should never experience any errors. But, just in case, there is error handling included (because I know you ALL include error handling in ALL your code, right?)

And that’s it! It’s really not too complicated, but perhaps some of you may have thought that it was too daunting a task to tackle. Now you have some options.

I’d also appreciate any feedback on better ways to execute this code. I’m always willing to learn!

Hope this hepps!

[zmbd]

Thank You Twinnyfo... this is certainly alot to read thru/

A few things about Access:
It is not a secure database in the same aspects as enterprise level RDMS such as Oracle, SQLServer, MySQL, etc..

Anything done at the Access interface level can be undone/bypassed by someone like myself, or for that fact, any reasonably advanced Access user (I would suspect that Twinnyfo could do so with his eyes shut (^_^) )

Thus, passwords and other sensitive information (SSN,TaxID,etc. ..) should never ever be stored in plan text within the database tables... even if the database is using encryption, one should encrypt the field value too!

As a supplement to this article I would recommend reading at least the following:

• Insight: Securing Access Database
• Insight: SHA2
• Insight: AES Encryption
• Insight: RC4 Encryption
• Insight: MD5

Public Variables:
If you are using Access 2010 or later, I suggest that one transition to the TempVars collection.
(TempVars Object
Unlike the public variable, these will hold their values in case the code errors (often the public/global variables will dump their value in an error condition) usually only losing the set value upon an explicit command to delete, reset, or the program is shutdown. Another advantage is that the Tempvar collection values can be directly referenced by queries and other ms-access-database objects.

And for those that would like to add a PIN or Passphrase to the user:
I for one, actually use SHA2 or MD5 along with a salt and user input to hash the User name in my table:
SHA2(username&p assword&(SHA2(r ndSalt)) ==> stored value
I then Store the SHA2(username) and the salt value in one table so that the random salt can be determined later.
Thus, neither the actual user name nor their passwords are ever actually in the database.

I also never store the user name and the group privilege level in plan text. I'll use the API call to retrieve the current PC user information and SHA2 that for storage along with the digest of the User+Group. Otherwise it is all too easy for a user to simply elevate privilege in the user table by simply changing the record values.

[twinnyfo]

Z is absolutely correct concerning security in MS Access: There is none!

However, our security is managed at the macro level. No user can even get into any of our systems without secure authentication using Smart Cards. Second, when an authorized user does get into our Network, their permissions are restricted concerning which locations they can access. If they are allowed access to our particular network location, then I control which type of access they have to our DB.

Granted, as Z states, just about anyone with any Access experience can hack into our back ends if they really wanted to. But, again, we are not dealing with hostile or malicious employees, just overworked ones....

This is not a truly "secure" solution, but a practical one. My article was focusing on the capability to code in custom user permissions that are not built into Access. In our world, we have a lot of people with Access databases which are truly "open", so one does not even need Access experience to edit data--or even delete a table "by accident".

I'm just trying to prevent the accidents.

But, if you want a secure DB, Z is correct, go with Oracle or one of the others (and have a big budget!).

[NeoPa]

I'm not sure you're quite getting what Z is trying to say there Twinny. Many of the ideas that he's suggesting are highly recommended for use in Access-only situations. He's already provided code for handling the basic encryption for you (and other readers of this excellent article). I've changed my systems to use some of his code and it wasn't difficult. All discoverable from Bytes.com.

Storing user names or account IDs in plain text is not a great idea, but not as critical as storing the passwords. As Z says, you should only ever store the encrypted values for passwords and when you check them you do a lookup using the return value from the encryption function itself with the parameter value of whatever the user enters onto the form.

Why is that so important?
Beyond the obvious point of making it much more secure with very little effort, you should also bear in mind that when people use passwords they often use the same ones for various different systems. This means that the responsibility for keeping them obscured goes well beyond the sometimes limited security requirements of the current system.

PS. I just looked again (as I couldn't see it first time through) to realise you're not handling security with a basic password system but using AD to integrate into your project. A good approach I use myself when I can.

However, when using this approach it makes good sense to use Function to Return UserName (NT Login) of Current User rather than the Environment variable as that is so easily hackable - without even any Access understanding.

As I say, this option isn't universally appropriate, but when it is it makes very good sense to utilise it.

[twinnyfo]

Good advice from all....

I will work to incorporate the alternate method for getting the UserName--although I doubt any of our systems would work if one were to log in with their smart Card and then change system's UserName to something else--but I guess it might be possible for them to do that and still operate their system. Why anyone here would want to do that so see a bunch of lists about Air Force Promotions is beyond me....

I should also clarify a satement I made in Post #3:

In our world, we have a lot of people with Access databases which are truly "open", so one does not even need Access experience to edit data--or even delete a table "by accident".

I'm just trying to prevent the accidents.

Those "wide-open" databases that people are using in our world... none of them were created nor maintained by me. I have yet to make a DB that is fully Gorilla-proof, but I've graduated from chimpanzee to orangutan!

My (very small) list of users who have network access to my DB, know that they can only do what I allow them to do. And I know them all well enough that they don't have the skills to crack encryption codes and hashes and mess with back ends. They don't even know what those words mean!

But, again, good info and advice. I am always working to improve what I have, and this is at least one improvement. Many thanks!

[NeoPa]

My advice was less about your own situation Twinny, than about the very large number of situations found by the very large number of people that will come here for help getting their systems to work. This is now, whether you realise it yet or not, a place where many many people will come looking for help in this area. Congratulations on a job well-done!

[nytewing]

Hi Twinnfo, awesome example, is it possible for you to send me or post a link to download the database example used in this so i can visually see it to help me in my project

thanks

[NeoPa]

That would be accomplished by attaching a ZIPped database to the thread. We'll see if that's something Twinny is happy to do for you.

[twinnyfo]

NeoPa and nytewing,

I'll see if I can do that once I get back to work. Just popped on to the site for a moment to see what was going on. Currently enjoying some much needed Island Time in Hawai'i...

[twinnyfo]

nytewing,

Attached you will see a compressed zip file of the requested DB. Please note that this DB will not work as is. You must first add an entry in the Table tblUsers for yourself. This is required for every user you want to add to the DB.

Additionally, you must manually update any changes in the Table tblSwitchboards , as I have not yet created a Switchboard manager (like the old MS version). However, since my menus very seldom change, I haven't put the effort into it.

I've also left a few bells and whistles in, but took out most other bells and whistles, as they made the DB too complex for an intro to Users and Permissions.

Hope this file is valuable for you. If anything breaks or you can't understand anything within, just let me know and I'll update and re-post.

Hope this hepps!

[PatrickStel]

Hello,

I've got a question about the database.
I don't understand how the users need to login, cause there isn't a login form or anything like that.

Kind regards,
Patrick

[twinnyfo]

Patrick,

The login is based on the Windows Userid. When you have networked systems, all your users will have unique usernames, so that permissions may be set for various network resources. The database determines the username. The DBA would set the permissions for those wishing to access the database.

Hope this hepps.

[PatrickStel]

Copy that, at the moment we have a local network with only Local user accounts, however we're planning to go on controled domain. For now I'll just make a login form and check every form according you're user tables :)

[Blade8639]

The code works great for controlling access, I have one question. Something is causing the database to minimize every time I click on a control on the switchboard. Any idea what is causing this?

[twinnyfo]

Blade8639,

This is intentional. The DB should always appear as "floating" forms on top of other applications. As long as your forms are modal and pop up, this should not be a problem.