データベースとオブジェクトのアクセス許可について説明する

10 分

すべてのリレーショナルデータベース管理プラットフォームには、データ操作言語 (DML) の操作を制御する基本的なアクセス許可が 4 つあります。 SELECT、INSERT、UPDATE、DELETE のアクセス許可で、すべての SQL Server プラットフォームに適用されます。これらのアクセス許可はすべて、テーブルとビューで許可、取り消し、または拒否できます。 GRANT ステートメントを使用してアクセス許可を付与した場合、GRANT ステートメントで参照されたユーザーまたはロールに対して、そのアクセス許可が与えられます。 DENY コマンドを使用して、ユーザーのアクセス許可を拒否することもできます。ユーザーにアクセス許可が付与され、同じアクセス許可が拒否された場合、 DENY は常に許可を優先し、ユーザーは特定のオブジェクトへのアクセスを拒否されます。

GRANT をオーバーライドする DENY の T-SQL の例

この例では、ユーザー Demo に SELECT の権限が付与され、その後 SELECT の権限が dbo.Company テーブルに対して拒否されています。ユーザーが dbo.Company テーブルから選択するクエリを実行しようとしたとき、ユーザーはアクセス許可が拒否されたことを示すエラーを受け取ります。

テーブルとビューのアクセス許可

テーブルとビューは、データベース内のアクセス許可の対象となるオブジェクトを表します。加えて、これらのテーブルとビュー内の、特定のセキュリティプリンシパル (ユーザーまたはログイン) がアクセスできる列を制限することができます。 SQL Server と Azure SQL Database には、さらに行レベルのセキュリティも備わっていて、それらを使用することで、より細かくアクセスを制限することができます。

アクセス許可	定義
`SELECT`	ユーザーがオブジェクト (テーブルまたはビュー) 内のデータを表示できるようにします。拒否されると、ユーザーはオブジェクト内のデータを表示できなくなります。
`INSERT`	ユーザーがオブジェクトにデータを挿入できるようにします。拒否されると、ユーザーはオブジェクトにデータを挿入できなくなります。
`UPDATE`	ユーザーがオブジェクト内のデータを更新できるようにします。拒否されると、ユーザーはオブジェクト内のデータを更新できなくなります。
`DELETE`	ユーザーがオブジェクト内のデータを削除できるようにします。拒否されると、ユーザーはオブジェクトからデータを削除できなくなります。

Azure SQL Database と Microsoft SQL Server には、必要に応じて許可、取り消し、または拒否できる他のアクセス許可があります。

アクセス許可	定義
`CONTROL`	オブジェクトにすべての権限を付与します。このアクセス許可を持つユーザーは、オブジェクトを削除することも含め、オブジェクトに対して必要なアクションをすべて実行できます。
`REFERENCES`	ユーザーがオブジェクトの外部キーを表示できるようにします。
`TAKE OWNERSHIP`	ユーザーがオブジェクトの所有権を取得できるようにします。
`VIEW CHANGE TRACKING`	ユーザーがオブジェクトの変更追跡の設定を表示できるようにします。
`VIEW DEFINITION`	ユーザーがオブジェクトの定義を表示できるようにします。

関数とストアドプロシージャのアクセス許可

テーブルやビューと同様、関数とストアドプロシージャにも、付与または拒否できるいくつかのアクセス許可があります。

アクセス許可	定義
`ALTER`	ユーザーがオブジェクトの定義を変更できるようにします。
`CONTROL`	オブジェクトに対するすべての権限をユーザーに付与します。
`EXECUTE`	ユーザーがオブジェクトを実行できるようにします。
`VIEW CHANGE TRACKING`	ユーザーがオブジェクトの変更追跡の設定を表示できるようにします。
`VIEW DEFINITION`	ユーザーがオブジェクトの定義を表示できるようにします。

EXECUTE AS で実行

EXECUTE AS [user name] コマンドと EXECUTE AS [login name] コマンド (SQL Server と Azure SQL Managed Instance でのみ使用可能) を使用すると、ユーザーコンテキストを変更することができます。以降のコマンドとステートメントは、そのコンテキストに付与されたアクセス許可を持つ新しいコンテキストを使用して実行されます。

ユーザーに付与されたアクセス許可が不要になった場合は、REVOKE コマンドを使用してアクセス許可 (GRANT または DENY) を削除することができます。 revoke コマンドは、指定されたユーザーに対して指定された権利に関する GRANT または DENY のアクセス許可を削除します。

所有権の継承

チェーンと呼ばれる概念は、ユーザーが他のオブジェクトからアクセス許可を継承できるようにするアクセス許可に適用されます。チェーンの最も一般的な例は、実行中にテーブルにアクセスする関数とストアドプロシージャです。テーブルにアクセスする権限が直接ユーザーにはなかったとしても、プロシージャの所有者がテーブルと同じであれば、ストアドプロシージャは実行されてテーブルにアクセスすることができます。このようにアクセスできるのは、テーブルへのアクセス権をユーザーがストアドプロシージャから継承しているためです。ただし、アクセスできるのは、ストアドプロシージャが実行されている間だけであり、その範囲も、ストアドプロシージャの実行コンテキストに限られます。

この例では、データベース所有者またはサーバー管理者として実行すると、新しいユーザーが作成され、新しい SalesReader ロールのメンバーとして追加されます。このロールには、任意のオブジェクトから選択して Sales スキーマ内のプロシージャを実行する権限が付与されます。その後、Production スキーマ内のテーブルにアクセスするストアドプロシージャが Sales スキーマに作成されます。

この例では、さらに、コンテンツを新しいユーザーに変更し、Production スキーマのテーブルに対して直接 SELECT を試みています。

USE AdventureWorks2016;
GO

CREATE USER [DP300User1] WITH PASSWORD = 'Pa55.w.rd';
GO

CREATE ROLE [SalesReader];
GO

ALTER ROLE [SalesReader] ADD MEMBER [DP300User1];
GO

GRANT SELECT, EXECUTE ON SCHEMA::Sales TO [SalesReader];
GO

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

GO

EXECUTE AS USER = 'DP300User1';

SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales,
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, 
    SOH.OrderDate
ORDER BY TotalSales DESC;

ユーザーが属しているロールに運用スキーマの権限がないため、ユーザー DP300User1 に SELECT アクセス許可がないため、クエリの結果としてエラーが発生します。次に、ストアドプロシージャを実行してみましょう。

EXECUTE AS USER = 'DP300User1';

EXECUTE Sales.DemoProc;

DP300User1 ユーザーにはEXECUTE スキーマのストアドプロシージャに対するアクセス許可があります。これは、ユーザーのロールに EXECUTE スキーマに対するアクセス許可があるためです。テーブルにはプロシージャと同じ所有者があるため、切れ目のない所有権があり、実行が成功し、結果が返されます。

ストアドプロシージャ内で動的 SQL が使用されているときは、アクセス許可の変更は適用されません。動的 SQL によってアクセス許可のチェーンが破綻する理由は、動的 SQL が呼び出し元のストアドプロシージャのコンテキスト外で実行されるためです。この動作を確認するには、次のように動的 SQL を使用して実行するストアドプロシージャを変更します。

CREATE OR ALTER PROCEDURE Sales.DemoProc
AS
DECLARE @sqlstring NVARCHAR(MAX)

SET @sqlstring = '
SELECT P.Name, 
    SUM(SOD.LineTotal) AS TotalSales, 
    SOH.OrderDate 
FROM Production.Product P
    INNER JOIN Sales.SalesOrderDetail SOD ON (SOD.ProductID = P.ProductID)
    INNER JOIN Sales.SalesOrderHeader SOH ON (SOH.SalesOrderID = SOD.SalesOrderID)
GROUP BY P.Name, SOH.OrderDate'

EXECUTE sp_executesql @sqlstring
GO

--

EXECUTE AS USER = 'DP300User1'

EXECUTE Sales.DemoProc

DP300User1 ユーザーは、ユーザーが直接クエリを実行しようとしたのと同様に、SELECT テーブルに対するアクセス許可を持っていないというエラーを受け取ります。動的 SQL 内ではアクセス許可のチェーンが適用されないため、そのコードで使用されているテーブルとビューに対する権限が、動的 SQL を実行しているユーザーアカウントに必要となります。

最小限の特権の原則

最小限の特権の原則は、きわめてシンプルです。ユーザーとアプリケーションに与えるアクセス許可はタスクを遂行するうえで必要な範囲に限定すべきであるというのが、この概念の背景にある基本的なアイデアです。アプリケーションには、目の前のタスクを遂行するうえで必要なアクセス許可のみを与える必要があります。

たとえば、アプリケーションがすべてのデータにストアドプロシージャを通じてアクセスするのであれば、そのアプリケーションに必要なのは、ストアドプロシージャを実行するアクセス許可だけです。テーブルへのアクセス権は必要ありません。

動的 SQL

動的 SQL は、クエリがプログラムによって構築されるという概念です。動的 SQL を使用すると、ストアドプロシージャまたはクエリ自体の中で T-SQL ステートメントを生成することができます。

SELECT 'BACKUP DATABASE ' + name + ' TO DISK =''\\backup\sql1\' + name + '.bak'''
FROM sys.databases

このステートメントは、サーバー上のすべてのデータベースをバックアップする T-SQL ステートメントの一覧を生成します。通常、この生成された T-SQL は、 sp_executesql を使用して実行されるか、別のプログラムに渡されて実行されます。

フィードバック

このページはお役に立ちましたか?