Predicated privileges include a system of flexible row-level access controls, allowing you to grant select, update, and delete privileges on data for different users, groups, or roles based on a predicate that is evaluated by Adaptive Server during data access. If the condition expressed by the predicate is not met for any row of data, Adaptive Server withholds that row from the result set.
Predicated privileges offer data privacy protection based on row-level access controls that dynamically grant privileges to a user based on data content or context information, allowing you to implement a privacy policy in the server instead of the client or a Web server.
A predicate may access other objects, such as tables, SQL functions, or built-in functions. Access to these other objects is checked against the permissions and roles of the predicate owner (the grantor of the predicated privilege). The user who executes the select, update, or delete command on the object targeted by the grant does not require explicit permission on the objects referenced by the predicate.
Predicated privileges allow a service provider to store data in a single database, and share the same tables for multiple customers instead of requiring separate views and instead of triggers for each customer. Adaptive Server filters the data for specific users according to the predicated privileges granted directly to the user, or granted indirectly through the user’s groups or roles.
Table 7-1 compares the advantages of predicated privileges over the access rules provided by versions of Adaptive Server earlier than 15.7, ESD #2:
Access rules |
Predicated privileges |
|
|---|---|---|
Predicate scope |
Can refer only to the column to which the rule is bound. Multiple access rules are required to reference more than one column. |
Can refer to any column in the table or, using a sub-select, any other table or function in the current database or in other databases. |
Statement scope |
All rules apply to select, update, and delete. Does not allow different restrictions across different kinds of access. |
Grantor specifies which predicate applies to which access. For example, stricter access control may be imposed for updating and deleting rows than for selecting rows. |
Combining predicates |
Gives a way to combine individual rules using and or or, but does not give a way to express precedence grouping; for example, ((rule1 and rule2) or rule3). |
A predicated privilege can construct an arbitrarily complex Boolean expression. You may combine multiple privileges against the same object based on well-defined rules. |
Restricting the scope to a subject |
Any restriction directed at a specific subject must be expressed as part of the rule, such as where name = user. |
You can apply a predicate to selected users, groups, or roles without the administrator needing to introduce complicated logic into the predicate. |
Integration with permissions system |
Decoupled from SQL authorization. Requires rule analysis to understand how users are restricted from some action. |
Strongly enforced through Adaptive Server permission system. Diagnostic commands allow an application developer to predict how the predicates modify a query . |
