The grantor of a predicated privilege must have authorization to access any tables build-in or SQL functions referenced by the predicate. For row-filtering predicates, Adaptive Server verifies accesses made by the predicate at the same point in execution that is verifies accesses made by the user’s select, update or delete commands. Accesses made by role activation predicates are checked when the user activates a role. The user who enters the command to which a predicate is attached is not required to have authorization on the predicate’s referenced objects.
For example, enforcing data privacy requires you specify predicates that access tables and columns that contain the data owner’s privacy policies. Access to these privacy policies should be restricted to a small set of users; data consumers should not necessarily have the right to view the internal data used to enforce policies.
Adaptive Server enforces permissions on accesses made by predicated privileges against the grantor of the privilege, taking into account the grantor’s direct and indirect permissions through group and default role membership. A user’s default roles include roles granted through login profiles for automatic activation.
You may want to create an application security role that has permission on privacy metadata tables. You can then assign object owners this role as a default or automatically activated role so that the grant predicates have access to the necessary data.
In this example Bob, the owner of the purchases table, grants permission to market analysts with the market_role to view product purchasing information for those customers who opted to share their data (customer preferences are stored in the privacy_db).
grant select on purchases p where exists (select 1 from privacy_db..choices c where p.custid = c.id) to market_role
When user Alice, who has activated the market_role, selects from the purchases table, Adaptive Server performs these access checks:
Alice’s direct or indirect select permission on purchases
Bob’s direct or indirect permission to select from the privacy_db..choices table
The ownership of a predicated privilege is modified
when the object to which the predicated grant applies
changes ownership by the alter .. modify owner command.
If the new owner has not been granted permission on the objects
accessed by the predicate, application of the predicate causes a
run time error.
