Search code examples
mysqlacl

MySql query to manage user rights

I am actually stuck on a MySql Query :

I have two tables :

Access

Access Table

Version

Version Table

The Version table is used to store software versions. I have several softwares that can have several versions.

Versions from the same "family" ( = same software) have a common root value. The lvl column describes the software's version : v1 is level 0 , v3 is level 2 etc.

The access table describes what versions an user is allowed to access. The access can be enabled or not.

vstart_id and vend_id are used to describe the first and the last version the user is allowed to access (we assume that vstart_id and vend_id are reffering to versions with the same root.)

The last constraint :

vstart_id | vend_id    
--------------------
  NULL    |  NULL       The user can access ALL versions
   3      |  NULL       The user can access all versions from the version n°3
  NULL    |   7         The user cannot access version higher than the version n°7
   3      |   6         The user can access all versions from 3 (included) to 6 (included)

I Really don't know how to do the query answering the following question :

What versions is user n°2 allowed to access ?

Here is the database structure and some data for testing 

    CREATE TABLE IF NOT EXISTS `access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL,
  `root` int(11) NOT NULL,
  `vstart_id` int(11) DEFAULT NULL,
  `vend_id` int(11) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `access` (`id`, `enabled`, `root`, `vstart_id`, `vend_id`, `user_id`) VALUES
(1, 1, 1, 2, 3, 1),
(2, 1, 2, 5, 7, 1),
(3, 1, 2, 4, NULL, 2),
(4, 1, 1, NULL, 2, 2),
(5, 1, 2, NULL, 7, 3),
(7, 1, 1, NULL, NULL, 4);

CREATE TABLE IF NOT EXISTS `version` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `root` int(11) NOT NULL,
  `lvl` int(11) NOT NULL,
  `title` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `version` (`id`, `root`, `lvl`, `title`) VALUES
(1, 1, 0, 'A - V1'),
(2, 1, 1, 'A - V2'),
(3, 1, 2, 'A - V3'),
(4, 2, 0, 'B - V1'),
(5, 2, 1, 'B - V2'),
(6, 2, 2, 'B - V3'),
(7, 2, 3, 'B - V4'),
(8, 2, 4, 'B - V5'),
(9, 2, 5, 'B - V6');

Thanks in advance

Solution

  • Assuming an user has a maximum of one access record per root, start and end id in access referring to version.id:

    SELECT v.*
FROM `access` a
    INNER JOIN version v ON (v.root = a.root)
WHERE a.user_id = 2
    AND v.id >= IFNULL(a.vstart_id, 0)
    AND (v.id <= a.vend_id OR a.vend_id IS NULL)

    Get version info based on the min and max lvl from the linked root records

    SELECT v.*
FROM version v
INNER JOIN(SELECT MIN(v.lvl) AS MinLvl, MAX(v.lvl) as MaxLvl, v.root
    FROM `access` a
    INNER JOIN version v ON (v.root = a.root)
    WHERE a.user_id = 2
    GROUP BY root
) t1 ON (t1.root = v.root AND v.lvl >= t1.MinLvl AND v.lvl <= t1.MaxLvl)

    http://sqlfiddle.com/#!2/09b3c/10

    Get version info based on lvl from vstart_id and vend_id (note that the lvl of vstart_id must be lower than the lvl of vend_id):

    SELECT v.*
FROM version v
  INNER JOIN (SELECT a.root, v.lvl as StartLvl, v2.lvl AS EndLvl
    FROM access a
      LEFT JOIN version v ON (v.id = a.vstart_id AND v.root = a.root)
      LEFT JOIN version v2 ON (v2.id = a.vend_id AND v2.root = a.root)
    WHERE a.user_id = 2
) t1 ON (t1.root = v.root 
          AND CASE WHEN StartLvl IS NULL THEN 1=1 ELSE v.lvl >= StartLvl END
          AND CASE WHEN EndLvl IS NULL THEN 1=1 ELSE v.lvl <= EndLvl END
)

    http://sqlfiddle.com/#!2/09b3c/28