MySQL: Select Items for a navigation based on binary rights

Question

I've set up a simple rights management, based on binary addition.

Every navigation entry in my database follows this structure:

and so on..

Every user has his own access level..

user = 3 (1+2 - access level) admin = 15 (1+2+4+8)

So far so good. My problem is now, selecting the corresponding fields from the table via MySQL. My first approach was to select all entries with a access_level <= the sum of the users access_level, but very soon I realized that this also includes other pages with a lower access_level.

Answer 1

You must use a binary AND to see if the Bit is set. you also can use a set field. it is also a bitfield with naming each bit.

AND like this:

WHERE (access_level & 15);

sample - create table and fill

mysql> CREATE TABLE `access_table` (
    ->   `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `item_text` VARCHAR(32) DEFAULT NULL,
    ->   `url` VARCHAR(32) DEFAULT NULL,
    ->   `access_level` SET('home','help','backend','admin') DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0,02 sec)

mysql>
mysql> INSERT INTO `access_table` (`id`, `item_text`, `url`, `access_level`)
    -> VALUES
    ->     (1, 'home', '/home', 'home'),
    ->     (2, 'help', '/help', 'help'),
    ->     (3, 'backend', '/backend', 'backend'),
    ->     (4, 'hoadmpage', '/admin', 'admin');
Query OK, 4 rows affected (0,00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

sample - show all rows and select some via bits

mysql> SELECT *,access_level+0 FROM ACCESS_TABLE;
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
|  4 | hoadmpage | /admin   | admin        |              8 |
+----+-----------+----------+--------------+----------------+
4 rows in set (0,00 sec)

mysql>
mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 15);
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
|  4 | hoadmpage | /admin   | admin        |              8 |
+----+-----------+----------+--------------+----------------+
4 rows in set (0,00 sec)

mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 7);
+----+-----------+----------+--------------+----------------+
| id | item_text | url      | access_level | access_level+0 |
+----+-----------+----------+--------------+----------------+
|  1 | home      | /home    | home         |              1 |
|  2 | help      | /help    | help         |              2 |
|  3 | backend   | /backend | backend      |              4 |
+----+-----------+----------+--------------+----------------+
3 rows in set (0,00 sec)

mysql> SELECT *,access_level+0
    -> FROM ACCESS_TABLE
    -> WHERE (access_level & 9);
+----+-----------+--------+--------------+----------------+
| id | item_text | url    | access_level | access_level+0 |
+----+-----------+--------+--------------+----------------+
|  1 | home      | /home  | home         |              1 |
|  4 | hoadmpage | /admin | admin        |              8 |
+----+-----------+--------+--------------+----------------+
2 rows in set (0,00 sec)

mysql>