arthur hoornweg

Hi everyone, sorry for bringing up this topic. It has probably been
asked a million times before?

I want to limit user access in a table to only certain records.

Assume the table (table1) has a foreign key "projectnr", and I want my
users to only access a subset of all projects. Would the following
scenario work?


My idea is to create a security table with pairs of username and
projectnr. The access to this table is restricted to the DBO, nobody
else should be able to write to this table.

Create table Security (user VARCHAR (50) not null
projectnr INTEGER not null);


Then I would create a view that grants access only to those records
whose projects are allowed for the user:

CREATE VIEW table1_view AS
SELECT * FROM TABLE1 T WHERE EXISTS
(SELECT projectnr from security S WHERE T.projectnr = S.projectnr AND
S.USER=CURRENT_USER)


Subsequently I would forbid direct access to Table1, the user can only
read and write the table through Table1_View.

Is this possible at all, or am I thinking in the wrong direction ??

Arthur Hoornweg
(please remove the ".net" from my email address)

russell fields

Arthur,

Here is a handy paper that should help you:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

Russell Fields

russell fields

Arthur,

You will note that he is using views, the stored procedures are the way he
suggest that you access the views. The key, however, is the view
definitions.

If you view the stored procedures you will see that they are doing a little
error handling, but nothing else really. If your application is data-aware,
then it should not need the stored procedures and can directly access the
views.

You have to be careful not to create a view that cannot be updated, but
beyond that you should be fine.

Russell Fields

arthur hoornweg

Thank you! I'll go the "view" way then...


--
Arthur Hoornweg
(please remove the ".net" from my e-mail address)