ugrás a tartalomhoz

jogosultság kezelés - sql

Theo76 · 2016. Dec. 5. (H), 14.22
Sziasztok!

Egy sql alapú jogosultság kezelő rendszeren dolgozok, és kicsit elakadtam... A feladat leírása:

Felhasználói hieararchia kiépítése:
Csoportok, alcsoportok

Egy alcsoport tartozhat több csoporthoz, és egy csoportnak több alcsoportja lehet.
Pl.:
                 Cég
                  |
             Felsővezető
               |     |
         Osztály1   Osztály2
            |          |
   Osztály vezető   Osztály vezető
stb...
Az azonos alcsoportoknak azonos jogaik vannak, de a főcsoport határozza meg, hogy melyik modult láthassa, vagy az adott modulon belül mit tehet, mit nem.

Ehhez még hozzá jön, hogy akár egyénenként is lehet jogokat adni, mert pl lehet, hogy az osztály1 vezetője más elfoglaltság miatt átadja a szerepét egy másik tagnak, vagy akár a másik osztály vezetőjének is.

Eddig nagyjából el is jutottam, hogy hogy építsem fel az adatbázist, viszont ahol elakadtam, hogy vannak menü pontok, és van olyan menü, aminek további almenüji is vannak...
Minden egyes menü, és almenü pont csatlakozik egy modulhoz, illetve a modul egy meghatározott részéhez.
Pl Admin modul:
- Saját adataink módosítása
- Új felhasználó hozzáadása
- Más felhasználó adatainak módosítása
- Hozzáférési jogok adása, módosítasa csoportoknál/alcsoportoknál
...
A modul egyes részeihez nem mindenkinek szabad hozzáférnie.

Itt akadtam el, hogy ebbe a modellbe hogy tudom beilleszteni a menüknek a kezelését, illetve abban, hogy hogy lehet megoldani, ha még nincs bejelentkezve felhasználó, mert egyes menüknek/moduloknak akkor is működnie kellene...

Itt egy kép ameddig eljutottam az adatbázis tervezésében.

Remélem sikerült érthetően leírnom... :) Előre is köszönöm a segítséget, ötleteket!
 
1

Én azt tudom ajánlani, hogy

Tanul0 · 2016. Dec. 5. (H), 17.25
Én azt tudom ajánlani, hogy nézz meg egy nagy keretrendszert, hogy hogyan oldották meg. Nem feltétlenül kell feltalálni a spanyol viaszt, sokan kitaposták már ezt az ösvényt előtted. Itt egy példa cikk:

Acl

Ami a barátod lehet: acl, bitmask stb...
2

köszi átnézem ezeket is, bár

Theo76 · 2016. Dec. 5. (H), 22.29
köszi átnézem ezeket is, bár az angol tudásom nem valami nagy... Amúgy nem akarom feltalálni. Ezt az adatbázis modellt is egy korábbi cikk alapján csináltam, csak kicsit kibővítettem. :)
3

nézegettem a linket... de ez

Theo76 · 2016. Dec. 6. (K), 06.18
nézegettem a linket... de ez nem az amit keresek... nekem a táblaszerkezet kell, ami a modulok, almodulok jogosultság kezelését kezeli, amit elkezdtem kialakítani. Próbáltam belenézni más által írt keretrendszerbe (pl drupal), de úgy meg mint tű a szénakazalban...
5

modul nem kell adatbázisba

Pepita · 2016. Dec. 10. (Szo), 15.15
Szia, egy egyszerű példa:
A usereknek egyénileg tárolod a jogait.
Az egyes modulok (vagy akár csak egy egy funkció) egyetlen fv hívással le tudja kérdezni, hogy adott (aktuális) user rendelkezik e xy joggal. Ha nem -》 hibát dob. Így a modulokat nem kell tárolni (persze lehet, ha mondjuk az is változhat, hogy adott feature hez milyen role kell).

Így a szükséges táblák:
Users, Roles, UsersRoles (*) (kapcsoló tábla), Groups, GroupsRoles (kapcsoló), UsersGroup.

* Azért hasznos, mert ugyan módosításkor kicsit több a matek (insert / update), de lekérdezéskor, ami sokkal gyakrabban van, csak 3 tábla lesz LEFT JOIN, nem 5.

Természetesen Groups lehet fa struktúra is, ha szükséges.

Remélem érthetően írtam le. :)
6

Köszönöm a választ! :) Még

Theo76 · 2016. Dec. 13. (K), 13.29
Köszönöm a választ! :) Még most kezdtem el foglalkozni ezekkel az eljárásokkal... Jól értelmezem az általad leírt táblákat (lehetséges minimális oszlopokkal):

-- Users:
- u_id
- u_login
- password

-- Roles
- r_id
- r_name
- r_role (pl jogosultsági szint)

-- UsersRoles
- ur_u_id (kapcsolódik a Users tábla u_id oszlopához)
- ur_r_id (kapcsolódik a Roles táblához)
- ur_g_id (kapcsolódik a Groups táblához)

-- Groups
- g_id
- g_name

-- GroupsRole
- gr_g_id (kapcsolódik a Groups táblához)

a többinél kicsit elakadtam... A UserGroup táblának mi a konkrét funkciója?
7

UserGroup

Pepita · 2016. Dec. 16. (P), 14.30
Össze kapcsolja a user t egy v több group al.

Menet:
- user t azt mondod, hogy legyen A és B csoport tagja.
- Beírod UsersGroup ba.
- GroupsRoles ből kikeresed, milyen Role ok vannak a csoportokban.
- Ezeket a User Id val be teszed UsersRoles ba.

Amikor a User jogai kellenek, elég csak a User, UsersRoles és Roles táblák join.

(Ami private kérdés volt, kérlek tedd fel itt, hogy ne csak én tudjak válaszolni)

Szerk.
UsersGroup akkor érdekes, ha egy Group változik, akkor ez alapján tudsz végig menni, hogy kiket kell update.
8

Rendben :)

Theo76 · 2016. Dec. 16. (P), 15.27
Sajnos nem tudom hogy tudom így megnézni a levelet visszamenőleg, de erre jutottam az eddigiek alapján (persze az előző eredményemet kicsit átgondoltam. :)):

a táblák részletei
tábla kapcsolatok

A kérdés az lenne, hogy hogy tudnék optimális csoport fát csinálni.
Ahogy ki kellene néznie pl:

Főcsoport: Munkavégzés helye
Alcsoport: Beosztás

Ami még fontos, hogy vannak olyan csoportok, amiknek nincs alcsoportja pl:
oldal tulajdonos
admin

illetve vannak olyan helyzetek mikor valaki több főcsoportba, vagy akár több alcsoportba is tartozhat. Pl helyettesítés, vagy több munkakörben való részvétel.

Hozzá kell tennem, hogy ebben a témában nagyon kezdő vagyok, így a tábla kapcsolatokat is főleg saját kútfőből állítottam össze...
9

eddig jó

Pepita · 2016. Dec. 16. (P), 21.22
A táblák és kapcsolatok jók így, Kb erre gondoltam.
Annyi kis kiegészítés, hogy érdemes következetesnek maradni kis és nagy betű és egyes és többes szám terén a nevekben.

A munkavégzés helye illetve beosztás nem hinném, hogy jogosultsági kérdés lenne.
Eredetileg cégen belüli osztályok és csoportok voltak, ezek vezetői, stb.
Ennek a hierarchiának a kitalálása a te feladatod, a cég vezetőivel egyeztetve. :)
Én nem ismerem a céget és az igényt.

Tetszőleges fa struktúra ki alakítható pl nested set megoldással, ehhez a Groups táblába kell még 2 integer oszlop. (Ha meg találom, linkelem)

Bármennyi csoportba tartozhat egy user, erre szolgál a UsersGroups tábla.
Arra érdemes figyelni, hogy ugyanazt a Role t ugyanazzal a userrel ne szúrd be többször a UsersRoles táblába.

Helyettesítés stb: admin hozzá adja azt a csoportot (Groups), majd mikor már nem kell, elveszi.

Hozzá kell tennem, hogy kezdőtől nagyon jó adatbázis tervet láttam. :)


szerk:

Nested tree
10

Köszönöm. :) Át is rágom

Theo76 · 2016. Dec. 16. (P), 22.43
Köszönöm. :) Át is rágom akkor ezt a témát. :) Mondjuk elég sok netes kutatás van a háttérben igyekeztem tanulmányozni más adatbázis sémákat, többek között egy itteni régebbi cikket is, ami a biztonságos felhasználó kezelést kezdte taglalni, de sajnos a cikket nem folytatta az írója...
Egyébként melyik adatbázis kezelőt érdemesebb használni? Mert bár elterjedt a mysql, van aki mégis a postgresql-t ajánlja... Én eddig mysql-el dolgoztam, de csak nagyon alapszintű
adatbázis kezeléssel, alap táblakapcsolatokkal csak nem rég... Ilyen adatmodellezést meg csak 2-3 hete kezdtem el csinálni. Most feltettem a pgsql-t is. Az esetleges könnyebb váltás miatt már PDO-t használok.
12

db server

Pepita · 2016. Dec. 18. (V), 16.47
Nyugodtan használhatsz mysql t is, tévhitek vannak, hogy esetleg meg szűnik, de ez nem igaz.
Egyes csomagok fizetősek, de ott van pl a MariaDB is, ami elvileg soha nem lesz fizetős. Mondjuk ezt amiatt nem javaslom, hogy sok mindent kérés nélkül kis betűsre alakít...

Annyi, hogy PHP alól ne használd az elavult mysql_ ... fv eket.
Helyette mysqli_ .
11

Átolvastam a linket... A

Theo76 · 2016. Dec. 17. (Szo), 00.23
Átolvastam a linket...
A munkavégzéssel kapcsolatban viszont nem fogalmaztam elég világosan, pontosabban rosszul. :) Ez egy szociális központ, ami több munkacsoportot ölel fel, és minden munkacsoportnak megvannak a maga alcsoportjai, vagyis beosztásai: csoportvezető, alkalmazottak (de az alkalmazottak csoportja is változó, mert van ahol sima beosztottak vannak csak, de van ahol további almunkacsoportok tartoznak egy vezető alá). Ezek a munka csoportok, egyrészt más más adatokat láthatnak, másrészt meg más más modulokat érhetnek el. Az általad linkelt fa szerkezet nagyon jó arra, hogy pl az egyes vezetők csak az alattuk lévő csoportba tartozók adatait láthassák. Bár sajnos a cikk nem folytatódott, pedig lényeges lenne a tábla kitöltése, mert pl egy új csoport felvétele, törlése, vagy épp áthelyezése megváltoztatja a fa kinézetét...
13

Túl bonyolítod

Pepita · 2016. Dec. 18. (V), 16.55
A nested tree cikk szerintem teljes, lehet, hogy nincs minden le is kódolva, de a módszer adott.

Viszont máris jócskán túl lőttünk a célon.

Felhasználó és jogosultság kezelés, ahonnan indultunk. Ezt kell jól megvalósítani, ne tágítsd tovább a scope ot.

Így is ez már túl sok lett: vagy arra van szükség, hogy egy user több group ba tartozhasson, vagy arra, hogy a group ok lehessenek parent - child viszonyban.
A kettő együtt biztosan felesleges.

Az, hogy később milyen láthatóság hogyan dől el, nem része ennek a feladatnak.
Mint mondtam, az pusztán role fv e lehet. Ez a háttér group dolog csak arra való, hogy könnyebben tudd kezelni a Role okat.
Admin felület pedig természetesen tudjon létrehozni Role is, Group is, stb, és minden fajta update is.


Szerk.:

Bocsi, Poetro cikke az igazán jó, itt van insert és update is. De a sorozatban te is megtaláltad volna. :)
14

igen ez igaz. :) Egyébként

Theo76 · 2016. Dec. 20. (K), 16.29
igen ez igaz. :)

Egyébként ebből gondoltam hogy folytatás lesz, és emiatt nem olvastam visszafele:
"Zárószó:
...
A hierarchia módosítása még érdekesebb és egyeben bonyolultabb feladat. Ha a téma érdekel, és lenne kedved továbbdolgozni, örömmel várnám a cikk IV. részét."
15

Ok, neked is van igazad,

Pepita · 2016. Dec. 20. (K), 19.17
Annyit érdemes azért ilyenkor figyelembe venni, hogy mikori a cikk, ahhoz képest van-e újdonság.

Van további kérdésed, vagy boldogulsz?
Ha meg lesz a számodra megfelelő megoldás, én (is) szívesen látnám itt. (És gondolom sokan mások is.)
16

