CREATE SECURITY POLICY (Transact-SQL)CREATE SECURITY POLICY (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

建立資料列層級安全性的安全性原則。Creates a security policy for row level security.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

CREATE SECURITY POLICY [schema_name. ] security_policy_name    
    { ADD [ FILTER | BLOCK ] } PREDICATE tvf_schema_name.security_predicate_function_name   
      ( { column_name | expression } [ , ...n] ) ON table_schema_name. table_name    
      [ <block_dml_operation> ] , [ , ...n] 
    [ WITH ( STATE = { ON | OFF }  [,] [ SCHEMABINDING = { ON | OFF } ] ) ]  
    [ NOT FOR REPLICATION ] 
[;]  
  
<block_dml_operation>  
    [ { AFTER { INSERT | UPDATE } }   
    | { BEFORE { UPDATE | DELETE } } ]  

引數Arguments

security_policy_namesecurity_policy_name
安全性原則的名稱。The name of the security policy. 安全性原則名稱必須符合識別碼的規則,並且在資料庫內及對於它的結構描述都必須是唯一的。Security policy names must comply with the rules for identifiers and must be unique within the database and to its schema.

schema_nameschema_name
這是安全性原則所屬的結構描述名稱。Is the name of the schema to which the security policy belongs. 因為結構描述繫結,schema_name 是必要的。schema_name is required because of schema binding.

[ FILTER | BLOCK ][ FILTER | BLOCK ]
繫結至目標資料表之函式的安全性述詞類型。The type of security predicate for the function being bound to the target table. FILTER 述詞會以無訊息方式篩選讀取作業可用的資料列。FILTER predicates silently filter the rows that are available to read operations. BLOCK 述詞會明確封鎖違反述詞函式的寫入作業。BLOCK predicates explicitly block write operations that violate the predicate function.

tvf_schema_name.security_predicate_function_nametvf_schema_name.security_predicate_function_name
這是內嵌資料表值函數,可做為述詞使用,並會在查詢目標資料表時強制執行。Is the inline table value function that will be used as a predicate and that will be enforced upon queries against a target table. 針對每個特定資料表的每項特定 DML 作業,最多只能定義一個安全性述詞。At most one security predicate can be defined for a particular DML operation against a particular table. 內嵌資料表值函數必須使用 SCHEMABINDING 選項建立。The inline table value function must have been created using the SCHEMABINDING option.

{ column_name | expression }{ column_name | expression }
做為安全性述詞函數參數的資料行名稱或運算式。A column name or expression used as a parameter for the security predicate function. 可以使用目標資料表上的任何資料行。Any column on the target table can be used. 運算式只可包含目標資料表的常數、內建純量函數、運算子和資料行中。An Expression can only include constants, built in scalar functions, operators and columns from the target table. 必須針對函式的各個參數指定資料行名稱或運算式。A column name or expression needs to be specified for each parameter of the function.

table_schema_name.table_nametable_schema_name.table_name
這是套用安全性述詞的目標資料表。Is the target table to which the security predicate will be applied. 您可以將多個已停用的安全性原則目標設為特定 DML 作業的單一資料表,但無論何時都只能啟用一個安全性原則。Multiple disabled security policies can target a single table for a particular DML operation, but only one can be enabled at any given time.

<block_dml_operation> 要套用封鎖述詞的特定 DML 作業。<block_dml_operation> The particular DML operation for which the block predicate will be applied. AFTER 可指定在 DML 作業 (INSERT 或 UPDATE) 執行之後,根據資料列的值來評估述詞。AFTER specifies that the predicate will be evaluated on the values of the rows after the DML operation was performed (INSERT or UPDATE). BEFORE 可指定在 DML 作業 (UPDATE 或 DELETE) 執行之前,根據資料列的值來評估述詞。BEFORE specifies that the predicate will be evaluated on the values of the rows before the DML operation is performed (UPDATE or DELETE). 如果沒有指定作業,則會將述詞套用至所有作業。If no operation is specified, the predicate will apply to all operations.

[ STATE = { ON | OFF } ][ STATE = { ON | OFF } ]
啟用或停用強制對目標資料表執行其安全性述詞的安全性原則。Enables or disables the security policy from enforcing its security predicates against the target tables. 如果未指定,則會啟用正在建立的安全性原則。If not specified the security policy being created is enabled.

[ SCHEMABINDING = { ON | OFF } ][ SCHEMABINDING = { ON | OFF } ]
指出是否必須使用 SCHEMABINDING 選項來建立原則中的所有述詞函式。Indicates whether all predicate functions in the policy must be created with the SCHEMABINDING option. 根據預設,所有函式都必須以 SCHEMABINDING 來建立。By default, all functions must be created with SCHEMABINDING.

NOT FOR REPLICATIONNOT FOR REPLICATION
表示當複寫代理程式修改目標物件時,不應執行安全性原則。Indicates that the security policy should not be executed when a replication agent modifies the target object. 如需詳細資訊,請參閱在同步處理期間控制觸發程序和條件約束的行為 (複寫 Transact-SQL 程式設計)For more information, see Control the Behavior of Triggers and Constraints During Synchronization (Replication Transact-SQL Programming).

[table_schema_name.] table_name[table_schema_name.] table_name
這是套用安全性述詞的目標資料表。Is the target table to which the security predicate will be applied. 單一資料表可以有多個已停用的安全性原則,但無論何時都只能啟用一個安全性原則。Multiple disabled security policies can target a single table, but only one can be enabled at any given time.

RemarksRemarks

搭配使用述詞函式與記憶體最佳化資料表時,您必須包含 SCHEMABINDING 並使用 WITH NATIVE_COMPILATION 編譯提示。When using predicate functions with memory-optimized tables, you must include SCHEMABINDING and use the WITH NATIVE_COMPILATION compilation hint.

系統會在執行對應的 DML 作業後評估封鎖述詞。Block predicates are evaluated after the corresponding DML operation is executed. 因此,READ UNCOMMITTED 查詢可以查看之後會回復的暫時性值。Therefore, a READ UNCOMMITTED query can see transient values that will be rolled back.

權限Permissions

需要結構描述的 ALTER ANY SECURITY POLICY 權限和 ALTER 權限。Requires the ALTER ANY SECURITY POLICY permission and 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.

範例Examples

下列範例示範如何使用 CREATE SECURITY POLICY 語法。The following examples demonstrate the use of the CREATE SECURITY POLICY syntax. 如需完整安全性原則案例的範例,請參閱資料列層級安全性For an example of a complete security policy scenario, see Row-Level Security.

A.A. 建立安全性原則Creating a security policy

下列語法使用篩選器述詞建立客戶資料表的安全性原則,並保持停用安全性原則。The following syntax creates a security policy with a filter predicate for the Customer table, and leaves the security policy disabled.

CREATE SECURITY POLICY [FederatedSecurityPolicy]   
ADD FILTER PREDICATE [rls].[fn_securitypredicate]([CustomerId])   
ON [dbo].[Customer];  

B.B. 建立影響多個資料表的原則Creating a policy that affects multiple tables

下列語法使用三個篩選器述詞建立三種資料表的安全性原則,並啟用安全性原則。The following syntax creates a security policy with three filter predicates on three different tables, and enables the security policy.

CREATE SECURITY POLICY [FederatedSecurityPolicy]   
ADD FILTER PREDICATE [rls].[fn_securitypredicate1]([CustomerId])   
    ON [dbo].[Customer],  
ADD FILTER PREDICATE [rls].[fn_securitypredicate1]([VendorId])   
    ON [dbo].[ Vendor],  
ADD FILTER PREDICATE [rls].[fn_securitypredicate2]([WingId])   
    ON [dbo].[Patient]  
WITH (STATE = ON);  

C.C. 建立包含多種安全性述詞類型的原則Creating a policy with multiple types of security predicates

將篩選述詞和封鎖述詞新增至 Sales 資料表。Adding both a filter predicate and a block predicate to the Sales table.

CREATE SECURITY POLICY rls.SecPol  
    ADD FILTER PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales,  
    ADD BLOCK PREDICATE rls.tenantAccessPredicate(TenantId) ON dbo.Sales AFTER INSERT;  

另請參閱See Also

資料列層級安全性 Row-Level Security
ALTER SECURITY POLICY (Transact-SQL) ALTER SECURITY POLICY (Transact-SQL)
DROP SECURITY POLICY (Transact-SQL) DROP SECURITY POLICY (Transact-SQL)
sys.security_policies (Transact-SQL) sys.security_policies (Transact-SQL)
sys.security_predicates (Transact-SQL)sys.security_predicates (Transact-SQL)