grant allows you to grant row-level access control on a table based on conditions specified by a where clause. The syntax is:
grant {all [privileges] | permission_list}
on table_name [correlation_name]
[(column_name_list)]
[where search_conditions
[as pred_name]]
to {public | name_list | role_list}
[with grant option]
See the Reference Manual: Commands.
The grant ... where statement on a table allows you to define conditions on select, update, or delete commands against that table that qualify or disqualify rows from the result set. The search_conditions act as a row filter, working with the where clause specified on the select, update, or delete. You can compose search_conditions that contain complex SQL conditions and reference functions, tables, and SQL functions. However, predicated privileges cannot reference a view.
This example gives engineers permission to see their own salary and the salary of the workers reporting to them:
grant select on employee (salary) where ename = USER or emgr = USER to eng_role
The statement filters rows on the select command (including selects that are part of compound commands such as select into, insert select, and update from, and select commands wrapped by a view), as well as on select, update, and delete commands that reference the salary column in their where clauses. The predicate, effectively, restricts workers from seeing another worker’s salary.
Adaptive Server applies the where clause from the grant statement when a query against the table is processed, and evaluates the conditions when a query is executed.
