Datenbankrolle Benutzer überprüfen

[tutter 0]

Hallo an alle, ich habe ein weiteres Problem was mir Kopfschmerzen bereitet.

 

Vielleicht könnt ihr ja helfen.

 

Ich habe eine eigene Datenbankrolle unter einer Datenbank erstellt. In diese Rolle habe ich ein Paar User gepackt.

 

Nun will ich über die Funktion IS_ROLEMEMBER herausbekommen ob der User sich auch darin befindet.

 

IF IS_ROLEMEMBER ('Testrolle') = 1
   print 'Current user is a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER (''Testrolle) = 0
   print 'Current user is NOT a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('Testrolle') IS NULL
   print 'ERROR: The database role specified is not valid.';

 

Naturlich bekomme ich die Meldung 'ERROR: The database role specified is not valid', aber wie kann ich den Skript sagen das er auf Datenbankebene suchen soll also zb. auf Testdatenbank.dbo.testtabelle

 

kann jemand helfen?

[NilsK 2.800]

Moin,

 

lautet deine Verbindung denn auf die Zieldatenbank?

 

Gruß, Nils

[tutter 0]

japp macht sie also :

 

USE Testdatenbank;

GO

 

IF IS_ROLEMEMBER ('Testrolle') = 1
   print 'Current user is a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER (''Testrolle) = 0
   print 'Current user is NOT a member of the db_datareader role'
ELSE IF IS_ROLEMEMBER ('Testrolle') IS NULL
   print 'ERROR: The database role specified is not valid.';

 

Unter dieser Testdatenbank befindet sich auch der angelegt User. muss ich im Skript noch irgendwas machen?

[NilsK 2.800]

Moin,

 

also, das Skript prüft in der abgedruckten Form, ob der gerade angemeldete User (also der, der die Abfrage ausführt) Mitglied in der angegebenen Rolle ist. Das funktioniert hier auch einwandfrei.

 

Wenn du die dritte Meldung bekommst, heißt das, dass der Rollenname nicht richtig ist. Was gibt dir denn "exec sp_helprole" zurück?

 

Gruß, Nils

PS. Der Tippfehler im zweiten IF-Statement ist im Original sicher nicht vorhanden, oder?

[tutter 0]

was ich jetzt weiter herausgefunden habe das er die User die ich in die Rolle übernehme auch mit IS_Rolemember auslesen kann bzw. er mir eben sagt das es ein Mitglied der Rolle ist.

 

Jedoch macht der das nicht mit den sich in der Domän-befindlichen Usern. Warum ? verstehe das Verhalten überhaupt nicht!

 

Der DomainUser wird bestens ausgelesen vom SQL Server und kann der Rollle hinterlegt werden. Wenn man sich nun mit dem DomainUser am SQL Server über Windows Authentifizierung anmeldet kann ich mich zwar im SQL Server sauber anmelden aber das Skript funktioniert nicht bzw. oder mach ich was falsch oder interpretier hier was falsch?

Fehler gefunden!

 

If the domain controller is not available, the call to IS_ROLEMEMBER will return accurate information when the Windows principle can be authenticated locally, such as a local Windows account or a SQL Server Login.

 

Zitat Microsoft!

 

Jedoch stellt sich mir nun die Frage wie sonst? Wie kann ich Überprüfen ob ein Domän-Mitglied in einer Rolle ist?

[NilsK 2.800]

Moin,

 

gut, dann sollten wir jetzt mal anfangen, über deine eigentliche Anforderung zu reden. Sonst suchen wir nur nach einem Problem deiner Lösung und nicht nach einer Lösung deines Problems.

 

Also, was genau willst du erreichen? Wozu brauchst du die Prüfung? Was willst du damit anfstellen?

 

Gruß, Nils

[tutter 0]

Die Anforderung ist eigentlich ganz einfach. Es soll zwei (in MS-Deutsch Rollen)Gruppen geben pro Datenbank. Die eine kann nur lesen und die andere kann lesen und schreiben. Wenn nun ein Programm(PHP Eigenentwicklungen) etwas in die Datenbank schreiben will dann soll eine Fehlermeldung kommen. zb. Du hast keine Rechte dafür. Das wollte ich gewährleisten mit dem auslesen der Rechte an sich. zb, Wenn User X in Rolle lesen ist dann gib über php beim ausführen die Meldung Du hast keine Rechte dafür aus.  

 

Also sozusagen eine einfache Rechtesteuerung für die Programme die mit dem SQL Server zusammenarbeiten. Ein muss ist das das ganze aber über die Windows AD Authentifizierung laufen muss! Die SQL Authentifizierung geht dafür leider nicht!

[NilsK 2.800]

Moin,

 

aha. Und reicht es nicht, in der PHP-Anwendung die SQL-Fehlermeldung auszulesen, wenn der nicht berechtigte User nicht "schreiben" kann? Eine Fehlerbehandlung brauchst du ja ohnehin.

Oder: Wenn es sowieso Windows-Authentifizierung sein soll, kannst du ja auch die Windows-Gruppenmitgliedschaft direkt abfragen, statt dies indirekt über SQL Server zu tun. Spart dir auch eine Datenbankabfrage. Wäre vom Design her stimmiger.

 

Ganz abgesehen davon, hat genau das von dir beschriebene Szenario bei mir funktioniert: Zugriff mit einem Windows-User, der in der Datenbankrolle Mitglied ist, das Skript-Fragment hat die richtige Antwort gegeben. (Wie gesagt, die von dir genannte Meldung tritt dann auf, wenn es die Rolle gar nicht gibt - mit DC usw. hat das gar nichts zu tun. Vielleicht willst du ja noch mal meine obige Frage beantworten.)

 

Gruß, Nils

[tutter 0]

das exec sp_helprole habe ich naturlich auch ausprobiert und es kommt naturlich nur 0 raus! was ja auch klar ist, weil es ja keine anwendungsbezogene Rolle ist sondern eben benutzerbezogene rolle.

 

ja eigentlich könnte man auch die fehlermeldung auswerten. aber ich wollte eine elegantere lösung.

 

gib mal noch ein paar mehr infos wie das bei dir abgelaufen ist und wie oder was du konkret getestet hast. wie gesagt bei einem user aus dem DC, der sich in der rolle befindet, klappt das skript nicht! ich erhalte immer NULL bzw. die Fehlermeldung die das Skript ausgeben soll. Nutze ich das Skript für einen lokalen User oder nur einen DatenbankUser klappt das Skript und ich erhalte 0 wenn nicht in Rolle oder eben 1 wenn in Rolle.

 

Das Unterstreicht dann auch die Aussage von Microsoft.

 

Aber wenn das bei dir klappt, würde ich gern wissen was du einsetzt. Denn für mich wäre diese Lösung noch immer die beste.

 

Achso eine Auslesung der Gruppenmitgliedschaft würde auch nichts bringen, da diese Gruppen leider nicht mal ansatzweise mit den Gruppen die ich im SQL Server benötige übereinstimmen.

[NilsK 2.800]

Moin,

 

es wäre schön, wenn du dir eine exaktere Ausdrucksweise angewöhnst. (Und darüber hinaus die Shift-Taste nicht nur sporadisch einsetzt, das macht das Lesen leichter.)

 

sp_helprole kann gar nicht "0" ausgeben, weil es eine Tabelle zurückgibt, und zwar die Liste der vorhandenen Datenbankrollen. Sowohl Anwendungsrollen als auch herkömmliche Rollen. Da es in jeder Datenbank Rollen gibt, ist eine leere Ausgabe nicht möglich. Was eine "benutzerbezogene Rolle" sein soll, weiß ich nicht - SQL Server kennt sowas jedenfalls nicht.

 

In meinem Test habe ich eine neue Rolle in einer Datenbank angelegt und dort einen (= meinen) Windows-Domänen-Account aufgenommen. Dann habe ich ein Abfragefenster mit der Datenbank verbunden und dein Skriptfragment ausgeführt. Das Ergebnis war korrekt.

 

Das Skript kann auch gar nicht NULL ausgeben. Es gibt eine der drei Meldungen, oder der Server gibt eine Fehlermeldung zurück.

 

Also, entweder einigen wir uns jetzt auf exakte Beschreibungen, oder ich kann dir nicht mehr helfen.

 

Gruß, Nils

bearbeitet 1. August 2014 von NilsK

[tutter 0]

ok Tipps werde ich versuchen umzusetzen. Also noch mal exakter! (war vielleicht auch schon etwas Spät gestern)

 

sp_helprole liefert natürlich nicht einfach so eine schnöde 0, sondern listet eine Tabelle auf und in der Spalte IsAppRole steht, bei den hier beleuchteten Rollen, eine 0. Wie schon geschrieben, soweit ok!  

 

Nun zurück zum eigentlichen Problem! Mittlerweile (nach einem Neustart des SQL Servers) erhalte ich als Rückgabewert eine 0! Somit erhalte ich die Fehlermeldung : "Current user is NOT a member of the ..." Das stimmt aber nicht da dieser User ein Domain Mitglied ist und sich sauber über Remote auf dem Server und anschließend an dem SQL Server (über WindowsAuthentifizierung) angemeldet hat. Schön zu sehen ist dies im Management Studio, wo der User sauber mit seinen Daten angezeigt wird. Soll heißen er steht dort mit Domain\User. Wenn ich nun in die Rolle schaue steht er ebenfalls in der Rolle mit exakt dieser Bezeichnung drin! Der User hat in der Rolle min. db_datareader Rechte. In einer anderen Rolle (ebenfalls schon getestet) hat er db_datareader und db_datawriter Rechte.

 

Wenn ich nun einen User nehme welcher nur dem lokalen System bekannt ist, also kein Domain User, wird is_rolemember mit 1 Quittiert da dieser User sich in der entsprechenden Rolle befindet. Nehme ich eine Rolle in der sich der User nicht befindet wird dies mit 0 quittiert. Eine nicht existierende Rolle wird mit NULL Quittiert! Soweit also alles Richtig!

 

Mach ich das ganze mit einen User der nur dem SQL Server bekannt ist und welcher sich dann auch mit einem SQL Server Login am SQL Server anmeldet klappt das auch!

 

Aber warum nicht beim Domain User?

 

eine eventueller Hinweis könnte dies hier sein: http://msdn.microsoft.com/de-de/library/ee677633.aspx

 

ich stolpere immer über diesen Satz :

 

Wenn database_principal auf einer Windows-Anmeldung basiert, z. B. Contoso\Mary5, gibt IS_ROLEMEMBER den Wert NULL zurück, falls database_principal der direkte Zugriff auf SQL Server nicht erteilt oder verweigert wurde.

 

kann aber damit noch nicht so recht etwas anfangen. Vielleicht kann jemand hierzu was sagen? Vielleicht fehlt mir genau diese Einstellung?

[NilsK 2.800]

Moin,

 

wie gesagt: Du solltest dich bei dem, was du vorhast, bitte mal mit den Grundlagen befassen. Hier insbesondere mit dem Zugriffsmodell des SQL Server.

 

 

Der User hat in der Rolle min. db_datareader Rechte. In einer anderen Rolle (ebenfalls schon getestet) hat er db_datareader und db_datawriter Rechte.

 

Mir ist völlig unklar, was du damit sagen willst. In einer Rolle ist man drin oder eben nicht. Man kann "in einer Rolle" keine "Rechte haben". Man kann höchstens in mehreren Rollen gleichzeitig Mitglied sein, aber die Rollen haben nichts miteinander zu tun.

 

Verbinde dich bitte mal mit deiner Datenbank und führe dann aus:

EXEC sp_helprolemember 'NAME-DER-ROLLE'

Was gibt das zurück?

 

Dann verbinde dich mit der master-Datenbank und führe aus:

EXEC sp_helplogins

Taucht dein User dort auf?

 

Dann wieder zurück in die Datenbank und dort:

EXEC sp_helpuser

Taucht dein User dort auf?

 

Vielleicht liegt das Problem auch im Verständnis. Dein Skriptfragment prüft nicht, ob "irgendein" User in der Rolle ist. Es prüft, ob der User, der das Skript ausführt, in der Rolle ist. Du müsstest dich als als dieser User anmelden und dann das Skript ausführen.

 

Gruß, Nils

bearbeitet 1. August 2014 von NilsK

[tutter 0]

Hallo Nils, danke erst mal für deine Geduld :jau: .  

 

Aber ich glaub jetzt zweifelst du langsam endgültig an mir und ich kann mir auch keine Reim daraus machen.

 

Also ich habe eine Datenbankrolle erstellt welche einfach test_lesend heißt! Diese Besitzt die Rechte von db_datareader (MS nennt das: "Schemas im Besitz dieser Rolle: ")und hat Mitglieder wie zb. unseren Domain\testaccount weitere Mitglieder sind lokal_user, SQL_user

 

die letzten 2 User sind entweder als lokaler User dem System aber nicht der Domain bekannt und SQL_user ist nur dem SQL Server bekannt. Mit den zweien klappt ja auch alles deswegen gehe ich auf beide auch nicht weiter ein.

 

Nur der User Domain\testaccount macht Ärger!

 

Nun zum besten :rolleyes: in allen deinen 3 Abfragen steht mein User (Domain\testaccount) drin! :( Tja nun weiß ich auch nicht mehr warum es nicht geht, aber ich habe einen Verdacht. Ich habe irgendwie noch im Kopf das sich die Rollen unter SQL Server 2012 nun erstmals auch auf Serverebene erstellen lassen. Das kann mein SQL Server 2008 R2 noch nicht :nene:. Könnte es daran mit liegen? ich würde es gern mal testen aber ein 2012er System steht mir heute Abend erst wieder zur Verfügung.  

 

Nachtrag: Grundsätzlich nutze ich über Remote Desktop die Thematik aus, das ich mich auf dem SQL Server einmal per DomainUser und einmal per Lokalen User sowie über einen SQL Server User anmelde und das Skript laufen lasse.

bearbeitet 1. August 2014 von tutter

[NilsK 2.800]

Moin,

 

sorry, ich strecke hier die Waffen. Hier gehen so viele Begriffe durcheinander, dass wir das auf diesem Weg nicht mehr hinbekommen. Vermutlich würden wir das Thema schnell lösen können, wenn wir gemeinsam vor dem Bildschirm säßen, aber schriftlich reden wir offenbar zwei verschiedene Sprachen.

 

Gruß, Nils

[tutter 0]

ja das habe ich mir schon gedacht! ich habe noch 2 vm´s und werde es weiter probieren! Ich gehe auch noch mal der Thematik mit den Versionen nach. Sollte ich was finden poste ist es hier rein. Trotzdem danke für die Mühen!