ansi_permissions and predicated privileges

If an update or a delete command includes a where clause that references columns in a table that has rows being updated or deleted, Adaptive Server checks for select permission privileges on these columns only if ansi_permissions is enabled for the session. Adaptive Server applies predicated privileges on the update and delete statements according to whether:

• ansi_permissions is enabled – predicated privileges from grant select are applied in addition to any predicate specified in the grant update statement if the table owner granted predicated select access on the columns in the where clause. Verify that applying both predicates does not unexpectedly restrict the row set.

• ansi_permissions is disabled – select permission is not required on the columns of the updated or deleted table referenced in the where clause. Any predicates granted for select access on the table are not applied to the update or delete statement.

This example illustrates how ansi_permissions affects predicated privileges:

• The first grant allows user “Bob” to update employee salaries in the sales department, while the second grant allows Bob to select the employee ID and address of only those employees who are his direct reports:

  grant update on employee (salary) 
    where edept = "sales"
    to bob
  
  grant select on employee (eid, eaddress)
    where user_name(mgrid) = USER
    to bob 
  

  If Bob attempts to update an employee’s salary based on eid, one or both of the predicates from grant commands in the examples above are applied:

  update employee
    set salary = salary * 0.1
    where eid = 1006
  

• If the session has set ansi_permissions enabled, Adaptive Server executes the update command after adding the predicates for both the update and the select statements:

  update employee
    set salary = salary * 0.1
    where eid = 1006
    and edept = "sales"
    and user_name(mgrid) = USER
  

  Since Bob’s predicated select permission allows him to search IDs only of employees who are his direct reports, Bob's predicated select access does not restrict the rows he can update or delete.

  If ansi_permissions are disabled, Adaptive Server executes the update command as follows:

  update employee
    set salary = salary * 0.1
    where eid = 1006
    and edept = 'sales'
  

  To summarize, if ansi_permissions is:

  • Disabled – Bob can update and delete rows that he is not allowed to select.

  • Enabled – the where clause on a predicated grant for select access qualifies the row set that may be updated or deleted.

  Either one or both of the predicates from the grant commands in the examples above are applied.