query dynamically based on a user id

[Quotidius]

In attempting to secure my database I have established the necessary users and groups. Can anyone recommend how I might be able to allow users when selecting a form to view only the rows that belong to them?

I gleaned and idea from a forum here to build a unique table for each user. The respective user would then be the only one given read write privileges to this table. Next, the query underlying my form is augmented with the table of an individual user. The join is such that only those records are retrieved belonging to the user? Is there a method by which I can vary this table according to the user's sign-on (for example if I have 10 users then ten individual user tables would be made with their foreign key linking to the larger more general table)?

The ultimate goal: Users sign on and open a form displaying their data only.

Thanks for any recommendations.

 

[lespaul]

First, having a table for each user isn't really a good idea. How can you tell which records belong to which user? have you read the fundamentals document link below?



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Golom]

I agree with Leslie.

Users should never be confronted with tables directly ... only with queries. If you have a "UserID" field in the table then SQL of the form

Select <some fields>
From [The One and Only Table]
Where UserID = "'" & [The System UserID] & "'"

Then each user will see their own records. There are then no issues about creating tables for new users; reassigning tables to users; deleting tables when a user leaves, etc. It's all just data and you handle those things by changing data values ... not by restructuring the database.

 

[Quotidius]

Leslie, I agree is isn't ideal but then again how can I create vertical security within a table in Access? All my data is otherwised normalized.

My Thinking: One table for each employee making them the owner of it and permitting no one else to view it. This Usertable has only one field and one record, UserID. My main query and form for data editing will be with a join to the corresponding user_table thus restricting what rows they can read.

My Goal: Once a user signs on with id and password to have the form open reading the rows that are theirs only.

My question: is there a way to make the underlying query variable (Query1 joins User1_table to main table, Query2 joins User2_table to main table etc..) according to which user signs on?

Any other suggestions also welcome.

 

[Quotidius]

Golom, your post preceded my second one by minutes. I will try your suggestion. One question, I have the users logging on to the database with accounts I established. So does it matter weather user_id = System User ID or user_id= workgroup user_id?

Thank you

 

[traingamer]

Why not just link it to your login / security table as suggested above? You absouletly do not need nor want one table for each user. Your main data table MUST have a [user id] column (or a scondary linking table).

Securitytable
userid user
1 Bill
2 Sue

datatable
id field 1 ... userid
1 ESI ... 1
2 JKX ... 2
3 SDF ... 1

select field1, field2, userid from datatable t1 inner join datatable d1 on t1.userid = d1.userid

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Golom]

It matters if one user logged onto the machine (System User ID) but another one then sat down and fired up the App (WorkGroup UserID).

If they are always the same person then it shouldn't matter.

When I'm doing stuff like this I always create a User Logon screen and maintain my own table of authorized users. There is just too much "... lemme use your computer for a sec ..." stuff out there that relying on the system maintained user information is chancy when you really don't know who is at the keyboard.

 

[Quotidius]

Guys, I hate to be a pest but I need a little more clarification in trying to streamline the application.

I have succesfully implemented Golom's suggestion, making a user table (employees) with username and password so that a when user signs-on they first, are authenticated, 2nd have an appropritate form open (equalt to their user type in the employees table) and third, have their form's query based on their user_id within the employees table so that only appropriate records show.

The Issue: This is also a secured Access db with user groups of different security levels. So now, the user must first log on with their Access User Id & PW and then be immediately taken to a start up for that ask the username and pw to match against the employees table. It all works but this is rather tedious to have users sign in twice.

Is there a way to capture in a temporary table the user's workgroup sign-on Id and PW (not the network sign-on)?

My goal is a) to have just one sign-on, b) forms that will filter to just the users id, and c) a secure DB restricting user privledges.

Thanks again!

 

[PHV]

Have 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 FAQ181-2886

 

[Quotidius]

PHV, currentuser was helpful eventually leading me to Environ("USERNAME"). Thanks! -Q