Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Record-level" User-level security?

Status
Not open for further replies.

MaddiMond

Technical User
Mar 28, 2005
76
US
I was asked to install user-level security for a database I built. They want that if let's say Bob Smith accesses the database, he will only see the records he entered into the database, form, table. Intuitively, this seems impossible for me to achieve. As I recall, it is only possible to set up object-level security (such as access to forms, reports or tables, right? Do you know if one can set up record-level security?
Any tip is greatly appreciated.

Regards,

Maddi
 
I can think of a couple of ways to approach this. However, much would depend upon the intended use of the database.

Possiblities:

1. Each record includes a username field of who entered the record. Then any forms or reports could display only those records with that user's name through selective queries.

2. A separate table is created for each user, perhaps named the current username. The table access could then be restricted to that user (and whomever else may have permission). Collective reports, etc. for management could use JOIN queries.

Personally, I think the 1st approach would be the easiest to manage. But, it would require the users to not have access to the database window so they could view tables directly. You would have to restrict their access to data through queries which base data retrieval on the username field.

Keep in mind, just about any security scheme in MS Access is to thwart the honest and curious. Really serious intruders are hard to keep out.
 
One way to approach this is to use JET security, do disallow ALL table access. As MoLaker said, have a User (or Group) field that entered the record (or that can access the record).

However the difference I'd suggest is that the queries upon which the forms & reports are based will be OWNER ACCESS queries. This allows 'peasant' users to view data in the tables they're disallowed from. A 'god' user creates the queries, and picks and chooses fields which you want a 'peasant' user to be able to see, and only the records via a WHERE clause something like:
WHERE tableuser = currentuser() (Pseudocode, I'm not doing the quote thing for examples here)

This way, peasants can't change the design of the queries and they can only view fields that the god user chooses, and only records that pertain to him and they can't open the tables in the db window.
--Jim
 
I had a similar problem and used the first method described by MoLaker, using Environ("USERNAME") to capture the users windows login name in a field whenever creating a new record. Use the same as part of sql in vba to set the record source of/ filter a form/ report when opening it to edit or view records.
 
Thank you all very much for these valuable posts. Does anyone know a good book that would help me achieve the Environ("USERNAME") set-up?

Maddi
 
No good on book titles but you can do it in a couple of ways. I think this is the simplest...

Note this is not 'real' security and you must at least hide the database window permanently to make this work well.

Say you're creating a new record on Form1 whose recordsource is Table1. Table1 includes the field named InputBy as well as all the other fields you want the user to populate.

On the form, along with all the other fields, create one whose control source is the field InputBy and set it's visible property to false. Set it's default value to =Environ("username").

Every time a user creates a new record, their logon name is input in the field.

Then, create queries to base your reports or read only forms on and put =Environ("username") in the Criteria for the InputBy field.

As mentioned, you may want to use VBA if you want some people to be able to select records for a particular user other than themselves etc. In my case, this lead to requests for auditing on who looked at what records when etc and lots of sql...
 
Thanks sozzer,

Is there a possibility to not use the windows login, but the login for the access database, the user-level username for the database?

Maddi
 
Take a look at CurrentUser

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
See faq181-4888

Never give up never give in.

There are no short cuts to anything worth doing :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top