Row-Level Security?

[former_SQLA_member1694868]

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.

[Breck_Carter · Accepted solution]

Views are the traditional solution to providing secure selection criteria:

the end user can't SELECT on the base table, only the view,

the view FROM WHERE clause joins the base table to a user security table, and

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... 🙂

[VolkerBarth · Accepted solution]

I surely agree on the need to use a WHERE EXISTS part for updatable views - but IMHO this usually leads to a clear way to define them, so that need not be a disadvantage by itself.

For performance reason, it would be nice to know whether the usual "subquery flattening" (i.e. turning them into joins) would be done here by the query optimizer, too, or whether the "updatable" property will prevent such optimizations - Glenn, what do you say?

[VolkerBarth · Accepted solution]

FWIW, here's the blog article Breck has announced:)

[VolkerBarth]

As to Breck's suggestion with views and a security table:

The following answer contains some detailed samples.

[Former Member]

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.

[thomas_duemesnil]

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.