[SQL] Joining a user permissions table - Tilted Forum Project Discussion Community

moskie_work · 07-27-2004, 11:17 AM

I'm having trouble figuring this query out, maybe you guys can help me.

I'm adding a user administration module to an existing system, where an administrator can view and modify users' access rights to particular sections.

We got three tables: Users, Sections, and UserSectionRights. UserSectionRights has a foriegn key to User, a Foreign key to userSectionRights, and an integer representing the access level for that user/section combination.

For this admin module, i would like to get a table that looks like this:

Code:

	  |  Section 1 Access Level  |  Section 2 Access Level  |  ... <more columns for however many sections>
-----------------------------------------------------------------------------------------------------------------------
UserName1 |			     |                          |
UserName2 |			     |                          |
UserName3 |			     |                          |
UserName4 |			     |                          |
UserName5 |			     |                          |
...etc...

If I use joins, i end up getting a line for each user/section combination, as opposed to having all the combinations together. That's gonna be what I'll end up dealing with, if I can't figure this out.

I don't think subqueries are an option, because i'd like this to be dynamic, so that this query will still work when new sections are added. As I see it, i'd have to hard-code a subquery for each section.

Any ideas? hopefully i've explained things clearly! :-)

Rawb · 07-27-2004, 11:17 PM

Joining into rows.

I would love to be able to do that, but so far, in all of my questing, nothing of the sort has ever turned up in any database server I have found.

I once tried to hack around it by using a join and joining the same table 8 times or so, naming the tables and columns different things every time. I know that some MySQL developer has my face on a dartboard for that. I quickly learned my lesson (it was cool, until it involved anything over a trivial amount of data, then it all exploded.) and reverted it to a single join, and from that single join, I looped through it in PHP and output a hash of data that my Mom could be proud of.

Sometimes, SQL won't do what you want it to, good thing we have programming languages to fall back on.

On an aside, do you think you are over-engineering this login system? Is a comma-delimited list of permissions somehow totally out of order here? Are you going to need to be able to search by permission for who has it, or are you going to just want to search for if a person has a particular permission. Although it is awful for data normalcy, and the SQL gods will strike me down for saying this, sometimes, it is far far more efficent to just throw a little delimeted list in someplace instead of going through convultions in an attempt to make everything it's own little column and row.

bubbagumpshrimp · 08-13-2004, 06:00 PM

Could you not just have a field in the Users table? Call it 'permissions' or something obvious like that. Rather than having a seperate field in the db for each section, you have a single int field, and using different bitmasks, your php app can work out who has permission to do what, without eating up tons of space in your db.

Example:

<Table: Users>
uId int auto_increment primary key
uName varchar not_null
uPass varchar not_null
... (etc) ...
uPermissions int not_null

Say you have 8 sections, and a user name 'bubba', who has a uId of 6. Bubba's uPermissions field holds the number 7. That's just great you say, but what does it mean?

Lets look at the binary; 7 turns into 0000 0111. Now we can see that bubba can access sections 1, 2, and 3. (we start from the right).

To figure this out using php, we can do something like this:

Code:

// user is trying to access section 2.  lets see if he's allowed.
$q = "select uPermissions from users where uId=6;" // recall that bubba's uid is 6
$rs = mysql_query($q,$db); // where db is an existing database connection
$row = mysql_fetch_array($rs);
$userPerm = $row['uPermissions'];

if(($userPerm & 2) == 2)
  echo "welcome to the section!"
else
  echo  "go away";

// for section 3, we'd & with 4.  for section 4, with 8, and so on, using each bit to show permission to a given section.

I hope that made some kind of sense.
there are a couple example of stuff at http://ca2.php.net/manual/en/languag...rs.bitwise.php

good luck

magua · 08-13-2004, 07:22 PM

If I'm reading you right, what you want to do is pivot the data; ie, transform

Code:

User     Section     Access
--------------------------------
Bob       Sec 1        1
Bob       Sec 2        1
Bob       Sec 3        0

Into

Code:

User     Section 1     Section 2     Section 3
Bob       1                 1                  0

Right? Unfortunately, you don't say which db you're using, or what programming language (if any). There's a way to do what you want, but it requires the ability to concatenate strings, and the SQL has to understand the CASE statement. This is valid in SQL Server; you're on your own trying to port it. The string concatenation I'm doing in SQL Server syntax for simplicity; you could just as easily do it in any programming language, and pass it to the db.

Code:

DECLARE @SQL varchar(4000)

SET @SQL = 'SELECT Users.Name'

-- This is the same as looping through each section, concatenating each line.
SELECT @SQL = @SQL + ', MAX(CASE WHEN UserSectionRights.SectionID = ' + Sections.SectionID + ' THEN UserSectionRights.Value ELSE 0 END) AS ' + Sections.Name
FROM Sections
ORDER BY Sections.Name

