How to implement selective data access rules

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to implement selective data access rules

Author

Topic

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-22 : 19:21:09

I need to define 'selective access filters' that limit the data set that each user can see. The application is metadata-driven and completely customizable. My plan is to dynamically add to the WHERE clause something like

AND <additional filter criteria>

Has anyone here done this and what are your suggestions?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-22 : 19:49:01

How are you going to differentiate users?
How are you going to define which records/columns they are allowed to see?
The most flexible method is to filter the dataset in the stored procedure based upon the login.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-22 : 23:32:35

Yes. I have done similar things. In my case, we define which group of products a user can access.

I have a table that defines the users and the user group. And another that defines what are the product groups that a user group can access.
And in the SP that retrive the products, we will have a WHERE clause that check for this.

In simplify structure

table Product
- product_code
- product_name
- product_grp

table UserGrp
- user_code
- user_grp

table UserAccess
- user_grp
- product_grp

Sample Code to illustrate what i mean

create table #usergrp
(
	user_code	varchar(30),
	user_grp	varchar(10)
)

create table #product
(
	product_code	varchar(10),
	product_name	varchar(50),
	product_grp	varchar(10)
)

create table #useraccess
(
	user_grp	varchar(10),
	product_grp	varchar(10)
)

insert into #usergrp (user_code, user_grp)
select 'sa',     'ADMIN'	union all
select 'user01', 'USER'

insert into #product (product_code, product_name, product_grp)
select 'P001', 'Product 001', 'GRP-01' union all
select 'P002', 'Product 002', 'GRP-01' union all
select 'P003', 'Product 003', 'GRP-02' union all
select 'P004', 'Product 004', 'GRP-02' union all
select 'P005', 'Product 005', 'GRP-03'

insert into #useraccess (user_grp, product_grp)
select 'ADMIN', 'GRP-01' union all
select 'ADMIN', 'GRP-02' union all
select 'USER',  'GRP-02' union all
select 'USER', 'GRP-03'

select 	* 
from	#product
where	product_grp in (select y.product_grp from #usergrp x inner join #useraccess y 
				on	x.user_grp	= y.user_grp
				where 	x.user_code 	= system_user)

You can change system_user to 'sa' or 'user01' to see the effect

-----------------
[KH]

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-23 : 10:23:02

quote:
Originally posted by khtan

Yes. I have done similar things. In my case, we define which group of products a user can access.

We do it similarly except that we would allow defining a dynamic filter for the WHERE clause. Thank you for the instructive example.

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-23 : 10:25:54

Reply to blindman:

>>How are you going to differentiate users?<<

Through a record in a user table matching the logon.

>>How are you going to define which records/columns they are allowed to see?<<

In our metadata table. We are interested in records only, columns will be hidden based on license/module consideration.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-23 : 12:14:24

Matching their server logon? This is my preference, but a lot of apps validate the user in the middle tier and then use a single login for all connections, in which case the user login will need to be passed as a parameter rather than determined from one of the built-in niladic functions.
But how are you going to say "Record 17 can be viewed by users A, B, and D, but not by user C"? Are you going to have permissions tables for each data table? Or is each record going to have a single "owner"?
I've implemented row-level security in SQL Server before with some success, but you need a good schema and well-defined application.

Marioi
Posting Yak Master

132 Posts

Posted - 2005-11-23 : 14:36:56

Reply to blindman:

>>Matching their server logon?<<

That's the current plan. The ASP.net app would log onto SS with the user's logon. I hope we won't need to do stuff beyond the user's permissions. If we do, we might put stuff that requires extra permissions in a second DB.

>>But how are you going to say "Record 17 can be viewed by users A, B, and D, but not by user C"?<<

In phase 1 (current product) we don't try to restrict user access in the row. A user is assigned permission based on conditions such as Activities linked to Territory = User's Territory or Message linked to User = logged on user. Whatever query that user applies, (s)he can see only the subset based on the condition(s).

In phase 2, we plan to add the ability to link users to rows for 'Allow viewing this record only to' and 'Allow viewing the record also to'. Apparently this complicates matters, but should be doable.

>> Are you going to have permissions tables for each data table? Or is each record going to have a single "owner"?<<

Each record has the creator linked to it so that can be used in a condition. Selective permission conditions are persisted for each user group/table combination. Users are linked to user groups.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-23 : 18:00:35

Ok, I've implemented systems where each records is "owned" by the user that created it, and then you can have a table defining who can see who's records, and who can modify who's records. For instance, if you have your users in a hierarchy you can say that a record can be viewed by that user and any parent user (boss, bosses boss, .... CEO).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources