行レベルのセキュリティRow-Level Security

適用対象: ○SQL Server ○Azure SQL Database ○Azure SQL Data Warehouse XParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

行レベルのセキュリティの図Row level security graphic

行レベルのセキュリティでは、グループ メンバーシップや実行コンテキストを使用して、データベース テーブル内の行へのアクセスを制御することができます。Row-Level Security enables you to use group membership or execution context to control access to rows in a database table.

Row-Level Security (RLS) は、アプリケーションでセキュリティの設計やコーディングを簡略化します。Row-Level Security (RLS) simplifies the design and coding of security in your application. RLS は、データ行アクセスに対して制限を実装するのに役立ちます。RLS helps you implement restrictions on data row access. たとえば、作業者が自分の部署に関連するデータ行にしかアクセスしないようにすることができます。For example, you can ensure that workers access only those data rows that are pertinent to their department. 別の例として、顧客のデータ アクセスをその顧客の会社に関連するデータだけに制限することがあります。Another example is to restrict customers' data access to only the data relevant to their company.

アクセスの制限のロジックは、別のアプリケーション層のデータから離れてではなく、データベース層にあります。The access restriction logic is located in the database tier rather than away from the data in another application tier. 任意の層からデータへのアクセスが試行されるたびに、データベース システムにはアクセス制限が適用されます。The database system applies the access restrictions every time that data access is attempted from any tier. これにより、セキュリティ システムの表層領域を減少することで、セキュリティ システムはより信頼性の高い堅牢なものになります。This makes your security system more reliable and robust by reducing the surface area of your security system.

CREATE SECURITY POLICYTransact-SQLTransact-SQL ステートメントを使用して RLS を実装すると、インライン テーブル値関数として述語が作成されます。Implement RLS by using the CREATE SECURITY POLICYTransact-SQLTransact-SQL statement, and predicates created as inline table-valued functions.

適用対象: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) から現在のバージョンまで)、SQL データベースSQL Database (入手)、SQL データ ウェアハウスSQL Data WarehouseApplies to: SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version), SQL データベースSQL Database (Get it), SQL データ ウェアハウスSQL Data Warehouse.

注意

Azure SQL Data Warehouse では、フィルター述語のみがサポートされています。Azure SQL Data Warehouse supports filter predicates only. ブロック述語は現在、Azure SQL Data Warehouse でサポートされていません。Block predicates aren't currently supported in Azure SQL Data Warehouse.

説明Description

RLS では、2 種類のセキュリティ述語をサポートしています。RLS supports two types of security predicates.

  • フィルター述語は、読み取り操作 (SELECT、UPDATE、DELETE) が可能な行を通知なしにフィルター処理します。Filter predicates silently filter the rows available to read operations (SELECT, UPDATE, and DELETE).

  • ブロック述語は、その述語に違反する書き込み操作 (AFTER INSERT、AFTER UPDATE、BEFORE UPDATE、BEFORE DELETE) を明示的に禁止します。Block predicates explicitly block write operations (AFTER INSERT, AFTER UPDATE, BEFORE UPDATE, BEFORE DELETE) that violate the predicate.

テーブルの行レベルのデータへのアクセスは、インライン テーブル値関数として定義されたセキュリティ述語によって制限されます。Access to row-level data in a table is restricted by a security predicate defined as an inline table-valued function. 関数が呼び出され、セキュリティ ポリシーによって適用されます。The function is then invoked and enforced by a security policy. フィルター述語の場合、結果セットからフィルター処理されている行はアプリケーションによって認識されません。For filter predicates, the application is unaware of rows that are filtered from the result set. すべての行がフィルター処理されると、null セットが返されます。If all rows are filtered, then a null set will be returned. ブロック述語の場合、その述語に違反するすべての操作がエラーで失敗します。For block predicates, any operations that violate the predicate will fail with an error.

フィルター述語は、ベース テーブルからのデータの読み取り中に適用されます。Filter predicates are applied while reading data from the base table. 次のすべての get 操作に影響します:SELECTDELETEUPDATEThey affect all get operations: SELECT, DELETE and UPDATE. ユーザーは、フィルター処理される行を選択または削除できません。The users can't select or delete rows that are filtered. ユーザーは、フィルター処理される行を更新できません。The user can't update rows that are filtered. しかし、後でフィルター処理されるようにすれば行を更新できます。But, it's possible to update rows in such a way that they'll be filtered afterward. ブロック述語はすべての書き込み操作に影響します。Block predicates affect all write operations.

  • AFTER INSERT と AFTER UPDATE の各述語は、ユーザーが行を述語に違反する値に更新できないようにします。AFTER INSERT and AFTER UPDATE predicates can prevent users from updating rows to values that violate the predicate.

  • BEFORE UPDATE 述語は、ユーザーが現在述語に違反している行を更新できないようにします。BEFORE UPDATE predicates can prevent users from updating rows that currently violate the predicate.

  • BEFORE DELETE 述語は削除操作を禁止します。BEFORE DELETE predicates can block delete operations.

フィルター述語とブロック述語およびセキュリティ ポリシーの動作は次のとおりです。Both filter and block predicates and security policies have the following behavior:

  • 別のテーブルとの結合や関数の呼び出しを実行する述語関数を定義できます。You may define a predicate function that joins with another table and/or invokes a function. SCHEMABINDING = ONでセキュリティ ポリシーが作成された場合、結合または関数にはクエリからアクセスでき、追加のアクセス許可の確認を必要とせず、期待どおりに動作します。If the security policy is created with SCHEMABINDING = ON, then the join or function is accessible from the query and works as expected without any additional permission checks. SCHEMABINDING = OFF でセキュリティ ポリシーが作成された場合、対象テーブルに対してクエリを実行するには、ユーザーに、これらの追加のテーブルと関数に対する SELECT 権限または EXECUTE 権限が必要です。If the security policy is created with SCHEMABINDING = OFF, then users will need SELECT or EXECUTE permissions on these additional tables and functions to query the target table.

  • セキュリティ述語は定義されているが無効になっているテーブルに対してクエリを発行できます。You may issue a query against a table that has a security predicate defined but disabled. フィルター処理またはブロックされている行には影響しません。Any rows that are filtered or blocked aren't affected.

  • dbo ユーザー、db_owner ロールのメンバー、またはテーブルの所有者が、セキュリティ ポリシーが定義され有効になっているテーブルに対してクエリを実行すると、セキュリティ ポリシーでの定義に従って行がフィルター処理またはブロックされます。If a dbo user, a member of the db_owner role, or the table owner queries a table that has a security policy defined and enabled, the rows are filtered or blocked as defined by the security policy.

  • スキーマ バインドされたセキュリティ ポリシーによってバインドされているテーブルのスキーマを変更しようとすると、エラーが発生します。Attempts to alter the schema of a table bound by a schema bound security policy will result in an error. ただし、述語で参照されていない列は変更できます。However, columns not referenced by the predicate can be altered.

  • 指定された操作に対する述語が既に定義されているテーブルに述語を追加しようとすると、エラーが発生します。Attempts to add a predicate on a table that already has one defined for the specified operation results in an error. これは、述語が有効になっているかどうかを問わず発生します。This will happen whether the predicate is enabled or not.

  • スキーマ バインドされたセキュリティ ポリシー内のテーブルで述語として使用されている関数を変更しようとすると、エラーが発生します。Attempts to modify a function, that is used as a predicate on a table within a schema bound security policy, will result in an error.

  • 重複しない述語が含まれる複数のアクティブなセキュリティ ポリシーの定義は、行うことができます。Defining multiple active security policies that contain non-overlapping predicates, succeeds.

フィルター述語の動作は次のとおりです。Filter predicates have the following behavior:

  • テーブルの行をフィルター処理するセキュリティ ポリシーを定義します。Define a security policy that filters the rows of a table. アプリケーションでは、SELECTUPDATEDELETE 操作の場合、フィルター処理された行はいずれも認識されません。The application is unaware of any rows that are filtered for SELECT, UPDATE, and DELETE operations. これには、すべての行がフィルター処理された状況も含まれます。アプリケーションでは、他の操作中にフィルター処理される場合でも、行の INSERT を行うことができます。Including situations where all the rows are filtered out. The application can INSERT rows, even if they will be filtered during any other operation.

ブロック述語の動作は次のとおりです。Block predicates have the following behavior:

  • UPDATE のブロック述語は、BEFORE と AFTER の個別の操作に分けられています。Block predicates for UPDATE are split into separate operations for BEFORE and AFTER. そのため、たとえば、ユーザーが行を更新して現在の値よりも大きい値を含めることを禁止することはできません。Consequently, you can't, for example, block users from updating a row to have a value higher than the current one. このようなロジックが必要な場合は、DELETED および INSERTED 中間テーブルでトリガーを使用して、古い値と新しい値を一緒に参照する必要があります。If this kind of logic is required, you must use triggers with the DELETED and INSERTED intermediate tables to reference the old and new values together.

  • 述語関数で使用されている列が変更されていない場合、オプティマイザーでは AFTER UPDATE ブロック述語がチェックされません。The optimizer will not check an AFTER UPDATE block predicate if the columns used by the predicate function weren't changed. 例:Alice は、給与を 100,000 より大きく変更することはできません。For example: Alice shouldn't be able to change a salary to be greater than 100,000. Alice は、述語内で参照される列が変更されていない場合に限り、給与が既に 100,000 を超えている従業員のアドレスを変更できます。Alice can change the address of an employee whose salary is already greater than 100,000 as long as the columns referenced in the predicate weren't changed.

  • BULK INSERT などの Bulk API は変更されていません。No changes have been made to the bulk APIs, including BULK INSERT. つまり、AFTER INSERT ブロック述語は、通常の挿入操作と同様に一括挿入操作に適用されます。This means that block predicates AFTER INSERT will apply to bulk insert operations just as they would regular insert operations.

Use Cases

RLS をどのように使用するかの設計例を次に示します。Here are design examples of how RLS can be used:

  • 病院では、看護師が自分の患者のデータ行のみを見ることができるようなセキュリティ ポリシーを作成できます。A hospital can create a security policy that allows nurses to view data rows for their patients only.

  • 銀行では、従業員のビジネス部門や会社における役割に基づき、財務データの行へのアクセスを制限するポリシーを作成できます。A bank can create a policy to restrict access to financial data rows based on an employee's business division or role in the company.

  • マルチ テナント アプリケーションでは、他のすべてのテナントの行から各テナントのデータ行を論理的に分離するポリシーを作成できます。A multi-tenant application can create a policy to enforce a logical separation of each tenant's data rows from every other tenant's rows. 1 つのテーブルで多くのテナントのデータを保存することで効率性が高まります。Efficiencies are achieved by the storage of data for many tenants in a single table. 各テナントはそのデータ行のみ表示できます。Each tenant can see only its data rows.

RLS フィルター述語は機能的には WHERE 句の追加と同等です。RLS filter predicates are functionally equivalent to appending a WHERE clause. 述語はビジネス プラクティスの規定と同じくらいに洗練されたものであり、句は WHERE TenantId = 42と同じくらいに簡単です。The predicate can be as sophisticated as business practices dictate, or the clause can be as simple as WHERE TenantId = 42.

より形式的に表現すると、RLS はアクセス制御に基づく述語を採用しています。In more formal terms, RLS introduces predicate based access control. 柔軟で、集中管理された、述語ベースの評価を備えています。It features a flexible, centralized, predicate-based evaluation. 述語は、管理者が適切に決定したメタデータや他の条件に基づくことができます。The predicate can be based on metadata or any other criteria the administrator determines as appropriate. 述語は、ユーザーがその属性に基づいて適切にデータにアクセスできるかどうかを決定する条件として使用されます。The predicate is used as a criterion to determine if the user has the appropriate access to the data based on user attributes. ラベルに基づくアクセス制御は、述語に基づくアクセス制御を使用して実装できます。Label-based access control can be implemented by using predicate-based access control.

PermissionsPermissions

セキュリティ ポリシーの作成、変更、削除には、 ALTER ANY SECURITY POLICY 権限が必要です。Creating, altering, or dropping security policies requires the ALTER ANY SECURITY POLICY permission. セキュリティ ポリシーの作成か削除には、スキーマ上で ALTER 権限が必要です。Creating or dropping a security policy requires ALTER permission on the schema.

また、追加される各述語に関しては次のアクセス許可も必要になります。Additionally the following permissions are required for each predicate that is added:

  • 述語として使用している関数に関するSELECT および REFERENCES 権限。SELECT and REFERENCES permissions on the function being used as a predicate.

  • ポリシーにバインドしているターゲット テーブルに対するREFERENCES 権限。REFERENCES permission on the target table being bound to the policy.

  • 引数として使用しているターゲット テーブルのすべての列に対するREFERENCES 権限。REFERENCES permission on every column from the target table used as arguments.

セキュリティ ポリシーは、データベースの dbo ユーザーを含めてすべてのユーザーに適用されます。Security policies apply to all users, including dbo users in the database. dbo ユーザーはセキュリティ ポリシーを変更したり削除したりできますが、セキュリティ ポリシーに加えた変更は監査することができます。Dbo users can alter or drop security policies however their changes to security policies can be audited. sysadmin や db_owner などの高い権限を持つユーザーがトラブルシューティングやデータ検証のためにすべての行を表示する必要がある場合は、これを許可するセキュリティ ポリシーを作成する必要があります。If high privileged users, such as sysadmin or db_owner, need to see all rows to troubleshoot or validate data, the security policy must be written to allow that.

SCHEMABINDING = OFFでセキュリティ ポリシーが作成された場合、ユーザーは、対象テーブルにクエリを実行するために、述語関数とその述語関数で使用される追加のテーブル、ビュー、または関数に対する SELECT 権限または EXECUTE 権限が必要です。If a security policy is created with SCHEMABINDING = OFF, then to query the target table, users must have the SELECT or EXECUTE permission on the predicate function and any additional tables, views, or functions used within the predicate function. SCHEMABINDING = ON (既定) でセキュリティ ポリシーが作成された場合、ユーザーが対象テーブルに対してクエリを実行すると、これらの権限チェックは迂回されます。If a security policy is created with SCHEMABINDING = ON (the default), then these permission checks are bypassed when users query the target table.

ベスト プラクティスBest Practices

  • RLS オブジェクト、述語関数、セキュリティ ポリシーに対して別のスキーマを作成することを強くお勧めします。It's highly recommended to create a separate schema for the RLS objects, predicate function, and security policy.

  • ALTER ANY SECURITY POLICY 権限は、セキュリティ ポリシー マネージャーなどの高い権限を持つユーザーを対象としています。The ALTER ANY SECURITY POLICY permission is intended for highly privileged users (such as a security policy manager). セキュリティ ポリシー マネージャーには、保護しているテーブルでは SELECT 権限は必要とされません。The security policy manager doesn't require SELECT permission on the tables they protect.

  • 潜在的なランタイム エラーを回避する述語関数では型変換しないようにします。Avoid type conversions in predicate functions to avoid potential runtime errors.

  • パフォーマンスの低下を避けるため、可能な場合は、述語関数では再帰しないようにします。Avoid recursion in predicate functions wherever possible to avoid performance degradation. クエリ オプティマイザーでは直接再帰の検出を試みますが、間接再帰の検出は保証されません。The query optimizer will try to detect direct recursions, but isn't guaranteed to find indirect recursions. 間接再帰では、2 番目の関数によって述語関数が呼び出されます。An indirect recursion is where a second function calls the predicate function.

  • パフォーマンスを最適化する述語関数で過剰にテーブルを結合しないようにします。Avoid using excessive table joins in predicate functions to maximize performance.

セッション固有の SET オプションに依存する述語ロジックは避けます。実用的なアプリケーションで使用されることはほとんどありませんが、ロジックが特定のセッション固有の SET オプションに依存する述語関数では、ユーザーが任意のクエリを実行できる場合に情報が漏洩する可能性があります。Avoid predicate logic that depends on session-specific SET options: While unlikely to be used in practical applications, predicate functions whose logic depends on certain session-specific SET options can leak information if users are able to execute arbitrary queries. たとえば、文字列を datetime に暗黙的に変換する述語関数は、現在のセッションの SET DATEFORMAT オプションに基づいてさまざまな行をフィルター処理する可能性があります。For example, a predicate function that implicitly converts a string to datetime could filter different rows based on the SET DATEFORMAT option for the current session. 一般に、述語関数は次のルールに従う必要があります。In general, predicate functions should abide by the following rules:

セキュリティに関する注意:サイドチャネル攻撃Security Note: Side-Channel Attacks

悪意のあるセキュリティ ポリシー マネージャーMalicious security policy manager

悪意のあるセキュリティ ポリシー マネージャーを監視することが重要です。これは、機密性の高い列にセキュリティ ポリシーを作成する十分な権限と、インライン テーブル値関数を作成したり、変更したりする権限がそのセキュリティ ポリシー マネージャーにある場合、テーブルで選択権限を持つ別のユーザーと共謀し、サイドチャネル攻撃を使ってデータを推測するインライン テーブル値関数を作成して、悪意を持ってデータを流出させることも可能になるためです。It is important to observe that a malicious security policy manager, with sufficient permissions to create a security policy on top of a sensitive column and having permission to create or alter inline table-valued functions, can collude with another user who has select permissions on a table to perform data exfiltration by maliciously creating inline table-valued functions designed to use side channel attacks to infer data. このような攻撃では、共謀 (または過剰な権限を悪意のあるユーザーに与えること) が必要で、ポリシーの変更が何度も必要になる可能性が高く (スキーマ バインドを解除するために述語を削除する権限を要求する)、インライン テーブル値関数が変更され、対象のテーブルに対して select ステートメントが繰り返し実行されることになります。Such attacks would require collusion (or excessive permissions granted to a malicious user) and would likely require several iterations of modifying the policy (requiring permission to remove the predicate in order to break the schema binding), modifying the inline table-valued functions, and repeatedly running select statements on the target table. 必要に応じてアクセス許可を制限し、疑わしいアクティビティを監視することをお勧めします。We recommend you limit permissions as necessary and monitor for any suspicious activity. 行レベルのセキュリティに関連して、絶えず変化するポリシーやインライン テーブル値関数などのアクティビティを監視する必要があります。Activity such as constantly changing policies and inline table-valued functions related to row-level security should be monitored.

慎重に作成されたクエリCarefully crafted queries

慎重に作成されたクエリを通じて、情報漏洩が発生する可能性があります。It is possible to cause information leakage through the use of carefully crafted queries. たとえば、 SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' というクエリで、悪意のあるユーザーに John doe さんの給与が 100,000 ドルであることが知らされました。For example, SELECT 1/(SALARY-100000) FROM PAYROLL WHERE NAME='John Doe' would let a malicious user know that John Doe's salary is $100,000. 悪意のあるユーザーが他のユーザーの給与を直接照会するような事態を防ぐため、セキュリティ述語がある場合でも、ゼロ除算の例外がクエリ結果として返されることで、悪意のあるユーザーによって知られてしまいます。Even though there is a security predicate in place to prevent a malicious user from directly querying other people's salary, the user can determine when the query returns a divide-by-zero exception.

機能間の互換性Cross-Feature Compatibility

一般に、行レベルのセキュリティは機能間で予想どおりに機能します。In general, row-level security will work as expected across features. ただし、例外がいくつかあります。However, there are a few exceptions. ここでは、 SQL ServerSQL Serverの他の特定の機能で行レベルのセキュリティを使用する場合の注意事項について説明します。This section documents several notes and caveats for using row-level security with certain other features of SQL ServerSQL Server.

  • DBCC SHOW_STATISTICS からはフィルター処理されていないデータに対する統計が報告されるため、セキュリティ ポリシーによって保護されていない場合に情報が漏洩する可能性があります。DBCC SHOW_STATISTICS reports statistics on unfiltered data, and can leak information otherwise protected by a security policy. このため、行レベルのセキュリティ ポリシーを持つテーブルの統計オブジェクトを表示するアクセス権は制限されます。For this reason, access to view a statistics object for a table with a row-level security policy is restricted. ユーザーがテーブルを所有しているか、ユーザーが sysadmin 固定サーバー ロール、db_owner 固定データベース ロール、または db_ddladmin 固定データベース ロールのメンバーである必要があります。The user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

  • Filestream: RLS は Filestream と互換性がありません。Filestream: RLS is incompatible with Filestream.

  • PolyBase: RLS は Azure SQL Data Warehouse 用の Polybase 外部テーブルでのみサポートされます。PolyBase: RLS is supported with Polybase external tables for Azure SQL Data Warehouse only.

  • メモリ最適化テーブル: メモリ最適化テーブルでセキュリティ述語として使用されるインライン テーブル値関数は、WITH NATIVE_COMPILATION オプションを使用して定義する必要があります。Memory-Optimized Tables: The inline table-valued function used as a security predicate on a memory-optimized table must be defined using the WITH NATIVE_COMPILATION option. このオプションを使用すると、メモリ最適化テーブルでサポートされていない言語機能が禁止され、作成時に該当するエラーが発行されます。With this option, language features not supported by memory-optimized tables will be banned and the appropriate error will be issued at creation time. 詳細については、「 メモリ最適化テーブルの概要 」の「 メモリ最適化テーブルの行レベルのセキュリティ」をご覧ください。For more information, see the Row-Level Security in Memory Optimized Tables section in Introduction to Memory-Optimized Tables.

  • インデックス付きビュー: 一般に、セキュリティ ポリシーはビューに対して作成でき、ビューはセキュリティ ポリシーによってバインドされたテーブルに作成できます。Indexed views: In general, security policies can be created on top of views, and views can be created on top of tables that are bound by security policies. ただし、インデックスによる行の参照はポリシーを回避するため、セキュリティ ポリシーが適用されたテーブルにインデックス付きビューを作成することはできません。However, indexed views cannot be created on top of tables that have a security policy, because row lookups via the index would bypass the policy.

  • Change Data Capture: Change Data Capture では、db_owner のメンバー、またはテーブルで CDC が有効になっているときに指定された "ゲーティング" ロールのメンバーであるユーザーに、フィルター処理する必要があるすべての行が漏洩する可能性があります (注: この関数を明示的に NULL に設定すると、すべてのユーザーが変更データにアクセスできるようになります)。Change Data Capture: Change Data Capture can leak entire rows that should be filtered to members of db_owner or users who are members of the "gating" role specified when CDC is enabled for a table (note: you can explicitly set this function to NULL to enable all users to access the change data). 実際には、 db_owner と、このゲーティング ロールのメンバーは、テーブルにセキュリティ ポリシーが存在する場合でも、テーブルのすべてのデータ変更を表示できます。In effect, db_owner and members of this gating role can see all data changes on a table, even if there is a security policy on the table.

  • Change Tracking: Change Tracking では、SELECTVIEW CHANGE TRACKING の両方の権限を持つユーザーに、フィルター処理する必要がある行の主キーが漏洩する可能性があります。Change Tracking: Change Tracking can leak the primary key of rows that should be filtered to users with both SELECT and VIEW CHANGE TRACKING permissions. 実際のデータ値は漏洩しません。漏洩するのは、主キー B を持つ行の列 A が更新/挿入/削除されたという事実だけです。Actual data values are not leaked; only the fact that column A was updated/inserted/deleted for the row with B primary key. これは、主キーに社会保障番号などの機密要素が含まれている場合に問題になります。This is problematic if the primary key contains a confidential element, such as a Social Security Number. ただし、実際には、この CHANGETABLE は、最新のデータを取得するために、ほとんどの場合、元のテーブルと結合されます。However, in practice, this CHANGETABLE is almost always joined with the original table in order to get the latest data.

  • フルテキスト検索: 次のフルテキスト検索関数やセマンティック検索関数を使用したクエリでは、行レベルのセキュリティを適用し、フィルター処理する必要のある行の主キーの漏洩を防ぐために余分な結合が発生するため、パフォーマンスが低下することが予想されます。CONTAINSTABLEFREETEXTTABLE、semantickeyphrasetable、semanticsimilaritydetailstable、semanticsimilaritytable。Full-Text Search: A performance hit is expected for queries using the following Full-Text Search and Semantic Search functions, because of an extra join introduced to apply row-level security and avoid leaking the primary keys of rows that should be filtered: CONTAINSTABLE, FREETEXTTABLE, semantickeyphrasetable, semanticsimilaritydetailstable, semanticsimilaritytable.

  • 列ストア インデックス: RLS は、クラスター化列ストア インデックスと非クラスター化列ストア インデックスの両方と互換性があります。Columnstore Indexes: RLS is compatible with both clustered and non-clustered columnstore indexes. ただし、行レベルのセキュリティによって関数が適用されるため、オプティマイザーではバッチ モードを使用しないようにクエリ プランが変更される可能性があります。However, because row-level security applies a function, it is possible that the optimizer may modify the query plan so that it doesn't use batch mode.

  • パーティション ビュー: パーティション ビューでブロック述語を定義することはできません。また、ブロック述語を使用するテーブルにパーティション ビューを作成することはできません。Partitioned Views: Block predicates cannot be defined on partitioned views, and partitioned views cannot be created on top of tables that use block predicates. フィルター述語はパーティション ビューと互換性があります。Filter predicates are compatible with partitioned views.

  • テンポラル テーブル: テンポラル テーブルは RLS と互換性があります。Temporal tables: Temporal tables are compatible with RLS. ただし、現在のテーブルのセキュリティ述語は、履歴テーブルに自動的にはレプリケートされません。However, security predicates on the current table are not automatically replicated to the history table. 現在のテーブルと履歴テーブルの両方にセキュリティ ポリシーを適用するには、テーブルごとにセキュリティ述語を個別に追加する必要があります。To apply a security policy to both the current and the history tables, you must individually add a security predicate on each table.

使用例Examples

A.A. データベースに対して認証するユーザーのシナリオScenario for users who authenticate to the database

この例では、3 人のユーザーを作成し、6 行のテーブルを作成して設定します。This example creates three users and creates and populates a table with six rows. その後、インライン テーブル値関数とテーブルのセキュリティ ポリシーが作成されます。It then creates an inline table-valued function and a security policy for the table. さらにこの例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。The example then shows how select statements are filtered for the various users.

別のアクセス機能を示す 3 つのユーザー アカウントを作成します。Create three user accounts that will demonstrate different access capabilities.

注意

Azure SQL Data Warehouse では EXECUTE AS USER がサポートされていないので、事前にユーザーごとに CREATE LOGIN を実行する必要があります。Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so you must CREATE LOGIN for each user beforehand. 後で、この動作をテストするために、適切なユーザーとしてログインします。Later, you will log in as the appropriate user to test this behavior.

CREATE USER Manager WITHOUT LOGIN;  
CREATE USER Sales1 WITHOUT LOGIN;  
CREATE USER Sales2 WITHOUT LOGIN;  

データを保持するテーブルを作成します。Create a table to hold data.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

そのテーブルに、各営業担当者の 3 つの注文を表示する、6 つのデータ行を設定します。Populate the table with six rows of data, showing three orders for each sales representative.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

各ユーザーに、テーブルに対する読み取りアクセス権を付与します。Grant read access on the table to each of the users.

GRANT SELECT ON Sales TO Manager;  
GRANT SELECT ON Sales TO Sales1;  
GRANT SELECT ON Sales TO Sales2;  

新しいスキーマと、インライン テーブル値関数を作成します。Create a new schema, and an inline table-valued function. SalesRep 列内の行がクエリを実行しているユーザーと同じである場合 (@SalesRep = USER_NAME())、またはクエリを実行しているユーザーがマネージャー ユーザーである場合 (USER_NAME() = 'Manager')、関数は 1 を返しますThe function returns 1 when a row in the SalesRep column is the same as the user executing the query (@SalesRep = USER_NAME()) or if the user executing the query is the Manager user (USER_NAME() = 'Manager').

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@SalesRep AS sysname)  
    RETURNS TABLE  
WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @SalesRep = USER_NAME() OR USER_NAME() = 'Manager';  

フィルター述語として関数を追加するセキュリティ ポリシーを作成します。Create a security policy adding the function as a filter predicate. 状態を ON に設定してポリシーを有効にする必要があります。The state must be set to ON to enable the policy.

CREATE SECURITY POLICY SalesFilter  
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales  
WITH (STATE = ON);  

fn_securitypredicate 関数に対する SELECT 権限を許可するAllow SELECT permissions to the fn_securitypredicate function

GRANT SELECT ON security.fn_securitypredicate TO Manager;  
GRANT SELECT ON security.fn_securitypredicate TO Sales1;  
GRANT SELECT ON security.fn_securitypredicate TO Sales2;  

各ユーザーとして Sales テーブルから選択されたフィルター述語を今すぐテストしてみましょう。Now test the filtering predicate, by selected from the Sales table as each user.

EXECUTE AS USER = 'Sales1';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Sales2';  
SELECT * FROM Sales;
REVERT;  
  
EXECUTE AS USER = 'Manager';  
SELECT * FROM Sales;
REVERT;  

注意

Azure SQL Data Warehouse では EXECUTE AS USER がサポートされていないので、適切なユーザーとしてログインして上記の動作をテストします。Azure SQL Data Warehouse doesn't support EXECUTE AS USER, so log in as the appropriate user to test the above behavior.

マネージャーには、6 つの行すべてが表示されるはずです。The Manager should see all six rows. Sales1 と Sales2 のユーザーには、それぞれの売上のみ表示されます。The Sales1 and Sales2 users should only see their own sales.

セキュリティ ポリシーを変更してポリシーを無効にします。Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter  
WITH (STATE = OFF);  

これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。Now Sales1 and Sales2 users can see all six rows.

リソースをクリーンアップする SQL データベースに接続しますConnect to the SQL database to clean up resources

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

B.B. Azure SQL Data Warehouse 外部テーブルに対して行レベルのセキュリティを使用する場合のシナリオScenarios for using Row Level Security on an Azure SQL Data Warehouse external table

この簡単な例では、3 人のユーザーと 6 行の外部テーブルを作成します。This short example creates three users and an external table with six rows. その後、インライン テーブル値関数と外部テーブルのセキュリティ ポリシーが作成されます。It then creates an inline table-valued function and a security policy for the external table. この例では、select ステートメントがさまざまなユーザーに対してどのようにフィルター処理されるかが示されます。The example shows how select statements are filtered for the various users.

別のアクセス機能を示す 3 つのユーザー アカウントを作成します。Create three user accounts that will demonstrate different access capabilities.

CREATE LOGIN Manager WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales1 WITH PASSWORD = 'somepassword'
GO
CREATE LOGIN Sales2 WITH PASSWORD = 'somepassword'
GO

CREATE USER Manager FOR LOGIN Manager;  
CREATE USER Sales1  FOR LOGIN Sales1;  
CREATE USER Sales2  FOR LOGIN Sales2 ;

データを保持するテーブルを作成します。Create a table to hold data.

CREATE TABLE Sales  
    (  
    OrderID int,  
    SalesRep sysname,  
    Product varchar(10),  
    Qty int  
    );  

そのテーブルに、各営業担当者の 3 つの注文を表示する、6 つのデータ行を設定します。Populate the table with six rows of data, showing three orders for each sales representative.

INSERT INTO Sales VALUES (1, 'Sales1', 'Valve', 5);
INSERT INTO Sales VALUES (2, 'Sales1', 'Wheel', 2);
INSERT INTO Sales VALUES (3, 'Sales1', 'Valve', 4);
INSERT INTO Sales VALUES (4, 'Sales2', 'Bracket', 2);
INSERT INTO Sales VALUES (5, 'Sales2', 'Wheel', 5);
INSERT INTO Sales VALUES (6, 'Sales2', 'Seat', 5);
-- View the 6 rows in the table  
SELECT * FROM Sales;

作成した Sales テーブルから Azure SQL Data Warehouse 外部テーブルを作成します。Create an Azure SQL Data Warehouse external table from the Sales table created.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword';

CREATE DATABASE SCOPED CREDENTIAL msi_cred WITH IDENTITY = 'Managed Service Identity';

CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss WITH (TYPE = hadoop, LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net', CREDENTIAL = msi_cred);

CREATE EXTERNAL FILE FORMAT MSIFormat  WITH (FORMAT_TYPE=DELIMITEDTEXT);
  
CREATE EXTERNAL TABLE Sales_ext WITH (LOCATION='RLSExtTabletest.tbl', DATA_SOURCE=ext_datasource_with_abfss, FILE_FORMAT=MSIFormat, REJECT_TYPE=Percentage, REJECT_SAMPLE_VALUE=100, REJECT_VALUE=100)
AS SELECT * FROM sales;

3 人のユーザーに外部テーブルの SELECT 権限を付与します。Grant SELECT for the three users external table.

GRANT SELECT ON Sales_ext TO Sales1;  
GRANT SELECT ON Sales_ext TO Sales2;  
GRANT SELECT ON Sales_ext TO Manager;

セッション A 内で関数をフィルター述語として使用して外部テーブル上にセキュリティ ポリシーを作成します。Create a security policy on external table using the function in session A as a filter predicate. 状態を ON に設定してポリシーを有効にする必要があります。The state must be set to ON to enable the policy.

CREATE SECURITY POLICY SalesFilter_ext
ADD FILTER PREDICATE Security.fn_securitypredicate(SalesRep)
ON dbo.Sales_ext  
WITH (STATE = ON);

次に、Sales_ext 外部テーブルから選択することで、フィルター述語をテストします。Now test the filtering predicate, by selecting from the Sales_ext external table. 各ユーザー (Sales1、Sales2、およびマネージャー) としてサインインします。Sign in as each user, Sales1, Sales2, and manager. 次のコマンドを各ユーザーとして実行します。Run the following command as each user.

SELECT * FROM Sales_ext;

マネージャーには、6 つの行すべてが表示されるはずです。The Manager should see all six rows. Sales1 と Sales2 のユーザーには、各自の売上のみ表示されます。The Sales1 and Sales2 users should only see their sales.

セキュリティ ポリシーを変更してポリシーを無効にします。Alter the security policy to disable the policy.

ALTER SECURITY POLICY SalesFilter_ext  
WITH (STATE = OFF);  

これで、Sales1 と Sales2 のユーザーに 6 つの行すべてが表示されます。Now the Sales1 and Sales2 users can see all six rows.

SQL Data Warehouse データベースに接続してリソースをクリーンアップしますConnect to the SQL Data Warehouse database to clean up resources

DROP USER Sales1;
DROP USER Sales2;
DROP USER Manager;

DROP SECURITY POLICY SalesFilter_ext;
DROP TABLE Sales;
DROP EXTERNAL TABLE Sales_ext;
DROP EXTERNAL DATA SOURCE ext_datasource_with_abfss ;
DROP EXTERNAL FILE FORMAT MSIFormat;
DROP DATABASE SCOPED CREDENTIAL msi_cred; 
DROP MASTER KEY;

論理マスターに接続してリソースをクリーンアップします。Connect to logical master to clean up resources.

DROP LOGIN Sales1;
DROP LOGIN Sales2;
DROP LOGIN Manager;

C.C. 中間層アプリケーションからデータベースに接続するユーザーのシナリオScenario for users who connect to the database through a middle-tier application

注意

この例の場合、Azure SQL Data Warehouse では、ブロック述語機能が現在サポートされていないため、正しくないユーザー ID の行の挿入がブロックされません。In this example block predicates functionality isn't currently supported for Azure SQL Data Warehouse, hence inserting rows for the wrong user ID isn't blocked with Azure SQL Data Warehouse.

この例では、アプリケーション ユーザー (またはテナント) が同じ SQL ServerSQL Server ユーザー (アプリケーション) を共有している場合、中間層のアプリケーションが接続フィルタリングを実装する方法を示します。This example shows how a middle-tier application can implement connection filtering, where application users (or tenants) share the same SQL ServerSQL Server user (the application). アプリケーションは、データベースに接続した後、 SESSION_CONTEXT (Transact-SQL) で現在のアプリケーション ユーザー ID を設定します。その後、セキュリティ ポリシーによって、この ID に対して表示しない行が透過的にフィルター処理されます。また、ユーザーが間違ったユーザー ID の行を挿入できないようにします。The application sets the current application user ID in SESSION_CONTEXT (Transact-SQL) after connecting to the database, and then security policies transparently filter rows that shouldn't be visible to this ID, and also block the user from inserting rows for the wrong user ID. その他のアプリケーションの変更は必要ありません。No other app changes are necessary.

データを保持するテーブルを作成します。Create a table to hold data.

CREATE TABLE Sales (  
    OrderId int,  
    AppUserId int,  
    Product varchar(10),  
    Qty int  
);  

そのテーブルに、各アプリケーション ユーザーの 3 つの注文を表示する、6 つのデータ行を設定します。Populate the table with six rows of data, showing three orders for each application user.

INSERT Sales VALUES
    (1, 1, 'Valve', 5),
    (2, 1, 'Wheel', 2),
    (3, 1, 'Valve', 4),  
    (4, 2, 'Bracket', 2),
    (5, 2, 'Wheel', 5),
    (6, 2, 'Seat', 5);  

アプリケーションが接続に使用する権限の低いユーザーを作成します。Create a low-privileged user that the application will use to connect.

-- Without login only for demo  
CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO AppUser;  
  
-- Never allow updates on this column  
DENY UPDATE ON Sales(AppUserId) TO AppUser;  

SESSION_CONTEXT に格納されたアプリケーション ユーザー ID を使用して行をフィルター処理する、新しいスキーマと述語関数を作成します。Create a new schema and predicate function, which will use the application user ID stored in SESSION_CONTEXT to filter rows.

CREATE SCHEMA Security;  
GO  
  
CREATE FUNCTION Security.fn_securitypredicate(@AppUserId int)  
    RETURNS TABLE  
    WITH SCHEMABINDING  
AS  
    RETURN SELECT 1 AS fn_securitypredicate_result  
    WHERE  
        DATABASE_PRINCIPAL_ID() = DATABASE_PRINCIPAL_ID('AppUser')
        AND CAST(SESSION_CONTEXT(N'UserId') AS int) = @AppUserId;
GO  

Salesのフィルター述語およびブロック述語としてこの関数を追加するセキュリティ ポリシーを作成します。Create a security policy that adds this function as a filter predicate and a block predicate on Sales. BEFORE UPDATEBEFORE DELETE は既にフィルター処理されているため、ブロック述語に必要なのは AFTER INSERT だけです。また、以前に設定した列権限により、 列は他の値に更新できないため、 AFTER UPDATE AppUserId は不要です。The block predicate only needs AFTER INSERT, because BEFORE UPDATE and BEFORE DELETE are already filtered, and AFTER UPDATE is unnecessary because the AppUserId column cannot be updated to other values, due to the column permission set earlier.

CREATE SECURITY POLICY Security.SalesFilter  
    ADD FILTER PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales,  
    ADD BLOCK PREDICATE Security.fn_securitypredicate(AppUserId)
        ON dbo.Sales AFTER INSERT
    WITH (STATE = ON);  

Sales SESSION_CONTEXT でさまざまなユーザー ID を設定した後、 テーブルから選択することで、接続フィルタリングをシミュレートできます。Now we can simulate the connection filtering by selecting from the Sales table after setting different user IDs in SESSION_CONTEXT. 実際には、アプリケーションが、接続を開いた後に SESSION_CONTEXT で現在のユーザー ID を設定します。In practice, the application is responsible for setting the current user ID in SESSION_CONTEXT after opening a connection.

EXECUTE AS USER = 'AppUser';  
EXEC sp_set_session_context @key=N'UserId', @value=1;  
SELECT * FROM Sales;  
GO  
  
/* Note: @read_only prevents the value from changing again until the connection is closed (returned to the connection pool)*/
EXEC sp_set_session_context @key=N'UserId', @value=2, @read_only=1;
  
SELECT * FROM Sales;  
GO  
  
INSERT INTO Sales VALUES (7, 1, 'Seat', 12); -- error: blocked from inserting row for the wrong user ID  
GO  
  
REVERT;  
GO  

データベース リソースをクリーンアップします。Clean up database resources.

DROP USER AppUser;

DROP SECURITY POLICY Security.SalesFilter;
DROP TABLE Sales;
DROP FUNCTION Security.fn_securitypredicate;
DROP SCHEMA Security;

参照See Also

CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)
ALTER SECURITY POLICY (Transact-SQL)ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL)DROP SECURITY POLICY (Transact-SQL)
CREATE FUNCTION (Transact-SQL)CREATE FUNCTION (Transact-SQL)
SESSION_CONTEXT (Transact-SQL)SESSION_CONTEXT (Transact-SQL)
sp_set_session_context (Transact-SQL)sp_set_session_context (Transact-SQL)
sys.security_policies (Transact-SQL)sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)sys.security_predicates (Transact-SQL)
ユーザー定義関数の作成 (データベース エンジン)Create User-defined Functions (Database Engine)