Рекурсивно запрашивать членство в группе AD через SQL

Фон

Я создаю некоторый SQL, чтобы помочь с аудитом безопасности; при этом информация о безопасности будет взята из различных системных баз данных и из Active Directory, и будет получен список всех аномалий (т. е. случаи, когда учетные записи закрыты в одной системе, но не в других.

Текущий код

Чтобы получить список пользователей, которые входят в группу безопасности, я запускаю следующий SQL:

if not exists(select 1 from sys.servers where name = 'ADSI') 
    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

SELECT sAMAccountName, displayName, givenName, sn, isDeleted --, lastLogonTimestamp --, lastLogon (Could not convert the data value due to reasons other than sign mismatch or overflow.)
FROM OPENQUERY(ADSI
, 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
FROM ''LDAP://DC=myDomain,DC=myCompany,DC=com''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' 
AND memberOf = ''CN=mySecurityGroup,OU=Security Groups,OU=UK,DC=myDomain,DC=myCompany,DC=com''
')
order by sAMAccountName

Проблема / Вопрос

Я хотел бы, чтобы этот код работал рекурсивно; т. е. если пользователь является членом группы, которая является членом указанной группы, они также должны быть включены (для полной иерархии). Кто-нибудь знает, как это сделать через SQL?

ОБНОВИТЬ

Теперь я решил несколько проблем (не связанных с указанной проблемой, но некоторые другие проблемы, которые у меня были).

  • lastLogon выдавал ошибку. Это было потому, что версия сервера была x86. Использование базы данных x64 решило проблему.
  • lastLogon был возвращен как число. Добавлен код для преобразования этого в DateTime2.
  • Мне удалось переместить имя группы из жестко запрограммированной строки, сделав сам OpenQuery динамическим, поэтому в контексте OpenQuery сгенерированная строка выглядит статичной.

..

--create linked server
if not exists(select 1 from sys.servers where name = 'ADSI')
begin
    --EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'
    EXEC master.dbo.sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation compatible',  @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'data access', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'dist', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'pub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'rpc out', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'sub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'connect timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'collation name', @optvalue=null
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'lazy schema validation',  @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'query timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'use remote collation',  @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'ADSI', @optname=N'remote proc transaction promotion', @optvalue=N'true'
end


declare @path nvarchar(1024) = 'DC=myDomain,DC=myCompany,DC=com'
declare @groupCN nvarchar(1024) = 'CN=My Security Group,OU=Security Groups,OU=UK,' + @path
, @sql nvarchar(max)

--construct the query we send to AD
set @sql = '
SELECT sAMAccountName, displayName, givenName, sn, isDeleted, lastLogon
FROM ''LDAP://' + replace(@path,'''','''''') + '''
WHERE objectCategory = ''Person''
AND objectClass = ''user'' 
AND memberOf = ''' + replace(@groupCN,'''','''''') + '''
'

--now wrap that query in the outer query
set @sql = 'SELECT sAMAccountName, displayName, givenName, sn, isDeleted
, case     
    when cast([lastLogon] as bigint) = 0 then null
    else dateadd(mi,(cast([lastlogon] as bigint) / 600000000), cast(''1601-01-01'' as datetime2)) 
  end LastLogon
FROM OPENQUERY(ADSI, ''' + replace(@sql,'''','''''') + ''')
order by sAMAccountName'

--now run it
exec(@sql)
Источник

2 ответа

Решение

Хотя это старая публикация, Google по-прежнему любит подбрасывать ее в верхнюю часть результатов, поэтому, поскольку я очень много боролся с этой же проблемой, я хотел опубликовать свои выводы / решение, отдавая должное Riverway за помощь в правильном пути.

Создайте хранимую процедуру:

CREATE PROCEDURE [dbo].[GetLdapUserGroups]
    (
    @LdapUsername NVARCHAR(max)
    )
AS
BEGIN
DECLARE @Query NVARCHAR(max), @Path NVARCHAR(max)

SET @Query = '
    SELECT @Path = distinguishedName
    FROM OPENQUERY(ADSI, ''
        SELECT distinguishedName 
        FROM ''''LDAP://DC=DOMAIN,DC=COM''''
        WHERE 
            objectClass = ''''user'''' AND
            sAMAccountName = ''''' + @LdapUsername + '''''
    '')
'

EXEC SP_EXECUTESQL @Query, N'@Path NVARCHAR(max) OUTPUT', @Path = @Path OUTPUT 

  SET @Query = '
    SELECT cn AS [LdapGroup]
    FROM OPENQUERY (ADSI, ''<LDAP://DOMAIN.COM>;
    (&(objectClass=group)(member:1.2.840.113556.1.4.1941:= ' + @Path + '));
    cn, adspath;subtree'')
    ORDER BY cn;
'

EXEC SP_EXECUTESQL @Query
END

Затем позвоните своему SP, просто передав имя пользователя:

DECLARE @UserGroup table (LdapGroup nvarchar(max))
INSERT INTO @UserGroup exec Datamart.dbo.GetLdapUserGroups @LdapUser

Затем я использую хеш-таблицу, чтобы правильно сопоставить группу AD с данными SQL и тем, что должен видеть конечный пользователь.

DECLARE @RptPermissions table (ldapGroup nvarchar(max),scholarshipCode nvarchar(50),gender nvarchar(2))
INSERT INTO @RptPermissions VALUES('EMP_Enrollment_Admissions','ALL','MF')

В моем случае я использую это для извлечения пользовательской переменной SSRS и передачи ее в запрос для выбора записей на основе членства в группе AD.

;WITH CTE_Permissions AS
(
    SELECT
        p.scholarshipCode
        ,p.gender
    FROM @UserGroup AS g
    JOIN @RptPermissions AS p ON
        g.ldapGroup = p.ldapGroup
)

... позже в запросе

JOIN CTE_Permissions AS p ON
         s.SCHOLARSHIP_ID = p.scholarshipCode
         OR p.scholarshipCode = 'ALL'

Надеюсь это поможет.

Источник

Как это?

--Get all members of a group
SELECT cn,AdsPath 
FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectCategory=person)(memberOf:1.2.840.113556.1.4.1941:=CN=Administrators,CN=Builtin,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree')
ORDER BY cn; 

--get all groups a user is a member of
SELECT cn,AdsPath
FROM OPENQUERY (ADSI, '<LDAP: dc="corp,dc=mycorp,dc=com">;(&(objectClass=group)(member:1.2.840.113556.1.4.1941:=CN=John Doe,OU=Developers,OU=Staff,DC=corp,DC=mycorp,DC=com));cn, adspath;subtree')
ORDER BY cn;

См. http://msdn.microsoft.com/en-us/library/aa746475(VS.85).aspx для рекурсивных условий поиска.

Источник
Другие вопросы по тегам