create table for permissions, then build dynamic WHERE clause of query

[beckyh]

This is an example of my current permissions table.

Username LName FName MI Password Roles ChangePassword Locked UnitsAllowed
108 Schneider William M. 108 Budget No No 6120,6121

I've got the validation and login working correctly. My question is what would be the best way to setup my table above to store Units Allowed? This is what I am hoping to dynamically create. Do I use a loop to go through the comma separated values? I am open to changing my table structure if there is an easier way.

SELECT YAASREP.ASALCD AS UNIT, YAASREP.ASAQNA AS UNITDESC
FROM YAASREP
WHERE (((YAASREP.ASALCD)="6120")) OR (((YAASREP.ASALCD)="6121"))
ORDER BY YAASREP.ASALCD

Thank you!!

 

[philhege]

You should put the units allowed in another table with a one-to-many or many-to-many relationship.

I assume "username" is the unique record ID for YAASREP.

So, the table (let's call it "UserUnits") would look like

ID FK_USERID UNIT_ID
1 108 6120
2 108 6121

Now, if you have a ton of "units", they are in another table, right? In this case, use a many-to-many relationship

Assuming that the Units table has an ID field called, say, "unit_ID", then the many-to-many table would look like

ID FK_USERID FK_UNIT_ID
1 108 1
2 108 2

where the FK_UNIT_ID values 1 and 2 would refer to the primary key of the records in the Units table that contain units 6120 and 6121.

To query the database for those users who have access to these units:

One-to-many:

SELECT * from users
JOIN UserUnits on FK_USERID = Username
where UNIT_ID IN(6120,6121)

Many-to-many:

SELECT * from users
JOIN UserUnits on FK_USERID = Username
JOIN Units on unit_ID = FK_unit_ID
where UNITs.Unit IN(6120,6121)

HTH,


Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their conversation with Yiddish expressions.

 

[beckyh]

Thank you philhege! I've created a second table as you mentioned already and my SQL is working correctly to allow user security.