From 3d93817b855dcbe1c4d2573faa2901ae3db0f7d8 Mon Sep 17 00:00:00 2001
From: alex <alex@alexloehr.net>
Date: Fri, 28 Nov 2025 10:29:25 +0000
Subject: [PATCH] audit
---
lib/db.js | 109 ++++++++++++++++++++++++++++++++++++------------------
1 files changed, 72 insertions(+), 37 deletions(-)
diff --git a/lib/db.js b/lib/db.js
index f552b95..dd4dc76 100644
--- a/lib/db.js
+++ b/lib/db.js
@@ -58,8 +58,8 @@
setStatus,
- getCourseAdmins,
- getCoursesWithNoAdmins,
+ getCourseAdminRoles,
+ getCourseWithoutAdminRoles,
}
/////////////////////////////////////////////////////////////////////////
@@ -680,50 +680,85 @@
/////// ADMINS ////////////////////////////////////////////////////////////////
-async function getCourseAdmins() {
+
+async function getCourseAdminRoles() {
const pool = await poolP
- const q = `SELECT om.obj_id as kurs_obj_id,
- t.ref_id as kurs_ref_id,
- om.usr_id,
- ud.login,
- om.admin,
- ud.firstname,
- ud.lastname,
- od2.title
- FROM ${database}.obj_members om
- INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id
- INNER JOIN ${database}.object_data od2 ON od2.obj_id = om.obj_id
- INNER JOIN ${database}.object_reference t ON t.obj_id = om.obj_id
- WHERE om.admin = 1
+ const q = `
+ SELECT od.obj_id as crs_obj_id, t.ref_id as crs_ref_id, od.title as crs_title, rf.rol_id, od2.title as role
+ FROM ${database}.object_data od
+ INNER JOIN ${database}.object_reference t ON t.obj_id = od.obj_id
+ INNER JOIN ${database}.rbac_fa rf ON rf.parent = t.ref_id
+ INNER JOIN ${database}.object_data od2 ON od2.obj_id = rf.rol_id
+ WHERE od.\`type\` = "crs"
+ AND od2.title LIKE "%admin%"
`
const [results] = await pool.query(q)
return results
}
-/**
- * Liefert die Kurse ohne Admins
- * Admins hier definiert als Einträge in obj_members wo admin==1
- * Darüber hinaus gibt es offenbar noch einen anderen Mechanismus über die Rolle.
- * Denn ein Kurs ohne Admin (z.B. lokal Fliesenratgeber ref_id=88) hat in ILIAS
- * trotzdem einen Admin im Screen "Members".
- * Dort wird wohl über die Rolle zugeordnet.
- *
- * Die Frage ist wo der Fehler GS-2333 auftritt.
- * Bei obj_members oder bei fehlender Rolle.
- * @return {Promise<*>}
- */
-async function getCoursesWithNoAdmins() {
+async function getCourseWithoutAdminRoles() {
const pool = await poolP
const q = `
- SELECT asdf.obj_id, t.ref_id, asdf.numTn, asdf.title
- FROM (SELECT om.obj_id, COUNT(*) as numTn, od.title, MAX(om.admin) as maxAdmin
- FROM ${database}.obj_members om
- INNER JOIN ${database}.object_data od ON od.obj_id = om.obj_id
- GROUP by om.obj_id
- ORDER BY numTn DESC) asdf
- INNER JOIN ${database}.object_reference t ON t.obj_id = asdf.obj_id
- WHERE asdf.maxAdmin = 0
+ SELECT adminRoles.crs_obj_id, adminRoles.crs_ref_id, adminRoles.crs_title, adminRoles.rol_id, adminRoles.role
+ FROM (SELECT od.obj_id as crs_obj_id, t.ref_id as crs_ref_id, od.title as crs_title, rf.rol_id, od2.title as role
+ FROM ${database}.object_data od
+ INNER JOIN ${database}.object_reference t ON t.obj_id = od.obj_id
+ INNER JOIN ${database}.rbac_fa rf ON rf.parent = t.ref_id
+ INNER JOIN ${database}.object_data od2 ON od2.obj_id = rf.rol_id
+ WHERE od.\`type\` = "crs"
+ AND od2.title LIKE "%admin%") adminRoles
+ WHERE adminRoles.rol_id NOT IN (SELECT ru2.rol_id
+ FROM ${database}.rbac_ua ru2)
`
const [results] = await pool.query(q)
return results
}
+
+// !! wird nicht gebraucht - Admin Erkennung in ILIAS läuft anders ab - über die Rolle
+// async function getCourseAdmins() {
+// const pool = await poolP
+// const q = `SELECT om.obj_id as kurs_obj_id,
+// t.ref_id as kurs_ref_id,
+// om.usr_id,
+// ud.login,
+// om.admin,
+// ud.firstname,
+// ud.lastname,
+// od2.title
+// FROM ${database}.obj_members om
+// INNER JOIN ${database}.usr_data ud ON ud.usr_id = om.usr_id
+// INNER JOIN ${database}.object_data od2 ON od2.obj_id = om.obj_id
+// INNER JOIN ${database}.object_reference t ON t.obj_id = om.obj_id
+// WHERE om.admin = 1
+// `
+// const [results] = await pool.query(q)
+// return results
+// }
+//
+// /**
+// * Liefert die Kurse ohne Admins
+// * Admins hier definiert als Einträge in obj_members wo admin==1
+// * Darüber hinaus gibt es offenbar noch einen anderen Mechanismus über die Rolle.
+// * Denn ein Kurs ohne Admin (z.B. lokal Fliesenratgeber ref_id=88) hat in ILIAS
+// * trotzdem einen Admin im Screen "Members".
+// * Dort wird wohl über die Rolle zugeordnet.
+// *
+// * Die Frage ist wo der Fehler GS-2333 auftritt.
+// * Bei obj_members oder bei fehlender Rolle.
+// * @return {Promise<*>}
+// */
+// async function getCoursesWithNoAdmins() {
+// const pool = await poolP
+// const q = `
+// SELECT asdf.obj_id, t.ref_id, asdf.numTn, asdf.title
+// FROM (SELECT om.obj_id, COUNT(*) as numTn, od.title, MAX(om.admin) as maxAdmin
+// FROM ${database}.obj_members om
+// INNER JOIN ${database}.object_data od ON od.obj_id = om.obj_id
+// GROUP by om.obj_id
+// ORDER BY numTn DESC) asdf
+// INNER JOIN ${database}.object_reference t ON t.obj_id = asdf.obj_id
+// WHERE asdf.maxAdmin = 0
+// `
+// const [results] = await pool.query(q)
+// return results
+// }
--
Gitblit v1.8.0