Security

[StephenD]

Hi everyone,

I need to setup security on my database to allow different people to do different things. The database is operated by a user using a combo box drop down to select a particular coutry-it then gives the user information on that country and provides futher buttons for the user to select that open more forms containing filtered information based on the country they have selected.
I need a specific user in each country to be able to update/edit information on the forms-but only information for their own country-i.e. Someone in France could view all the data but only make changes to information relating to France.
I've setup access security going through the various stages and I have log-ins for each user-I don't know how to get to the next step, can anybody help??

 

[DaveMere]

You could set up another table, created by yourself, listing usernames and the countrys to which they belong. The property 'CurrentUser' will give you the username at run-time. Retrieve the users's country from this table, either upon use of the control in question, or early on and store it in a global variable. Then make the various options available to the user (control visibility, choices in a list box, etc) dependant on this variable. Or just add this country information to whatever query returns data from a table and limit the data returned.
I'm not sure if security settings can limit user access to specific data rather than just whole tables/queries etc. I would suspect this isn't possible, but would be happy to be enlightened.

 

[The_Doc_Man]

DaveMere is correct. You don't use Workgroup-style security to restrict data by user. You need to "roll your own" for that chore.

The only thing you need to do is to carefully review your exact security requirements.

If an identifiable subset of your data is limited to one user (plus, of course, the admin user), you would make a single field in each affected table and put that user's name in the field.

Or you could do a lookup of a code from a table to define each user's data access rights. (DLookup works.) In that case, you might have something in the Form_Load action routine to define a global value from a general module's declaration. Or simply a value global to all code in a given class module, something with a Static declaration so it will persist for the life of that user's session. Then just use the code. Makes queries easier to write because you need fewer quotes to define the DLookup criteria.

Now, if you run into the case where more than one non-admin user needs access to the same data, you have to do a lookup of sorts. In this case, you assign each country a code and each user a different code. Then you have an "access allowed" table that lists pairs of <country,user> to show that user X has the right to muck around in country Y. You would have to do a DLookup based on TWO selection criteria for that one.

Note that if you allow users to see the database window and open tables directly, you have no selective-data security at all.