Access Levels - Security

[Fekla]

I have a database with tables of Restaurants and their Manager (one-to-many)

Now I need to set "Access Levels", that is, when the manager enters his password (which is a field in the table) - his relative restaurants etc to be displayed (in a form/report/query, thats not the question).

I started by defining User Levels and making queries with password criterias. However, if I do not allow users to open tables, queries can not be run! but if they can open tables, no point in access levels.

so how can i make a person to be able to see/change only his records? any help more than welcome. if you can give me a link to a sample database, i would very much appreciate it.

Thank you in advance.

 

[The_Doc_Man]

Do this through a form so you can impose code underneath the process.

Once you do that, you can open a form which opens a thing that LOOKS like a query/table - but isn't.

In Access help, look up the following topics: Workgroup Security, Securing a Database, Form Datasheet View, Sub-Forms, Form Filter. Also, this forum has a search feature that works just fine.

The general idea is that when you force folks to log in using a recognized username and password, you can capture the name with the CurrentUser() function. Then you can tag each record with the name of the person allowed to see those records. Then you can open a SUB FORM in datasheet view based on the username as a FILTER of the sub-form.

 

[Fekla]

Yes, I got your idea. May I ask 2 clarifying questions?
1) if I "tag each record with the name of the person allowed to see those records" wont I be creating data redundancy?
2) still, i understand what is a form in a datasheet view. but its source is a table. so logically, the user will still be able to go to the tables menu and open the tables and see all the records!

 

[rainman89]

Yes, I got your idea. May I ask 2 clarifying questions?
1) if I "tag each record with the name of the person allowed to see those records" wont I be creating data redundancy?

No, its more like a filter that will open only to their records. that just means that when a user logs in the database recognizes that specific user and finds records base on that users login name, id, or whatever u are doing

2) still, i understand what is a form in a datasheet view. but its source is a table. so logically, the user will still be able to go to the tables menu and open the tables and see all the records!

A user should NEVER have access to tables.They should be hidden from the user Use forms to manipulate the data in the table. Queries can also be done with forms!! just base a forms recordsource on your query and your record will show

 

[The_Doc_Man]

The preferred way to secure your data from meddling is to lock everyone into a switchboard form. In this form, you can build buttons that when you click them, they open a form for which the recordsource has already been filtered. To make this not eat YOUR socks, you fix it so that the switchboard knows it is you and enables another button that says CLOSE THIS FORM (and let me work in the database window).

This is why you should have workgroup security enabled. Search this forum for articles on security. You will see such things as ways to stop folks from using key sequences to bypass startup forms. There are ways to make a query owned by someone else and not accessible to the general public, yet when run using ByOwner features, can allow data access under highly limited conditions. There are ways to encode the username so that instead of having to store the user name in the other tables, you just use the code as the filtering element. There are a thousand ways to skin this cat. And the SEARCH feature of the forum is how you find out what people do to approach this class of problem.

 

[Fekla]

Hm. Thank you all. I think I am slowly progressing.
Can anyone idle help me with the code that finds a record in a table whose 'password' field equals the value entered to a textbox and then saves the primary key of the found record to a variable?
(i mean, i will ask for password on startup, then remember who is the user and then use his id as a filter)

 

[Fekla]

Update: i m using a script similar to
http://www.databasedev.co.uk/login.html

 

[KKilfoil]

You may find it easier to take advantage of the user's network ID (rather than using CurrentUser()), and then have a 'rights lookup table' that allows code to enable rights (assuming you do all of the stuff described by the rest of this thread to lock down access.

In a code module, add the following:

[COLOR="Blue"]Option Compare Database
Option Explicit 'hopefully, you already have this!
[/COLOR]
' Access the GetUserNameA function in advapi32.dll and
' call the function GetUserName.
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetCurrentUserName() As String
Dim lpBuff As String * 25
Dim ret As Long, Username As String
ret = GetUserName(lpBuff, 25)
Username = Left(lpBuff, InStr(lpBuff, Chr(0)) - 1)
GetCurrentUserName = Nz(Username, "")
End Function

The users presumably had to log into Windows, and this will get you their 'Windows ID'.

You can then, during the OnOpen event of your form(s), lookup the user's 'rights' as YOU need, and grant them appropriate privilages. You can even create 'user groups' with one more table, and shorten your tables.

I have done this a few times, and it means the users can work in my databases without having to enter additional MSAccess passwords.

However, if you want any sort of meaningful security, you must follow the basic steps of overriding the default security settings applied by MsAccess, which was described in some of the above links.

 

[Len Boorman]

Where I work peoples windows ID and their network ID are identical.

I detect their "Logon ID" through a form that opens automatically and is then hidden by the application Opening Form.

When a User creates a record I "stamp the record with their Logon ID

Retrieval/Edit of records is controlled such that the User can only access records that have a matching Logon ID to that which is detected automatically.

I also use this to a) make invisible command buttons that I do not want them to see and also if the Logon ID is not in a master table the database quits immediately the Opening Form opens

Not bomb proof but will control most situations.

Disable F11 and shift key of course

L

 

[Wiz47]

The example uses User Levels to limit access after login. The followng are the login names and passwords. This is but one way to accomplish what you want, but at least you will have a basic example to work from.

John 111
James 222
Edward 333
Fred 444