Alternate Security

[nmodhi]

Hey guys,

So I thought about this different method of making my access database secure within my workplace, I just can't implement it I really hope I can.

I have a table of employees where one of the fields includes a check box whether or not the employee is an Administrator. The filed name is "Administrator".

Currently on my form the information is queried according to the user's windows login ID. The form retrieves the information based on an employee query where the criteria of the employee field is a function that retrieves the windows login ID.

The only thing I'm looking to do really, is to check whether or not the user is an administrator using it's associated check box, and if so, remove the function from the criteria of the employee query. The reason I say remove is so that the Admin can have access to all other employee information.

I thought of alternating the Where statement in the query but I'm still a novice at this. Perhaps I can alter the function that retrieves the windows login ID?

Any help would be greatly appreciated; I hope all information is provided.

 

[vbaInet]

Use the DLookup() function to check that. I don't understand the "remove criteria" bit? You can use an IIF() function too. So maybe a combination of both.

 

[nmodhi]

hello again vbaInet

Currently the employee table is being queried. If the user is an admin, then the query is not necessary. Hope that makes more sense.

I've never used the DLookup function, I tried using the IFF in place of the criteria. Something like what I said earlier..

IF admin is true, criteria =null, if admin is false, criteria = windowsloginfunction()

Can I use an IFF statement in place of that?

 

[vbaInet]

Hello! That's the sort of syntax you need.

IIF(LCase(DLookup()) = "admin", TRUE action here, FALSE action here)

Checkout the Help files on how best to use the DLookup() function. If you get stuck, just post back.

 

[gemma-the-husky]

if you have a switchboard (MS or your own design) then its relatively easy (but still possibly painstaking) to test the login, and change the functionality appearance of the forms - based on who is logged in. You may need to think about what every form actually allows in this respect

BUT then you have to make sure that your database cannot be compromised - because if the users can get to the database window, they can do things you dont want them to do.

If they can open a new dbs, and import all your objects, they may even be able to cicrcumvent you in that way.

So the hard bit is actually locking down the database securely.

 

[ghudson]

workgroup security will prevent users from linking to your data tables

 

[nmodhi]

Thanks for the tip gemma-the-husky & ghudson. I thought long and hard about using the Access security feature..even creating a switchboard to have an associated user name and password for each user, but why the mess when a user can simply click on the database and not have to go through the extra login procedure?
The windows login ID worked for me great and there will only be 2 types of users accessing the database, admins and users..which is why im not too concerned about creating user and group accounts through the access security feature even though it would be more secure. Im trying to make this easy for my co-workers. The only coding I need is to set the criteria. The criteria is the ultimate control of querying the user. If the criteria did not exist, all users' information will be displayed (admin access).

To vbaInet:
I tried this but it's giving me an error, wrong number of arguments. The windowsloginID has been declared. Is this how it should look like?

IIF(LCase(DLookup())="Administrator", Null, windowsloginID())

 

[vbaInet]

Thanks for the tip gemma-the-husky. I thought long and hard about using the Access security feature..even creating a switchboard to have an associated user name and password for each user, but why the mess when a user can simply click on the database and not have to go through the extra login procedure?
The windows login ID worked for me great and there will only be 2 types of users accessing the database, admins and users..which is why im not too concerned about creating user and group accounts through the access security feature even though it would be more secure.

So if I was a user and went to a computer that has Windows Admin rights I would be able to run on admin rights on your db? Nice

IIF(LCase(DLookup())="Administrator", Null, windowsloginID())

That was just a skeleton code. There's more to the DLookup function. Check the help files for an example.

DLookup("[Field_To_Return]", "Table_Or_Query_Name", "[Field_To_Lookup] = " & lookupValue)

 

[nmodhi]

Im pretty sure this looks okay but the query seems to query according to the loginID only. I am an admin, thus the query should not query anything..here's what I have

IIf(LCase(DLookUp("[Administrator]=True","Employee")),Null,windowsloginID())

If the admin is checked as true from the employee table, display nothing in the employee user query criteria ..otherwise set the criteria according to the windowsloginID. My logic seems to be fine

 

[vbaInet]

IIf(LCase(DLookUp("[Administrator]=True","Employee")),Null,windowsloginID())

You've got the parameters in the DLookup function mixed up. Syntax:

DLookup("[UserGroup]","Employee", "[UserName] = '" & nameOfUser & "'")

Where UserGroup is the field where admin or user is saved.

What does WindowsLoginID function do? You don't need the "()"

 

[nmodhi]

I got it!
It looks like this:

IIf(LCase(DLookUp("[UserID]=WindowsloginID()","Employee","[Administrator]=True")),([Employee].[UserID]),WindowsloginID())

The windowsloginID function retrieves your unique windows login ID when you login in so that's used to query the user information. I have a list of employees and certain employees are checked as admins. So if Im correct the code finds the user and checks if he/she has admin access. If he/she has admin acess, the criteria is set to Employee.UserID which allows the admin to see all employees. IF the user was not an admin, then it sets the criteria to his windowsloginID and displays only his/her information on the form.

Pretty neat! Thank you once again vbaInet!

 

[vbaInet]

What you've written does this:

If user Is admin Then

     Return: userID

Else 

     Return: Value of WindowsLoginID function

End If

Not sure if this is the sort of thing you want to do?

 

[nmodhi]

I know it might seem awkward but it is the right thing. When the userID is returned, it returns the entire table. The return is displayed in the criteria which is perfect

 

[vbaInet]

As long as it works for you that's good. Enjoy!