What's the best way to implement a row-level security type of setup for an already existing structure in SQL Anywhere 12+ ?
We would have 2 use cases for such a setup:
1) Customer A wants to let their Client X into the database to run generic reports but only be able to see the information that matches up to Client X, not any other data for other clients.
2) Customer B wants to segregate within their own organization that if you are responsible for a client's records you can change/update them, but you cannot change/update them for clients assigned to others.
Views are the traditional solution to providing secure selection criteria:
the end user can't SELECT on the base table, only the view,
the user security table provides the WHERE lookup value(s) required to identify visible base table rows.
PERFORMANCE ALERT: Some shops go crazy with security views; every base table is accessible only through a view which joins the table to a multi-layer (multi-table) security schema, so that a query involving ten base tables suddenly has a join involving twenty, thirty, forty or more tables.
The edit from FROM to WHERE recognizes that it is not easy to make a view updatable. Updatable views are really cool because the WITH CHECK OPTION can be used to prevent inserts, updates and deletes on unauthorized rows. Sadly, INSERT and DELETE statements only work on views with exactly one table in the FROM clause, so you have to replace those nifty INNER JOIN ... ON clauses with old-fashioned WHERE EXISTS ( SELECT * FROM ... ) and such-like.
I feel a blog coming on... :)
close access to all critical tables all insert/update/delete made only via special stored procedure, where you realize security checks. for reporting purposes try to use views or stored procedures that realize security filters within.
answered 17 Feb '12, 17:12
I use in some views the SysGroups View to limit the rows.
WHERE "TABLE"."GROUP_NAME" in ( select SG.group_name from sys.sysgroups as SG where SG.group_name like '<GroupSelectionCriteria>' and SG.member_name = current user )
Due to the in clause the user can see rows from all groups he is a member.
Only to give a additional idea.
answered 21 Feb '12, 06:42