Join Query in Sql server

Question

I am having trouble with a join in sql. I have 3 tables.

1: Lists the user details
2: Lists the permissions the user group has
3: Lists the page that that group can access

 

   Table1 users :
   ****************************************
   username |  group
   ****************************************
   admin    |  administrator 


   Table2 groups :
   *********************************************
   user_group    | create | view | system_admin
   *********************************************
   administrator |   1    |   0  |      1


   Table3 urls:
   *********************************************
   create     |  view      | system_admin
   *********************************************
   create.php |  view.php  |  system.php

(apologies for my table drawing)

What I am doing via php , is grabbing the user_group they belong to. I then need to check if they have access to the page they have just hit or redirect them back.

Can I accomplish this with the current table layout the way they are through a join?, Or shall I look to re-design these tables as they are not intuitive for this kind of thing.

Answer 1

I actually might redesign the tables to make them easier to query:

create table users
(
    id int,
    username varchar(10),
    groupid int
);
insert into users values (1, 'admin', 1);

create table groups
(
    groupid int,
    groupname varchar(20)
);
insert into groups values (1, 'administrator');

create table permissions
(
    permissionid int,
    permissionname varchar(20)
);
insert into permissions values (1, 'create');
insert into permissions values (2, 'view');
insert into permissions values (3, 'system_admin');

create table urls
(
    urlid int,
    name varchar(10)
);
insert into urls values(1, 'create.php');
insert into urls values(2, 'view.php');
insert into urls values(3, 'system.php');

create table group_permission_urls
(
    groupid int,
    permissionid int,
    urlid int
);
insert into group_permission_urls values(1, 1, 1);
insert into group_permission_urls values(1, 0, 2);
insert into group_permission_urls values(1, 3, 3);

Then your query would be similar to this:

select *
from users us
left join groups g
  on us.groupid = g.groupid
left join group_permission_urls gpu
  on us.groupid = gpu.groupid
left join permissions p
  on gpu.permissionid = p.permissionid
left join urls u
  on gpu.urlid = u.urlid

see SQL Fiddle with Demo

Answer 2

select case when count(1) > 0 then 'come in' else 'go away' end 
from users, groups, urls
where 
users.username = '$username' and
users.user_group = groups.user_group and
  ((urls.create = '$url' and groups.create = 1) or 
   (urls.view = '$url' and groups.view = 1) or 
   (urls.system_admin = '$url' and groups.system_admin = 1))

Answer 3

By comparing the $current_page with the results of an IN() subquery, you can do this in one query. If the page matches any listed in a column the user has permission for, this will return a row. It should not return any row if there is no match in an allowed column.

SELECT
  groups.create,
  groups.view, 
  groups.system_admin,
  1 AS can_access
FROM 
  users
  JOIN groups ON users.group = groups.user_group 
WHERE 
  users.username = '$some_username'
  AND (
    /* Substitute the current page. Better, use a prepared statement placeholder if your API supports it */
    (groups.create = 1 AND '$current_page' IN (SELECT DISTINCT create FROM urls))
    OR 
    (groups.view = 1 AND '$current_page' IN (SELECT DISTINCT view FROM urls))
    OR 
    (groups.system_admin = 1 AND '$current_page' IN (SELECT DISTINCT system_admin FROM urls))
  )

This works by comparing the $current_page to the distinct set of possible values from each of your 3 columns. If it matches a column and also the user's group has permission on that type, a row is returned.