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.

asked 17 Feb '12, 16:58

Siger%20Matt's gravatar image

Siger Matt
3.2k516998
accept rate: 13%

edited 15 Mar '13, 20:41

Mark%20Culp's gravatar image

Mark Culp
23.4k9132275


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... :)

permanent link

answered 17 Feb '12, 17:19

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

edited 18 Feb '12, 10:28

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?

(18 Feb '12, 17:24) Volker Barth

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

(21 Feb '12, 06:52) Volker Barth

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.

permanent link

answered 17 Feb '12, 17:12

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

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

The following answer contains some detailed samples.

permanent link

answered 18 Feb '12, 07:49

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

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.

permanent link

answered 21 Feb '12, 06:42

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k263661
accept rate: 17%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×415
×37
×1

question asked: 17 Feb '12, 16:58

question was seen: 1,483 times

last updated: 15 Mar '13, 20:41