By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 1,904 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Restricting Users to Certain Data in DB by filtering a field

P: n/a
MX1
Here's an interesting one. I have a database that has user level security
invoked. The design is normalized and I have one field in particular called
Rep ID. Any thoughts on how I can allow all the reps to have read-only
access to the DB BUT somehow restrict each REP to only see data or records
that have his or her REP ID associated with them. Basically, there is a
table of clients and a bunch of other tables, income, sales, etc. that I can
filter against certain REP IDs.

I can't imagine I'd have to make a unique set of queries and forms that have
a REP ID filter on them, right? If I did, can I make that filter dynamic
based on the user account logged intot he database. I'm hoping there is a
more efficient way. Any thoughts are welcome.

Thanks in Advance.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"MX1" <mx*@mx1.abc> wrote in message
news:qhmob.51926$9E1.236135@attbi_s52...
Here's an interesting one. I have a database that has user level security invoked. The design is normalized and I have one field in particular called Rep ID. Any thoughts on how I can allow all the reps to have read-only
access to the DB BUT somehow restrict each REP to only see data or records that have his or her REP ID associated with them. Basically, there is a
table of clients and a bunch of other tables, income, sales, etc. that I can filter against certain REP IDs.

I can't imagine I'd have to make a unique set of queries and forms that have a REP ID filter on them, right? If I did, can I make that filter dynamic
based on the user account logged intot he database. I'm hoping there is a more efficient way. Any thoughts are welcome.


A saved query can be created with the ability for a dynamic criteria by
having it reference a form.

SELECT *
FROM SomeTable
WHERE [REP ID] = Forms!SomeForm!SomeTextBox

If you can have a form open that contains their REP ID value then the query
will only show those records.

You "could" use a filter instead, but the GUI provides lots of ways to
change or remove filters on forms so you would have to disable all of
those. Easier to just use the query.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
MX1
Interesting. I could make their user ID in the database security equal
their REPID in the database. Question is, can I have the query be smart
enough to detect the currently logged in user ID and populate that in the
query instead of the form text box. For example:
SELECT *
FROM SomeTable
WHERE [REP ID] = (some code to detect the user ID person is logged in as)
???

"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:bn*************@ID-98015.news.uni-berlin.de... "MX1" <mx*@mx1.abc> wrote in message
news:qhmob.51926$9E1.236135@attbi_s52...
Here's an interesting one. I have a database that has user level security
invoked. The design is normalized and I have one field in particular

called
Rep ID. Any thoughts on how I can allow all the reps to have read-only
access to the DB BUT somehow restrict each REP to only see data or

records
that have his or her REP ID associated with them. Basically, there is a
table of clients and a bunch of other tables, income, sales, etc. that I

can
filter against certain REP IDs.

I can't imagine I'd have to make a unique set of queries and forms that

have
a REP ID filter on them, right? If I did, can I make that filter dynamic based on the user account logged intot he database. I'm hoping there is

a
more efficient way. Any thoughts are welcome.


A saved query can be created with the ability for a dynamic criteria by
having it reference a form.

SELECT *
FROM SomeTable
WHERE [REP ID] = Forms!SomeForm!SomeTextBox

If you can have a form open that contains their REP ID value then the

query will only show those records.

You "could" use a filter instead, but the GUI provides lots of ways to
change or remove filters on forms so you would have to disable all of
those. Easier to just use the query.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 12 '05 #3

P: n/a
On Fri, 31 Oct 2003 13:49:31 GMT, "MX1" <mx*@mx1.abc> wrote:

Eh... CurrentUser() ?
-Tom.

Interesting. I could make their user ID in the database security equal
their REPID in the database. Question is, can I have the query be smart
enough to detect the currently logged in user ID and populate that in the
query instead of the form text box. For example:
SELECT *
FROM SomeTable
WHERE [REP ID] = (some code to detect the user ID person is logged in as)


???

<clip>

Nov 12 '05 #4

P: n/a
MX1
Sweet! Works like a champ...
"Tom van Stiphout" <to*****@no.spam.cox.net> wrote in message
news:qn********************************@4ax.com...
On Fri, 31 Oct 2003 13:49:31 GMT, "MX1" <mx*@mx1.abc> wrote:

Eh... CurrentUser() ?
-Tom.

Interesting. I could make their user ID in the database security equal
their REPID in the database. Question is, can I have the query be smart
enough to detect the currently logged in user ID and populate that in the
query instead of the form text box. For example:
SELECT *
FROM SomeTable
WHERE [REP ID] = (some code to detect the user ID person is logged in
as)
???

<clip>

Nov 12 '05 #5

P: n/a
"MX1" <mx*@mx1.abc> wrote in message news:<qhmob.51926$9E1.236135@attbi_s52>...
Here's an interesting one. I have a database that has user level security
invoked. The design is normalized and I have one field in particular called
Rep ID. Any thoughts on how I can allow all the reps to have read-only
access to the DB BUT somehow restrict each REP to only see data or records
that have his or her REP ID associated with them. Basically, there is a
table of clients and a bunch of other tables, income, sales, etc. that I can
filter against certain REP IDs.

I can't imagine I'd have to make a unique set of queries and forms that have
a REP ID filter on them, right? If I did, can I make that filter dynamic
based on the user account logged intot he database. I'm hoping there is a
more efficient way. Any thoughts are welcome.

Thanks in Advance.


This assumes an NT-type network where each user must log in under his
own account... you can add each user's domain login to their records
and then filter based on that. Or you could use Access security and
filter based on the database username (more secure). I think either
way you'd need to base everything off queries that hid the data not
pertinent to the person logging in...
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.