Určení efektivních oprávnění databázového stroje

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytický platformový systém (PDW)databáze SQL v Microsoft Fabric

Tento článek popisuje, jak určit, kdo má oprávnění k různým objektům v databázovém stroji SQL Serveru. SQL Server implementuje dva systémy oprávnění pro databázový stroj. Starší systém pevných rolí má předkonfigurovaná oprávnění. Od SQL Serveru 2005 (9.x) je k dispozici flexibilnější a přesnější systém.

Poznámka

Informace v tomto článku platí pro SQL Server 2005 (9.x) a novější verze. Některé typy oprávnění nejsou v některých verzích SQL Serveru k dispozici.

Vždy byste měli mít na paměti následující body:

• Platná oprávnění jsou agregací obou systémů oprávnění.
• Odepření oprávnění přepíše udělení oprávnění.
• Pokud je uživatel členem pevné role serveru sysadmin, oprávnění se dál nekontrolují, takže k zamítnutí nedojde.
• Starý systém a nový systém mají podobnosti. Například členství v pevné roli serveru sysadmin se podobá tomu, mít oprávnění CONTROL SERVER. Ale systémy nejsou identické. Pokud má například přihlášení jenom oprávnění CONTROL SERVER a uložené procedury kontrolují členství v pevné roli serveru sysadmin, ověření oprávnění selže. Opak je také pravdivý.
• V databázi SQL Fabric je jediným podporovaným způsobem ověřování MICROSOFT Entra ID pro uživatele databáze. Role a oprávnění na úrovni serveru nejsou k dispozici, pouze na úrovni databáze. Další informace naleznete v tématu Autorizace v databázi SQL v Microsoft Fabric.

Shrnutí

• Oprávnění na úrovni serveru může pocházet z členství v pevných rolích serveru nebo rolích serveru definovaných uživatelem. Všichni patří do public pevné role serveru a obdrží všechna oprávnění, která jsou tam přiřazená.
• Oprávnění na úrovni serveru můžou pocházet z udělení oprávnění pro přihlášení nebo role serveru definované uživatelem.
• Oprávnění na úrovni databáze může pocházet z členství v pevných databázových rolích nebo uživatelsky definovaných databázových rolích v každé databázi. Všichni patří do public pevné databázové role a obdrží všechna oprávnění, která jsou tam přiřazená.
• Oprávnění na úrovni databáze můžou pocházet z udělení oprávnění uživatelům nebo uživatelsky definovaným databázovým rolím v každé databázi.
• Oprávnění je možné přijímat od uživatele databáze guest nebo uživatele databáze guest, pokud je tato možnost povolená. Ve výchozím nastavení jsou přihlášení guest a uživatelé zakázáni.
• Uživatelé Windows můžou být členy skupin Windows, které můžou mít přihlášení. SQL Server zjistí členství ve skupinách Windows, když se uživatel Windows připojí a zobrazí token Windows s identifikátorem zabezpečení skupiny Windows. Protože SQL Server nespravuje ani nepřijímá automatické aktualizace členství ve skupinách Windows, SQL Server nemůže spolehlivě hlásit oprávnění uživatelů Systému Windows přijatých z členství ve skupině Windows.
• Oprávnění je možné získat přepnutím na roli aplikace a zadáním hesla.
• Oprávnění lze získat spuštěním uložené procedury, která obsahuje klauzuli EXECUTE AS.
• Oprávnění můžou získat přihlášení nebo uživatelé s oprávněním IMPERSONATE.
• Členové skupiny správců místního počítače mohou vždy zvýšit svá oprávnění k sysadmin. (Nevztahuje se na SQL Database.)
• Členové securityadmin pevné role serveru mohou zvýšit řadu svých oprávnění a v některých případech mohou zvýšit oprávnění na sysadmin. (Nevztahuje se na SQL Database.)
• Správci SQL Serveru můžou zobrazit informace o všech přihlášeních a uživatelích. Méně privilegovaní uživatelé obvykle vidí informace o svých vlastních identitách.

Starší systém s pevnými oprávněními role

Pevné role serveru a pevné databázové role mají předkonfigurovaná oprávnění, která se nedají změnit. Pokud chcete zjistit, kdo je členem pevné role serveru, spusťte následující dotaz:

Poznámka

Nevztahuje se na SLUŽBU SQL Database ani Azure Synapse Analytics, kde není k dispozici oprávnění na úrovni serveru. V SQL Serveru 2012 (11.x) byl přidán sloupec is_fixed_role tabulky sys.server_principals. Pro starší verze SQL Serveru není potřeba.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Poznámka

Všechny uživatelské účty jsou členy veřejného role a nelze je odebrat. Dotaz zkontroluje tabulky v master databázi, ale dá se spustit v jakékoli databázi pro místní produkt.

