From 26229c110fb92646b36c7b2f48ac7518fd3810a5 Mon Sep 17 00:00:00 2001
From: George Halkiadakis <gchalkiadakis@sklavenitis.co.gr>
Date: Fri, 17 Mar 2023 02:19:27 +0200
Subject: set algorithns for user authentication and authorization

---
 html/app/models/admin/User_model.php | 227 +++++++++++++++++++++++++++++++++++
 1 file changed, 227 insertions(+)
 create mode 100644 html/app/models/admin/User_model.php

(limited to 'html/app/models/admin/User_model.php')

diff --git a/html/app/models/admin/User_model.php b/html/app/models/admin/User_model.php
new file mode 100644
index 0000000..63d826f
--- /dev/null
+++ b/html/app/models/admin/User_model.php
@@ -0,0 +1,227 @@
+<?php
+
+namespace app\models\admin;
+
+use \Registry;
+use app\models\admin\history;
+
+class User_model
+{
+
+    /** check user (by index key)
+     * 
+     * @param $indexKey (string) : key for user identification
+     * @param $value (string)
+     * 
+     * NOTE: $indexKey MUST BE a unique key
+     * 
+     * @return $user : [array] or false
+     */
+    public static function checkUser($indexKey, $value)
+    {
+        $user = Registry::use('database')->query(
+            "SELECT * FROM user WHERE {$indexKey} = :val",
+            [ ':val' => $value ]
+        )->getFirst();
+       
+        // if no user, return false
+        if ($user === false)     return false;
+       
+        return $user;
+    }
+
+
+    /** get user (by index key)
+     * 
+     * user detailed array
+     * includes all user properties + granted roles + privileges
+     * 
+     * @param $id (int) : user id
+     * @param $value (string)
+     */
+    public static function getUser($id)
+    {
+        $user = Registry::use('database')->query(
+            "SELECT user.*,
+            (   -- construct array (json) of roles granted to user
+                SELECT CONCAT(
+                    '[',
+                    GROUP_CONCAT(role.id),
+                    ']'
+                )
+                FROM `role`
+                WHERE role.id IN (
+                    SELECT user_role.role_id
+                    FROM user_role
+                    WHERE user_role.user_id = :id
+                )
+            ) AS Roles_json,
+            (   -- construct array of (root-)privileges granted to user
+                SELECT CONCAT(
+                    '[',
+                    GROUP_CONCAT(privilege.id),
+                    ']'
+                )
+                FROM privilege
+                WHERE privilege.id IN (
+                    SELECT user_privilege.privilege_id
+                    FROM user_privilege
+                    WHERE user_privilege.user_id = :id
+                )
+            ) AS RootPrivileges_json,
+            (
+                SELECT CONCAT(  -- array of array of sub-privileges
+                    '[',
+                    GROUP_CONCAT(
+                        (
+                            SELECT CONCAT(  -- array (json) of subprivileges
+                                '[',
+                                GROUP_CONCAT(included_id),
+                                ']'
+                            )
+                            FROM privilege_includes
+                            WHERE privilege_id = privilege.id
+                        )
+                    ),
+                    ']'
+                )
+                FROM privilege
+                WHERE privilege.id IN (
+                    SELECT user_privilege.privilege_id
+                    FROM user_privilege
+                    WHERE user_id = :id
+                )
+            ) AS SubPrivileges_json
+            FROM user
+            WHERE id = :id",
+            [ ':id' => $id ]
+        )->getFirst();
+
+        
+        // if no user, return false
+        if ($user === false)     return false;
+
+
+        // TODO:
+        // * merge root+sub privilede lists
+        // * convert json strings to php arrays
+
+
+        // TODO:
+        // cache user super array
+      
+        return $user;
+    }
+
+
+    /** create user
+     * 
+     */
+    public static function createUser($data, $privileges = ['ec.1', 'pr.1'])
+    {
+        $user = [
+            'prefix' => $data['prefix'],
+            'name' => $data['name'],
+            'surname' => $data['surname'],
+            'email' => $data['email'],
+            'password' => $data['password'],
+            'active' => 0       // needs email confirmation
+        ];
+
+        $sql = "INSERT INTO user (`prefix`, `name`, `surname`, email, `password`, user_id)
+            VALUES (:prrfic, :name, :surname, :email, :password, :userid)";
+		$stmt = $this->pdo->prepare($sql);
+		$stmt->execute($user);
+
+        $inserted_id = $this->pdo->lastInsertId();
+
+        // set default privileges
+        self::set_user_privileges($privileges);
+
+        // update history
+
+
+      
+		return [ "success" => true, 'id' => $inserted_id ];
+    }
+
+
+
+
+    /** inherited privileges
+     * 
+     * list of all inhereted (sub-)privileges
+     * from a list of root-privileges
+     * 
+     * @param $list (array of int): list of root privilege id(s)
+     */
+    public static function inheritedPrivileges($list)
+    {
+        $rootList = "(". implode(', ', $list) .")";
+
+        $subPrivileges = Registry::use('database')->runQuery(
+            "SELECT privilege.id,   (
+                SELECT CONCAT('[', GROUP_CONCAT(included_id), ']')   FROM privilege_includes
+                WHERE privilege_id = privilege.id
+            ) as subprivilege_json
+            FROM privilege
+            WHERE privilege.id IN {$rootlist}", []
+        );
+    }
+
+
+
+}
+
+/* example query getUser (super-array)
+--- -- -- - - -
+
+SELECT user.*,
+(   -- array (json) of roles granted to user
+    SELECT CONCAT('[', GROUP_CONCAT(role.id), ']')
+    FROM `role`
+    WHERE role.id IN (
+        SELECT user_role.role_id
+        FROM user_role
+        WHERE user_role.user_id = 1
+    )
+) AS Roles_json,
+(
+    SELECT CONCAT(
+        '[',
+        GROUP_CONCAT(privilege.id),
+        ']'
+    )
+    FROM privilege
+    WHERE privilege.id IN (
+        SELECT user_privilege.privilege_id
+        FROM user_privilege
+        WHERE user_privilege.user_id = 1
+    )
+) AS RootPrivileges_json,
+(
+    SELECT CONCAT(  -- array of array of sub-privileges
+        '[',
+        GROUP_CONCAT(   -- array (json) of subprivileges
+            (
+                SELECT CONCAT(
+                    '[',
+                    GROUP_CONCAT(included_id),
+                    ']'
+                )
+                FROM privilege_includes
+                WHERE privilege_id = privilege.id
+            )
+        ),
+        ']'
+    )
+    FROM privilege
+    WHERE privilege.id IN (
+        SELECT user_privilege.privilege_id
+        FROM user_privilege
+        WHERE user_id = 1
+    )
+) AS SubPrivileges_json
+FROM user
+WHERE id = 1
+--- */
\ No newline at end of file
-- 
cgit v1.2.3