Zmiana właściciela schematu i znikające uprawnienia

W SQL Server istnieje dosyć ciekawe zjawisko związane z uprawnieniami i zmianą właściciela schematu. Co ciekawe mimo, iż informacje o tym możemy znaleźć w oficjalnej dokumentacji to jednak wiedza ta nie jest rozpowszechniona – dlatego też postanowiłem napisać o tym parę słów.

Tak więc na samym początku stwórzmy sobie w dowolnej testowej instancji trzy loginy. Każdy z nich będzie odgrywał w naszej demonstracji inną rolę tj.:

• TestUser – użytkownik będący właścicielem schematu o nazwie testSchema.
• Test_NewOwner – użytkownik, który przejmuje uprawnienia właściciela do schematu o nazwie testSchema
• Test_Reader – użytkownik, który ma przypisane prawa m.in do odczytu obiektów w schemacie testSchema

Stwórzmy więc ich za pomocą poniższych komend:

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'zaq1@WSX', DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [Test_NewOwner] WITH PASSWORD=N'zaq1@WSX', DEFAULT_DATABASE=[master]
GO
CREATE LOGIN [Test_Reader] WITH PASSWORD=N'zaq1@WSX', DEFAULT_DATABASE=[master]
GO

Następnie musimy stworzyć odpowiadających użytkowników w testowej bazie danych:

USE [test]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
CREATE USER [Test_NewOwner] FOR LOGIN [Test_NewOwner]
GO
CREATE USER [Test_Reader] FOR LOGIN [Test_Reader]
GO

Po stworzeniu użytkowników musimy stwórzyć schemat o nazwie testSchema, którego właścicielem będzie stworzony przed chwilą przez nas użytkownik TestUser:

USE [test]
GO
CREATE SCHEMA [testSchema] AUTHORIZATION [testUser]
GO

Następnie nadajmy dowolne uprawnienia dla użytkownika Test_Reader do schematu Test_Schema.

use [test]
GO
GRANT ALTER ON SCHEMA::[testSchema] TO [Test_Reader]
GO
GRANT DELETE ON SCHEMA::[testSchema] TO [Test_Reader]
GO
GRANT SELECT ON SCHEMA::[testSchema] TO [Test_Reader]
GO

Dla potwierdzenia podejrzymy okno uprawnień z poziomu GUI:

Teraz właśnie będzie miało miejsce ciekawe zachowanie SQL Server, wystarczy że zmienimy właściciela naszego testowego schematu na Test_NewOwner:

USE [test]
GO
ALTER AUTHORIZATION ON SCHEMA::[testSchema] TO [test_NewOwner]
GO

Naszym celem była zmiana własciciela całego schematu i tak też się stało, jednak to nie wszystko. Dodatkowo wraz ze zmianą własciciela użytkownik test_Reader utracił wszystkie uprawnienia jakie mu nadaliśmy do schematu! Dokładnie wszystkie tak jak możecie zobaczyć na poniższym zrzucie ekranowym:

Uprawnienia po prostu.. zniknęły. W dokumentacji znajdziemy opis mówiący o tym zjawisku. Dowiemy się również, że uprawnienia zostaną zabrane przy zmianie właściciela tylko dla obiektów, które nie mają jawnie wskazanego właściciela. Ważne jest to aby zawsze przy zmianie właściciela schematu sprawdzić czy nie ma uprawnień do tego właśnie obiektu i jeśli są to liczyć się z tym, że zostaną one usunięte. Przed wszelkimi operacjami tego typu warto stworzyć sobie skrypt uprawnień do schematu aby w późniejszym czasie w łatwy sposób je przywrócić.  Dobrym wyjściem jest również ustawienie jako właściciela wszystkich schematów w bazie użytkownika dbo lub też użytkownika dedykowanego i już w późniejszym czasie tego nie zmieniać.