sql server - SQL property from inherited Parent-Child relationship -
your inputs appreciated..!! please have , suggest..
two tables -
table1
user(pk) | permission_on usr1 | folder_a usr2 | folder_b usr3 | folder_c
and
table2
rowaddedtimestamp(pk) | user(pk) | substitute_user | begindate | enddate xxxx1 | usr1 | usr1 | 1-1-2010 | 1-1-2010 xxxx2 | usr2 | usr2 | 1-1-2010 | 1-1-2010 xxxx3 | usr1 | usr2 | 1-1-2010 | 1-1-2012 xxxx4 | usr3 | usr3 | 1-1-2010 | 1-1-2010 xxxx5 | usr2 | usr3 | 1-1-2011 | 1-1-2013
i have tried not getting how exact permission on particular user consideration of substitute user.
like if today -
2-2-2011 permission on usr3 - folder_a, folder_b, folder_c
folder_c -- direct permission usr3 folder_b -- usr3 substitute of usr2 (between 1-1-2011 1-1-2013) folder_a -- usr2 substitute of usr1 (between 1-1-2010 1-1-2012)
2-2-2012 permission on usr3 - folder_b, folder_c
folder_c -- direct permission usr3 folder_b -- usr3 substitute of usr2 (between 1-1-2011 1-1-2013) folder_a -- **** no because usr2 substitute of usr1 (between 1-1-2010 1-1-2012) , today 2-2-2012
2-2-2013 permission on usr3 - folder_c
folder_c -- direct permission usr3 folder_b -- **** no because usr3 substitute of usr2 (between 1-1-2011 1-1-2013) , today 2-2-2013 folder_a -- **** no because usr2 substitute of usr1 (between 1-1-2010 1-1-2012) , today 2-2-2013
use mssql functions. drastically improve performance comparing nested , recursive queries.
e.g. function:
create function getuserlist (@inputuser varchar(250), @inputdate varchar(250)) returns @userlist table ( username varchar(250), ) begin insert @userlist select a.substitute_user, table2 a.user = @inputuser , a.begindate <= @inputdate , a.enddate >= @inputdate return end
and afterwards use source substitute user list e.g.
select permission_on table1 user in (select username table1('usr1','2-2-2011')) or user = 'usr1'
--edit--
ohh, see, misunderstood user substitute user.. in such case can use sql cte functionality:
take @ sqlfiddle
in query should replace user , both dates in query
with accesshierarchy ( userr ) ( -- base case select userr table1 userr = 'usr3' union -- recursive step select e.userr table2 e inner join accesshierarchy eh on e.substitute_user = eh.userr e.userr != e.substitute_user , begindate <= '2011-05-05' , enddate >= '2011-05-05' ) select permission_on table1 userr in (select userr accesshierarchy )
and can create test tables statement:
create table table1 ( userr varchar(255) primary key, permission_on varchar(255) ); insert table1 (userr, permission_on) values ('usr1', 'fola'), ('usr2', 'folb'), ('usr3', 'folc'); create table table2 ( userr varchar(255), substitute_user varchar(255), begindate datetime, enddate datetime, ); insert table2 (userr, substitute_user, begindate, enddate) values ('usr1', 'usr1', '2010-01-01', '2010-01-01'), ('usr2', 'usr2', '2010-01-01', '2010-01-01'), ('usr1', 'usr2', '2010-01-01', '2012-01-01'), ('usr3', 'usr3', '2010-01-01', '2010-01-01'), ('usr2', 'usr3', '2011-01-01', '2013-01-01');
Comments
Post a Comment