Database security - data restriction

[barrysprout]

I have a database that contains sales data from various countries around the world. I would like to restrict the countries that individual users can view the data of, i.e. the country manager for Germany can only see Germany sales data and not say, France.

Can anyone guide me as to how I would approach this problem?

 

[alvechurchdata]

If the users have no Access skills then you could drive all the forms and reports from queries which select data for that particular country.

If you've got people who know how to get to the Database Window and open tables then I would create separate databases for each country and build myself an administrator's database which linked in all the tables from the separate country for reporting and maintenance.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[barrysprout]

The first option is more viable but it would mean having a separate query for every country (over 70) for every report and setting the permissions for each one. I was hoping there was an easier solution. Thanks anyway.

 

[alvechurchdata]

The first option is more viable but it would mean having a separate query for every country (over 70) for every report

I'm sure you could drive this from a lookup table of some sort. We have a similar problem here where each of the three offices only needs to see their own clients. I've got one query for each report but I add that office's filter to it as it runs.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[barrysprout]

I'll give it a go. Thanks

 

[psemianonymous]

I have three generic security recommendations:

a) absolutely none

b) casual security - grab their Windows username via code and use this 'identity' to determine their security. This heavily relies on the honor system.

c) good security - don't store your data in Access, period, period, period. If your data is valuable, then they'll be willing to pay money to secure that data, right?


In your case I'm leaning towards option b). You can make a lookup table that determines their country (or whatever you're filtering) based on their username. Of course this means new users would have to be "set up" in the system, else they couldn't see any countries.

 

[barrysprout]

Actually you've given me an idea which is a variation of (b). Users will have to access the database via a username and password. The username is stored in a permanently open hidden form and the value used as a criterion for the queries. Hopefully that's not too simplistic.

 

[alvechurchdata]

a) absolutely none

I tend towards this model a lot of the time. There's no point adding heavy security to a database when the data is printed out in public on the main office printer and stored in a cheap tin filing cabinet.

Geoff Franklin

http://www.alvechurchdata.co.uk