Odmawiaj dostępu do schematu informacyjnego w SQL Server

Szukam najlepszego sposobu na wyłączenie dostępu do sys.tables/ Information Schemadla użytkownika / grupy w SQL Server.

Znalazłem ten wątek z 2008 roku

Pokazuje sposób odmowy dostępu w następujący [sys].[something]sposób:

 DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
 GO
 DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
 GO

Ale nie ma mowy, jak wyłączyć dostęp w Information Schema:

DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRole

To wydaje się nie działać.

Jak mogę wyłączyć dostęp do Information_schema?

Czy jest łatwiejszy sposób zablokowania dostępu do wszystkich sys/ information_schema?

Aktualizacja: w rzeczywistości nie mogę uruchomić obu następujących instrukcji:

DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GO

Próbowałem uruchomić je na konkretnej bazie danych, na której istnieje Użytkownik, a także próbowałem na „głównym”.

Nadal mogę biegać:

 SELECT * from
 INFORMATION_SCHEMA.TABLES 

-> nadal zwraca wyniki

 SELECT * from
 sys.TABLES 

-> brak wyników

Uwzględnienie SCHEMA::w zapytaniu umożliwiło utworzenie zabezpieczeń

DENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GO

Ale teraz nadal mogę wybrać wszystkie informacje z bazy danych.

Rzuciłem okiem na kartę „Zabezpieczenia” w oknie właściwości użytkownika w Management Studio 2008, wygląda to tak:

Wpis, który blokuje wybór sys.tables

Schemat: sys, Nazwa: tabele, Typ: Widok

Uprawnienia do sys.tables: Uprawnienie: Wybierz, Udzielający: dbo, Odmowa jest zaznaczona

Wpis, który nie blokuje żadnego wyboru

Schemat :, Nazwa: INFORMACJE_SCHEMA, Typ: Schemat

Uprawnienia dla INFORMACJE_SCHEMA: Zezwolenie: Wybierz, Udzielający: dbo, Odmowa NIE jest zaznaczona (próbowałem to sprawdzić, ale nie ma szans ..)

Pozwolenie: Wybierz, Udzielający: INFORMACJE_SCHEMA, Odmowa jest zaznaczona

Próbowałem ustawić uprawnienia w interfejsie GUI, ale wtedy pojawia się ten sam błąd, że ustawienie uprawnień będzie możliwe tylko w głównej bazie danych. Ale nie mam dodanego użytkownika / loginu do głównego zabezpieczenia DB.

Rozwiązanie:

Jedynym sposobem mogę zrobić denypracę za information_schemabyło dodać użytkownika do master-db i uruchomić deny selectna wzorcu:

DENY SELECT ON [sys].[tables] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA.TABLES To reducedDBO
GO

I jak w tym kodzie, można go wykonać tylko dla pojedynczych tabel.

Powinieneś być w stanie po prostu odmówić uprawnień dla całego sysi całego information_schemaschematu:

DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]

To powinno w zasadzie po prostu uniemożliwić temu użytkownikowi dokonywanie wyborów w tych dwóch schematach.

Po pierwsze, masz rację, ponieważ (nieco sprzecznym z intuicją) sposobem zapobiegania dostępowi do schematów [sys] i [INFORMACJE_SCHEMA] jest najpierw upewnienie się, że login (cóż, nazwa użytkownika na poziomie serwera) istnieje jako użytkownik (erm, główny poziom bazy danych) w głównej bazie danych.

Załóżmy, że masz login SQL dla uproszczenia:

CREATE LOGIN [testy] WITH PASSWORD=N'SCoBIqlJELGzrY9zYsKWC5z3kHtMsyCAP6yBHLUYQ0w='
go

Teraz utwórz odpowiedniego użytkownika w głównej bazie danych:

use [master]
go
CREATE USER [testy] FOR LOGIN [testy]
go

Teraz chcesz uniemożliwić temu logowaniu dostęp do dowolnej tabeli w schematach dostarczonych przez system - [sys] i [INFORMACJE_SCHEMA].

