User Forum

Code: Select all

/*
Kopieren der Rechte einer Benutzerrolle
VERWENDUNG AUF EIGENE GEFAHR

- manuelle Anlage der neuen Rolle
- Pflege der Quelle (von dort werden Daten übernommen)
- Pflege Ziel (dort werden erst Rechte entfernt, dann neu gesetzt)
- Einschränkungen über OU/Location werden nicht berücksichtigt, manuell nachpflegen

*/
DECLARE @Quelle AS NVARCHAR(50)
DECLARE @Ziel AS NVARCHAR(50)
DECLARE @QuelleID AS NVARCHAR(50)
DECLARE @ZielID AS NVARCHAR(50)
DECLARE @ZielCID AS NVARCHAR(50)
SET @Quelle = 'LHS Assetmanager Amt 10 Read'	-- Datenpflege achtung, Lokalisierung
SET @Ziel = 'LHS Assetmanager Amt 15 Read'		-- Datenpflege

SET @QuelleID = 
(SELECT [Expression-ObjectID]
FROM SPSSecurityClassRole scr
WHERE scr.Name = @Quelle)

SET @ZielID = 
(SELECT [Expression-ObjectID]
FROM SPSSecurityClassRole scr
WHERE scr.Name = @Ziel)

SET @ZielCID = 
(SELECT [ID]
FROM SPSSecurityClassRole scr
WHERE scr.Name = @Ziel)

--SELECT @QuelleID, @ZielID

/* Typenrechte löschen */
DELETE FROM PLSCTypeRule
WHERE ID IN
(SELECT tr.ID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN SPSCommonClassBase com ON scr.[Expression-ObjectID] = com.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdOU ON rcb.[Expression-ObjectID] = rdOU.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdLoc ON rcb.[Expression-ObjectID] = rdLoc.[Expression-ObjectID]
LEFT JOIN PLSCTypeRule tr ON scr.[Expression-ObjectID] = tr.[Expression-ObjectID] --Typenrechte
--LEFT JOIN PLSCAccessRule ar ON rcb.[Expression-ObjectID] = ar.[Expression-ObjectID] --Rechte Aktionen
WHERE tr.[Expression-ObjectID] = @ZielID
)

/* Typenrechte einfügen */
INSERT INTO PLSCTypeRule
(ID,[Read],[Insert],[Write],[Delete],[Type], UsedInTypeSPSSecurityTypeRole)
SELECT 
NEWID(),
tr.[Read],
tr.[Insert],
tr.[Write],
tr.[Delete],
tr.[Type],
@ZielID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN SPSCommonClassBase com ON scr.[Expression-ObjectID] = com.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdOU ON rcb.[Expression-ObjectID] = rdOU.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdLoc ON rcb.[Expression-ObjectID] = rdLoc.[Expression-ObjectID]
LEFT JOIN PLSCTypeRule tr ON scr.[Expression-ObjectID] = tr.[Expression-ObjectID] --Typenrechte
--LEFT JOIN PLSCAccessRule ar ON rcb.[Expression-ObjectID] = ar.[Expression-ObjectID] --Rechte Aktionen
WHERE scr.[Expression-ObjectID] = @QuelleID
AND @ZielID IS NOT NULL

/* Aktionsrechte entfernen */
DELETE FROM PLSCAccessRule
WHERE ID IN
(SELECT ar.ID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN SPSCommonClassBase com ON scr.[Expression-ObjectID] = com.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdOU ON rcb.[Expression-ObjectID] = rdOU.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdLoc ON rcb.[Expression-ObjectID] = rdLoc.[Expression-ObjectID]
--LEFT JOIN PLSCTypeRule tr ON rcb.[Expression-ObjectID] = tr.[Expression-ObjectID] --Typenrechte
LEFT JOIN PLSCAccessRule ar ON scr.[Expression-ObjectID] = ar.[Expression-ObjectID] --Rechte Aktionen
WHERE ar.[Expression-ObjectID] = @ZielID
)

/* Aktionsrechte setzen */
INSERT INTO PLSCAccessRule
(ID, [Execute], [Action], UsedInTypeSPSSecurityTypeRole)
SELECT 
NEWID(),
ar.[Execute],
ar.[Action],
@ZielID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN SPSCommonClassBase com ON scr.[Expression-ObjectID] = com.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdOU ON rcb.[Expression-ObjectID] = rdOU.[Expression-ObjectID]
--LEFT JOIN PLSCRuleDimensionOU rdLoc ON rcb.[Expression-ObjectID] = rdLoc.[Expression-ObjectID]
--LEFT JOIN PLSCTypeRule tr ON rcb.[Expression-ObjectID] = tr.[Expression-ObjectID] --Typenrechte
LEFT JOIN PLSCAccessRule ar ON scr.[Expression-ObjectID] = ar.[Expression-ObjectID] --Rechte Aktionen
WHERE scr.[Expression-ObjectID] = @QuelleID
AND @ZielID IS NOT NULL


/* Tab- und Dialogerechte entfernen */
DELETE FROM [SchemaRelation-SPSSecurityClassRole2SPSContentClassObjectDialogTab]
WHERE ID IN
(SELECT r2d.ID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN [SchemaRelation-SPSSecurityClassRole2SPSContentClassObjectDialogTab] r2d
	ON scr.ID = r2d.VisibleDialogTabs
WHERE scr.[Expression-ObjectID] = @ZielID
)


/* Tab- und Dialogerechte setzen */
INSERT INTO [SchemaRelation-SPSSecurityClassRole2SPSContentClassObjectDialogTab]
(ID, VisibleRoles, VisibleDialogTabs)
SELECT 
NEWID(),
r2d.VisibleRoles,
@ZielCID
FROM SPSSecurityClassRole scr
--INNER JOIN SPSScRoleClassBase rcb ON scr.[Expression-ObjectID] = rcb.[Expression-ObjectID]
INNER JOIN [SchemaRelation-SPSSecurityClassRole2SPSContentClassObjectDialogTab] r2d
	ON scr.ID = r2d.VisibleDialogTabs
WHERE scr.[Expression-ObjectID] = @QuelleID
AND @ZielID IS NOT NULL