SET @SQL = @SQL + '
FROM Users
INNER JOIN UserSectionRights ON Users.UserID = UserSectionRights.UserID
GROUP BY Users.UserID, Users.Name'

EXECUTE (@SQL)

So it might generate something like this:

Code:

SELECT Users.Name
, MAX(CASE WHEN UserSectionRights.SectionID = 1 THEN UserSectionRights.Value ELSE 0 END) AS Section 1
, MAX(CASE WHEN UserSectionRights.SectionID = 2 THEN UserSectionRights.Value ELSE 0 END) AS Section 2
, MAX(CASE WHEN UserSectionRights.SectionID = 3 THEN UserSectionRights.Value ELSE 0 END) AS Section 3
FROM Users
INNER JOIN UserSectionRights ON Users.UserID = UserSectionRights.UserID
GROUP BY Users.UserID, Users.Name

Hope this helps.

07-27-2004, 11:17 AM	#1 (permalink)
moskie_work Upright	[SQL] Joining a user permissions table I'm having trouble figuring this query out, maybe you guys can help me. I'm adding a user administration module to an existing system, where an administrator can view and modify users' access rights to particular sections. We got three tables: Users, Sections, and UserSectionRights. UserSectionRights has a foriegn key to User, a Foreign key to userSectionRights, and an integer representing the access level for that user/section combination. For this admin module, i would like to get a table that looks like this: Code: \| Section 1 Access Level \| Section 2 Access Level \| ... <more columns for however many sections> ----------------------------------------------------------------------------------------------------------------------- UserName1 \| \| \| UserName2 \| \| \| UserName3 \| \| \| UserName4 \| \| \| UserName5 \| \| \| ...etc... If I use joins, i end up getting a line for each user/section combination, as opposed to having all the combinations together. That's gonna be what I'll end up dealing with, if I can't figure this out. I don't think subqueries are an option, because i'd like this to be dynamic, so that this query will still work when new sections are added. As I see it, i'd have to hard-code a subquery for each section. Any ideas? hopefully i've explained things clearly! :-)

08-13-2004, 06:00 PM	#3 (permalink)
bubbagumpshrimp Crazy	Could you not just have a field in the Users table? Call it 'permissions' or something obvious like that. Rather than having a seperate field in the db for each section, you have a single int field, and using different bitmasks, your php app can work out who has permission to do what, without eating up tons of space in your db. Example: <Table: Users> uId int auto_increment primary key uName varchar not_null uPass varchar not_null ... (etc) ... uPermissions int not_null Say you have 8 sections, and a user name 'bubba', who has a uId of 6. Bubba's uPermissions field holds the number 7. That's just great you say, but what does it mean? Lets look at the binary; 7 turns into 0000 0111. Now we can see that bubba can access sections 1, 2, and 3. (we start from the right). To figure this out using php, we can do something like this: Code: // user is trying to access section 2. lets see if he's allowed. $q = "select uPermissions from users where uId=6;" // recall that bubba's uid is 6 $rs = mysql_query($q,$db); // where db is an existing database connection $row = mysql_fetch_array($rs); $userPerm = $row['uPermissions']; if(($userPerm & 2) == 2) echo "welcome to the section!" else echo "go away"; // for section 3, we'd & with 4. for section 4, with 8, and so on, using each bit to show permission to a given section. I hope that made some kind of sense. there are a couple example of stuff at http://ca2.php.net/manual/en/languag...rs.bitwise.php good luck

07-27-2004, 11:17 PM	#2 (permalink)
Rawb Crazy Location: Salt Town, UT	Joining into rows. I would love to be able to do that, but so far, in all of my questing, nothing of the sort has ever turned up in any database server I have found. I once tried to hack around it by using a join and joining the same table 8 times or so, naming the tables and columns different things every time. I know that some MySQL developer has my face on a dartboard for that. I quickly learned my lesson (it was cool, until it involved anything over a trivial amount of data, then it all exploded.) and reverted it to a single join, and from that single join, I looped through it in PHP and output a hash of data that my Mom could be proud of. Sometimes, SQL won't do what you want it to, good thing we have programming languages to fall back on. On an aside, do you think you are over-engineering this login system? Is a comma-delimited list of permissions somehow totally out of order here? Are you going to need to be able to search by permission for who has it, or are you going to just want to search for if a person has a particular permission. Although it is awful for data normalcy, and the SQL gods will strike me down for saying this, sometimes, it is far far more efficent to just throw a little delimeted list in someplace instead of going through convultions in an attempt to make everything it's own little column and row.