This section shows the solution of a problem: each of five users, on different security levels, should see only rows with a value less than or equal to the user’s security level. This solution uses access rules, with the Application Context Facility, to display only the rows that one of the users, Dave, sees.
There are five logins:
Anne has security level 1.
Bob has security level 1.
Cassie has security level 2.
Dave has security level 2.
Ellie has security level 4.
Users should see only rows with a value in rlac that is less than or equal to their own security level. To accomplish this, create an access rule and apply ACF.
The rlac column is type integer, and appcontext arguments are type char.
create access rule rlac_rule as
@value <= convert(int, get_appcontext("titles",
"rlac"))
sp_bindrule rlac_rule, "titles.rlac"
/* log in as Dave and apply ACF value of 2*/
select set_appcontext("titles", "rlac", "2")
/*this value persists throughout the session*/
/*select all rows*/
select title_id, rlac from titles
---------------------
title_id |
rlac |
|---|---|
PC8888 |
1 |
BU1032 |
2 |
PS7777 |
1 |
PS3333 |
1 |
BU1111 |
2 |
PC1035 |
1 |
BU2075 |
2 |
PS2091 |
1 |
PS2106 |
1 |
BU7832 |
2 |
PS1372 |
1 |
(11 rows affected)
