Syntax for access rules

Use the access parameter in the create rule syntax to create access rules.

create [or|and] access rule (access_rule_name)
as (condition)

This section shows the process of creating a table and binding an access rule to it.

A table owner creates and populates table T (username char(30), title char(30), classified_data char(1024)):

AA, "Administrative Assistant","Memo to President"
AA, "Administrative Assistant","Tracking Stock 						Movements"
VP1, "Vice President", "Meeting Schedule"
VP2, "Vice President", "Meeting Schedule"

The table owner creates access rule uname_acc_rule and binds it to the username column on table T.

create access rule uname_acc_rule
as @username = suser_name()
-----------
sp_bindrule uname_acc_rule, "T.username"

When you issue the following query:

select * from T

Adaptive Server processes the access rule that is bound to the username column on table T and attaches it to the query tree. The tree is then optimized and an execution plan is generated and executed, as though the user had executed the query with the filter clause given in the access rule. In other words, Adaptive Server attaches the access rule and executes the query as:

select * from T where T.username = suser_name().

The condition where T.username = suser_name() is enforced by the server. The user cannot bypass the access rule.

The result of an Administrative Assistant executing the select query is:

AA, "Administrative Assistant","Memo to President"
AA, "Administrative Assistant","Tracking Stock Movements"

Before you drop an access rule, you must unbind it from any columns or datatypes, using sp_unbindrule, as in the following example:

sp_unbindrule "T.username",
NULL, "all"

sp_unbindrule unbinds any domain rules attached to the column by default.

After you unbind the rule, you can drop it:

drop rule "rule_name"

For example:

drop rule "T.username"

Each access rule is bound to one column, but you can have multiple access rules in a table. create rule provides AND and OR parameters to handle evaluating multiple access rules. To create AND access rules and OR access rules, use extended access rule syntax:

• AND access rule:

  create and access rule rule_name
  

• OR access rule

  create or access rule rule_name as 
  

You can bind AND access rules and OR access rules to a column or user-defined datatype. With the extended access rule syntax, you can bind multiple access rules to the table, although you can bind only one per column. When the table is accessed, the access rules go into effect, the AND rules bound first by default, and then the OR access rules.

If you bind multiple access rules to a table without defining AND or OR access, the default access rule is AND.

If there is only one access rule on a row of the table and it is defined as an OR access rule, it behaves as an AND access rule.