March 27, 2014 at 3:08 am
Hi All,
I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! :-D)
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty...hence the post!
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
)
CREATE TABLE #FileSystemAccess (
FSIDINTEGER NOT NULL
,RoleNameVARCHAR(50) NOT NULL
,StartDateDATETIME NULL
,EndDateDATETIME NULL
)
CREATE TABLE #RoleMembership (
RoleNameVARCHAR(50) NOT NULL
,UsernameVARCHAR(50) NOT NULL
)
INSERT INTO #RoleMembership
(RoleName, Username)
SELECT'ITUsers','Kevin'
UNION
SELECT'SalesTeam','Paul'
SET IDENTITY_INSERT #FileSystem ON
INSERT INTO #FileSystem
(FSID,Name,ParentFSID,RecordType)
SELECT1,'IT',NULL,'F'
UNION
SELECT2,'Sales',NULL,'F'
UNION
SELECT3,'Testing',1,'F'
UNION
SELECT4,'Customers',2,'F'
UNION
SELECT5,'Accounts-2014.xls',4,'L'
UNION
SELECT6,'Accounts-2013.xls',5,'L'
SET IDENTITY_INSERT #FileSystem OFF
/*
create the access records. We only want to create them for the root folders, or for individual items
where an addition to the inherited access is required. In this case, the "ITUsers" role should be allowed to view the "IT" folder as well as the "Accounts-2014.xls" file, and subsequently the "Customers" and "Sales" folders.
*/
INSERT INTO #FileSystemAccess
(FSID,RoleName,StartDate,EndDate)
SELECT1, 'ITUsers',NULL,NULL
UNION
SELECT2, 'SalesTeam',NULL,NULL
UNION
SELECT5,'IT',NULL,NULL
/* THIS BIT IS THE ISSUE!!! */
;WITH cteFileSystem
AS (
SELECTfs.FSID, fs.ParentFSID
FROM#FileSystem AS fs
WHEREEXISTS(SELECTNULL
FROM#FileSystemAccess AS fsa INNER JOIN
#RoleMembership rm
ON(rm.RoleName= fsa.Rolename)
WHERErm.UserName= 'Kevin'
AND(fsa.FSID= fs.FSID
ORfsa.FSID= fs.ParentFSID)
)
UNION ALL
SELECTfs.FSID, fs.ParentFSID
FROM#FileSystem AS fs INNER JOIN
cteFileSystem c
ON(c.FSID= fs.ParentFSID)
)
SELECTcfs.FSID
INTO#FileSystem_TEMP
FROMcteFileSystem AS cfs INNER JOIN
#FileSystem AS fs
ON(fs.FSID= cfs.FSID)
SELECTDISTINCT fs.*
FROM#FileSystem AS fs
WHEREfs.FSIDIN(SELECTfst.FSID
FROM#FileSystem_TEMP AS fst)
ORDER BY fs.ParentFSID, fs.Name ASC
DROP TABLE #FileSystem_TEMP
DROP TABLE #FileSystem
DROP TABLE #FileSystemAccess
DROP TABLE #RoleMembership
As always, any help is absolutely appreciated.
Thanks in advance,
Kev.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club!
March 28, 2014 at 11:28 am
A bit of a tricky problem. I don't think you can achieve your result in one statement. However, you can break it up like this:
if OBJECT_ID('tempdb..#filefolders') is not null drop table #filefolders
if OBJECT_ID('tempdb..#folders') is not null drop table #folders
if OBJECT_ID('tempdb..#permissions') is not null drop table #permissions
------------------------------
--Object permissions for user.
------------------------------
select fs.*
into #permissions
from #FileSystemAccess as fsa
join #RoleMembership rm on (rm.RoleName = fsa.Rolename)
join #FileSystem fs on fs.fsid = fsa.fsid
where 1 = 1
and rm.UserName = 'Kevin'
------------------------------------------------------
--The folders containing the files you have access to.
------------------------------------------------------
;with cteFileSystem
as (
--The files you have access to.
select *
from #permissions
where 1=1
and recordtype = 'L'
union all
select fs.*
from #FileSystem as fs
join cteFileSystem c on fs.FSID = c.parentFSID
)
select *
into #filefolders
from cteFileSystem fs
where 1=1
and recordtype = 'F'
---------------------------------
--The folders you have access to.
---------------------------------
;with cteFileSystem
as (
--The folders you have direct access to.
select *
from #permissions
where 1=1
and recordtype = 'F'
union all
select fs.*
from #FileSystem as fs
join cteFileSystem c on fs.FSID = c.parentFSID
)
select *
into #folders
from cteFileSystem fs
where 1=1
-------------
--result set.
-------------
select * from #permissions where 1=1 and recordtype = 'L' union
select * from #filefolders union
select * from #folders
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply