create rule

Syntax

Changes are in bold.

create [or replace] [[and | or] access] rule          
    [owner.]rule_name            
    as condition_expression

create – creates a rule if one does not already exist.
or replace – if the rule already exists, replaces the rule definition with the new definition.
access – an access rule can be changed from an “and” rule to an “or” rule, and vice versa. Access rules cannot be replaced with a domain rule of the same name, and vice versa.
rule_name – if the specified rule name already exists, it is replaced with the new rule definition, but the name is preserved.
constant_expression – you can change the definition of the rule when the rule is replaced. The new rule value overrides the old rule value.

Example 1

This example creates a rule named limit, which limits the value of advance to $1000:

 create rule limit    
    as @advance < $1000      

select object_id("limit")      
 -----------      
1017051628

This next command replaces the created rule. The limit is changed using the or replace clause. The object ID of the rule remains the same.

 create or replace rule limit    
    as @advance < $2000      

select object_id("limit")      
 -----------      
1017051628

Example 2

The table owner creates an AND access rule called uname_acc_rule:

create access rule uname_acc_rule
as @username = suser_name()

select object_id("uname_acc_rule")

----------- 
1033051685

Replace uname_acc_rule with an OR access rule:

create or replace or access rule uname_acc_rule
as @username = suser_name()

select object_id("uname_acc_rule")

----------- 
1033051685

Procedures that access these columns will be recompiled when the rule is replaced and the procedure is executed.

Any user who impersonates the rule owner through an alias or setuser cannot replace the rule.

Changes for replacing a rule are in bold.

Granular permissions enabled	With granular permissions enabled, you must have the `create rule` privilege. You must have the `create any rule` privilege to use create rule for other users. You must be the rule owner to replace the rule.
Granular permissions disabled	With granular permissions disabled, you must have the `create rule` privilege, be the database owner, or a user with sa_role. You must be a user with sa_role to use create rule for other users. You must be the rule owner to replace the rule.

Changes are in bold.

Event	Audit Option	Command or access audited	Information in extrainfo
13	create	create rule	Roles – current active roles Keywords or options – NULL Previous value – NULL Other information – NULL Current value – NULL Proxy information – original login name, if set proxy is in effect or replace – for create or replace