SQL Schleife für Rechtevergabe

[Tomahawk145]

Guten Tag,

ich soll für meinen Chef einen skript schreiben, der beim ausführen 2 Rollen erstellt.
Eine Reader und eine Writer Rolle. Diese sollen beim erstellen eines neuen Users verteilt werden können.
Der Reader soll die Rechte SELECT und EXECUTE haben. Der Writer die Rechte UPDATE, DELETE und INSERT.

Allerdings muss ich diese Rechte für viele unterschiedliche Prozeduren vergeben. Diese sind in einer Tabelle abgelegt.
Ich muss eine Schleife schreiben, die dynamisch die Rechte auf die verschiedenen Prozeduren vergibt.
Also beispielsweise GRANT EXECUTE ON sp_INVPluginSelect TO [ed_reader]

Ich habe mir überlegt das mit einer WHILE schleife zu realisieren. allerdings weiß ich nicht wie ich das anstellen soll,
da der name ja jedes mal anders ist. gibt es vielleicht die möglichkeit die positionen durchlaufen zu lassen?

ich weiß nicht welche infos noch gebraucht werden, ist das erste mal das ich etwas mit SQL mache. habe vorher noch nichts damit zu tun gehabt

Grüße Tomahawk145

[Ralf Jansen]

Welches Datenbanksystem?

[akretschmer]

ich weiß nicht welche infos noch gebraucht werden, ist das erste mal das ich etwas mit SQL mache. habe vorher noch nichts damit zu tun gehabt

Grüße Tomahawk145

Du wirst dynamisches SQL brauchen. Je nach verwendetem DB-System, was ja geheim ist (bis jetzt).

[Tomahawk145]

öhm, also ich benutze die software "Atlantis SQL Everywhere" um ein "SQL Script File" zu schreiben. mehr infos hab ich leider auch nicht

[Christian Marquardt]

Du musst doch Wissen, für was für ein RDBMS du entwickelst; gerade wenn du mit Usern und Rechten arbeiten willst:

Atlantis SQL Everywhere is a free SQL Server IDE (Integrated Development Environment) which supports all versions of Microsoft SQL Server from 2000 to 2008 and comes with the most fully-featured and accurate SQL Intellisense® engine available – use it to turbocharge your productivity.

http://www.atlantis-interactive.co.u...sqleverywhere/

Verschoben

[Tomahawk145]

Für Windows Server bzw Microsoft SQL server. oder was meinst du? ob 2008 oder 2012 kann ich nicht genau sagen. weiß nicht wo mein cheffe das überall einsetzen will. soll als art schablone für neue systeme dienen

[Ralf Jansen]

Für Windows Server bzw Microsoft SQL server. oder was meinst du?

Das wir jetzt wissen das es ein MS SQL Server ist reicht.


Zur Anregung wie man einen Cursor und dynamisches SQL benutzt folgendes (habe ich nicht ausprobiert). INFORMATION_SCHEMA.ROUTINES solltest du durch deine eigene Tabelle ersetzen.

[HIGHLIGHT=SQL]DECLARE @query NVARCHAR(500),
@schema NVARCHAR(128),
@name NVARCHAR(128)

DECLARE curProcedures CURSOR FOR
SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'

OPEN curProcedures
FETCH NEXT FROM curProcedures Into @schema, @name

WHILE @@Fetch_Status = 0 BEGIN
SET @query = 'GRANT EXECUTE ON [' + @schema + '].[' + @name + '] TO [ed_reader]'
EXECUTE(@query)
FETCH NEXT FROM curProcedures Into @schema, @name
END

CLOSE curProcedures
DEALLOCATE curProcedures [/HIGHLIGHT]

[Tomahawk145]

das klingt doch schonmal gut. habe mich da jetzt etwas mit beschäftigt. jedoch bereitet mir eine zeile kopfzerbrechen. Hier erstmal der angepasste code:

DECLARE @query  NVARCHAR(500),
        @schema NVARCHAR(128),
        @name   NVARCHAR(128)
 
DECLARE ProcedureCursor CURSOR FOR 
 SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM [dbo] WHERE ROUTINE_TYPE = 'PROCEDURE'
 
OPEN ProcedureCursor 
FETCH NEXT FROM ProcedureCursor INTO @schema, @name
 
WHILE @@Fetch_Status = 0 BEGIN
    SET @query = 'GRANT EXECUTE ON [' + @schema + '].[' + @name + '] TO [Role_Reader]'  
    EXECUTE(@query)
    FETCH NEXT FROM ProcedureCursor INTO @schema, @name
END
 
CLOSE ProcedureCursor
DEALLOCATE ProcedureCursor

Die Zeile macht mir kopfschmerzen:

SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM [dbo] WHERE ROUTINE_TYPE = 'PROCEDURE'

Was genau muss in ROUTINE_SCHEMA, ROUTINE_NAME und ROUTINE_TYPE = 'PROCEDURE' rein?
und sagt jetzt nicht das schema, der name und den typ
wie hängt das genau zusammen? verstehe ich das richtig, dass man mit dem DECLARE eine zeile vorher den cursor erstellt
und ihm danach mit der SELECT zeile eben diese werte zu weist?

[Ralf Jansen]

INFORMATION_SCHEMA.ROUTINES ist eine Systemtabelle die alle Procedures und Functions enthält. Die habe ich nur als Beispiel verwendet. Du sprachst du hast ein eigene Tabelle die die Prozedurnamen enthält die solltest du dann auch benutzen.
Insofern sind ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE für dich irrelevant (da müßte auch eh nix rein darüber kämme was zurück) das sind Spalten aus besagter Systemtabelle. Wie die entsprechenden Spalten in deiner Tabelle heißen weiß ich nicht.

SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM [dbo] WHERE ROUTINE_TYPE = 'PROCEDURE'

dbo ist ein Schema keine Tabelle da kann man nix drauf abfragen. Außer du hast eine Tabelle dbo genannt. Indem Fall sag ich schon mal 'Gute Nacht'. Du mußt schon eine Tabelle nehmen.
Also ersetzte in dieser Abfrage [dbo] durch deine Tabelle und ROUTINE_SCHEMA, ROUTINE_NAME durch die Spaltennamen die du in deiner Tabelle verwendet hast.

wie hängt das genau zusammen? verstehe ich das richtig, dass man mit dem DECLARE eine zeile vorher den cursor erstellt
und ihm danach mit der SELECT zeile eben diese werte zu weist?

Man erstellt einen Cursor mit dem Declare ja. Der Select ist die vom Cursor verwendete Anweisung. Der wird an dieser Stelle aber nicht ausgeführt. Sondern jeder folgende FETCH führt den SELECT weiter aus und liefert die jedesmal die nächste Zeile, falls vorhanden, und packt die in die angegebenen Variablen (1. Spalte des Sql in erset Variable u.s.w.)

[Tomahawk145]

die prozeduren liegen in der sys.objects wenn ich das richtig verstehe. hier mal ein bild:

Unbenannt.jpg

also müsste es doch heißen:

SELECT name FROM sys.objects

[Ralf Jansen]

Hast du jetzt ein eigene Tabelle für die Prozeduren oder nicht?

Wen du keine hast und du alle ändern willst dann nimm so wie in meinem Beispiel INFORMATION_SCHEMA.ROUTINES oder wenn es unbedingt was aus dem sys Schema sein soll nimm sys.procedures. INFORMATION_SCHEMA ist ein standardisiertes Schema das solltest du eigentlich dem sys Schema bevorzugen.

[Tomahawk145]

habe soeben raus gefunden das die datenbank dbIdc heißt. Sorry, ist alles neu für mich. sei nachsichtig :-)

SELECT name FROM [dbIdc] WHERE ......

[Ralf Jansen]

Ich hoffe du meinst das dbIdc eine Tabelle ist und nicht eine Datenbank.

[Tomahawk145]

jetzt bin ich verwirrt. Weiß echt nicht wie meine Tabelle jetzt heißt.
Meine Datenbank ist die dbIdc, wo sehe ich jetzt wie die tabelle heißt, in der die prozeduren liegen?

2.jpg <--- hier mal der Baum

ist das dann doch die dbo?

[Ralf Jansen]

Wenn du alle Stored Procedures aus der Datenabnk willst dann ist es INFORMATION_SCHEMA.ROUTINES.

Du hast dich die ganze Zeit so ausgedrückt als ob du eine eigene selbst angelegte Tabelle mit den Namen der zu berücksichtigen Procedures hast.
In den Systemtabellen sind natürlich alle Procedures drin ich bzweifle das du die Rechte aller Procedures ändern willst. (Zum Beispiel wohl kaum die Systemeigenen die der SQL Server schon mitbringt)