Almost a Delimited Split kind of Join?
-
Wow I'm having a lot of trouble with this one today.
I'm working with existing DDL, so I cannot change the the currently underlying structure, but can add views/procs/ functions where they are needed.
we have a security kind of report that looks a lot like a file system, where a permission can be assigned at any level, and it cascades "down" to it's lower nodes.
So for example, if a permission is assigned as , say, readonly, for a path that looks like 'Program Files\Microsoft SQL Server\100',
then any sub folders, like 'Program Files\Microsoft SQL Server\100\COM' and
'Program Files\Microsoft SQL Server\100\DTS' have the same permissions.
implementing that is no problem, but coming up with a view that shows those permissions is my problem. because i have to join the deeper keys to try and get the hierarchy parent from above.
my issue is suppose i want to "see" what permissions were assigned to "Program Files\Microsoft SQL Server\100\DTS\Tasks\SubFolder"
I need to join and peel back on each directory above until i find an explicit permissions for the user.
so i'm trying to split, or really get a Tally kind of representation of each possible path joined to teh permissions tree,
for' Program Files\Microsoft SQL Server\100\DTS\Tasks'
I'm trying to get
something that provides data like the following:
UserName,AccessLevel, 'Program Files\Microsoft SQL Server\100\DTS\Tasks'
UserName,AccessLevel, 'Program Files\Microsoft SQL Server\100\DTS'
UserName,AccessLevel, 'Program Files\Microsoft SQL Server\100'
UserName,AccessLevel, 'Program Files\Microsoft SQL Server'
UserName,AccessLevel, 'Program Files'
Here's some sample code representative of the two tables involved; one is a master list of all possible paths,and the other is the Explicit permissions someone might have added.
Splitting() the path doesn't help me, I need to somehow shorten the join criteria on each backslash , and i just don't see it at all.
With MasterList ([Path])
AS
(
SELECT 'Program Files\Microsoft SQL Server\100' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\80' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\90' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\COM' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\KeyFile' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\License Terms' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\SDK' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Setup Bootstrap' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Shared' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Tools' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\COM\Resources\1033' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Binn' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Connections' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\LogProviders' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\MappingFiles' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Packages' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\PipelineComponents' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Tasks' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\UpgradeMappings' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Binn\Resources' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Connections\en')
,ExplicitPermissions (UserName,AccessLevel,Path)
AS
SELECT 'Lowell',0,'Program Files\Microsoft SQL Server' UNION ALL
SELECT 'Bob',1,'Program Files\Microsoft SQL Server\100\DTS' UNION ALL
SELECT 'Tom',15,'Program Files\Microsoft SQL Server\100'
)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Maybe I'm just missing something here, but can't you join on the partial path using LIKE
SELECT m.[Path],p.UserName,p.AccessLevel,p.Path,ROW_NUMBER() OVER(PARTITION BY m.[Path] ORDER BY LEN(p.[Path])) AS rn
FROM MasterList m
LEFT OUTER JOIN ExplicitPermissions p ON m.[Path] LIKE p.[Path] +'%'
ORDER BY m.[Path],rn
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, but i think that won't quote work;in the full table, i have more paths than just the "Program Files\Microsoft SQL Server" path example, many other paths might have the same length of the data, but wouldn't be related; the length only works when all the data is the same base path.
i misread your example.
my issue is the path to join via the LIKE, might not exist, so i have to check the next higher directory.
if not exists Program Files\Microsoft SQL Server\100\DTS\Tasks then
if not exists Program Files\Microsoft SQL Server\100\DTS if not exists Program Files\Microsoft SQL Server\100\ etc
That's why i think i need to split the "explicit" permissions into each directory\subdirectory combination, and join it to the master list, but i can't seem to cross join/generate that data.
i had tried some stuff like rejoining the table, like this using CHARINDEX2,but i had to join, say 9 times for 9 levels.
SELECT
m.[Path],
p.UserName,
p.AccessLevel,
p.[Path]
FROM MasterList m
LEFT OUTER JOIN ExplicitPermissions p ON m.[Path] = SUBSTRING(p.[Path],dbo.CHARINDEX2('\',p.[Path],3),255)
ORDER BY m.[Path]
CHARINDEx2 for reference
CREATE FUNCTION CHARINDEX2(
@TargetStr varchar(8000),
@SearchedStr varchar(8000),
@Occurrence int)
RETURNS int
AS
BEGIN
DECLARE @pos int, @counter int, @ret int
SET @pos = CHARINDEX(@TargetStr, @SearchedStr)
SET @counter = 1
IF @Occurrence = 1
SET @ret = @pos
ELSE
BEGIN
WHILE (@counter < @Occurrence)
BEGIN
SELECT @ret = CHARINDEX(@TargetStr, @SearchedStr, @pos + 1)
SET @counter = @counter + 1
SET @pos = @ret
END
END
RETURN(@ret)
END
[/code]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Well i was expecting to see some answer on this when i got in this morning... but alas no 🙁
Not 100% sure about the result you want. But if i have understood it correctly the code below should produce the right result.
Might not work for you but maybe give you a push in the right direction.
PS Added a few more permissions and limit the result to one user... for easier reading.
create table #MasterList (Path varchar(255))
create table #ExplicitPermissions (UserName varchar(32), AccessLevel int, Path varchar(255))
go
insert into #MasterList
select * from (
SELECT 'Program Files\Microsoft SQL Server\100' path UNION ALL
SELECT 'Program Files\Microsoft SQL Server\80' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\90' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\COM' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\KeyFile' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\License Terms' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\SDK' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Setup Bootstrap' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Shared' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\Tools' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\COM\Resources\1033' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Binn' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Connections' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\ForEachEnumerators' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\LogProviders' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\MappingFiles' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Packages' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\PipelineComponents' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Tasks' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\UpgradeMappings' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Binn\Resources' UNION ALL
SELECT 'Program Files\Microsoft SQL Server\100\DTS\Connections\en') x
insert into #ExplicitPermissions (UserName,AccessLevel,Path)
select UserName,AccessLevel,Path from
(
SELECT 'Lowell' UserName,0 AccessLevel,'Program Files\Microsoft SQL Server' path UNION ALL
SELECT 'Lowell' UserName,50 AccessLevel,'Program Files\Microsoft SQL Server\100\DTS' path UNION ALL
SELECT 'Lowell' UserName,1 AccessLevel,'Program Files\Microsoft SQL Server\90' path UNION ALL
SELECT 'Bob',1,'Program Files\Microsoft SQL Server\100\DTS' UNION ALL
SELECT 'Tom',15,'Program Files\Microsoft SQL Server\100'
) x
update #MasterList set Path = Path + '\'
update #ExplicitPermissions set Path = Path + '\'
;with cte as (
select e.UserName, m.Path, e.AccessLevel, t.N
from #MasterList m
join Tally t on t.n <= 255 and SubString(m.Path, t.n, 1) = '\'
join #ExplicitPermissions e on e.Path = SubString(m.Path, 1, t.n)
where e.username = 'Lowell')
select * from cte c
where n = (select max(c2.n) from cte c2 where c2.UserName = c.UserName and c2.Path = c.Path)
go
drop table #MasterList
drop table #ExplicitPermissions
/T
-
Hi Lowell
This seems to work - at least, the logic is correct according to my interpretation of your requirements. If you add a nonsense character to one of the user's paths, you still get the same result:
;WITH MatchedData AS (
SELECT
e.UserName,
e.AccessLevel,
e.[Path],
MatchCount = MAX(es.ItemNumber) OVER(PARTITION BY e.Username, m.[path]),
m_Path = m.[Path],
es_ItemNumber = es.ItemNumber
FROM #Masterlist m
CROSS JOIN #ExplicitPermissions e
CROSS APPLY dbo.DelimitedSplit8K_T1(m.[Path],'\') ms
CROSS APPLY dbo.DelimitedSplit8K_T1(e.[path],'\') es
WHERE es.ItemNumber = ms.ItemNumber AND es.Item = ms.Item
)
SELECT
m.Username,
m.AccessLevel,
m.[Path],
m.m_Path
FROM MatchedData m
INNER JOIN (
SELECT
UserName,
MatchCount = MAX(MatchCount)
FROM MatchedData
GROUP BY UserName
) d
ON d.UserName = m.UserName AND d.MatchCount = m.es_ItemNumber
ORDER BY m.UserName, m.[Path]
Cheers
ChrisM
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply