[SQL] Maken van een test script voor grants en views

[UPPERKEES]

Beste Tweakers,

Ik ben vandaag bezig gegaan met een SQL database. Ik heb een database gemaakt met daarop wat views en grants. Op het internet was hier veel informatie voor beschikbaar, maar nu wil ik het ook kunnen testen in een script die kijkt of dit werkt. En daar vind ik helaas niets over op het internet.

Daarmee bedoel ik dus een script die checkt of de juiste gebruiker is ingelogd, en zo ja of die inderdaad de views die hij mag zien, ook kan zien en die hij niet mag zien, niet ziet.

Mijn vraag aan jullie is dus hoe zou zo'n test script er ongeveer uit moeten zien? Ik hoop dat dit niet te lui klinkt, maar ik heb simpel weg niets gehad aan Google searches

Alvast bedankt!

Hieronder staan alle gegevens

---

ERD:
-- Vakkenpakket ( vakkenpakketnr, minaantpunt )
-- Vakken ( vaknr, naam, omschrijving, studiepunten, <vakkenpakketnr> )
-- Groepen ( groepnr, <vakkenpakketnr> )
-- Leerlingen ( leerling_userid, naam, adres, postcode, woonplaats, telefoonnr, mobielnr , email ,<groepnr>)
-- Docenten ( docent_userid, naam, adres, postcode, woonplaats, telefoonnr, mobielnr , email ,<vaknr>)
-- Rooster ( roosterid, <groepnr>,<vaknr>,<docent_userid>, datum, tijd, lokaal )
-- Cijfers ( cijferid, <vaknr>,<leerling_userid>, cijfer )
-- Absentie ( absentienr, <leerling_userid>,<docent_userid>, datum)

Views

code:

select LEERLING_USERID, lrl.naam, lrl.adres, lrl.postcode, lrl.woonplaats, lrl.woonplaats, lrl.telefoonnr, lrl.mobielnr, lrl.email, grp.naam as groep, vpk.naam as opleiding
from leerlingen lrl, vakkenpakket vpk, groepen grp
where grp.vakkenpakketnr = vpk.vakkenpakketnr
and lrl.groepnr = grp.groepnr
and leerling_userid = USER;

--view van cijfers van leerlingen
create view Cijferlijst as
select cijfers.cijfer, vakken.naam
from Cijfers, Vakken
where Cijfers.vaknr = Vakken.vaknr
and Leerlingen.leerling_userid = USER;

--view van rooster van leerlingen
create view Rooster as
select datum, tijd, lokaal, Leerlingen.groepnr, Docenten.naam
from Docenten, Roosters, Leerlingen, Groepen
where Docenten.docent_userid = Roosters.docent_userid
and Leerlingen.groepnr = Groepen.groepnr
and Groepen.groepnr = Roosters.groepnr
and Leerlingen.leerling_userid = USER;

Grants:

code:

--Grants voor de leerlingen op de views
GRANT   SELECT
ON      Leerling_gegevens
TO      Leerling;

GRANT   SELECT
ON      Docenten_overzicht 
TO      Leerling;

GRANT   SELECT
ON      Cijferlijst 
TO      Leerling;

GRANT   SELECT
ON      Rooster
TO      Leerling;

--Grants voor de administratie op alle tabellen behalve de cijfers
GRANT   ALL PRIVILEGES
ON      Vakken
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Vakkenpakket
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Groepen
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Leerlingen
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Docenten
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Rooster
TO      Administratie;

GRANT   ALL PRIVILEGES
ON      Absentie
TO      Administratie;

--grants voor de docenten
grant insert, select, update, delete
on Cijfers
to Docent

grant select 
on leerlingen, (view rooster)
to Docent

grant insert, select
on absentie
to Docent

[MrBucket]

Er vanuitgaande dat je met "test script" niet een geautomatiseerd (unit-)test script bedoelt (waar meestal NUnit of JUnit voor gebruikt wordt), zou ik zeggen dat je gewoon met de login van (bijv.) een leerling verbinding moet maken met je database, en een select, insert, etc. op de betreffende tabel of view moet uitvoeren?

Als je een resultset krijgt of er zijn rows affected, dan heb je genoeg rechten, krijg je een error dan heb je niet genoeg rechten.

Of snap ik je vraag niet goed?

[ Voor 5% gewijzigd door MrBucket op 24-01-2009 19:37 . Reden: Kleine uitbreiding ]

[Verwijderd]

Je moet dit soort dingen helemaal niet met SQL rechten doen. Je applicatie moet alle nodige rechten hebben op de database om de nodige mutaties te kunnen doen. Vervolgens moet je op applicatie-niveau regelen wie wat mag zien en wat mag wijzigen.

[MrBucket]

Verwijderd schreef op zaterdag 24 januari 2009 @ 19:41:
Je moet dit soort dingen helemaal niet met SQL rechten doen. Je applicatie moet alle nodige rechten hebben op de database om de nodige mutaties te kunnen doen. Vervolgens moet je op applicatie-niveau regelen wie wat mag zien en wat mag wijzigen.

Een beetje kort door de bocht. Je weet helemaal niet welke architectuur de topicstarter wil hanteren, voor hetzelfde geld wordt het een 2-tier applicatie waar thick clients rechtstreeks tegen de database aan gaan kletsen...

[cariolive23]

Verwijderd schreef op zaterdag 24 januari 2009 @ 19:41:
Je moet dit soort dingen helemaal niet met SQL rechten doen. Je applicatie moet alle nodige rechten hebben op de database om de nodige mutaties te kunnen doen. Vervolgens moet je op applicatie-niveau regelen wie wat mag zien en wat mag wijzigen.

Tuurlijk, dat is vooral handig wanneer je meerdere applicaties op één database hebt zitten... NOT!

Een database gooi je dicht, tenzij je voor een specifieke gebruiker (of gebruikersgroep) toegang moet geven. En dan geef je uitsluitend toegang tot die delen die noodzakelijk zijn. Of denk je nu echt dat het aanmaken van gebruikers, rollen en het toepassen van GRANT en REVOKE klinkklare onzin is en is bedacht door mensen met teveel vrije tijd?

Dat 9 van de 10 databases zo lek als een mandje zijn, wil niet zeggen dat dit goed is...

[Verwijderd]

MrBucket schreef op zaterdag 24 januari 2009 @ 19:53:

Een beetje kort door de bocht. Je weet helemaal niet welke architectuur de topicstarter wil hanteren, voor hetzelfde geld wordt het een 2-tier applicatie waar thick clients rechtstreeks tegen de database aan gaan kletsen...

Waarbij elke leerling dus alle gegevens zou kunnen zien van andere leerlingen?

cariolive23 schreef op zondag 25 januari 2009 @ 20:59:

Tuurlijk, dat is vooral handig wanneer je meerdere applicaties op één database hebt zitten... NOT!

Een database gooi je dicht, tenzij je voor een specifieke gebruiker (of gebruikersgroep) toegang moet geven. En dan geef je uitsluitend toegang tot die delen die noodzakelijk zijn. Of denk je nu echt dat het aanmaken van gebruikers, rollen en het toepassen van GRANT en REVOKE klinkklare onzin is en is bedacht door mensen met teveel vrije tijd?

Nee, die gebruikers c.q. gebruikersgroepen zou je meer moeten zien om applicaties te scheiden. Als een applicatie geen INSERT, UPDATE of DELETE hoeft uit te voeren, moet je die ook niet geven. Eindgebruikers voor je applicatie zijn totaal iets anders dan database users. Dat moet je absoluut apart zien en ook zo behandelen.

[eamelink]

Verwijderd schreef op zondag 25 januari 2009 @ 21:04:
Eindgebruikers voor je applicatie zijn totaal iets anders dan database users. Dat moet je absoluut apart zien en ook zo behandelen.

In de dagelijkse praktijk is dat inderdaad zo. Maar ik zie niet zo goed in waarom dat absoluut zou moeten? Meestal is het gewoon uit gemakszucht om één databaseuser aan te maken voor een applicatie.

[MrBucket]

Verwijderd schreef op zondag 25 januari 2009 @ 21:04:
[...]

Waarbij elke leerling dus alle gegevens zou kunnen zien van andere leerlingen?

Als je de grants alleen op views en tables uitdeelt wel ja. Als je de toegang beperkt tot stored procedures die ervoor zorgen dat elke leerling alleen zijn eigen informatie mag raadplegen, dan ben je perfectly safe.
[...]

Nee, die gebruikers c.q. gebruikersgroepen zou je meer moeten zien om applicaties te scheiden. Als een applicatie geen INSERT, UPDATE of DELETE hoeft uit te voeren, moet je die ook niet geven. Eindgebruikers voor je applicatie zijn totaal iets anders dan database users. Dat moet je absoluut apart zien en ook zo behandelen.

In de context van een SOA-applicatie waarschijnlijk wel ja, maar nogmaals: je kent de hele architectuur niet van de applicatie. In 2-tier applicaties kun je best gebruikers mappen op database-users (SQL server ondersteunt niet voor niets windows authentication), en in het pre-SOA tijdperk was dat ook een solide oplossing: database dichttimmeren - rechten worden uitgedeeld door de DBA.

Wanneer je met een three-tier (of hoger) architectuur gaat werken, waar de database alleen benaderd kan worden door een tier die volledig onder controle staat van beheerders, dan kun je overwegen om je rechten niet op de database uit te delen, maar op een hogerliggende tier.

[cariolive23]

Nee, die gebruikers c.q. gebruikersgroepen zou je meer moeten zien om applicaties te scheiden. Als een applicatie geen INSERT, UPDATE of DELETE hoeft uit te voeren, moet je die ook niet geven. Eindgebruikers voor je applicatie zijn totaal iets anders dan database users. Dat moet je absoluut apart zien en ook zo behandelen.

En een gebruiker die meerdere applicaties gebruikt op dezelfde database, kan de ene keer wel over bepaalde rechten beschikken en de andere keer niet? Ik moet er niet aan denken om dat te moeten gaan beheren.

Gewoon via LDAP voor iedere gebruiker een eigen account die is gekoppeld aan een bepaalde rol binnen de database en de problemen zijn opgelost. Nooit gezeik, ik weet precies wat ze wel en niet kunnen doen. Tevens kan ik nu loggen wie wat heeft ingevoerd, gewijzigd of verwijderd, ik kan op databaseniveau zien wie er is ingelogd. De verschillende applicaties hoeven dus niet zelf een loggingsmechanisme opnieuw uit te vinden, dat gebeurt op 1 centrale plek: de database.