|Chapter 16. Secure Your Tables (STEP 9)||Home||Chapter 18. Assign Initial Roles (STEP 11)|
Just as with tables, user-facing views should be secured. You can do this either explicitly, by adding privilege testing functions to the view definitions, or implicitly by building the views on tables that have already been secured. In this latter case, the views will need to be owned by a separate database role from that which owns the tables as views are processed with the access rights of their owners.
This is best explained by example. Here is an example from the demo:
create or replace view party_roles ( party_id, role_name, context_type, corp_context_id, org_context_id) as select ar.accessor_id, r.role_name, st.scope_type_name, case when ar.context_type_id = 3 then ar.context_id else null end, case when ar.context_type_id = 4 then ar.context_id else null end from veil2.accessor_roles ar inner join veil2.roles r on r.role_id = ar.role_id inner join veil2.scope_types st on st.scope_type_id = ar.context_type_id where veil2.i_have_global_priv(20) or veil2.i_have_priv_in_scope(20, 3, context_id) or veil2.i_have_priv_in_scope(20, 4, context_id);
Note that if the view is updatable you will also need to build instead-of triggers or some equivalent, that take into account update, insert and delete privileges. One wrinkle with updatable views is that they should not allow modifications of rows that they would not be allowed to see, so the select privilege must usually be explicitly tested in addition to the select, insert or update privilege.
Views do provide some interesting possibilities though. One is to use the privilege testing functions on specific columns to make access controls even more fine-grained.