Facebook type security. - 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 2005 Forums

Transact-SQL (2005)

Facebook type security.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-07-28 : 21:36:04

I am trying to work out the best way to handle having these types of security, which are similiar to facebook style for if you want to only allow certain people to view something.

To make things more messy is each item will have sub items, so if the parent item has a security, the sub items will be blocked, but for simplicity purpose let's not factor this into the equation.

Anyone have a better method than just running 3 differant queries?

The security types are :
1: no security
2: all listed users
3: no listed users

Se basically I have a item table and each item can have 1 of the above security types

if no security is selected, all users can view item

if #2 is selected then only users who are listed can see item

and if #3 is selected all users except listed ones can see it.

All items will be returned to the user that they have access to. Anyone have a good idea on how to manage this. Currently the method I have that does not call for three distinct queries joined by a union will cause me to need to left join and code for each type, which does not seem very efficiant. I am open to any sugestions on a good way to handle this type of security..

These would be the items I wanted to see.


drop table items
drop table mysecurity
Create table items(userid int,itemid int)
insert into items
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5 union all
select 2,6 

--TypeID 1 means can see, type 2 means can't see
Create table mySecurity(ItemId int,TypeID int,UserID int)
insert into Mysecurity
select 1,1,1 union all
select 2,2,1 union all
select 5,2,1 union all
select 6,1,1 


declare @userIDofPersonLookedUp int 
set @UserIdOfPersonLookedUp = 2
declare @userID int 
set @UserID =1 --this represents you

--This shows me everyone that can see
select * from 
Items a
where 
a.UserID = @UserIDOfPersonLookedUp
and
not exists (Select * from mySecurity aa where aa.ItemID = a.itemID)
union
select * from 
Items a
where 
a.UserID = @UserIDOfPersonLookedUp
and
not exists (Select * from mySecurity aa where aa.ItemID = a.itemID and aa.typeID = 2 and aa.UserID = @userID)
union
select * from 
Items a
where 
a.UserID = @UserIDOfPersonLookedUp
and
exists (Select * from mySecurity aa where aa.ItemID = a.itemID and aa.typeID = 1 and aa.UserID = @userID)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-07-29 : 15:11:28

I just opted to go with another table and properly index it.

I'm open for other options though if anyone has a better way to achieve the same results!



drop table items
drop table mysecurity
drop table mysecurityDetail
Create table items(userid int,itemid int)
insert into items
select 2,1 union all
select 2,2 union all
select 2,3 union all
select 2,4 union all
select 2,5 union all
select 2,6 

--TypeID 1 means can see, type 2 means can't see
Create table mySecurity(ItemId int,TypeID int)
insert into Mysecurity
select 1,1 union all
select 2,2 union all
select 5,2 union all
select 6,1 


Create table mySecurityDetail(ItemId int,UserID int)
insert into MysecurityDetail
select 1,1 union all
select 2,1 union all
select 5,1 union all
select 6,1 


declare @userIDofPersonLookedUp int 
set @UserIdOfPersonLookedUp = 2
declare @userID int 
set @UserID =1 --this represents you

--This shows me everyone that can see
select a.* from 
Items a
left join 
mySecurity b
on a.ItemID = b.ItemID
where
a.UserID = @UserIDOfPersonLookedUp
and
(
b.ItemID is null
or
(b.TypeID = 1
and exists (Select * from mySecurityDetail aa where aa.ItemID = a.itemID and aa.UserID = @userID)
)
or
(b.TypeID = 2
and not exists (Select * from mySecurityDetail aa where aa.ItemID = a.itemID and aa.UserID = @userID)
)
)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources