Changes between Version 1 and Version 2 of AdvancedDatabaseDevelopment
- Timestamp:
- 08/23/26 20:16:07 (4 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedDatabaseDevelopment
v1 v2 1 1 = Advanced Database Development = 2 2 3 Забелешка: наменето за PostgreSQL '''project''' шемата (v04). Во v04: 4 security_alerts.id е INTEGER IDENTITY (не се внесува рачно), computer_id е INTEGER, 5 timestamp е TIMESTAMP, resolved е BOOLEAN. Затоа INSERT-ите не внесуваат id, 6 користат NOW() (не текст) и false (не 'false'). 7 3 8 == Автоматска детекција и пријавување на безбедносни аномалии == 4 9 5 Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број Sysmon настани во краток период, или кога ресурсната потрошувачка (CPU, RAM) ги надминува критичните прагови. Без автоматска детекција, администраторите мора рачно да ги прегледуваат логовите, што е неефикасно и бавно.10 Системот треба автоматски да детектира невообичаено однесување на компјутерите и да генерира безбедносни предупредувања без рачна интервенција. Ова вклучува ситуации кога одреден компјутер генерира голем број Sysmon настани во краток период, или кога ресурсната потрошувачка (CPU, RAM) ги надминува критичните прагови. 6 11 7 12 === Implementation === … … 19 24 IF NEW.cpu_usage > 90 THEN 20 25 INSERT INTO security_alerts( 21 id,computer_id, alert_type, severity,26 computer_id, alert_type, severity, 22 27 description, timestamp, resolved 23 28 ) 24 29 VALUES ( 25 gen_random_uuid()::text, 26 NEW.computer_id::text, 30 NEW.computer_id, 27 31 'High CPU Usage', 28 32 'HIGH', 29 33 'CPU usage exceeded 90%', 30 NOW() ::text,31 'false'34 NOW(), 35 false 32 36 ); 33 37 END IF; … … 36 40 $$ LANGUAGE plpgsql; 37 41 42 DROP TRIGGER IF EXISTS cpu_alert_trigger ON computer_history; 38 43 CREATE TRIGGER cpu_alert_trigger 39 44 AFTER INSERT ON computer_history … … 52 57 IF NEW.ram_usage > 90 THEN 53 58 INSERT INTO security_alerts( 54 id,computer_id, alert_type, severity,59 computer_id, alert_type, severity, 55 60 description, timestamp, resolved 56 61 ) 57 62 VALUES ( 58 gen_random_uuid()::text, 59 NEW.computer_id::text, 63 NEW.computer_id, 60 64 'High RAM Usage', 61 65 'HIGH', 62 66 'RAM usage exceeded 90%', 63 NOW() ::text,64 'false'67 NOW(), 68 false 65 69 ); 66 70 END IF; … … 69 73 $$ LANGUAGE plpgsql; 70 74 75 DROP TRIGGER IF EXISTS ram_alert_trigger ON computer_history; 71 76 CREATE TRIGGER ram_alert_trigger 72 77 AFTER INSERT ON computer_history … … 78 83 79 84 Процедура за детекција на сомнителна активност врз основа на Sysmon настани. 80 Ако еден компјутер генерирал повеќе од 50 Sysmon настани во последниот час, автоматски се внесува безбедносно предупредување. Оваа процедура може да се извршува периодично (на пр. преку `pg_cron`):85 Ако еден компјутер генерирал повеќе од 50 Sysmon настани во последниот час, автоматски се внесува безбедносно предупредување. Може да се извршува периодично (на пр. преку `pg_cron`): 81 86 82 87 {{{ … … 86 91 BEGIN 87 92 INSERT INTO security_alerts( 88 id,computer_id, alert_type, severity,93 computer_id, alert_type, severity, 89 94 description, timestamp, resolved 90 95 ) 91 96 SELECT 92 gen_random_uuid()::text, 93 computer_id::text, 97 computer_id, 94 98 'Suspicious Activity', 95 99 'HIGH', 96 100 'More than 50 Sysmon events in the last hour', 97 NOW() ::text,98 'false'101 NOW(), 102 false 99 103 FROM sysmon_events 100 WHERE CAST(timestamp AS timestamp)> NOW() - INTERVAL '1 hour'104 WHERE timestamp > NOW() - INTERVAL '1 hour' 101 105 GROUP BY computer_id 102 106 HAVING COUNT(*) > 50; … … 108 112 109 113 Поглед за безбедносен преглед на системот. 110 Обезбедува брз преглед на вкупниот број Sysmon настани по компјутер , корисен за администраторите при секојдневен мониторинг:111 112 {{{ 113 CREATE VIEW security_summary_view AS114 Обезбедува брз преглед на вкупниот број Sysmon настани по компјутер: 115 116 {{{ 117 CREATE OR REPLACE VIEW security_summary_view AS 114 118 SELECT 115 119 c.name, … … 124 128 == Автоматско одржување и чистење на историски податоци == 125 129 126 Со текот на времето, табелите со историски податоци (`sysmon_events`, `computer_history`) акумулираат голем број записи кои ги успоруваат пребарувањата и ја зголемуваат потрошувачката на дисковен простор. Потребен е механизам за автоматско бришење на застарени записи без рачна интервенција, со цел одржување на перформансите на системот на долг рок.130 Со текот на времето, историските табели (`sysmon_events`, `computer_history`, `computer_processes_history`, `network_connections_history`) акумулираат голем број записи кои ги успоруваат пребарувањата и го зголемуваат дисковниот простор. Потребен е механизам за автоматско бришење застарени записи. 127 131 128 132 === Implementation === … … 130 134 ==== Stored Procedures/Functions ==== 131 135 132 Процедура за автоматско отстранување настари Sysmon логови (постари од 90 дена):136 Процедура за отстранување стари Sysmon логови (постари од 90 дена): 133 137 134 138 {{{ … … 138 142 BEGIN 139 143 DELETE FROM sysmon_events 140 WHERE CAST(timestamp AS timestamp)< NOW() - INTERVAL '90 days';141 END; 142 $$; 143 }}} 144 145 Процедура за автоматско отстранување настари записи од `computer_history` (постари од 180 дена):144 WHERE timestamp < NOW() - INTERVAL '90 days'; 145 END; 146 $$; 147 }}} 148 149 Процедура за отстранување стари записи од `computer_history` (постари од 180 дена): 146 150 147 151 {{{ … … 151 155 BEGIN 152 156 DELETE FROM computer_history 153 WHERE CAST(timestamp AS timestamp) < NOW() - INTERVAL '180 days'; 154 END; 155 $$; 156 }}} 157 158 Двете процедури може да се закажат за периодично извршување преку `pg_cron`: 159 160 {{{ 161 -- Извршување секој ден во полноќ 157 WHERE timestamp < NOW() - INTERVAL '180 days'; 158 END; 159 $$; 160 }}} 161 162 Процедура за чистење на историските табели за процеси и мрежни конекции (v04), постари од 90 дена: 163 164 {{{ 165 CREATE OR REPLACE PROCEDURE cleanup_old_history() 166 LANGUAGE plpgsql AS 167 $$ 168 BEGIN 169 DELETE FROM computer_processes_history 170 WHERE timestamp < NOW() - INTERVAL '90 days'; 171 172 DELETE FROM network_connections_history 173 WHERE timestamp < NOW() - INTERVAL '90 days'; 174 END; 175 $$; 176 }}} 177 178 Процедурите може да се закажат за периодично извршување преку `pg_cron`: 179 180 {{{ 181 -- Извршување секој ден во раните утрински часови 162 182 SELECT cron.schedule('0 0 * * *', $$CALL cleanup_old_sysmon_events()$$); 163 183 SELECT cron.schedule('0 1 * * *', $$CALL cleanup_old_computer_history()$$); 184 SELECT cron.schedule('0 2 * * *', $$CALL cleanup_old_history()$$); 164 185 }}} 165 186 … … 168 189 == Аналитички погледи за брз пристап до системски информации == 169 190 170 Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите и нивната активност. Директното пресметување на овие информации при секое барање е бавно кога базата содржи голем број записи. Погледите и материјализираните погледи овозможуваат побрз пристап до вакви статистики.191 Администраторите и апликацијата честопати бараат консолидирани информации за состојбата на компјутерите. Директното пресметување при секое барање е бавно кога базата содржи голем број записи. Погледите и материјализираните погледи овозможуваат побрз пристап. 171 192 172 193 === Implementation === … … 175 196 176 197 Детален поглед на компјутерите. 177 Консолидиран приказ на основните информации за сите компјутери во системот, без потреба од повторување на JOIN логиката во апликацијата:178 179 {{{ 180 CREATE VIEW computer_details_view AS198 Консолидиран приказ на основните информации за сите компјутери: 199 200 {{{ 201 CREATE OR REPLACE VIEW computer_details_view AS 181 202 SELECT 182 203 name, … … 190 211 191 212 Поглед за активни (online) компјутери. 192 Прикажува само компјутери кои билеактивни во последните 5 минути, корисен за real-time dashboard:193 194 {{{ 195 CREATE VIEW active_computers_view AS213 Прикажува само компјутери активни во последните 5 минути, корисен за real-time dashboard: 214 215 {{{ 216 CREATE OR REPLACE VIEW active_computers_view AS 196 217 SELECT 197 218 c.id, … … 201 222 c.last_seen 202 223 FROM computers c 203 WHERE CAST(c.last_seen AS timestamp)>= NOW() - INTERVAL '5 minutes';224 WHERE c.last_seen >= NOW() - INTERVAL '5 minutes'; 204 225 }}} 205 226 … … 207 228 208 229 Материјализиран поглед за најактивни компјутери. 209 За разлика од обичните погледи, резултатите се физички зачувани, што овозможува значителнопобрзо извршување на аналитички пребарувања врз голем број историски записи:210 211 {{{ 212 CREATE MATERIALIZED VIEW most_active_computers AS230 Резултатите се физички зачувани, што овозможува побрзо извршување на аналитички пребарувања врз голем број историски записи: 231 232 {{{ 233 CREATE MATERIALIZED VIEW IF NOT EXISTS most_active_computers AS 213 234 SELECT 214 235 c.id, … … 220 241 }}} 221 242 222 Рачно освежување на материјализираниот поглед (може да се закаже периодично):243 Рачно (или закажано) освежување на материјализираниот поглед: 223 244 224 245 {{{ … … 230 251 == Статистички извештаи по околини (environments) == 231 252 232 Администраторите треба да имаат увид во тоа колку компјутери се регистрирани во секоја околина, со цел следење на растот на системот и планирање на капацитети. Оваа информација е корисна за квартални извештаи и при носење одлуки за скалирање.253 Администраторите треба увид во тоа колку компјутери се регистрирани во секоја околина, за следење на растот и планирање капацитети. 233 254 234 255 === Implementation === … … 236 257 ==== Stored Procedures/Functions ==== 237 258 238 Процедура за генерирање статистика по околини. 239 Ја пресметува и ја испечатува (RAISE NOTICE) распределбата на компјутери по секоја околина: 259 Процедура за генерирање статистика по околини (RAISE NOTICE): 240 260 241 261 {{{ … … 258 278 }}} 259 279 260 Функција која враќа статистика по околини во форма на табела (погодна за употреба воапликацијата):280 Функција која враќа статистика по околини во форма на табела (погодна за апликацијата): 261 281 262 282 {{{
