create rule

Syntax

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

Parameters

• 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 – specifies that you are creating an access rule. 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.

  See Managing User Permissions in the System Administration Guide.

• rule_name – is the name of the new rule, which must conform to the rules for identifiers and cannot be a variable. Specify the owner’s name to create another rule of the same name owned by a different user in the current database. The default value for owner is the current user.

  If the specified rule name already exists, it is replaced with the new rule definition, but the name is preserved.

• condition_expression – specifies the conditions that define the rule. It can be any expression that is valid in a where clause, and can include arithmetic operators, relational operators, in, like, between, and so on. However, condition_expression cannot reference a column or any other database object. Built-in functions that do not reference database objects can be included.

  A condition_expression takes one argument, which must be prefixed by the @ sign and refers to the value that is entered via the update or insert command. You can use any name or symbol to represent the value when you write the rule. Enclose character and date constants in quotes, and precede binary constants with “0x”.

  You can change the definition of the rule when the rule is replaced. The new rule value overrides the old rule value.

Examples

• Example 1 – Creates a rule named limit, which limits the value of advance to less than $1000:

  create rule limit 
  as @advance < $1000

• Example 2 – Creates a rule named pubid_rule, which restricts the values of pub_id to 1389, 0736, or 0877:

  create rule pubid_rule 
  as @pub_id in ('1389', '0736', '0877')

• Example 3 – Creates a rule named picture, which restricts the value of value to always begin with the indicated characters:

  create rule picture 
  as @value like '_-%[0-9]'

• Example 4 – 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 5 – 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
  

Usage

• To hide the text of a rule, use sp_hidetext.

• To rename a rule, use sp_rename.

See also sp_bindrule, sp_help, sp_helptext, sp_hidetext, sp_rename, sp_unbindrule in Reference Manual: Procedures

Standards

ANSI SQL – Compliance level: Entry-level compliant.

To create rules using ANSI SQL-compliant syntax, use the check clause of the create table statement.

Permissions

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

The permission checks for create rule differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: