Differences

This shows you the differences between two versions of the page.

--- rb-projects-access:start [17/07/2024 11:15] – created - external edit 127.0.0.1
+++ rb-projects-access:start [21/02/2025 13:25] (current) – [Access Control] andrew
@@ Line 1: / Line 1: @@
 ====== Access Control ======
+{{ :rb-projects-access:hid_reader_and_mifare_card.jpg?200|}}
 Very much embryonic and alpha project to play with commands which would be required to implement a electronic door lock system.
 Key cards with a serial number are issued and each door has a Wiegand or rfid style reader, optionally requiring a PIN as well as card to unlock. Maybe bluetooth instead of PIN?
-Also, by abstracting the keypad / reader and lock from the physical device, it should enable MQTT enabled devices to be included. If they are to be trusted..... So PKI is probably needed to maintain trust and security. (Trust but Verify!)
+Also, by abstracting the keypad / reader and lock from the physical device, it should enable MQTT enabled devices to be included. If they are to be trusted..... So PKI is probably needed to maintain trust and security.  [[https://en.wikipedia.org/wiki/Trust,_but_verify| (Trust but Verify!) ]]
+[[https://dbdiagram.io/d]]
+[[https://www.usmartcards.co.uk/Access-control]]
+===== Flow =====
+person present card to reader
+card id read and submitted to control system with reader (door) id
+card id used to get user and door information from db
+<code mysql>
+-> > select userID, cardID from keycards where cardNumber = 1234;
++--------+--------+
+| userID | cardID |
++--------+--------+
+|      3 |      1 |
++--------+--------+
+</code>
+note cardID is an index to the table, use this as it allows a card ID to be changed if a card is lost
+select door information from readerID to decide if PIN is needed.
+<code mysql>
+ select * from doors where readerID = 7678;
++--------+----------+-----------+---------+
+| doorId | readerId | doorDesc  | pinReqd |
++--------+----------+-----------+---------+
+|      3 |     7678 | Back door |       1 |
++--------+----------+-----------+---------+
+</code>
+Get access level for user, I don't really like this, I need a way to record info for many users for many doors whilst remaining workable. My idea to store JSON in the SQL was a way to combine a NoSQL style db with MySQL. Seemed cool at the time.....
+<code sql> select accesslevel from access where userID = 3;
++------------------------------------------------------------+
+| accesslevel                                                |
++------------------------------------------------------------+
+| {
+: "denied",
+: "denied",
+: "denied"
+} |
++------------------------------------------------------------+
+</code>
+so card number 1234 is cardID 1 and  userID 3.
+presented to reader 7678 which is doorID 3 back door, as it is external it requires a PIN as well
+userID 3 has access level to doorID "3" of "denied".
 ===== DB structure =====
+<code mysql>
+show tables;
++-------------------------+
+| Tables_in_accesscontrol |
++-------------------------+
+| access                  |
+| doors                   |
+| keycards                |
+| users                   |
++-------------------------+
+rows in set (0.000 sec)
+</code>
+<code mysql>
+> show create table access \G;
+*************************** 1. row ***************************
+       Table: access
+Create Table: CREATE TABLE `access` (
+  `accessID` int(11) NOT NULL,
+  `userID` int(11) NOT NULL,
+  `accesslevel` varchar(1024) NOT NULL,
+  PRIMARY KEY (`accessID`),
+  UNIQUE KEY `userID` (`userID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
+row in set (0.001 sec)
+> describe access;
++-------------+---------------+------+-----+---------+-------+
+| Field       | Type          | Null | Key | Default | Extra |
++-------------+---------------+------+-----+---------+-------+
+| accessID    | int(11)       | NO   | PRI | NULL    |       |
+| userID      | int(11)       | NO   | UNI | NULL    |       |
+| accesslevel | varchar(1024) | NO   |     | NULL    |       |
++-------------+---------------+------+-----+---------+-------+
+</code>
+<code mysql>
+show create table doors \G;
+*************************** 1. row ***************************
+       Table: doors
+Create Table: CREATE TABLE `doors` (
+  `doorID` int(11) NOT NULL AUTO_INCREMENT,
+  `readerID` int(11) NOT NULL,
+  `doorDesc` varchar(255) NOT NULL,
+  `pinReqd` tinyint(1) NOT NULL,
+  PRIMARY KEY (`doorID`),
+  UNIQUE KEY `doorDesc` (`doorDesc`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
+row in set (0.000 sec)
+> describe doors;
++----------+--------------+------+-----+---------+----------------+
+| Field    | Type         | Null | Key | Default | Extra          |
++----------+--------------+------+-----+---------+----------------+
+| doorID   | int(11)      | NO   | PRI | NULL    | auto_increment |
+| readerID | int(11)      | NO   |     | NULL    |                |
+| doorDesc | varchar(255) | NO   | UNI | NULL    |                |
+| pinReqd  | tinyint(1)   | NO   |     | NULL    |                |
++----------+--------------+------+-----+---------+----------------+
+</code>
+<code mysql>
+show create table keycards \G;
+*************************** 1. row ***************************
+       Table: keycards
+Create Table: CREATE TABLE `keycards` (
+  `cardID` int(11) NOT NULL,
+  `cardNumber` varchar(256) NOT NULL,
+  `Created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `Deleted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  `userID` int(11) NOT NULL,
+  PRIMARY KEY (`cardID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
+row in set (0.000 sec)
+> describe keycards;
++------------+--------------+------+-----+---------------------+-------------------------------+
+| Field      | Type         | Null | Key | Default             | Extra                         |
++------------+--------------+------+-----+---------------------+-------------------------------+
+| cardID     | int(11)      | NO   | PRI | NULL                |                               |
+| cardNumber | varchar(256) | NO   |     | NULL                |                               |
+| Created    | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+| Deleted    | timestamp    | NO   |     | 0000-00-00 00:00:00 |                               |
+| userID     | int(11)      | NO   |     | NULL                |                               |
++------------+--------------+------+-----+---------------------+-------------------------------+
+</code>
+<code mysql>
+show create table users \G;
+*************************** 1. row ***************************
+       Table: users
+Create Table: CREATE TABLE `users` (
+  `userID` int(11) NOT NULL,
+  `username` varchar(256) NOT NULL,
+  `firstname` varchar(256) NOT NULL,
+  `surname` varchar(256) NOT NULL,
+  `pin` int(11) NOT NULL,
+  `created` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
+  `deleted` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
+  PRIMARY KEY (`userID`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
+row in set (0.001 sec)
+ describe users;
++-----------+--------------+------+-----+---------------------+-------------------------------+
+| Field     | Type         | Null | Key | Default             | Extra                         |
++-----------+--------------+------+-----+---------------------+-------------------------------+
+| userID    | int(11)      | NO   | PRI | NULL                |                               |
+| username  | varchar(256) | NO   |     | NULL                |                               |
+| firstname | varchar(256) | NO   |     | NULL                |                               |
+| surname   | varchar(256) | NO   |     | NULL                |                               |
+| pin       | int(11)      | NO   |     | NULL                |                               |
+| created   | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+| deleted   | timestamp    | NO   |     | 0000-00-00 00:00:00 |                               |
++-----------+--------------+------+-----+---------------------+-------------------------------+
+</code>