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 1 Predicates from multiple grants for the same table access are combined using a Boolean or operator when all predicates apply to the columns referenced by the user command.
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 2 Predicates from multiple grants applying to different sets of columns are combined using the boolean or and and operators.
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
Example 3 Predicates from multiple grants for different accesses are combined using the Boolean and operator.
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