Almost a Delimited Split kind of Join?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 23, 2012 at 6:07 am

  #144877

  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!

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  January 23, 2012 at 6:33 am

  #1437048

  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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  January 23, 2012 at 6:58 am

  #1437064

  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!

• tommyh

  SSCertifiable

  Points: 6302

  More actions

  January 23, 2012 at 11:57 pm

  #1437485

  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

• ChrisM@home

  SSC-Insane

  Points: 24260

  More actions

  January 24, 2012 at 2:30 am

  #1437518

  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]