security - multiple users accessing part of a table

[ubgu3]

Hi,

I'm building a DB where I have multiple users. I would like to have all data in 1 table but only give access to records created by the individual user. Is this possible?

Alternatively, if each user has it's own table, how can I report on the sum of all tables.

Cheers,

Rene

 

[gemma-the-husky]

very difficult to achieve. Access security is based on restricting access to whole forms, tables etc.

you would have to do this with code etc, and it will not be easy, as you probably would need to modify virtually every query/form/report. You also need to consider the use of features such as the binocular function which might defeat your attemted security model.

There have been threads here about similar requirements, so have a search

 

[uplate]

Yes, it's possible. However, it's very difficult to achieve secure applications. How much security have you built in and how much time do you have to add more? How important is it to prevent access by the wrong person? A thoughtful answer to that question may shape your alternatives.

Have you implemented full user-level security; do your users have read permission on the tables; do you have a totally locked backend with RWOP queries in the frontend; are you using groups for permissions and just assigning users to groups - what is your current security regime? If you can describe your current database security scheme, the importance of preventing improper access, and the degree to which you can invest time in securing your application, I could probably help you figure out your options.

 

[ubgu3]

Thanks for your help on this

Security is my number one priority.
Data maintenance:
My database is based on a table with client information (extremely sensitive).
Each client is investigated by an investigator.
An investigator could have several clients.
The investigator needs to update the client's information every week.
Investigators only should have access to their own clients information.
Reporting:
There are 2 types of clients, say X clients and Y clients.
All reporting needs to be able to report on X and/or Y clients.
Client’s names are encrypted through the use of an extra table.
Access to this table is only at admin level.
Current security:
I have 2 groups. 1 with access to the X table/forms and the other with access to the Y table/reports.
Also, I will need to split the DB as there will be many users over the network (haven't implemented this yet.
Last point
I'm new to Access (as if you haven't picked up on this...)

Your suggestions will be valued greatly.

Rene

 

[uplate]

I am interested in helping you get the best solution for your situation.

With that in mind, please know that this post is meant to be helpful rather than negative. Also, the gist of my response is based on your use of the term "extremely sensitive" - I interpret that to mean that the potential consequences of a breach in security are vastly greater than the substantial cost in money, time, and resources necessary to successfully implement a secure application. If the potential consequences of a security breach are not really so bad after all, then perhaps my comments are overly cautious.

If security is truly your number one priority, and you are new to Access, then you should consider giving this task to a person or company with a proven track record in Access security. Learning by doing is fine for forms, but not a good idea for security. Defects in forms typically result in a temporary loss in usability or functionality, and can be fixed by simple editing. Defects in security can result in unrecoverable and potentially huge losses.

Access security is difficult to master, and there are numerous potential weak links that can be exploited by a knowledgeable person to gain unauthorized access to your data. If you search the web on this topic, you will even find many products and services that claim the ability to open your secure database. Many people assert that Access is fundamentally insecure.

In addition to security, you mentioned:
* splitting the DB - This is almost always a good idea, but adds slightly to the comlexity to your security scheme.
* encrypting via extra table - Encrypting comes with its own can of worms - what encryption technology are you using?
* access restricted to "admin level" - Do you mean access to the file is restricted by operating system permissions, or do you mean the "admin" user or "Admins" group in Access? If you mean the latter, this is big trouble, because, contrary to intuition, the admin user is totally insecure as anyone with even slight familiarity with Access security will confirm.

Implementing user-level security for a multi-user, two-tiered Access application is not a trivial task, especially for a beginner. If you choose to do it on your own, you have a risky and big task ahead of you. I'd recommend getting someone else to do it, but if you choose not to, I'd recommend (1) taking formal training on Access development and Access security, (2) building many practice databases until you know where the weak links are and until you know you have the secure development process down pat, and (3) testing your security implementation by trying some of the products and/or services that claim to be able to open your secured database.

I put some time into this reply because I hope you will get the best solution for your situation. However, I really think you should get formal training before implementing a secure database.

Good luck...

 

[neileg]

If security is such a high priority, then I don't think that Access is really the right tool for the job. Although it can be made to do some sophisticated things, you'd be better off using a heavyweight rdbms with record level security.