weird..weird question - SQL Server Forums

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

Transact-SQL (2000)

weird..weird question

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-06-29 : 04:21:37

The table creation script..


if exists (select * from dbo.sysobjects where id = object_id(N'[guest].[Address_Test]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [guest].[Address_Test]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[guest].[User_Test]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [guest].[User_Test]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[guest].[Details_Test]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [guest].[Details_Test]
GO

CREATE TABLE [guest].[Address_Test] (
	[ID] [int] NOT NULL ,
	[Address1] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address2] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Address3] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [guest].[User_Test] (
	[UserRole] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AllowedFields] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [guest].[Details_Test] (
	[ID] [int] NULL ,
	[FirstName] [nvarchar] (100) COLLATE 
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[LastName] [nvarchar] (100) COLLATE 
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EmailId] [nvarchar] (150) COLLATE 
SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

Table values ....


Table User_Test
UserRole    AllowedFields
Admin	    all
DEO	    ID,FirstName,Address1
Supervisor  ID,FirstName,LastName,Address1,Address2

Table Details_Test
ID      FirstName       LastName        EmailId
1	ABC             XYZ             abc@xyz.com 
2	AAA             BBB             aaa@bbb.com
3	XYZ             ABC             xyz@abc.com

Table Address_Test
ID      Address1     Address2     Address3
1       123          456
2       456          456           789
3       525          525           676

If we look at the User_Test table for every user there is a set of allowed fields. These fields can be different tables. What I'm try to figure out is based on this list of allowed fields is it possible to restrict the query the user executes.

For example:
A user with DEO role cannot execute a query with columns other than ID,FirstName,Address1 or he can can view or update only the columns of ID,FirstName,Address1.

If the query is like

"Select FirstName,LastName,EmailId,Address1,Address2,Address3 From Detail_Test,Address_Test Where Detail_Test.ID = Address_Test.ID"

While a user with admin role can see all the values of this result, the user with DEO role should be able to see values for the columns specified in AllowedFields in the User_Test table...

Any ideas pointers???

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-29 : 04:31:13

check column level permission

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-06-29 : 05:26:41

Jen,

I suppose, the column level permission can be granted only for users created in sql server itself. If you look at the code I have userrole is in a separate table these userroles might not be in the database.Kind of custom ones...

The other problem I noticed is when a specific userrole executes a query with columns where the userrole dont have a permission then I will get an error.But what I need is the query to just return a result with the columns where that userrole has permissions.

Say DEO executes the following query


Select FirstName,LastName,EmailId,Address1,Address2,Address3 
From Detail_Test,Address_Test Where Detail_Test.ID = Address_Test.ID

I will get an error stating that Select permission denied. Instead what I want the query to return is the values of ID,FirstName,Address1 columns, other columns can be null not in the resultset at all...something like this....

Result 1:
ID FirstName Address1
1 ABC 123
2 AAA 456
3 XYZ 525

OR

Result 2:
ID FirstName Address1 Othercolumns...
1 ABC 123 Null
2 AAA 456 Null
3 XYZ 525 Null

The basic idea is whether I can manage custom userlevel access to data from front end or not...Kind of security excerise...

May be asking for too much....?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-29 : 15:52:42

>>May be asking for too much....?

column/row level security as well as application object security is not asking too much. However, covering a general "how to" in a forum thread may be. :)

Homegrown security schemes can be quite varied. You obviously need to take your application architecture into account when designing the security. Often a hiearchal approach is used that starts with application modules and works through application controls and finally down to specific data columns and rows. The result is achieved with a coordinated effort between the application tiers and the database. How that division of labor is decided upon depends on the approach and the expertise of the resources.

Having said all that, here is one (off the top of my head) way to accomplish what you actually asked for:


set nocount on
create table TGuser (userid int identity(1,1), username varchar(30))
create table TGRole (roleid int identity(1,1), rolename varchar(30))
go
create table TGUserRole (userid int, roleid int)
go
create table TGDataSecurity (roleid int, tablename varchar(30), columnname varchar(30), accesslevel char(1))
create table TGDataTable (companyid int, companyname varchar(30), acctBal money, foundedDate datetime)
go

insert TGuser (username) select 'tg' union select 'Karuna'
insert TGrole (rolename) select 'Admin' union select 'user'
insert TGuserrole (userid, roleid) select 1,1 union select 2,2
go
insert TGDataTable select 1,'tg''s company', 20, getdate()
go
insert TGdatasecurity (roleid, tablename, columnname, accesslevel)
select 2, 'TGDataTable', 'acctBal', 'H' --(hidden, readonly, etc...)
union select 2, 'TGDataTable', 'foundedDate', 'R'

go
-----------------------------------------------------------
--Stored Procedure input parameters
create proc TGTestColumnSecurity
	@userid int
as

declare @hideAcctBal bit
	,@hideFoundedDate bit

select 	@HideAcctBal = convert(bit,sum(acctBal))
	,@HideFoundedDate = convert(bit,sum(foundedDate))
from	(
	select	userid
		,tablename
		,columnname
		,acctBal = case when columnname = 'acctBal' then 1 else 0 end
		,foundedDate = case when columnname = 'dateFounded' then 1 else 0 end
	from	TGDataSecurity a
	join	TGuserRole b
			on a.roleid = b.roleid
	where	b.userid = @userid
	and	tablename = 'TGDataTable'
	and	columnname in ('acctBal', 'foundedDate')
	and	accessLevel = 'H'
	) a

select	CalledByUser = @Userid
	,companyid
	,companyname
	,acctBal = case when @HideAcctBal = 1 then null else acctBal end
	,foundedDate = case when @HidefoundedDate = 1 then null else foundedDate end
from	TGDataTable

go

exec TGTestColumnSecurity 1
exec TGTestColumnSecurity 2

drop proc TGTestColumnSecurity
drop table TGdatatable
drop table TGDataSecurity
drop table TGUserRole
drop table TGrole
drop table TGUser

Be One with the Optimizer
TG

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-06-29 : 22:46:46

will it work for you if you can control the users using views or selective sproc for specific columns?

otherwise, you're talking dsql to build the query as implied by TG

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-06-30 : 00:03:14

quote:
Originally posted by TG

>>May be asking for too much....?

column/row level security as well as application object security is not asking too much. However, covering a general "how to" in a forum thread may be. :)

Be One with the Optimizer
TG

TG, We have a seperate security module in the application where we are planning to handle this issue. My idea is to see if I can filter it in db query level itself so that can avoid much hassles at front end level coding. Since its very early for us to decide upon anything just exploring the possiblities...

BTW, Thanks for all your help with my previous posts, which helped me a lot in completing the application.

quote:

>>will it work for you if you can control the users using views or selective sproc for specific columns?

>>otherwise, you're talking dsql to build the query as implied by TG

Jen,I'll check the options.Jen,The total users will be some where around 100+ or so..., the records might be around 300,000+, So dsql might not affect much I suppose...Let me check the options and post it here....

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-30 : 09:02:20

>>otherwise, you're talking dsql to build the query as implied by TG

Did I imply that? I didn't mean to

. When you say dsql does that mean dynamic sql? or dumb sql? I guess if it's the latter, I'm guilty.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources