Combining predicates to enforce row-level privileges

Adaptive Server combines and attaches predicates to update, delete, or select commands on a specific table, taking into account:

• Multiple grantors – grant commands are applied across all affected columns to a specific grantee, or to his group or roles, for the same access from one or more grantors.

• Multiple column sets – grant commands are applied across different column sets, to a specific grantee, or to his group or roles, for the same access from one or more grantors.

• Different access – granted privileges on the same object for different accesses.

If a revoke row exists for a specific user access at the user or group level, the aggregated grants consist of predicates that apply to the user’s set of roles.

Adaptive Server obeys a set of rules when combining predicates.

The following examples assume you create this table:

create table employee (
   eid int not null,
   ename char(8) not null,
   eaddr varchar(20) not null,
   ecity char(10) not null,
   ezip char(5) not null,
   ephone int not null,
   esalary money not null,
   edept char(10) not null
)

And insert these values:

insert employee
values (1001, 'Bill','6 Tree St','Baytown','97364',3973465,100000, 'eng')
insert employee
values (1009, 'Sally', '2 Sun Dr', 'Henton', '97821', 1239786, 50000, 'sales')
insert employee
values (1005, 'Drew','7 Inn Rd','Denville','29052',4960034,110000, 'payroll')
insert employee
values (1010, 'Navid', '1 Bat Ave', 'Busitown', '60734', 3094509,175000, 'hr')
insert employee
values (1004, 'Pat', '5 Dot Ln', 'Toytown', '33109', 4247184, 90000, 'eng')
insert employee
values (1006, 'Sue', '4 Tip St', 'Uptown', '76420', 5258641, 200000, 'sales')

Example 10

If a user is directly, or indirectly, granted (for example, through a role) multiple predicated privileges for the same access and on the same object, the predicates from the individual grant commands are combined using the or parameter. Additional grants with predicates for a single access usually results in more rows being made visible to the user.

For example, user Ben, a sale manager, has been granted these permissions on the employee table:

grant select on employee
  where edept = "sales"
  to sales_mgr_role
grant select on employee (eid, ename, eaddr,
  ezip)
  where ezip like "97%"
  to ben 

When Ben with sales_manager_role active, executes this statement, Adaptive Server notices that columns eid and ename are affected by the two predicated grant commands:

select eid, ename from employee

Adaptive Server internally executes:

select eid, ename from employee
  where edept = "sales" or
  ezip like "97%"

Adaptive Server then returns:

eid       ename
-----      ------
1001      Bill
1009      Sally
1006      Sue

Example 11

When a result set is affected non-uniformly by a set of predicates, Adaptive Server combines all predicates for a given access on each column using the boolean or operator. These Boolean expressions are combined across all columns using the Boolean and operator.

For example, Ben has been granted the same privileges as in Example 1, except the first grant affects a subset of employee columns:

grant select on employee (esalary) 
  where edept = "sales"
  to sales_mgr_role

grant select on employee (eid, ename, eaddr,
  ezip)
  where ezip like "97%"
  to ben

When Ben executes this statement, Adaptive Server notices that columns ename and esalary are affected by the different grant commands:

select ename, esalary from employee

Adaptive Server internally executes:

select ename, esalary from employee
  where edept = "sales" and ezip like "97%"

Adaptive Server then returns:

ename     esalary
sally     50000

If select access on an object is granted to a subject using a predicate, update access is allowed using a different predicate, and both predicated grants are required for access, the two predicates are combined using the Boolean and operator.

For example, the first grant allows California administrators to see employees who live in the “97…” area code. Through the second grant, those administrators are allowed to update only employees whose salary is less than $100,000 per year:

grant select on employee
  where ezip like "97%"
  to calif_admin

grant update on employees (esalary)
  where esalary < $100000
  to calif_admin

When a user with calif_admin role executes:

select eid, ename, esalary from employee

Adaptive Server returns:

eid     ename     esalary
----     ------    -----------
1001       Bill     100,000.00
1009      Sally      50,000.00

When a user with calif_admin executes:

update employees
set esalary = esalary + (esalary * 0.05)

Adaptive Server internally executes:

update employees
set esalary = esalary + (esalary * 0.05)
  where ezip like "97%"
  and esalary < $100000

Adaptive Server then updates the row as:

eid     ename     esalary
1009    Sally      $52500