Security through Queries

[LHolden]

Hi all,

I'm currently working on a database which will be used by a decent number of people (around 50), and I'm working on ways to make sure it stays secure. One of the things I want to do is require a name to be associated with records which are appended/updated through queries which each user can run. The only way I know of to do this at the moment is by requesting that each user enter their name (which is obviously not the best idea) or sending each individual user a different FE which will automatically enter their names as a default value. I was just wondering if there was an easier, more central way of doing something similar.

Thanks in advance for the help!

 

[mdlueck]

I do this sort of thing by assuming that the Windows userID is who is actually using the Access database. I grab that ID, and check a daatbase application permissions table. If a matching ID is found, then access to the Access application is granted based on the permissions found in that permissions table. Also, as users INSERT/UPDATE records, always the ID associated with their UserID and TimeStamp is captured, so that is an audit capability of knowing who/when changed the record last.

 

[LHolden]

That sounds like it would work fantastically. Unfortunately I'm really new to Access (I just learned how to use it before starting this database), so I wouldn't even know where to begin to do that. If you could help me with that I would be incredibly grateful.

 

[G37Sam]

1) You will a need a Permissions Table, that's where you store user permissions by username.

2) You can get the current username using the following:

Environ("Username")

3) You will need to use the dlookup() function to lookup what permissions the current user has from the user permissions table. Here's more on DLookup: http://msdn.microsoft.com/en-us/library/office/aa172176(v=office.11).aspx

 

[LHolden]

Many thanks to the both of you!!

 

[mdlueck]

2) You can get the current username using the following:

FWIW Actually, my implementation uses...

Rem /* "VBA: Sample Code to Retrieve the Current User Name" */
Rem /* http://support.microsoft.com/kb/161394 */