Pokud chcete zjistit, kdo je členem pevné databázové role, spusťte v každé databázi následující dotaz.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Pokud chcete porozumět oprávněním uděleným jednotlivým rolím, podívejte se na popisy rolí v knihách Online (role na úrovni serverua role na úrovni databáze).

Novější podrobný systém oprávnění

Tento systém je flexibilní, což znamená, že může být komplikovaný, pokud lidé, kteří ho nastavují, chtějí být přesní. Pro zjednodušení pomáhá vytvářet role, přiřazovat oprávnění k rolím a pak do rolí přidávat skupiny lidí. A je jednodušší, pokud vývojový tým databáze odděluje aktivitu podle schématu a pak uděluje oprávnění role k celému schématu místo jednotlivým tabulkám nebo procedurám. Reálné scénáře jsou složité a obchodní potřeby můžou vytvářet neočekávané požadavky na zabezpečení.

Následující obrázek znázorňuje oprávnění a jejich vztahy mezi sebou. Některá oprávnění vyšší úrovně (například CONTROL SERVER) jsou uvedená mnohokrát. V tomto článku je plakát příliš malý na přečtení. Plakát oprávnění databázového stroje v plné velikosti si můžete stáhnout ve formátu PDF .

Třídy zabezpečení

Oprávnění lze udělit na úrovni serveru, na úrovni databáze, na úrovni schématu nebo na úrovni objektu atd. Existuje 26 úrovní (označovaných jako třídy). Úplný seznam tříd v abecedním pořadí je: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Některé třídy nejsou k dispozici v některých typech SQL Serveru.) Chcete-li poskytnout úplné informace o každé třídě, vyžaduje jiný dotaz.

Ředitelé

Oprávnění jsou udělena subjektům. Hlavní prvky můžou být role serveru, přihlášení, databázové role nebo uživatelé. Přihlášení můžou představovat skupiny Windows, které obsahují mnoho uživatelů Windows. Vzhledem k tomu, že SQL Server neudržuje skupiny Windows, SQL Server vždy neví, kdo je členem skupiny Windows. Když se uživatel Windows připojí k SQL Serveru, obsahuje přihlašovací paket tokeny členství ve skupině Windows pro uživatele.

Když se uživatel Windows připojí pomocí přihlášení založeného na skupině Windows, můžou některé aktivity vyžadovat, aby SQL Server vytvořil přihlášení nebo uživatele, aby představoval jednotlivé uživatele Systému Windows. Například skupina Windows (technici) obsahuje uživatele (Mary, Todd, Pat) a skupina Technici mají uživatelský účet databáze. Pokud má Mary oprávnění a vytvoří tabulku, může být uživatel (Mary) vytvořen tak, aby byl vlastníkem tabulky. Nebo pokud je Toddovi odepřeno oprávnění, které má zbytek skupiny techniků, musí být uživatel Todd vytvořen pro sledování odepření oprávnění.

Mějte na paměti, že uživatel Windows může být členem více než jedné skupiny Windows (například technici i manažeři). Oprávnění udělená nebo odepřená pro přihlášení inženýrů, přihlášení správců, udělená nebo odepřená uživateli jednotlivě a udělená nebo odepřená rolím, kterým je uživatel členem, budou agregována a vyhodnocena jako platná oprávnění. Funkce HAS_PERMS_BY_NAME může zjistit, jestli má uživatel nebo přihlášení konkrétní oprávnění. Neexistuje však žádný zřejmý způsob, jak určit zdroj udělení nebo odepření oprávnění. Prostudujte si seznam oprávnění a zkuste experimentovat metodou pokus omyl.

Užitečné dotazy

Oprávnění serveru

Následující dotaz vrátí seznam oprávnění, která byla udělena nebo odepřena na úrovni serveru. Tento dotaz by se měl spustit v databázi master.

Poznámka

Oprávnění na úrovni serveru nelze udělit ani dotazovat v rámci SQL Database nebo Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Oprávnění k databázi

Následující dotaz vrátí seznam oprávnění, která byla udělena nebo odepřena na úrovni databáze. Tento dotaz by se měl spustit v každé databázi.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Každou třídu oprávnění lze v tabulce oprávnění spojit s jinými systémovými zobrazeními, která poskytují související informace o této třídě zabezpečení. Například následující dotaz poskytuje název databázového objektu, který je ovlivněn oprávněním.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Pomocí funkce HAS_PERMS_BY_NAME určete, jestli má konkrétní uživatel (v tomto případě TestUser) oprávnění. Například:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Podrobnosti o syntaxi najdete pod HAS_PERMS_BY_NAME.

Další kroky

• Úvod do oprávnění databázového systému
• kurz : Začínáme s databázovým strojem