Wygląda na to, że nastąpiła zmiana zachowania między SQL Server 2008 R2 a SQL Server 2012:

W SQL Server 2012 (i przypuszczalnie późniejszych wersjach) uruchamianie następujących czynności w [master] bazie danych działa tak, jak można się spodziewać:

DENY SELECT, VIEW DEFINITION ON SCHEMA::[sys] to [testy];
GO
DENY SELECT, VIEW DEFINITION ON SCHEMA::[INFORMATION_SCHEMA] to [testy];
GO

Jednak w SQL Server 2008 R2 (i przypuszczalnie wcześniejszych wersjach) oświadczenia o przyznaniu zapasów dające dostęp do obiektów w tych schematach członkom [public] wydają się zastępować powyższe instrukcje DENY, co wydaje mi się ogromną kupą porażki. W związku z tym w 2008 R2 musisz wyraźnie ODMOWAĆ dla każdego GRANTU [publicznego]. Oto skrypt, aby to zrobić:

declare
    @database_principal sysname,
    @cur cursor,
    @sql nvarchar( 4000 );

set @database_principal = 'testy';

set @cur = cursor local forward_only static for
    select 
        'DENY ' +
        permission_name + ' on ' +
        case class 
            when 1 then
                case minor_id
                    when 0 then 'OBJECT'
                    else 'COLUMN'
                end
            else
                class_desc
        end + '::' +
        case class
            when 0 then db_name()
            when 1 then quotename( OBJECT_SCHEMA_NAME(major_id) ) + '.' + quotename( object_name( major_id ) ) + case minor_id when 0 then '' else ( select '.' + quotename( name ) collate database_default from sys.columns where column_id=minor_id) end
            when 3 then schema_name( major_id )
        end + ' to ' +
        quotename( @database_principal )
    from
        sys.database_permissions
    where
        [grantee_principal_id] = 0 -- public
        and
        [state_desc] = 'GRANT'
        and
        [permission_name] = 'SELECT'
;

open @cur;

while
    1 = 1
begin
    fetch @cur into @sql;
    if @@fetch_status <> 0 break;

    print @sql;
    exec sys.sp_executesql @sql;
end;

close @cur;

deallocate @cur;

Uruchom powyższe w głównej bazie danych, a dostęp do zawartości tych schematów został usunięty.

Uwagi:

1. Ponieważ są to jawne instrukcje DENY, są poprawne w momencie uruchomienia skryptu. Jeśli ktoś następnie zmieni uprawnienia nadane publicznie (np. Dodatek Service Pack tworzy nową tabelę systemową), zostanie to ujawnione odmownemu użytkownikowi
2. Dobrym pomysłem jest użycie roli bazy danych jako celu instrukcji DENY i umieszczenie w tej roli zabronionych użytkowników.
3. Możesz to cofnąć, zmieniając ODRZUCENIE na ODWOŁANIE

4. Jeśli skomentujesz następujące dwa wiersze w powyższym skrypcie:

       and
       [permission_name] = 'SELECT'

   Spowoduje to cofnięcie WSZYSTKICH domyślnych DOTACJI dla publicznego. Zapobiegnie to dostępowi do np. Sys.sp_tables, a tym samym zepsuje np. Zdolność Microsoft Access do wyliczenia tabel w ogóle, ale w scenariuszach o wysokim poziomie bezpieczeństwa jest to przydatne, aby użytkownicy mieli dostęp tylko tam, gdzie zostało to wyraźnie przyznane to.

Nie jestem pewien, kiedy ta sztuczka stała się dostępna - ponieważ nikt o niej nie wspominał - ale wydaje się, że działa przynajmniej od SQL Server 2008.

DENY VIEW DEFINITION to [database-role / database-user];

Powyższe działa bez konieczności dodawania użytkownika do masterbazy danych, jak wspomniano w niektórych innych odpowiedziach.