create rule

The or replace clause allows you to replace a rule's definition using create rule.

Syntax

Changes are in bold.

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

Parameter Changes for create or replace rule

Examples

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

Objects Dependent on Replaced Rules

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

Permission Changes for create or replace rule

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.

Auditing Changes for create or replace 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