mindenképpen, ha összerakom,

Theo76 · 2016. Dec. 21. (Sze), 11.49
mindenképpen, ha összerakom, akkor kirakom ide is. :) Nagyjából igen... most rakom össze a cikk alapján... egy elakadás már van azért, mert van egy lekérdező sql parancs amin elakadtam, és egyenlőre sehogy nem akar működni (én PDO, és postgreSQL-t használok most ehhez...). A fa rendszert még nem vetettem el, legfőképp a jogosultság szintű lekérdezések miatt, hogy melyik felhasználó melyik városhoz, munkakörhöz, és beosztáshoz tartozik. Ha csak tényleg nem az lesz, hogy túl bonyolítom, és találok egyszerűbbet, csak hát még egyenlőre így kezdem átlátni. :D

Az sql amin elakadtam

SELECT node.g_id AS id,
       node.g_name AS name,
       COUNT(parent.g_name) - 1 AS depth
FROM groups AS node,
     groups AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.g_name
ORDER BY node.lft
ERROR: column "node.g_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT node.g_id AS id, node.g_name AS name, COUNT(*) - 1 AS...
17

hűha

Pepita · 2016. Dec. 21. (Sze), 20.15
- jogosultság: csak a Users és a Roles táblák ból.
- ha egy dump letölthető lenne a jelenlegi db ről, tök jó lenne. :)
- mi a célja ennek a query - nek, hogy saját magával csinál Descartes szorzatot? Valószínűleg nem kell neked ilyen, de egy jól megfogalmazott cél sokat segítene.
18

Bocsi. csak most tudtam

Theo76 · 2016. Dec. 24. (Szo), 08.59
Bocsi. csak most tudtam reagálni.

Az sql kérés nested tree leírásából vettem ki, a részfa lekérdezésének az sql parancsa. Mivel ezeket most tanulom, még nem igazán tudom értelmezni a teljes SELECT utasítást, pontosabban a WHERE ... BETWEEN ... AND ... részét, illetve miért kell két különböző néven meghívni ugyanazon táblát. Ezért is akadtam el ezen a részen.
Dumpot próbáltam készíteni, de valamiért a phppgadmin 0 bytos sql file-t ad vissza, de majd elküldöm az adatbázis designer által generált utasításokat, csak az másik gépen van.
Amin most dolgozok konkrétan, egy szabadság kérő rendszer. Szabadságok kérése, azok két szintű elfogadása, egyszer a szakmai vezetők által, és véglegesen az intézmény vezető által, illetve kimutatások készítése a kivett szabadságokról.
Ahogy a jogosultságoknak ki kell néznie:
- Honlap tulajdonos: teljes jogkör

- Admin: felhasználókat adhat hozzá tesztelheti a rendszert, más felhasználói névre válthat, és így annak nevében szabadság kérelmet, és törlést kezdeményezhet. Mindenkinek az adatait láthatja, módosíthatja

- Intézmény vezető: az összes kérelmet látja, és véglegesítheti, kimutatásokat készíthet, szabadságot kérhet, mindenki adatait láthatja, nem módosíthat, csak a saját személyes adatait

- Könyvelés: szabadságot kérhet, mindenki adatait láthatja, és teljeskörűen módosíthatja

- Szakmai vezető: a saját munkacsoportján belüli kérelmeket látja, azokat engedélyezheti, a saját dolgozóiról kimutatást készíthet, szabadságot kérhet, a dolgozói adatait láthatja, személyes adatait módosíthatja

- Beosztott: szabadságot kérhet, a saját adatait láthatja, saját személyes adatait módosíthatja. Lehet többféle beosztás, de csak a nevük más, jogosultság ugyanaz)



Szakmai vezetők alá tartozó dolgozók felosztása:
- Központ
---Egy város

- Házisegítség nyújtás
---Több város
---vannak szakmai vezetők, akik alá 1-1 város tartozik, de van akihez több is

- Hajléktalan szálló
--- egy város jelenleg

- Idősek nappali klubbja
---Több város
---vannak szakmai vezetők, akik alá 1-1 város tartozik, de van akihez több is

A városoknál lehet átfedés is, hogy pl egy városon belül több munkaterület van.

Van több is, de valójában egy sémára megy már az egész. Ami még megoldott kell hogy legyen, hogy van olyan szakmai vezető, aki alá több munkaterület is tartozik, illetve, ha valaki szabadságra megy, akkor a helyettesítés. Ez akár az intézményvezetőre is, aki pl most intézményvezető, de egyben a hajléktalanok szakmai vezetője is átmenetileg.

Illetve olyanra akarom, hogy hordozható legyen, pontosabban ne erre legyen konkrétan írva, hanem akár más jogosultsági rendszer is felépíthető legyen, ha lehetséges (pl ahol csak simán van pár jogosultsági szint, mint mondjuk egy egyszerűbb webáruháznál)

Csináltam már egy ilyen rendszert nemrég nekik, de ott teljesen alapszintű a jogosultságok kezelése, és pont ezért átláthatatlan, és bonyolult módosítani, és most szeretném ezt egy átláthatóbb, kezelhetőbb felületre lecserélni. Csak ugy most jött ki, hogy eddig csak az általános adatbázis kezelést ismertem, táblakapcsolatok nélkül, így ezeket mind most ezzel tanulom, az oop-vel együtt. :)

Remélem sikerült érthetően megfogalmaznom. :)
És ezúton is kívánok Neked nagyon boldog karácsonyt! És köszönöm, segítségedet. :)
19

Semmi gond :)

Pepita · 2016. Dec. 25. (V), 21.43
Az sql kérés nested tree leírásából vettem ki,

Nem jó cikkből,
miért kell két különböző néven meghívni ugyanazon táblát

Nem biztos, hogy neked kell. Ezért is kérdeztem, hogy mi a cél. Mindig az legyen az első: meghatározni a jól behatárolt objektív célt. Simán lehet, hogy neked elég ez is:
    // most lekérdezzük az elem összes leszármazottját  
    $query = 'SELECT id, lft, rgt FROM '.$this->table.  
             ' WHERE lft BETWEEN '.$row['lft'].' AND '.   
             $row['rgt'].' ORDER BY lft ASC';  
    $result = mysql_query($query);   
Dump hiányában konkrét, te tábláidon végrehajtott SQL-el nem tudok szolgálni..

Specifikációt olvasva egyelőre félre is teheted a nested setet, mert nem kell a megvalósításhoz.

Javaslat1: írj egy listát, kizárólag a jogosultságokról! Ne legyen benne, hogy ki kapja, csak azt sorold fel, hogy az alkalmazásban mit lehet jogosultsághoz kötötten megtenni! Lentről felfelé haladva, pl:
- regisztrálni,
- szabadságot kérni;
- szabit elfogadni 1 szinten,
stb.

Ilyet sose írj le magadnak specifikáció gyanánt, hogy "teljes jogkör", "teljeskörűen módosíthatja", ennél sokkal pontosabban be kell határolni.
Ha a "Honlap tulajdonos" nem te vagy, akkor ezt nem javaslom, inkább legyen erre egy "webfejlesztő" role. Én szoktam olyat meglépni, hogy ez a role id bent van egy eldugott configban is, és az a fv, amivel lekérdezem, hogy adott usernek van-e adott jogosultsága, az minden role esetében true-t ad vissza, ha van a fejlesztői... :)
De nagyon fontos, hogy az egyes funkciók legyenek külön-külön ojghoz kötve, nem pedig az emberek / csoportok. Ez szerintem félre ment, amint nincs kész az első lista jól, addig ne menj tovább.

Javaslat2:
Ne lődd tökön magad olyan funkcióval, hogy
más felhasználói névre válthat, és így annak nevében szabadság kérelmet, és törlést kezdeményezhet
Tesztelésre a Gipsz Jakab admin igenis regisztráljon magának akárhány darab Teszt Eleket, és lesz szíves logout / login útján felhasználót váltani.
Borzasztóan össze fogod keverni magad, ha vmit logolni fogsz, nem fogod tudni, mikor mi kell legyen sessionben, stb stb.
Emellett tudtommal törvény is tiltja az olyan megoldást, hogy a Gipsz Jakab csak úgy Teszt Elek nevében bármit tegyen az ő tudta és beleegyezése nélkül.

A "Szakmai vezetők alá tartozó dolgozók felosztása" részt pedig veheted teljesen külön, ez egy következő lépés lesz, ez nem jogosultsági kérdés.
Főleg akkor nem, ha
olyanra akarom, hogy hordozható legyen ... csak simán van pár jogosultsági szint
Itt a lényeg, pont erre adtam egy lehetséges megoldást, de ehhez még nested set sem kell! Addig marad egyszerű, míg nem bonyolítod túl. :)

Tehát legyen meg az első lista, az alapján lehet fejleszteni, aztán az legyen egy másik issou, hogy intézmény is van, meg a Hirtelenemelkedő dűlőn intézményvezető Marika néni ne tudja a pesti Józsika szabadságát is jóvá hagyni.

Így szemre akiket adminnak, intézmény vezetőnek, stb hívtál, azok lesznek a csoportok. Az csak az első lista után, meg fogod látni, mennyi mindent kihagytál. :) (Én ezt olvasva azt mondanám, hogy szabadság kérést is adnék a vezetőknek is, ne kelljen emiatt dolgozói csoportba is tenni pl.)

Egy fontos részlet még:
Lehet többféle beosztás, de csak a nevük más, jogosultság ugyanaz

Ennek nem sok értelme van. Megint nem jogosultsági kérdés, hanem cég struktúra(?). Egyelőre nem fontos, de elképzelhető, hogy lesz "Pesti-beosztott" és "Pécsi-beosztott" csoport is, nem jogosultság.
Arra figyelj, hogy ne vegyél fel két Id-val gyakorlatilag azonos értéket ("csak a név a más"), akkor az egy másik elem, nem az, amire gondoltál.
20

Most olvasom, és értelmezem

Theo76 · 2016. Dec. 28. (Sze), 17.02
Most olvasom, és értelmezem amiket írtál, addig is megcsináltam a dumpot amit mondtál:

CREATE TABLE "group_roles" (
	"gr_g_id" int4 NOT NULL,
	"gr_r_id" int4 NOT NULL,
	CONSTRAINT "group_roles_pk" PRIMARY KEY("gr_g_id","gr_r_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "group_roles" OWNER TO "postgres";

CREATE TABLE "groups" (
	"g_id" SERIAL NOT NULL,
	"g_name" varchar(150) NOT NULL,
	"lft" int4 NOT NULL,
	"rgt" int4 NOT NULL,
	CONSTRAINT "groups_pk" PRIMARY KEY("g_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "groups" OWNER TO "postgres";

CREATE TABLE "roles" (
	"r_id" SERIAL NOT NULL,
	"r_name" varchar(150) NOT NULL,
	"r_role" varchar NOT NULL,
	CONSTRAINT "roles_pk" PRIMARY KEY("r_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "roles" OWNER TO "postgres";

CREATE TABLE "user_groups" (
	"ug_u_id" int4 NOT NULL,
	"ug_g_id" int4 NOT NULL,
	CONSTRAINT "user_groups_pk" PRIMARY KEY("ug_u_id","ug_g_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "user_groups" OWNER TO "postgres";

CREATE TABLE "user_roles" (
	"ur_u_id" int4 NOT NULL,
	"ur_r_id" int4 NOT NULL,
	CONSTRAINT "user_roles_pk" PRIMARY KEY("ur_u_id","ur_r_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "user_roles" OWNER TO "postgres";

CREATE TABLE "users" (
	"u_id" SERIAL NOT NULL,
	"u_name" varchar(150) NOT NULL,
	"u_email" varchar(255) NOT NULL,
	"u_login" varchar(25) NOT NULL,
	"u_passwd" varchar(64) NOT NULL,
	"u_create" timestamp(0)[] NOT NULL,
	CONSTRAINT "users_pk" PRIMARY KEY("u_id")
)
WITH (
	OIDS = False
);

ALTER TABLE "users" OWNER TO "postgres";

CREATE TABLE "user_denied" (
	"ud_u_id" int4 NOT NULL,
	"ud_r_id" int4 NOT NULL,
	PRIMARY KEY("ud_u_id","ud_r_id")
);


ALTER TABLE "group_roles" ADD CONSTRAINT "groups_grouproles_fk" FOREIGN KEY ("gr_g_id")
	REFERENCES "groups"("g_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "group_roles" ADD CONSTRAINT "roles_grouproles_fk" FOREIGN KEY ("gr_r_id")
	REFERENCES "roles"("r_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_groups" ADD CONSTRAINT "groups_usergroup_fk" FOREIGN KEY ("ug_g_id")
	REFERENCES "groups"("g_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_groups" ADD CONSTRAINT "users_usergroup_fk" FOREIGN KEY ("ug_u_id")
	REFERENCES "users"("u_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_roles" ADD CONSTRAINT "roles_usersroles_fk" FOREIGN KEY ("ur_u_id")
	REFERENCES "roles"("r_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_roles" ADD CONSTRAINT "users_usersroles_fk" FOREIGN KEY ("ur_u_id")
	REFERENCES "users"("u_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_denied" ADD CONSTRAINT "Ref_user_denied_to_users" FOREIGN KEY ("ud_u_id")
	REFERENCES "users"("u_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;

ALTER TABLE "user_denied" ADD CONSTRAINT "Ref_user_denied_to_roles" FOREIGN KEY ("ud_r_id")
	REFERENCES "roles"("r_id")
	MATCH SIMPLE
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
	NOT DEFERRABLE;
22

húha

Pepita · 2016. Dec. 30. (P), 15.03
Bocsi, de jelenleg csak MySql szerverem van, így ez nekem nem használható.
Ezen kívül jó lenne tesztadatokkal együtt (amivel te is "játszol"), de ez a rész akkor részemről inkább skip, nincs annyi időm.

Szerk.: Sok helyen van generált key, helyette elképzelhető, hogy jobb egy autoincrement saját Id. Nagyon lassítja az INSERT / UPDATE - et, és ok, hogy a tábla így kisebb, de az index meg sokkal nagyobb.
21

Végül is akkor most előlről

Theo76 · 2016. Dec. 28. (Sze), 20.10
Végül is akkor most előlről kezdem az egész felépítését. Ha jól értelmeztelek, akkor külön kell választani az intézményt, és a jogköröket, ez mondjuk logikus is... túlságosan ragaszkodtam, hogy egybe legyen minden kezelve. :)

Akkor a lényeg: határozzam meg a jogköröket részletesen, egy teljesen sima lista, a szervezeti struktúrához viszont már a fa struktúra kell.

A jogosultságokat ilyen formában szedtem össze:

- regisztráció
- tagok alapadatainak módosítása
- tagok kiegészítő adatainak módosítása (pl ezesetben az éves szabadság meghatározása)
- tag inaktívvá tétele
- tag végleges törlése
- a tag munkavégzésének adatainak módosítása (hely, munkacsoport, munkakör)
- szabadság kérése
- szabadság engedélyezése 1. szinten
- szabadság engedélyezése 2. szinten
- kimutatások készítése
- egyéni jogkörök módosítása
- csoport jogkörök módosítása
- új munkavégzés hely hozzáadása
- új munkahely hozzáadása
- új munkakör hozzáadása
- új csoport létrehozása
- csoport módosítása
23

jó lista! :)

Pepita · 2016. Dec. 30. (P), 15.55
Legelső legyen szerintem a fejlesztő, Id = 1. Az egyetlen "spéci" role.

Ez a három biztosan elegendő, hogy egy role legyen:
- csoport jogkörök módosítása
- új csoport létrehozása
- csoport módosítása

Ezek szintén, sanszos, hogy előbbivel azonos role. Ha nem muszáj, ne legyen túl sok role, de ne is vonj össze olyat, ami nem tutibiztos, hogy lehet egy. Tapasztalat, hogy nagyjából egy tartalomtípus egy role. Aki hozzá nyúlhat, az általában mindent meg is tehet vele, max a törlés a kivétel.
- új munkavégzés hely hozzáadása
- új munkahely hozzáadása
- új munkakör hozzáadása

Kimaradt:
- Szabadság letiltás és végleges törlés (tévedésből írta be, stb), de lehet, hogy ez egy role: "saját szabadság létrehozás, szerkesztés, törlés", illetve adminnak hasonló, csak nem sajátra.
- Role létrehozás - update - delete.
- Group delete.
- User -> Group és vissza.

Ez mit jelent?
- regisztráció
Szerintem bárki regisztrálhat (saját magát), legfeljebb legyen elfogadáshoz kötött az aktiválás.
Másvalakit regelni nem igazán tanácsos, ha pl nem használ számítógépet, akkor email fiókja sincs, mindenképp a Marika néni fog a "helyébe lépni".
Viszont az már Role, hogy "ő egy regisztrált User", és ezt célszerű is kicsit külön kezelni (mondjuk Id = 2), hogy Group - al ne lehessen kapni / elveszíteni, ez mindenkinek van, aki nincs letiltva / törölve.
Apropó, User törlése. Id - k miatt nem (sem) célszerű fizikálisan törölni a rekordot, de minden egyes adatát anonymizálni vagy törölni kell, és nem visszafordíthatóvá kell tenni a folyamatot.

Ha van Group, akkor egyéni Role nincs ("egyéni jogkörök módosítása"). Nem fogod tudni lekezelni, amikor változik valami Group-on keresztül (kivéve egy-két nagyon spéci eset).

Ezen kívül ne szögezzük le előre, hogy
a szervezeti struktúrához viszont már a fa struktúra kell.

Értem én, hogy tetszik a nested set (nekem is :) ), de ha nem feltétlenül kell, akkor minek? * (l. lentebb)
Amúgyis: Role után Group jön, milyen felhasználói csoportokra lesz szükség? Ez megint nem egyenlő a beosztással, vagy a munkavégzés helyével.
Ha valami Role még hiányzik, akkor az esélyes, hogy kiderül, amint életszerű Group-okat akarsz felírni.
Tehát gondold át a Role listát, hiányokat pótold, ha van ami összevonható, azt vondd össze, és jöhet a Group terv.

DB:
- Nem láttam UserData táblát, pedig valahova a "kiegészítő adatok" is kerülnek. Azt gondold ki, hogy lesz-e olyan adatod, amiből 1 Usernek több is lehet. Más a szerkezet, mintha 1-1 a kapcsolat.
- Teli van ALTER-el, elég csúnya dump.. (Tudom (ill. gondolom), nem kézzel írtad.)
- Usernek szerintem minimum LastName és FirstName, de részemről még UserName is "jár".

* Fenti rész legyen előbb rendben, db-terv is legyen hozzá teljes, utána lehet szervezeti struktúra - terv. Ez jól különüljön el fentiektől, mert sanszosan csak ennek a cégnek készül így, más nem fogja tudni használni.
Felülről ("Hely", ami inkább "Munkahely") lefelé haladva, minden egyes "szinten" az a fő kérdés:
"neki milye van, milyen adatokra van szükség, hogy teljesen fedje, amit kell?"

pl Munkahely:
- Id,
- Név,
- Cím,
- Blabla,
- Osztályok ...

Hoppá, többes szám van, ez különálló egység lesz!
Kérdés: nem kell semmi más már Munkahelynek?
Akkor tervezek Osztály-t, ha Munkahely szerintem kész.

Osztály:
- Id,
- MunkahelyId,
- Név,
- Góré (Id),
- GóréHelyettes (Id),
- Blablabla,
- Dolgozók ...

Hoppá, többes szám ...

Nem biztos, ez a legjobb megközelítés, de én nagyjából így szoktam. A céget viszont te ismered.
Szerintem ennyi az egész, nem kell ehhez nested set, annyi, hogy a User-nek lesz egy OsztályId tulajdonsága is. A egy Marika néni képes több osztályon dolgozni, akkor több OsztályId-t kell megvalósítani: lehet pl külön tábla és LEFT JOIN.

Tervezési fázisban célszerű ugyanazt a dolgot mindig ugyanúgy hívni, pl: jogkör = Role. kisebb a félreértés esélye.
24

Kicsit átvariáltam a

Theo76 · 2016. Dec. 31. (Szo), 20.59
Kicsit átvariáltam a táblákat...
Hozzáadtam egy város táblát is, amit a posta adatbázisa alapján töltöttem fel város nevekkel. Ahhoz kapcsolva lehet majd kiolvastatni a város neveket a varos_id alapján.

Amit még változtattam, hogy beraktam egy segéd táblát a user, és az osztályok közé, mint a roloknál.
Most így néz ki a tábla terv: database_design
Itt vannak a role-ok is: roles

Továbbá csináltam egy sql dumpot is, most mysql-re. Igazából a postgresql-t csak ki akartam próbálni, mert hallottam róla, hogy állítólag gyorsabb, stabilabb mint a mysql. :) Amúgy jól érezted, a másik dumpot is programmal csinálta. A MicroOLAP Database Designer for PostgreSQL-elel. A mostanit kicsit átbabráltam, hogy ne legyen annyi ALTER. Bár ezt phpmyadminnal csináltam, az is tud rendesen szemetelni. :)

CREATE TABLE `logos_data_szabi` (
  `dsz_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `dsz_u_id` int(11) NOT NULL,
  `dsz_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dsz_tol` date NOT NULL DEFAULT '0000-00-00',
  `dsz_ig` date NOT NULL DEFAULT '0000-00-00',
  `dsz_nap` int(11) NOT NULL,
  `dsz_jovahagy_u_id` int(11) NOT NULL,
  `dsz_jovahagy` tinyint(4) DEFAULT NULL,
  `dsz_jovahagy_date` datetime DEFAULT '0000-00-00 00:00:00',
  `dsz_engedely_u_id` int(11) NOT NULL,
  `dsz_engedely_megjegyzes` text COLLATE utf8_unicode_ci NOT NULL,
  `dsz_engedely` tinyint(4) DEFAULT NULL,
  `dsz_engedely_date` datetime DEFAULT '0000-00-00 00:00:00',
  `dsz_torles_kerelem` datetime DEFAULT '0000-00-00 00:00:00',
  `dsz_torles_u_id` int(11) NOT NULL,
  `dsz_torles_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dsz_torles_megjegyzes` text COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_groups` (
  `g_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `g_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_group_roles` (
  `gr_g_id` int(11) NOT NULL,
  `gr_r_id` int(11) NOT NULL,
  PRIMARY KEY (`gr_g_id`, `gr_r_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_roles` (
  `r_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `r_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `r_role` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_users` (
  `u_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `u_fname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `u_lname` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `u_login` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `u_passwd` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `u_create` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_user_data` (
  `ud_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `ud_szul_hely` int(11) NOT NULL,
  `ud_szul_ido` date NOT NULL DEFAULT '0000-00-00',
  `ud_lakhely` int(11) NOT NULL,
  `ud_lakcim` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ud_tart_hely` int(11) NOT NULL,
  `ud_tart_cim` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `ud_szigsz` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  `ud_TAJ` int(11) NOT NULL,
  `ud_ado` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_user_classes` (
  `uc_u_id INT NOT NULL`,
  `uc_wc_id INT NOT NULL`
);

CREATE TABLE `logos_user_groups` (
  `ug_u_id` int(11) NOT NULL,
  `ug_g_id` int(11) NOT NULL,
  PRIMARY KEY(`ug_u_id`, `ug_g_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_user_szabi` (
  `usz_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `usz_athozott` int(11) NOT NULL,
  `usz_eves` int(11) NOT NULL,
  `usz_kivett` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_works` (
  `w_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `w_class` int(11) NOT NULL,
  `w_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `w_cim_varos` int(11) NOT NULL,
  `w_cim` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_work_classes` (
  `wc_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `wc_w_id` int(11) NOT NULL,
  `wc_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `wc_boss_u_id` int(11) NOT NULL,
  `wc_dep_boss_u_id` int(11) NOT NULL,
  `wc_worker_u_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `logos_varos` (
  `varos_id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `varos_irszam` int(4) NOT NULL,
  `varos_name` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `varos_kerulet` varchar(100) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE INDEX `logos_varos_logos_user_data_szul_hely_fk` USING BTREE
 ON `logos_user_data`
 ( `ud_szul_hely` ASC );

CREATE INDEX `logos_varos_logos_user_data_lakhely_fk` USING BTREE
 ON `logos_user_data`
 ( `ud_lakhely` ASC );

CREATE INDEX `logos_varos_logos_user_data_tart_hely_fk` USING BTREE
 ON `logos_user_data`
 ( `ud_tart_hely` ASC );


ALTER TABLE `logos_data_szabi`
  ADD KEY `logos_users_logos_data_szabi_fk` (`dsz_u_id`),
  ADD CONSTRAINT `logos_users_logos_data_szabi_fk` FOREIGN KEY (`dsz_u_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_user_szabi`
  ADD CONSTRAINT `logos_users_logos_user_szabi_fk` FOREIGN KEY (`usz_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_group_roles`
  ADD KEY `roles_grouproles_fk` (`gr_r_id`) USING BTREE,
  ADD CONSTRAINT `groups_grouproles_fk` FOREIGN KEY (`gr_g_id`) REFERENCES `logos_groups` (`g_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `roles_grouproles_fk` FOREIGN KEY (`gr_r_id`) REFERENCES `logos_roles` (`r_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_user_groups`
  ADD KEY `groups_usergroup_fk` (`ug_g_id`) USING BTREE,
  ADD CONSTRAINT `groups_usergroups_fk` FOREIGN KEY (`ug_g_id`) REFERENCES `logos_groups` (`g_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `users_usergroup_fk` FOREIGN KEY (`ug_u_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_works`
  ADD CONSTRAINT `logos_varos_logos_works_fk` FOREIGN KEY (`w_cim_varos`) REFERENCES `logos_varos` (`varos_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_work_classes`
  ADD KEY `logos_works_logos_work_classes_fk` (`wc_w_id`),
  ADD KEY `logos_users_logos_work_classes_boss_fk` (`wc_boss_u_id`),
  ADD KEY `logos_users_logos_work_classes_dep_boss_fk` (`wc_dep_boss_u_id`),
  ADD CONSTRAINT `logos_users_logos_work_classes_boss_fk` FOREIGN KEY (`wc_boss_u_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `logos_users_logos_work_classes_dep_boss_fk` FOREIGN KEY (`wc_dep_boss_u_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `logos_works_logos_work_classes_fk` FOREIGN KEY (`wc_w_id`) REFERENCES `logos_works` (`w_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_user_classes`
  ADD KEY `logos_users_logos_user_classes_fk` (`uc_u_id`,`uc_wc_id`),
  ADD CONSTRAINT `logos_users_logos_user_classes_fk` FOREIGN KEY (`uc_u_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `logos_work_classes_logos_user_classes_fk` FOREIGN KEY (`uc_wc_id`) REFERENCES `logos_work_classes` (`wc_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `logos_user_data`
  ADD CONSTRAINT `logos_users_logos_user_data_fk` FOREIGN KEY (`ud_id`) REFERENCES `logos_users` (`u_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `logos_varos_logos_user_data_szul_hely_fk` FOREIGN KEY (`ud_szul_hely`) REFERENCES `logos_varos` (`varos_id`),
  ADD CONSTRAINT `logos_varos_logos_user_data_lakhely_fk` FOREIGN KEY (`ud_lakhely`) REFERENCES `logos_varos` (`varos_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `logos_varos_logos_user_data_tart_hely_fk` FOREIGN KEY (`ud_tart_hely`) REFERENCES `logos_varos` (`varos_id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
Ezt még nem értem igazán:
Viszont az már Role, hogy "ő egy regisztrált User", és ezt célszerű is kicsit külön kezelni (mondjuk Id = 2), hogy Group - al ne lehessen kapni / elveszíteni, ez mindenkinek van, aki nincs letiltva / törölve.


Hogy kéne külön kezelni? Mert minden role kapcsolódik egy grouphoz. Legalább is eddig.
25

dump rossz

Pepita · 2017. Jan. 1. (V), 19.44
CREATE TABLE `logos_user_classes` (  
  `uc_u_id INT NOT NULL`,  
  `uc_wc_id INT NOT NULL`  
);
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '  
  `uc_wc_id INT NOT NULL`  
)' at line 2

Úgy tűnik, rossz helyen vannak a backtick-ek.
Ha már csinálsz egy dump-ot, célszerű azt kipróbálni publikálás előtt.

Mire való ez az új "logos" prefix? Célszerű lenne a nevek terén következetesnek maradni, így már kicsit nehéz követni. uc_wc_id egészen fantasztikus azonosító így első ránézésre... :)
Javaslat:
- Csak angol neveket használj (esetleg csak magyart), ne keverd
- Legyen CamelCase, és nem kell _ sem
- Ne legyen rövidítés oszlopnévben
- Felesleges oszlopnévben a tábla prefix, bonyolult Query-knél ha túl hosszú a táblanév, lehet használni alias-t.

Megint bele lett keverve a Munkahely / Osztály / Stb, pedig még nem látszik, hogy jogosultságok rendben lennének.

UsersRoles tábla nincs. Ezek szerint legalább minden login-nál legalább 4 táblát akarsz join-olni, plusz még amikor jogosultságot kell ellenőrizni. Ahelyett, hogy össz. hármat. Biztos, hogy jó lesz ez így?

Azt látom, hogy eléggé ide-oda kapkodsz, nyilván azért, mert szeretnél már kilóméterekkel előrrébb járni, de ez így nem fog menni, mert csak egyre nagyobb a káosz.
Amíg az alap felhasználókezelés nem működik teljesen jól (mondom működik, nem csak papíron van!), addig nem kellene semmi mással foglalkozni. Ugye ezt már jeleztem, és így segíteni se igen tudok mit, mert csak a kapkodás látszik.

Role-okat is megint kicsit összekeverted. Így, hogy képen van, nem tudok belőle másolni. De.
- Ha az egyszerű dolgozó csak bizonyos státuszig és csak a saját tartalmát (szabi) tudja módosítani, akkor az mindenképpen másfajta szerkesztési Role, mint aki bárkiét bármikor. Ezt kapásból célszerű 2 Role-nak felvenni.
Már amennyiben ez valóban szükséges. (Nem biztos!)

Spéci Role-ok:
1: Fejlesztő - csak az kaphatja, aki valóban az, el nem lehet venni.
2: Regisztrált user - automatikusan megkapja minden érvényes regisztrált user, nem lehet megkapni Group-al és el sem lehet veszíteni. (Mondjuk a letiltott / törölt felhasználó bukja el).

Group alapján csak olyan Role-ok kaphatók / veszíthetők el, amelyek Id > 2. Ennyi.

Namost, kicsit elkalandozva a szabadságokhoz:
HA a szabis rendszerben bármelyik user biztosan dolgozó is, aki szabit vehet ki, AKKOR az alapszintű szabi - szolgáltatásokhoz semmilyen további Role-ra nincs szükség, csak az eggyel fentebbi szinten lévő műveletekhez.

Lássunk egy valóban jó és átgondolt db-t kizárólag a felhasználó- és jogosultság kezelésre, ha az rendben van, akkor talán le is lehetne fejleszteni hozzá az alkalmazást is, és csak ezután variáljunk a szabikkal, helyekkel, stb.

Megj., hogy a címekkel is lesz bajod rendesen, ha az első körben nem fontos, akkor hagyd ki egyelőre.
26

Igazad van... vissza az alapokhoz

Theo76 · 2017. Jan. 4. (Sze), 10.50
Újra csináltam a táblákat, de most csak az alapadatokkal. A többi adattal, majd később foglalkozok, tényleg először működjön rendesen a jogosultság kezelése.

Egy szabadság kérés lépései így néznek ki:
1. A felhasználó bejelentkezik
2. Megkéri a szabadságát (pl. 2017.01.10-től, 2017.01.11-ig, 2 napot)
4. A középvezető engedélyezi a szabadság kérelmet
5. A felsővezető engedélyezi, véglegesíti a szabadság kérelmet (függetlenül a középvezetőtöl - ez kérés, hogy így legyen)

Kivételek:
Ha a felhasználó elírt valamit, és még nincs engedélyezve a szabadság, akkor azt még módosíthatja, vagy törölheti is.
Ha már engedélyezve van már 1 szinten, akkor a kérelem módosítását, vagy törlését kérheti.

Szükség van továbbá szabadságos kimutatások készítésére, ami szintén role-hoz köthető, mert nem mindenki készíthet.

A szabadság törlésére azért van szükség, mert volt már rá példa, hogy valaki megkérte, majd a szabadság előtti napokban visszavonta, még sem akart szabadságra menni, vagy módosul a kérelem, mert mondjuk 2 nappal el akarja csúsztatni a már megkért szabadságot. Viszont a főnök meg mindenről tudni akar még akkor is, ha őt nem is érinti az adott dolgozó (pl. nem a központban dolgozik, így közvetlenül nincs vele kapcsolatban).

A roleok:

- Fejlesztő
minden role-hoz köthető esemény automatikus true értéket kap
- Regisztrált felhasználó
ezzel a role-al módosíthatja a saját adatait
- Szabadság kérés/módosítás/törlés
megkérheti a saját szabadságát, módosíthatja, törölheti.
Az utóbbi két cselekményt befolyásolja, hogy engedélyezve van-e már vagy még nincs (ezt a részt maga a program kezeli majd)

- Felhasználók adatainak módosítása
más felhasználók adatainak módosítása
- Felhasználó inaktívvá tétele
a felhasználó zárolása, hozzáférés megszűntetése (pl megszűnik a szerződés)
- Felhasználó munkaadatainak módosítása
a munkavégzés helyének, jellegének, munkakörének módosítása
- Kimutatások készítése
kivett szabadságok összesítése, nyomtatása
- Munkavégzés helye/munkahely/munkakör módosítás
- Munkavégzés helye/munkahely/munkakör felvétel, törlés
- Csoport létrehozása/módosítása/törlése, beleértve a GroupRole-okat is
- Szabadság engedélyezése/megtagadása 1 szinten
- Szabadság engedélyezése/megtagadása 2 szinten
- Engedélyezett szabadság módosítsa/törlése
már engedélyezett szabadságok módosítása/törlése


sql táblák (most inkább kétszer is átnéztem/kipróbáltam, nem értem hogy lehetett az elsőben a hiba, mert a lefuttatás után másoltam be, de az tény, hogy hibásan lett bevíve)

CREATE TABLE `Groups` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `Name` VARCHAR(150) NOT NULL,
    PRIMARY KEY (`Id`)
);


CREATE TABLE `Roles` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `Name` VARCHAR(150) NOT NULL,
    `Note` TEXT,
    PRIMARY KEY (`Id`)
);


CREATE TABLE `GroupRoles` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `GroupId` INT NOT NULL,
    `RoleId` INT NOT NULL,
    PRIMARY KEY (`Id`)
);


CREATE TABLE `Users` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `FirstName` VARCHAR(100) NOT NULL,
    `LastName` VARCHAR(150) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `LoginName` VARCHAR(50) NOT NULL,
    `Password` VARCHAR(64) NOT NULL,
    `Created` DATETIME NOT NULL,
    PRIMARY KEY (`Id`)
);


CREATE TABLE `UserRoles` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `UserId` INT NOT NULL,
    `RoleId` INT NOT NULL,
    PRIMARY KEY (`Id`)
);


CREATE TABLE `UserGroups` (
    `Id` INT AUTO_INCREMENT NOT NULL,
    `UserId` INT NOT NULL,
    `GroupId` INT NOT NULL,
    PRIMARY KEY (`Id`)
);


-- Tábla kapcsolatok

ALTER TABLE `UserGroups` ADD CONSTRAINT `groups_usergroups_fk`
    FOREIGN KEY (`GroupId`)
    REFERENCES `Groups` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE `GroupRoles` ADD CONSTRAINT `groups_grouproles_fk`
    FOREIGN KEY (`GroupId`)
    REFERENCES `Groups` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE `GroupRoles` ADD CONSTRAINT `roles_grouproles_fk`
    FOREIGN KEY (`RoleId`)
    REFERENCES `Roles` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE `UserRoles` ADD CONSTRAINT `roles_userroles_fk`
    FOREIGN KEY (`RoleId`)
    REFERENCES `Roles` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE `UserGroups` ADD CONSTRAINT `users_usergroups_fk`
    FOREIGN KEY (`UserId`)
    REFERENCES `Users` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

ALTER TABLE `UserRoles` ADD CONSTRAINT `users_userroles_fk`
    FOREIGN KEY (`UserId`)
    REFERENCES `Users` (`Id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

-- Tábla adatok

INSERT INTO `Roles` (`Name`,`Note`) VALUES
    ('Fejlesztő',NULL),
    ('Regisztrált felhasználó','Saját adatait módosíthatja'),
    ('Szabadság kérés/módosítás','Szabadságot kérhet, azt módosíthatja/törölheti, engedélyezés előtt'),
    ('Felhasználók adatainak módosítása','Más felhasználók adatainak módosítása'),
    ('Felhasználó inaktívvá tétele','a felhasználó zárolása, hozzáférés megszüntetése (pl megszűnik a szerződés)'),
    ('Felhasználó munkaadatainak módosítása','a munkavégzés helyének, jellegének, munkakörének módosítása'),
    ('Kimutatások készítése',NULL),
    ('Munkavégzés helye/munkahely/munkakör módosítás',NULL),
    ('Munkavégzés helye/munkahely/munkakör felvétel, törlés',NULL),
    ('Csoport létrehozása/módosítása/törlése',NULL),
    ('Szabadság engedélyezése/megtagadása 1 szinten',NULL),
    ('Szabadság engedélyezése/megtagadása 2 szinten',NULL),
    ('Engedélyezett szabadság módosítása/törlése',NULL);
27

Remélem holnap :)

Pepita · 2017. Jan. 5. (Cs), 00.33
Bocsi, elég elfoglalt vagyok, remélem holnap jobban rá tudok nézni.

Ami helyből látszik:
- mobilon ez a szál mélység már nem olvasható (ez WL hiba)
- nem válaszoltad meg a szerintem legfontosabb kérdést: minden egyes user kérhet szabadság? Mert szerintem igen, és akkor megint van itt felesleges Role.

De összességében már sokkal inkább kerek, ez már emészthető egység. :)
28

Új szálon

Pepita · 2017. Jan. 5. (Cs), 10.18
DB észrevételek:
- Nincs character set és collation megadva, mindenképp pótolni kell.

Users
- email oszlop is legyen nagybetűs: Email
- FirstName - LastName jellemzően ugyanolyan hosszú, szerintem 128
- varchar - hosszokat érdemes 2 hatványaira beállítani: 50 -> 64, stb
- Indexek legalább név, email legyen felindexelve, biztosan keresel majd rá, de Password mindenképp
- Created: tmiestamp jobb erre a célra, én tennék Updated-et is, ez akár lehet ON UPDATE CURRENT TIMSTAMP is
- Lehet, hogy kéne egy pl Hash oszlop is, amikor bármilyen aktiváló / elfelejtett jelszó linket kiküldesz, azt is teszed vhova
- Status: honnan tudod majd, hogy valaki érvényes user, le van tiltva, törölt, egyéb?
- Lehet, hogy pár személyes adattal még bővíteném (teló, stb), amit legfeljebb nem használunk fel.

Roles
- varchar - hossz ugyanúgy
- Name inkább Title nekem, Note pláne Description
- Note text... Egyetlen jogosultság leírása 64k?? Szerintem max varchar(512)
- Title (Name) lehetne UNIQUE KEY

Groups
- L. Roles

Kapcsoló táblák / egyéb:
- ON DELETE NO ACTION ON UPDATE NO ACTION : gyönyörűen lehet inkonzisztens káoszt csinálni így egy kezdetben jó db-ből. Ha mindkettő RESTRICT, akkor hibát fog dobni, ha olyan elemet (pl Roles) akarsz törölni, amire van hivatkozás, és sikertelen a törlés. Ha CASCADE, akkor törli a hivatkozást is (vagy update), ami itt nem biztos, hogy hasznos lenne.

Folyamat 4-5 pontja (engedélyek) ha jól értem, akkor csak az kell, hogy mindkét engedély meg legyen, mindegy a sorrend.
És ha egy engedély is rajta van, már csak góré tudja módosítani.
Ha 2 van, érvényes.
Majd oda is kell még egy flag vagy státusz, hogy "otthon is maradt a csávó" (=lezárás szerű valami), akkor mehet statisztikákba, innentől már senki nem tudja babrálni.

SZERK.:
- Role-t lehet, hogy csak fejlesztő tudjon felvenni, általában Admin csak Group és GroupRole szinten garázdálkodjon. Mindenképp legyen külön Role a kettő. Amire program dependel, azt ne lehessen kitörölni.
- Roles szövegek lehetnének nyelvi kulcsok is.. :)
29

Módosítások

Theo76 · 2017. Jan. 6. (P), 12.09
Átnézegettem amiket írtál... Tényleg lemaradtak karakter beállítások, ezeket most pótoltam. Pontosabban most a phpmysql-el csináltattam meg a dumpot, nem a designer programmal. Csak kicsit kipucoltam. Továbbá a megválaszolatlan kérdés: mindenki kérhet szabadságot... Ki is vettem azt a Role-t

Amik módosításra kerültek:
Users tábla:
Az Indexeket hoztam létre, a FirstName, LastName, LoginName, Password-ra. Ezek alapján kell majd keresni a leggyakrabban. Felvettem még egy Phone oszlopot, illetve egy Statust bool értékkel. A későbbiekben akarok még címet, de ezzel most nem foglalkoztam. Bár arra már régebben találtam lehetséges megoldást, hogy mi van olyan esetben, ha a város nem létezik, egy javascript formájában.

Groups tábla:
Ezt nem igazán értettem, hogy mi a L. Role...

Kapcsoló táblák:
Beállítottam őket RESTRIC-re. Gondolkoztam, hogy a DELETE-re CASCADE-de nem voltam benne biztos... Viszont ezen a téren még semmi tapasztalatom nincs... Mert paraszti logikával nézve, ha pl törlök egy Role-t akkor lehet, hogy jó lenne ha a hozzá kapcsolt GroupRole-t is törölné... Nah de még ezzel kísérletezek. :)

Újragondoltam a Role-okat is, mert felvetődött némi módosítás, azok alapján amit írtál...
Átvaritáltam a sorrendeket figyelembe véve, hogy melyek tartozhatnak egy Role csoportba (ez nem egyezik meg a későbbi csoport role-okkal)

1. Fejlesztő
2. Regisztrált felhasználó

3. Munkavégzés helye/munkahely/munkakör módosítás
4. Munkavégzés helye/munkahely/munkakör felvétel, törlés
5. Felhasználók adatainak módosítása
6. Felhasználó inaktívvá tétele
7. Felhasználó munkaadatainak módosítása

8. Csoport létrehozása/módosítása/törlése
9. Group Role hozzáadás/törlés
10. Role felvétele/módosítása

11. Kimutatások készítése

12. Szabadság engedélyezése/megtagadása 1 szinten
13. 1 szinten engedélyezett szabadság módosítása/törlése
14. Szabadság engedélyezése/megtagadása 2 szinten
15. 2 szinten engedélyezett szabadság törlése

Gondolkoztam azon is, hogy csoportokra hogy lehet leosztani. Eddig erre jutottam:
Admin: 3-9, 11
Csoport1: 3-7, 11
Csoport2: 11, 12, 13
Csoport3: 11, 14, 15

A szabadság kérés engedélyezési menetét nem fejtettem ki rendesen:
Megkéred a szabadságot. Amíg nem engedélyezik, addig azt módosíthatod törölheted szabadon...
Engedélyezi a középvezető. Ezután csak a kisfőnök módosíthatja, törölheti. A kérelmező csak kérheti a módosítást/törlést. A góré csak azután látja a kérelmet, ha a kisfőnök jóváhagyta, vagy elutasította. Ezután lesz rá jogosultsága, hogy azt engedélyezze, módosítsa, vagy törölje. Bár a módosítást lehet nem kéne engedni, mert ugye változik az idő, és arról a kisfőnöknek is illene tudni, és beleegyezni... :) Ezen módosítottam is...

Az új dump:

--
-- Tábla szerkezet ehhez a táblához `GroupRoles`
--

CREATE TABLE `GroupRoles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `GroupId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Groups`
--

CREATE TABLE `Groups` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Name` varchar(150) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Roles`
--

CREATE TABLE `Roles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Title` varchar(128) COLLATE utf8_unicode_ci NOT NULL UNIQUE KEY,
  `Description` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- A tábla adatainak kiíratása `Roles`
--

INSERT INTO `Roles` (`Id`, `Title`, `Description`) VALUES
(1, 'Fejlesztő', NULL),
(2, 'Regisztrált felhasználó', 'Saját adatait módosíthatja'),
(3, 'Munkavégzés helye/munkahely/munkakör módosítás', NULL),
(4, 'Munkavégzés helye/munkahely/munkakör felvétel, törlés', NULL),
(5, 'Felhasználók adatainak módosítása', 'Más felhasználók adatainak módosítása'),
(6, 'Felhasználó inaktívvá tétele', 'a felhasználó zárolása, hozzáférés megszüntetése (pl megszűnik a szerződés)'),
(7, 'Felhasználó munkaadatainak módosítása', 'a munkavégzés helyének, jellegének, munkakörének módosítása'),
(8, 'Csoport létrehozása/módosítása/törlése', NULL),
(9, 'Group Role hozzáadás/törlés', NULL),
(10, 'Role felvétele/módosítása', NULL),
(11, 'Kimutatások készítése', NULL),
(12, 'Szabadság engedélyezése/megtagadása 1 szinten', NULL),
(13, '1 szinten engedélyezett szabadság módosítása/törlése', NULL),
(14, 'Szabadság engedélyezése/megtagadása 2 szinten', NULL),
(15, '2 szinten engedélyezett szabadság törlése', NULL);

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserGroups`
--

CREATE TABLE `UserGroups` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `GroupId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserRoles`
--

CREATE TABLE `UserRoles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Users`
--

CREATE TABLE `Users` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `FirstName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `LastName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `Email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `LoginName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `Hash` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Phone` int(11) DEFAULT NULL,
  `Status` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexek a kiírt táblákhoz
--

--
-- A tábla indexei `GroupRoles`
--
ALTER TABLE `GroupRoles`
  ADD KEY `groups_grouproles_fk` (`GroupId`),
  ADD KEY `roles_grouproles_fk` (`RoleId`);

--
-- A tábla indexei `UserGroups`
--
ALTER TABLE `UserGroups`
  ADD KEY `groups_usergroups_fk` (`GroupId`),
  ADD KEY `users_usergroups_fk` (`UserId`);

--
-- A tábla indexei `UserRoles`
--
ALTER TABLE `UserRoles`
  ADD KEY `users_userroles_fk` (`UserId`),
  ADD KEY `roles_userroles_fk` (`RoleId`);

--
-- A tábla indexei `Users`
--
ALTER TABLE `Users`
  ADD KEY `FirstName` (`FirstName`),
  ADD KEY `LastName` (`LastName`),
  ADD KEY `LoginName` (`LoginName`),
  ADD KEY `Password` (`Password`);

--
-- Megkötések a kiírt táblákhoz
--

--
-- Megkötések a táblához `GroupRoles`
--
ALTER TABLE `GroupRoles`
  ADD CONSTRAINT `groups_grouproles_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
  ADD CONSTRAINT `roles_grouproles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`);

--
-- Megkötések a táblához `UserGroups`
--
ALTER TABLE `UserGroups`
  ADD CONSTRAINT `groups_usergroups_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
  ADD CONSTRAINT `users_usergroups_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

--
-- Megkötések a táblához `UserRoles`
--
ALTER TABLE `UserRoles`
  ADD CONSTRAINT `roles_userroles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`),
  ADD CONSTRAINT `users_userroles_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);
Az eddigi tesztelésre használt kód:

public function auth( $userID, $perm, $developer = FALSE ) {
    if ( $developer ) {
        return TRUE;
    } else {
        if ( is_array( $perm ) ) $perm = implode( ',', $perm );

        $sql = "SELECT GroupRoles.RoleId "
             . "FROM UserGroups "
             . "INNER JOIN GroupRoles "
                 . "ON UserGroups.GroupId = GroupRoles.GroupId "
             . "WHERE UserGroups.UserId = {$userID} AND GroupRoles.RoleId IN ({$perm})"
             . "UNION "
             . "SELECT RoleId "
             . "FROM UserRoles "
             . "WHERE UserId = {$userID} AND RoleId IN ({$perm})";

        $result = self::select( $sql );
    }

    return ( $result ) ? TRUE : FALSE;
}
Még nem kiforrott, csak amolyan kísérleti jellegű, hogy tudjam tesztelni a táblákat.
30

Alakul

Pepita · 2017. Jan. 6. (P), 13.34
Felvettem még egy Phone oszlopot, illetve egy Statust bool értékkel
A User nem csak van vagy nincs, több állapota is lehet.
A teljesség igénye nélkül: aktív, letiltott, törölt, aktiválásra váró, email cím csere utáni aktiválás, ...
Egy boolean is egy byte a db-ben, legyen inkább tinyint.

Ezt nem igazán értettem, hogy mi a L. Role...
= Lásd Role. Ugyanaz a feladat.

RESTRICT vs CASCADE
Gyakorlatilag pusztán hozzáállás kérdése: lusta programozó vagy, vagy jó?
Ha lusta, akkor CASCADE, majd a db motor takarít helyetted (reméljük). Ha jó fejlesztő vagy, akkor örülsz neki, ha a db is elhasal és ordít, ha elb...ál vmit.

Roles szerintem jó így (majd kiderül, ha nem), csoportokat meg bőven ráérsz majd admin felületen kialakítani.

Dump-ra most nincs időm.

public function auth
Ha ez a fv nem azonosít, akkor rossz a neve.
Ha ez HaveRole szeretne lenni, akkor lényegesen egyszerűbben meg lehet oldani. De ne ezzel a query-vel kezdd.
$developer = FALSE pláne nem is kell, mert azt a jogot is ugyanez a fv mondja meg, hogy van-e neki.
De lehet, hogy egyáltalán nem ezt akartad itt vizsgálni?
Mindenesetre a User Role-jainak a vizsgálatához nem kell UserGroups és GroupRoles tábla.
Ha csak Id alapján elég, nem kell szövegesen, akkor csak a User és a UserRoles táblák LEFT JOIN. Ha szöveges is lehet, akkor még Roles is.

(Lehet eddig nem mondtam, de hasznos lenne Roles.Title unique key!)
(De, mondtam. :) )
31

Lassan de biztosan :)

Theo76 · 2017. Jan. 6. (P), 14.34
A teljesség igénye nélkül: aktív, letiltott, törölt, aktiválásra váró, email cím csere utáni aktiválás, ...
Egy boolean is egy byte a db-ben, legyen inkább tinyint.

Igen ez igaz... viszont aztnéztem, hogy alapból a bool-t tinyintnek állítja be, így akkor ez már megoldott. :)

= Lásd Role. Ugyanaz a feladat.

És tényleg... már elkeveredtem a rövidítésekben... :) Elvileg megcsináltam mind, de viszont ezt sikerült kihagyni...

Ha lusta, akkor CASCADE, majd a db motor takarít helyetted (reméljük). Ha jó fejlesztő vagy, akkor örülsz neki, ha a db is elhasal és ordít, ha elb...ál vmit.


Hááát... valahol a kettő között... :) Viszont szeretem látni, hogy mi történik, mert egyrészt nem okoz galibát amit esetleg csak későn veszek észre, másrészt meg abból lehet tanulni ha visszakiabál. :)

A funkció csak egy kísérlet, gondoltam, hogy lesz benne hiba. :) Egy másik cikkben láttam, mikor kutattam ebben a témakörben, és onnan írtam át. A $developer-nek tényleg az lenne a funkciója, hogy megállapítsa, hogy akinek a jogosultságát vizsgálja, fejlesztő, vagy sem. Ha nem fejlesztő, akkor nézze meg, hogy az adott feladat jogosultságával rendelkezik-e

(Lehet eddig nem mondtam, de hasznos lenne Roles.Title unique key!)
(De, mondtam. :) )


Igen mondtad, és benne is van a dumpban... Mikor kipróbáltam akkor szépen működött is. :)

A javított dump:
--
-- Tábla szerkezet ehhez a táblához `GroupRoles`
--

CREATE TABLE `GroupRoles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `GroupId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Groups`
--

CREATE TABLE `Groups` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Title` varchar(128) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Roles`
--

CREATE TABLE `Roles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `Title` varchar(128) COLLATE utf8_unicode_ci NOT NULL UNIQUE KEY,
  `Description` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- A tábla adatainak kiíratása `Roles`
--

INSERT INTO `Roles` (`Id`, `Title`, `Description`) VALUES
(1, 'Fejlesztő', NULL),
(2, 'Regisztrált felhasználó', 'Saját adatait módosíthatja'),
(3, 'Munkavégzés helye/munkahely/munkakör módosítás', NULL),
(4, 'Munkavégzés helye/munkahely/munkakör felvétel, törlés', NULL),
(5, 'Felhasználók adatainak módosítása', 'Más felhasználók adatainak módosítása'),
(6, 'Felhasználó inaktívvá tétele', 'a felhasználó zárolása, hozzáférés megszüntetése (pl megszűnik a szerződés)'),
(7, 'Felhasználó munkaadatainak módosítása', 'a munkavégzés helyének, jellegének, munkakörének módosítása'),
(8, 'Csoport létrehozása/módosítása/törlése', NULL),
(9, 'Group Role hozzáadás/törlés', NULL),
(10, 'Role felvétele/módosítása', NULL),
(11, 'Kimutatások készítése', NULL),
(12, 'Szabadság engedélyezése/megtagadása 1 szinten', NULL),
(13, '1 szinten engedélyezett szabadság módosítása/törlése', NULL),
(14, 'Szabadság engedélyezése/megtagadása 2 szinten', NULL),
(15, '2 szinten engedélyezett szabadság törlése', NULL);

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserGroups`
--

CREATE TABLE `UserGroups` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `GroupId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserRoles`
--

CREATE TABLE `UserRoles` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `UserId` int(11) NOT NULL,
  `RoleId` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Users`
--

CREATE TABLE `Users` (
  `Id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `FirstName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `LastName` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `Email` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `LoginName` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Password` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `Hash` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `Phone` int(11) DEFAULT NULL,
  `Status` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Indexek a kiírt táblákhoz
--

--
-- A tábla indexei `GroupRoles`
--
ALTER TABLE `GroupRoles`
  ADD KEY `groups_grouproles_fk` (`GroupId`),
  ADD KEY `roles_grouproles_fk` (`RoleId`);

--
-- A tábla indexei `UserGroups`
--
ALTER TABLE `UserGroups`
  ADD KEY `groups_usergroups_fk` (`GroupId`),
  ADD KEY `users_usergroups_fk` (`UserId`);

--
-- A tábla indexei `UserRoles`
--
ALTER TABLE `UserRoles`
  ADD KEY `users_userroles_fk` (`UserId`),
  ADD KEY `roles_userroles_fk` (`RoleId`);

--
-- A tábla indexei `Users`
--
ALTER TABLE `Users`
  ADD KEY `FirstName` (`FirstName`),
  ADD KEY `LastName` (`LastName`),
  ADD KEY `LoginName` (`LoginName`),
  ADD KEY `Password` (`Password`);

--
-- Megkötések a kiírt táblákhoz
--

--
-- Megkötések a táblához `GroupRoles`
--
ALTER TABLE `GroupRoles`
  ADD CONSTRAINT `groups_grouproles_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
  ADD CONSTRAINT `roles_grouproles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`);

--
-- Megkötések a táblához `UserGroups`
--
ALTER TABLE `UserGroups`
  ADD CONSTRAINT `groups_usergroups_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
  ADD CONSTRAINT `users_usergroups_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

--
-- Megkötések a táblához `UserRoles`
--
ALTER TABLE `UserRoles`
  ADD CONSTRAINT `roles_userroles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`),
  ADD CONSTRAINT `users_userroles_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);
32

Már csak kötöszködök :)

Pepita · 2017. Jan. 6. (P), 20.50
- Nincs Groups.Description mező, lehet, hogy nem ártana.
- Nem UNIQUE KEY a Groups.Title (Role.Title igen, azt benéztem előzőleg)
- User.Phone jobban jársz, ha varchar(legalább 16), mert akkor tudod ország-körzet-szám formában tárolni.
- Hint: ON UPDATE CURRENT_TIMESTAMP - et ha egy mód van rá, ne használd ki, egyik biztos megoldás arra, hogy mindig jó időpontok legyenek mentve, hogy mindig csak a PHP idejét használod, és megadod MySql-nek. Sok esetben másik vason fut, és nem egyformán jár a kettő órája.
- Roles.Description nekem NOT NULL lenne szintén, majd beszúrok üres stringet, ha nem használom (de miért ne használnám?).
- Ha helyes magyar ABC sorrendet szeretnél, akkor COLLATE=utf8_hungarian_ci
- UserGroups index ez jobb, érdemes megtenni UserRoles-on is:
CREATE TABLE `UserGroups` (
`Id`  int(11) NOT NULL AUTO_INCREMENT ,
`UserId`  int(11) NOT NULL ,
`GroupId`  int(11) NOT NULL ,
PRIMARY KEY (`Id`),
FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
UNIQUE INDEX `UserId_GroupId` (`UserId`, `GroupId`) USING BTREE ,
INDEX `groups_usergroups_fk` (`GroupId`) USING BTREE ,
INDEX `users_usergroups_fk` (`UserId`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_unicode_ci
AUTO_INCREMENT=1
ROW_FORMAT=DYNAMIC
;
És hogy ne csak kötöszködjek, hanem adjak is valamit:
itt egy szög egyszerű query, amivel addott (most épp 3-as) jogosultságot ellenőrzöl:
SELECT UR.RoleId FROM Users U
LEFT JOIN UserRoles UR ON UR.UserId = U.Id
WHERE U.Id = 1
AND U.`Status` > 0
AND (UR.RoleId = 1
     OR UR.RoleId = 3)
LIMIT 1
;
Ha egy sornyi az eredmény, akkor van joga, ha 0, akkor nincs.
Persze ha szövegesen is kellhet keresni, akkor bejön mellé még a Roles tábla is és még egy feltétel.
U.`Status` > 0 jelenti azt, hogy aktív a User, be tud lépni. PHP-ben gyors escape-elési módja egész számoknak az (int) $Value, ez biztos, hogy egész szám lesz (ha pl string, akár sql injection okán és szám nélkül, akkor 0 lesz).
33

Remélem most jó lesz. :)

Theo76 · 2017. Jan. 8. (V), 11.58
Abszolúte nem vettem kötöszködésnek. :)
Átírtam a táblákat. Annyit módosítottam még, hogy a GroupRoles, UserRoles táblákba is beállítottam a UNIQUE INDEXET, hogy ne lehessen egyazon usernek ugyan azt a jogot többször megadni.

Köszönöm az query-t. :) A UserRoles-t, és a GroupRoles-t külön kell lekérdezni?

--
-- Tábla szerkezet ehhez a táblához `GroupRoles`
--

CREATE TABLE `GroupRoles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`GroupId` int(11) NOT NULL,
	`RoleId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `GroupId_RoleId` (`GroupId`, `RoleId`) USING BTREE,
	INDEX `groups_grouproles_fk` (`GroupId`) USING BTREE,
	INDEX `roles_grouproles_fk` (`RoleId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Groups`
--

CREATE TABLE `Groups` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`Title` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Descripton` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `Title` (`Title`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Roles`
--

CREATE TABLE `Roles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`Title` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Description` varchar(512) COLLATE utf8_hungarian_ci DEFAULT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `Title` (`Title`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserGroups`
--

CREATE TABLE `UserGroups` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`UserId` int(11) NOT NULL,
	`GroupId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `UserId_GroupId` (`UserId`, `GroupId`) USING BTREE,
	INDEX `groups_usergroups_fk` (`GroupId`) USING BTREE,
	INDEX `users_usergroups_fk` (`UserId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserRoles`
--

CREATE TABLE `UserRoles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`UserId` int(11) NOT NULL,
	`RoleId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `UserId_RoleId` (`UserId`, `RoleId`) USING BTREE,
	INDEX `users_userroles_fk` (`UserId`) USING BTREE,
	INDEX `roles_userroles_fk` (`RoleId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Users`
--

CREATE TABLE `Users` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`FirstName` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`LastName` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Email` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`LoginName` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Password` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
	`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
	`Hash` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Phone` varchar(16) DEFAULT NULL,
	`Status` tinyint(1) DEFAULT NULL,
	PRIMARY KEY(`Id`),
	INDEX `FirstName` (`FirstName`) USING BTREE,
	INDEX `LastName` (`LastName`) USING BTREE,
	INDEX `LoginName` (`LoginName`) USING BTREE,
	INDEX `Password` (`Password`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- ------------------------------------------------------------

--
-- Megkötések a kiírt táblákhoz
--

--
-- Megkötések a táblához `GroupRoles`
--
ALTER TABLE `GroupRoles`
	ADD CONSTRAINT `groups_grouproles_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
	ADD CONSTRAINT `roles_grouproles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`);

--
-- Megkötések a táblához `UserGroups`
--
ALTER TABLE `UserGroups`
	ADD CONSTRAINT `groups_usergroups_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
	ADD CONSTRAINT `users_usergroups_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

--
-- Megkötések a táblához `UserRoles`
--
ALTER TABLE `UserRoles`
	ADD CONSTRAINT `roles_userroles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`),
	ADD CONSTRAINT `users_userroles_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

-- ------------------------------------------------------------

--
-- A tábla adatainak kiíratása `Roles`
--

INSERT INTO `Roles` (`Id`, `Title`, `Description`) VALUES
	(1, 'Fejlesztő', ''),
	(2, 'Regisztrált felhasználó', 'Saját adatait módosíthatja'),
	(3, 'Munkavégzés helye/munkahely/munkakör módosítás', ''),
	(4, 'Munkavégzés helye/munkahely/munkakör felvétel, törlés', ''),
	(5, 'Felhasználók adatainak módosítása', 'Más felhasználók adatainak módosítása'),
	(6, 'Felhasználó inaktívvá tétele', 'a felhasználó zárolása, hozzáférés megszüntetése (pl megszűnik a szerződés)'),
	(7, 'Felhasználó munkaadatainak módosítása', 'a munkavégzés helyének, jellegének, munkakörének módosítása'),
	(8, 'Csoport létrehozása/módosítása/törlése', ''),
	(9, 'Group Role hozzáadás/törlés', ''),
	(10, 'Role felvétele/módosítása', ''),
	(11, 'Kimutatások készítése', ''),
	(12, 'Szabadság engedélyezése/megtagadása 1 szinten', ''),
	(13, '1 szinten engedélyezett szabadság módosítása/törlése', ''),
	(14, 'Szabadság engedélyezése/megtagadása 2 szinten', ''),
	(15, '2 szinten engedélyezett szabadság törlése', '');
34

Én is

Pepita · 2017. Jan. 8. (V), 15.06
remélem, ha még sem, ki fog derülni... :)

A UserRoles-t, és a GroupRoles-t külön kell lekérdezni?
Hát itt nem értem a kérdést igazán, de valószínűleg igen.
Mint az elején is tárgyaltuk, a UserRoles tábla fő szerepe egyfajta "elő-pufferelés", a célja az, amit fenti query-ben is látsz, hogy két (max 3) tábla 1-n join-jából megkapjuk gyorsan valaki jogosultságát.
A Group és GroupRoles táblák szerepe pedig az admin funkciók segítése, hogy ne csak egyesével tudj egy-egy felhasználónak jogokat adni, hanem egyszerre több felhasználónak egyszerre több jogot.
Majd az egy érdekes feladat lesz, hogy admin Group-ot módosít. :)
Ilyenkor:
- Lekéred az összes GroupRoles-t adott Groupra;
- Lekéred az összes User-t (UserGroups), ezeken egyesével mész végig:
- Lekéred a UserRoles-t rá;
- Összehasonlítod GroupRoles-al;
- Amit kell, kitörölsz, amit kell INSERT (UserRoles-ban)

Persze egy kicsit bonyibb lesz a szitu, mert a User több Group-ba is tartozhat, és a másik Group-ból származó jogait nem kéne elvenni. :)

A megoldás egy generikus UserRoles-update-elő mutatvány (class) lesz, ami adott Usert fullosan beállít Group-ok alapján. Amikor pedig Group módosul, ezt a mutatványt kell futtatni minden tagjára (itt majd lehet, hogy érdemes lesz pár ezerben limitálni, hogy ne módosíts olyan csoportot, amiben pl 10000 User van már).
35

Jogosultság lekérdezés

Theo76 · 2017. Jan. 8. (V), 16.12
"A UserRoles-t, és a GroupRoles-t külön kell lekérdezni?"
Hát itt nem értem a kérdést igazán, de valószínűleg igen.


Azért merült fel bennem ez a kérdés, mert az elején úgy gondolkoztam, hogy vannak a csoportok, és annak vannak jogaik. Így mikor vizsgálni kezdem a user jogosultságát, akkor lekérem, hogy milyen csoportba tartozik, és akkor annak a csoportnak milyen jogai vannak. Ebben az esetben nem kell minden usernek külön-külön beállítani, role-okat. De mivel lehet, hogy valakinek csoportokon felüli jogosultsága van, pl a fejlesztő, akkor azt is figyelembe vegye. Ezért volt az UNION kapcsolás. Bár ott kicsit eltúloztam, hogy más role-okat is keres, hisz ott elég csak azt megnézni, hogy fejlesztő-e, elfelejtettem, hogy nem kéne egyéni jogokat adni a felhasználóknak az átláthatóság miatt. :) Illetve ami még lehetséges megoldás, amit írtál is korábban, hogy configba betenni, hogy ki, vagy kik a fejlesztők.
36

Nem igazán

Pepita · 2017. Jan. 8. (V), 20.49
Igen, az egyik megközelítés a Group felőli lehetne, de épp azért csináltuk a UserRoles táblát, hogy ezt sokkal gyorsabban megoldjuk. Gondolj arra, hogy le kérdezni nagyon sokszor kell, beállítani pedig ritkán.
Nem kell semmi union, a User minden jogosultsága bent van UserRoles ban. Amikor kérdezzük, nem érdekel, hogy honnan van neki adott Role.

Nem tesszük configba, hogy kik a fejlesztők.
Azt tesszük csak configba / kódba, hogy melyek azok a Role ok, amiket Group alapján sem lehet elvenni senkitől (jelenleg ami Id < 3).
Azért, hogy a Group alapú módosítás ne keverjen be a spéci Role okba. Ezeket ugyanúgy csoporthoz rendelni se lehet (GroupRoles), stb.

Ha egyéni jogokat is adsz, akkor az első Group alapú update felül fogja csapni, ami nem jó.
A fejlesztői jogot egyszerűen, db insert el oda tudod adni vagy elvenni. Te, aki hozzá férsz db hez.

Nem olyan bonyolult ez, csak próbáld meg helyén kezelni. :)
37

Akkor ha jól értem, a

Theo76 · 2017. Jan. 9. (H), 08.02
Akkor ha jól értem, a működést :):
- beállítom a GroupRoles táblán, hogy milyen role-ok tartoznak az adott grouphoz
- ha egy usert hozzáadok a grouphoz, akkor az adott groupe role-jait bemásolom a UserRoles táblába.

javítás:
Erre elfelejtettem még rákérdezni... mire gondoltál ezzel:
Roles szövegek lehetnének nyelvi kulcsok is.. :)
38

Kicsit több

Pepita · 2017. Jan. 11. (Sze), 21.16
Akkor ha jól értem, a működést
...
akkor az adott groupe role-jait bemásolom a UserRoles táblába
Ennél egy kicsit többre gondoltam, de asszem elég részletesen le is írtam.
User update:
- Lekéred UserGroups-ból, hogy melyik csoportok(!)ba tartozik
- JOIN GroupRoles, hogy meg legyen az összes szükséges jog
- A szép megoldás: Összehasonlítás UserRoles-al, és csak azt insert / delete, amit kell (pl. azért, hogy tudd követni mit mikor kapott).
- B lustáknak: kitörlöd összes jogát UserRoles-ból (marad a két spéci!), aztán beszúrod amit join-oltál előbb.

Fontos, hogy amit összeszedsz összes jogot, azt érdemes group by -olni, hogy ne duplikálj rekordot UserRoles-ban.

Erre elfelejtettem még rákérdezni... mire gondoltál ezzel:

Pont arra, amit írtam ( :) ): ha többnyelvű a rendszer, akkor ide nem kész szöveget írunk, csak a kulcsot hozzá, hogy megjelenítéskor a kívánt nyelven írjuk ki.
És miért ne lenne többnyelvű? ;)
39

User módosítás

Theo76 · 2017. Jan. 16. (H), 21.37
Nem sok időm volt mostanában, de a user módosítását megcsináltam, bár még biztos, hogy lehet rajta finomítani... :)

Az elindító class:
    public function addUserRole( $param ) {
        if ( isset( $param['group'] ) ) {
            $userRoles  = $this->dao->getUserRoles( $param['UserId'] );
/**
 * SELECT RoleId
 * FROM UserRoles
 * WHERE RoleId NOT IN (1,2) AND UserId=1
 **/
            $userGroups = $this->dao->getUserGroups( $param['UserId'] );
/**
 * SELECT g.GroupId, gr.RoleId
 * FROM `UserGroups` g
 * LEFT JOIN `GroupRoles` gr
 *      ON g.Id = gr.GroupId
 * WHERE g.UserId = 1
 * GROUP BY gr.RoleId
 **/
            $groups = array();

            foreach ( $userGroups as $value ) {
                if ( ( $key = Vars::in_array_md( $value['RoleId'], $userRoles ) ) !== FALSE ) {
                    unset( $userRoles[$key] );
                }

                if ( Vars::in_array_md( $value['GroupId'], $groups ) === FALSE ) {
                    $groups[] = $value['GroupId'];
                }
            }

            $groupRoles = $this->dao->getGroupRoles( implode( ',', $param['group'] ) );
/**
 * SELECT RoleId
 * FROM `GroupRoles`
 * WHERE GroupId IN (1)
 * GROUP BY RoleId
 **/

            $this->dao->addUserGroups( $param['UserId'], $groups );
            $this->dao->addUserRoles( $param['UserId'], $groupRoles );

            if ( !empty($userRoles ) )
                $this->dao->delUserRoles( $param['UserId'], implode( ',', $userRoles ) );
        }
    }
DAO class:
    public function getUserRoles( $id ) {
        $roles = $this->conn->getUserRoles( "UserRoles", SPEC_ROLE, $id );

        foreach ( $roles as $value ) {
            $role[] = $value['RoleId'];
        }

        return $role;
    }

    public function getUserGroups( $UserId ) {
        return $this->conn->getUserGroups( $UserId );
    }

    public function getGroupRoles( $needs ) {
        $groupRoles = $this->conn->getGroupRoles( $needs );

        foreach ( $groupRoles as $value ) {
            $groupRole[] = $value['RoleId'];
        }

        return $groupRole;
    }

    public function addUserRoles( $id, $param ) {
        $this->conn->addUserRoles( "UserRoles", array( 'UserId', 'RoleId' ), $id, $param );
    }

    public function addUserGroups( $id, $param ) {
        $this->conn->addUserGroups( "UserGroups", array( 'UserId', 'GroupId' ), $id, $param );
    }

    public function delUserRoles( $id, $param ) {
        $this->conn->delUserRoles( "UserRoles", $id, $param );
    }
A PDO class / Feldolgozó trait:

// Trait:
    public function getUserRoles( $table, $specRole, $id ) {
        return self::select( "SELECT RoleId FROM {$table} WHERE RoleId NOT IN ({$specRole}) AND UserId=" . intval( $id ) );
    }

    public function addUserRoles( $table, $fields, $id, $data ) {
        foreach ( $data as $value ) {
            $roles[] = array( 'UserId' => $id, 'RolesId' => $value  );
        }

        $where = " ON DUPLICATE KEY UPDATE UserId = VALUES(UserId), RoleId = VALUES(RoleId)";
        self::multiInsert( $table, $fields, $roles, $where );
    }

    public function addUserGroups( $table, $fields, $id, $data ) {
        foreach ( $data as $value ) {
            $roles[] = array( 'UserId' => $id, 'GroupId' => $value  );
        }

        $where = " ON DUPLICATE KEY UPDATE UserId = VALUES(UserId), GroupId = VALUES(GroupId)";
        self::multiInsert( $table, $fields, $roles, $where );
    }

    public function delUserRoles( $table, $id, $param ) {
        self::delete( $table, "UserId = {$id} AND RoleId IN ({$param})" );
    }


// PDO_Driver class:
    public function insert( $table, $data ) {
        ksort( $data );

        try {
            $fieldNames  = implode( ", ", array_keys( $data ) );
            $fieldValues = ":" . implode( ", :", array_keys( $data ) );

            $this->prepareStatement = $this->db->prepare( "INSERT INTO {$table} ({$fieldNames}) VALUES ({$fieldValues})" );

            foreach ( $data as $key => $value ) {
                $this->prepareStatement->bindValue( ":{$key}", $value );
            }

            $this->prepareStatement->execute();
        } catch (PDOException $exception) {
            die($exception->getCode() . ': ' . $exception->getCode());
        }

        return $this->db->lastInsertId();
    }

    public function multiInsert( $table, $fields, $data, $duplicateKey = '' ) {
        try {
            foreach ( $fields as $value ) {
                $fieldList[] = "`" . $value . "`";
            }

            $fieldList = '(' . implode( ",", $fieldList ) . ')';

            foreach ( $data as $value ) {
                $sqlArray[] = '(' . implode( ',', array_fill( 0, count( $value ), '?' ) ) . ')';

                foreach ( $value as $element ) {
                    $paramArray[] = $element;
                }
            }

            $sql = "INSERT INTO {$table} {$fieldList} VALUES " . implode( ',', $sqlArray ) . $duplicateKey;

            $this->prepareStatement = $this->db->prepare( $sql );

            $this->prepareStatement->execute( $paramArray );
        } catch ( PDOException $exception ) {
            die( $exception->getCode() . ': ' . $exception->getCode() );
        }
    }

    public function update( $table, $data, $where ) {
        ksort($data);

        $fieldDetails = NULL;
        foreach ($data as $key => $value) {
            $fieldDetails .= "{$key} = :{$key}, ";
        }

        $fieldDetails = rtrim($fieldDetails, ", ");

        $this->prepareStatement = $this->db->prepare( "UPDATE {$table} SET {$fieldDetails} WHERE {$where}" );

        foreach ( $data as $key => $value ) {
            $this->prepareStatement->bindValue( ":{$key}", $value );
        }

        $this->prepareStatement->execute();
    }

    public function delete( $table, $where, $limit = FALSE ) {
        $limit = ( $limit ) ? " LIMIT {$limit}" : "";

        return $this->db->exec( "DELETE FROM {$table} WHERE {$where}{$limit}" );
    }
40

Sajnos nem minden esetben működik...

Theo76 · 2017. Jan. 17. (K), 11.48
Hát még csiszolni kell ezt, csak nem tudtam törölni a bejegyzést...
Szerk:
Nah meg nézem kimaradt a csoport törlés...
41

Önállóan :)

Pepita · 2017. Jan. 17. (K), 16.57
Sorry, de többszáz sornyi kódot hobbyból napi szinten review-zni nem igazán tudok, ha más valakik igen, akkor hajrá, de szerintem fentiek alapján önállóan mennie kéne I. szakasznak.
Egy észrevétel: számomra a DAO (Data Access Object) egyetlen tábla egyetlen rekordját testesíti meg. Több rekordot már DataList reprezentál.
42

Egy lehetséges megoldásként

Theo76 · 2017. Jan. 17. (K), 17.16
Egy lehetséges megoldásként írtam ki a kódot, ahogy írtad még régebben, hogy ha megvan a megoldás tegyem közzé. :) Ezért próbáltam minél részletesebben kifejteni. Bár belegondolva így is maradtak ki dolgok. :) Közben már dolgozok a hiányosságon is, de majd a közzétételt lekorlátozom az sql parancsokra. :)
43

Ajjaj, nem jót mondtam

Pepita · 2017. Jan. 17. (K), 19.30
Ne haragudj, nem azt akartam mondani, hogy ne tegyél ki kódot - tegyél bátran! -, csak azt, hogy nekem nincs mindig időm (akár naponta) végignézni. (És ahogy látom, ebben a témában nem sok comment született kettőnkön kívül mástól.)
Ha már a kész a megoldás, és minden szempontból jól is műxik, akkor tök jó, ha megosztod.
Annak már jóval kevesebb értelmét látom, hogy ott van 100 - 300 sor, amikre hivatkozik, abból még részletek itt-ott, és pár mondat, aminek a vége az, hogy "még nem mindenütt műxik jól, ezeket javítom". Vagyis: "kár, hogy átnézted, mert más lesz, de még nem mondom meg, hogy mi". Kicsit sarkítottam, bocsi. :)

De. Nagyon fontos, hogy az én véleményem csakis vélemény, nem vagyok se moderátor, se semmi hasonló, szóval azt küldj be továbbra is, amit csak akarsz, ha az nekem sorozatosan nem tetszik, hát akkor nem olvasom. Ha WL-en nem való olyat beküldeni, majd szólnak moderátorok-szerkesztők és / vagy törlik.
44

Hát nem is lehet akármit :)

Pepita · 2017. Jan. 17. (K), 19.38
Gondold meg, mit írsz le! ;)
Én még törölni sose akartam, ha egy szál nagyon offba ment, akkor jeleztem szerkesztőknek, hogy tőlem törölhető - és eldöntik.
Így van ez, kirakod, mikor még csiszolatlan: örök időkre megmarad az utókornak, hogy bizony kapkodtál! :-D
Commentet szerkeszteni is csak egy ideig és / vagy addig lehet, míg nincs rá válasz. Én ilyenkor is csak hozzáírni szoktam, "Szerk:" kezdettel.
45

Igazad van! :)

Theo76 · 2017. Jan. 19. (Cs), 20.53
Köszönöm a tanácsod! A törlést azért akartam, mert másnap futottam bele a hibába, amikor is egy kivételbe ütköztem, ami relatív ritkán fordul elő. A bénázásokat nem szégyenlem, mert aki olvassa majd, talán tanul az én hibáimból, hogy hol lehet tökéletes módon félremenni. :) Amúgy nekem is feltűnt, hogy senki más nem szólt hozzá mint Te, de én annak tudtam be, hogy elég jó tanácsokat adtál, amibe nem nagyon volt érdemes beleszólni, mert max belekevertek volna. Aztán kitudja. :)

Viszont akkor most itt a már működő adatbázis, illetve azok egyes sql parancsok, amiket használok. Természetesen a User tábla csak alap adatokat használ még, illetve a nyelvi kulcsok még nincsenek megcsinálva.

--
-- Tábla szerkezet ehhez a táblához `GroupRoles`
--

CREATE TABLE `GroupRoles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`GroupId` int(11) NOT NULL,
	`RoleId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `GroupId_RoleId` (`GroupId`, `RoleId`) USING BTREE,
	INDEX `groups_grouproles_fk` (`GroupId`) USING BTREE,
	INDEX `roles_grouproles_fk` (`RoleId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Groups`
--

CREATE TABLE `Groups` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`Title` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Description` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `Title` (`Title`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Roles`
--

CREATE TABLE `Roles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`Title` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Description` varchar(512) COLLATE utf8_hungarian_ci DEFAULT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `Title` (`Title`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserGroups`
--

CREATE TABLE `UserGroups` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`UserId` int(11) NOT NULL,
	`GroupId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `UserId_GroupId` (`UserId`, `GroupId`) USING BTREE,
	INDEX `groups_usergroups_fk` (`GroupId`) USING BTREE,
	INDEX `users_usergroups_fk` (`UserId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `UserRoles`
--

CREATE TABLE `UserRoles` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`UserId` int(11) NOT NULL,
	`RoleId` int(11) NOT NULL,
	PRIMARY KEY(`Id`),
	UNIQUE INDEX `UserId_RoleId` (`UserId`, `RoleId`) USING BTREE,
	INDEX `users_userroles_fk` (`UserId`) USING BTREE,
	INDEX `roles_userroles_fk` (`RoleId`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- --------------------------------------------------------

--
-- Tábla szerkezet ehhez a táblához `Users`
--

CREATE TABLE `Users` (
	`Id` int(11) NOT NULL AUTO_INCREMENT,
	`FirstName` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`LastName` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`Email` varchar(128) COLLATE utf8_hungarian_ci NOT NULL,
	`LoginName` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Password` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
	`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
	`Hash` varchar(64) COLLATE utf8_hungarian_ci NOT NULL,
	`Phone` varchar(15) DEFAULT NULL,
	`Status` tinyint(1) DEFAULT NULL,
	PRIMARY KEY(`Id`),
	INDEX `FirstName` (`FirstName`) USING BTREE,
	INDEX `LastName` (`LastName`) USING BTREE,
	INDEX `LoginName` (`LoginName`) USING BTREE,
	INDEX `Password` (`Password`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci
AUTO_INCREMENT=1  
ROW_FORMAT=DYNAMIC;

-- ------------------------------------------------------------

--
-- Megkötések a kiírt táblákhoz
--

--
-- Megkötések a táblához `GroupRoles`
--
ALTER TABLE `GroupRoles`
	ADD CONSTRAINT `groups_grouproles_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
	ADD CONSTRAINT `roles_grouproles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`);

--
-- Megkötések a táblához `UserGroups`
--
ALTER TABLE `UserGroups`
	ADD CONSTRAINT `groups_usergroups_fk` FOREIGN KEY (`GroupId`) REFERENCES `Groups` (`Id`),
	ADD CONSTRAINT `users_usergroups_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

--
-- Megkötések a táblához `UserRoles`
--
ALTER TABLE `UserRoles`
	ADD CONSTRAINT `roles_userroles_fk` FOREIGN KEY (`RoleId`) REFERENCES `Roles` (`Id`),
	ADD CONSTRAINT `users_userroles_fk` FOREIGN KEY (`UserId`) REFERENCES `Users` (`Id`);

-- ------------------------------------------------------------

--
-- A tábla adatainak kiíratása `Roles`
--

INSERT INTO `Roles` (`Id`, `Title`, `Description`) VALUES
	(1, 'Fejlesztő', ''),
	(2, 'Regisztrált felhasználó', 'Saját adatait módosíthatja'),
	(3, 'Munkavégzés helye/munkahely/munkakör módosítás', ''),
	(4, 'Munkavégzés helye/munkahely/munkakör felvétel, törlés', ''),
	(5, 'Felhasználók adatainak módosítása', 'Más felhasználók adatainak módosítása'),
	(6, 'Felhasználó inaktívvá tétele', 'a felhasználó zárolása, hozzáférés megszüntetése (pl megszűnik a szerződés)'),
	(7, 'Felhasználó munkaadatainak módosítása', 'a munkavégzés helyének, jellegének, munkakörének módosítása'),
	(8, 'Csoport létrehozása/módosítása/törlése', ''),
	(9, 'Group Role hozzáadás/törlés', ''),
	(10, 'Role felvétele/módosítása', ''),
	(11, 'Kimutatások készítése', ''),
	(12, 'Szabadság engedélyezése/megtagadása 1 szinten', ''),
	(13, '1 szinten engedélyezett szabadság módosítása/törlése', ''),
	(14, 'Szabadság engedélyezése/megtagadása 2 szinten', ''),
	(15, '2 szinten engedélyezett szabadság törlése', '');

INSERT INTO `Groups` (`Id`, `Title`, `Description`) VALUES
	(1, 'Admin', ''),
	(2, 'Munkaügy', ''),
	(3, 'Szakmai vezető', ''),
	(4, 'Intézményvezető', '');

INSERT INTO `GroupRoles` (`Id`, `GroupId`, `RoleId`) VALUES
	(1, 1, 3),
	(2, 1, 4),
	(3, 1, 5),
	(4, 1, 6),
	(5, 1, 7),
	(6, 1, 8),
	(7, 1, 9),
	(8, 1, 11),
	(9, 2, 3),
	(10, 2, 4),
	(11, 2, 5),
	(12, 2, 6),
	(13, 2, 7),
	(14, 2, 11),
	(15, 3, 11),
	(16, 3, 12),
	(17, 3, 13),
	(18, 4, 11),
	(19, 4, 14),
	(20, 4, 15);
-- A user role-ok lekérdezése:
SELECT `RoleId`
FROM `UserRoles`
WHERE `RoleId` NOT IN (1,2) AND `UserId`=1;
-- User group-ok lekérdezése:
SELECT g.`GroupId`, gr.`RoleId`
FROM `UserGroups` g
LEFT JOIN `GroupRoles` gr
	 ON g.`Id` = gr.`GroupId`
WHERE g.`UserId` = 1
GROUP BY gr.`RoleId`;
-- User role-ok lekérés
SELECT `RoleId`
FROM `UserRoles`
WHERE `RoleId` NOT IN (1,2) AND `UserId`=1;
-- User group-ok hozzáadása (a user role-ok haszonlóképp)
Az ON DUPLICATE KEY UPDATE Id = VALUES(`Id`) sor kezeli le, hogy ne kerülhessen dupla bejegyzés a táblába.
INSERT INTO `UserGroups` (`UserId`,`GroupId`)
VALUES (1,1)
ON DUPLICATE KEY UPDATE `Id` = VALUES(`Id`);
Illetve az általad adott jogosultság lekérdezés kódja:
SELECT UR.`RoleId`
FROM `Users` U
LEFT JOIN `UserRoles` UR
     ON UR.`UserId` = U.Id
WHERE U.`Id` = 1
AND U.`Status` > 0
AND (UR.`RoleId` = 1
     OR UR.`RoleId` = 3)
LIMIT 1; 
Az eddigi tesztek alapján gyönyörűen teszi a dolgát. Mind a törlés, módosítás, és a hozzáadás terén, illetve a jogosultság ellenőrzéskor is.
Szóvak úgy érzem, hogy jöhet a következő fázis, amit az elején belekevertem a jogosultságokba... A cég hierarchia felépítése.