Rozwiązanie problemów z niskimi uprawnieniami

Wybrani użytkownicy przypisani są tylko do grupy DOMENA\Group_DevelopmentBasic. Grupa ta przeznaczona jest dla początkujących deweloperów, która ma dostęp tylko do kilku baz produkcyjnych z rolą db_datareader. W wyniku wstępnych testów okazało się że użytkownicy z DOMENA\Group_DevelopmentBasic,
nie mają:

1. dostępu do jobów – należy umożliwić samo przeglądanie
2. dostępu do metadanych SSISDB – należy umożliwić samo przeglądanie,
3. dostępu do katalogu z projektami SSIS – – należy umożliwić samo przeglądanie
4. możliwości uruchamiania „Execution Plan” – – należy umożliwić samo przeglądanie

Rozwiązania problemów:

1. Dodanie do roli SQLAgentReaderRole w bazie msdb grupy DOMENA\Group_DevelopmentBasic
   (dostęp do Jobów, możliwość podglądu bez zmian – rozwiązanie problemu nr I)

USE [msdb]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [DOMENA\Group_DevelopmentBasic]
GO

2. Dodanie do roli db_datareader w bazie SSISDB grupy DOMENA\Group_DevelopmentBasic
   (możliwość odczytu tabel z metadanymi – rozwiązanie problemu nr II):

USE [SSISDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DOMENA\Group_DevelopmentBasic]
GO

3. Dodanie do roli db_ssisoperator w bazie msdb grupy DOMENA\Group_DevelopmentBasic
   (dostęp do katalogów SSIS, możliwość podglądu bez zmian – – rozwiązanie problemu nr III)

USE [msdb]
GO
ALTER ROLE [db_ssisoperator] ADD MEMBER [DOMENA\Group_DevelopmentBasic]
GO

4. Dodanie uprawnień do planu wykonania dla baz, których konto ma dostęp
   (rozwiązanie problemu nr IV):

USE [BAZA1]
GO
GRANT SHOWPLAN TO [DOMENA\Group_DevelopmentBasic]
GO

USE [BAZA2]
GO
GRANT SHOWPLAN TO [DOMENA\Group_DevelopmentBasic]
GO

5. Po dodaniu roli db_ssisoperator w bazie msdb grupy DOMENA\Group_DevelopmentBasic okazało się, że użytkownicy mają dostęp do katalogu SSISDB, ale nie widzą w SSISDB katalogów i projektów. W tym celu należy wykonać punkty od 5 do9:

5. Utworzenie roli ssis_reader w bazie SSISDB w celu umożliwienia dostępu do katalogów i projektów SSIS. Rola ta będzie wymagana również przy modyfikacji widoku catalog.executions w bazie SSISDB – dokładny opis problemu w pkt. 9

USE [SSISDB]
GO

CREATE ROLE [ssis_reader] AUTHORIZATION [dbo]
GO

6. Dodanie do roli ssis_reader w bazie SSISDB grupy DOMENA\Group_DevelopmentBasic.

USE [SSISDB]
GO

ALTER ROLE [ssis_reader] ADD MEMBER [DOMENA\Group_DevelopmentBasic]
GO

7. Dodanie uprawnień SELECT dla schemy catalog dla roli ssis_reader

use [SSISDB]
GO

GRANT SELECT ON SCHEMA::[catalog] TO [ssis_reader]
GO

8. Nadanie uprawnień do odczytu w ramach katalogów dla roli ssis_reader
   1. p.m. na nazwie katalogu SSIS i z menu wybrać Properties
   2. Po otwarciu okna (poniżej) wybrać przycisk Browse. Po otwarciu okna zaznaczyć role ssis_reader
   3. W oknie Excplicit zaznaczyć GRANT dla Permisions: ”Reader” i „Reader Objects”

9. Wykonując wszystkie powyższe czynności użytkownicy z grupy DOMENA\Group_DevelopmentBasic mają dostęp do projektów i pakietów w katalogach SSIS.

• W momencie gdy użytkownicy próbują dla dowolnego pakietu wybrać z menu pozycje Reports->All Executions lub jakikolwiek inny raport to zwracany jest pusty raport.

Przypominam, że użytkownicy mają wyłącznie prawa do odczytu danych (rola db_datareader ) na bazie SSISDB i nie są przypisani do roli ssis_admin, a ich rola serwerowa to public.

10. Spowodowane jest to tym, że podczas wyświetlania raportów, SSMS korzysta z widoków SSISDB.catalog.executions i SSISDB.catalog.event_messages. Z widoków tych mogą korzystać tylko użytkownicy, którzy są przypisani do roli ssis_admin lub są przypisani do roli serwerowej sysadmin.

W Widokach tych mamy warunek:

SSISDB.catalog.executions

ALTER VIEW [catalog].[executions]
AS

SELECT     execs.[execution_id],
execs.[folder_name],
…
…
…
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE     opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)

Należy widok ten zmodyfikować do postaci:

ALTER VIEW [catalog].[executions]
AS
SELECT     execs.[execution_id],
execs.[folder_name],
…
…
…
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE     opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
-- modyfikacja
OR (IS_SRVROLEMEMBER('sysadmin') = 1) OR (IS_MEMBER('ssis_reader') = 1)
SSISDB.catalog.event_messages

SSISDB.catalog.event_messages

ALTER VIEW [catalog].[event_messages]
AS
SELECT     opmsg.[operation_message_id] as [event_message_id],
opmsg.[operation_id],
…
…
…
WHERE     opmsg.[operation_id] in (SELECT [id] FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)

Należy widok ten zmodyfikować do postaci:

ALTER VIEW [catalog].[event_messages]
AS
SELECT     opmsg.[operation_message_id] as [event_message_id],
opmsg.[operation_id],
…
…
WHERE     opmsg.[operation_id] in (SELECT [id] FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER('ssis_admin') = 1)</pre>
<pre>-- modyfikacja</pre>
<pre>OR (IS_SRVROLEMEMBER('sysadmin') = 1) OR (IS_MEMBER('ssis_reader') = 1) 
 

Tym samym umożliwiamy użytkownikom przypisanym do roli ssis_reader korzystanie z tych widoków.

Linki:

http://dba.stackexchange.com/questions/78341/permission-to-view-execution-report-in-ssis-catalog

http://sqlwriter.blogspot.com/2013/12/ssiscatalog-report-execution-permission.html