Database layout dealing with premissions

[Pests]

Hello,

I'm trying to design a mysql database layout for premission control. I decided I want a few requirements:

1. - User Groups

2. - Users can be in more then one group

3. - Can be any amount of premissions

4. - Premissions can easily be added

5. - All groups need to have access set on each premission
   [/list=1]

   I was thinking doing it something like so:
   Users Table
   id
   username
   password

   Group Table
   id
   name

   Users to Groups Table - Each row relates a user to a group, each user may be in more then one group
   id
   group_id
   user_id

   Premission List Table
   id
   name

   Access Table
   id
   group_id
   premission_id
   access (if they have premission to do whatever is in premission_id)

   The access table will have group_count * premission_count rows. Each group is represented by each premission in the access table.

   Basically, a user is assigned to zero or more groups. Then to find out if a user in a group has access, you find the premission_id of the premission name, and then find the correct row in the access table that has that premission_id, and the group_id of the user.

   The only problem I see with this approch is, because users can be in more then one group, how to settle conflicts between group access. I decided I want the access ORed togeather, as long as one premission says they can, then they can.

   I cannot think of a SQL query which would help me do this. I would like to only supply the premission name and user id and retrieve wether they have access or not. Or, if anyone else has a better way to do what I'm trying to do, please post.

   If you have any questions please ask.

   Thanks.

[mrhappiness]

$sql = "SELECT (COUNT(u.user_id) > 0) has_access
FROM
  usertable u
  INNER JOIN user_groups ug ON u.user_id = ug.user_id
    INNER JOIN grouptable g ON ug.group_id = g.group_id
      INNER JOIN accesstable a ON g.group_id = a.group_id
        INNER JOIN permissiontable p ON a.permission_id = p.permission_id
WHERE
  u.user_id = 45 and p.permission_name = 'dmoderate guestbook'";
$r = mysql_query($sql);
$has_access = current(mysql_fetch_row($r));
echo 'Access granted: '.(int)$has_access;

something like this should do the job

this way you don't need the access column (assuming that you only have stored permissions and no thingsthat are forbidden

[Pests]

Thank you, im checking it now to see if it will work.

However, Im confused on one point. How would you give a group access for a certain premission? Would you just add the group_id and premission_id to the access table?

[Pests]

Nevermind, I figured it out. This is exactly what I wanted, and all in one easy query. Thank you very much.

Now that I think of it, I need to figure out one more thing.

How would I get out all permissions from the permissiontable for a certain group, but have something signaling which premissions that group already has in the access table?

Basically, I want to get all the rows from the premission table, and have some way of knowing that a row has a matching pair in the accesstable with a certain group id.

Sorry, having trouble explaning this... or I may be doing a good job and not realize it.

premissiontable
premission_id|premission_name
1|News
2|Tutorial
3|Article

accesstable
group_id|premission_id
1|1
1|3

I want to be able to get three rows out, and I need a way to know that rows 1 and 3 have been defined in accesstable for the group_id 1.

Sorry if I'm confusing. I'm trying to express what I'm trying to do but having trouble.

[mrhappiness]

SELECT
  p.permission_id, p.permission_name, !ISNULL(a.group_id) assigned
FROM
  permissiontable p
  LEFT JOIN accesstable a ON p.permission_id = a.permission_id AND a.group_id = 1

[Pests]

Thank you, works great.