1USE [YOUR_IAM_DATABASE]
2GO
3
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER procedure [dbo].[task_import_active_directory_grp]
9(
10 @tenant_id "tenant_id" ,
11 @usr_grp_id "usr_grp_id" ,
12 @active_directory_grp_name "active_directory_grp_name" ,
13 @active_directory_domain_name "active_directory_domain_name" ,
14 @net_bios_domain_name "net_bios_domain_name" ,
15 @user_name "user_name" ,
16 @password "password"
17)
18as
19begin
20
21
22 SET NOCOUNT ON;
23
24
25
26
27
28
29 declare @query nvarchar(4000)
30 declare @path varchar(1000)
31 declare @error_msg as varchar(1000)
32
33 begin tran
34 begin try
35
36
37 delete from usr_grp_usr
38 where tenant_id = @tenant_id
39 and usr_grp_id = @usr_grp_id
40
41
42 set @query =
43 'select @ADsPath = ADsPath
44 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
45 SELECT ADsPath
46 FROM ''''LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
47 WHERE name =''''' + @active_directory_grp_name + '''''
48 '') a'
49
50 exec sp_executesql @query, N'@ADsPath varchar(1000) output', @ADsPath= @path output
51
52 if @path is null
53 begin
54 exec tsf_send_message 'active_directory_grp_not_found', null, 1
55 rollback tran
56 return
57 end
58
59 set @path = substring(@path, len('LDAP://') + 1, len(@path))
60
61
62
63 if exists( select 1
64 from usr_root_admin
65 where usr_id = dbo.tsf_user()
66 )
67 begin
68 set @error_msg = 'tenant_mismatch_for_root_admin'
69 end
70 else
71 begin
72 set @error_msg = 'tenant_mismatch_for_non_root_admin'
73 end
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94 set @query =
95 'if exists (select 1
96 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
97 SELECT
98 sAMAccountName
99 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
100 WHERE objectCategory=''''Person''''
101 AND objectCategory=''''user''''
102 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
103 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
104 '') a
105 join usr_general u
106 on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
107 where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
108 begin
109 exec tsf_send_message ''' + @error_msg + ''', null, 0
110 end
111 '
112
113 exec (@query)
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142 set @query =
143 'update u
144 set first_name = a.givenName,
145 sur_name = a.sn,
146 email = a.mail,
147 update_user = dbo.tsf_user(),
148 update_date_time = sysdatetime()
149 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
150 SELECT name
151 ,sn
152 ,sAMAccountName
153 ,givenName, mail
154 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
155 WHERE objectCategory=''''Person''''
156 AND objectCategory=''''user''''
157 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
158 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
159 '') a
160 join usr_general u
161 on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
162 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
163 where (u.first_name <> a.givenName or u.first_name is null and a.givenName is not null or u.first_name is not null and a.givenName is null
164 or u.sur_name <> a.sn or u.sur_name is null and a.sn is not null or u.sur_name is not null and a.sn is null
165 or u.email <> a.mail or u.email is null and a.mail is not null or u.email is not null and a.mail is null)
166 '
167
168 exec (@query)
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232 set @query =
233 'insert into usr
234 (
235 tenant_id,
236 usr_id,
237 first_name,
238 sur_name,
239 name,
240 gender,
241 email,
242 time_zone_id,
243 appl_lang_id,
244 authentication_type,
245 two_factor_authentication_type,
246 allow_fallback_to_email,
247 allow_change_password,
248 password_changed_count,
249 password_forgotten_count,
250 write_back_up_type_id,
251 insert_user,
252 insert_date_time,
253 update_user,
254 update_date_time
255 )
256 select
257 ' + cast(@tenant_id as varchar(10)) + ',
258 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
259 a.givenName as first_name,
260 a.sn as sur_name,
261 a.givenName as name,
262 0, -- Unknown
263 a.mail as email,
264 ''Etc/UTC'',
265 ''ENG'',
266 1, -- Windows
267 0, -- Password
268 0, -- No
269 0, -- No
270 0, -- Changed count
271 0, -- Forgotten count
272 1, -- None
273 dbo.tsf_user(),
274 sysdatetime(),
275 dbo.tsf_user(),
276 sysdatetime()
277 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
278 SELECT name
279 ,sn
280 ,sAMAccountName
281 ,givenName, mail
282 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
283 WHERE objectCategory=''''Person''''
284 AND objectCategory=''''user''''
285 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
286 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
287 '') a
288 where not exists (select 1
289 from usr u
290 -- do not check for tentant, because the usr id must be unique
291 where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
292
293 exec (@query)
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337 set @query =
338 'insert into usr_grp_usr
339 (
340 tenant_id,
341 usr_grp_id,
342 usr_id,
343 insert_user,
344 insert_date_time,
345 update_user,
346 update_date_time
347 )
348 select
349 ' + cast(@tenant_id as varchar(10)) + ',
350 ''' + @usr_grp_id + ''',
351 ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
352 dbo.tsf_user(),
353 sysdatetime(),
354 dbo.tsf_user(),
355 sysdatetime()
356 from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
357 SELECT name
358 ,sn
359 ,sAMAccountName
360 ,givenName, mail
361 FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + '''''
362 WHERE objectCategory=''''Person''''
363 AND objectCategory=''''user''''
364 AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
365 AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
366 '') a
367 where not exists (select 1
368 from usr_grp_usr u
369 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
370 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
371 and u.usr_grp_id = ''' + @usr_grp_id + ''')
372 -- Only add users from the same tenant as the user group
373 and exists (select 1
374 from usr u
375 where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
376 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'
377
378 exec (@query)
379
380
381 update usr_grp
382 set usr_grp_type = 1,
383 active_directory_domain_name = @active_directory_domain_name,
384 active_directory_grp_name = @active_directory_grp_name,
385 net_bios_domain_name = @net_bios_domain_name,
386 active_directory_updated_on = sysdatetime(),
387 update_user = dbo.tsf_user(),
388 update_date_time = sysdatetime()
389 where tenant_id = @tenant_id
390 and usr_grp_id = @usr_grp_id
391
392 end try
393 begin catch
394
395 if error_number() = 7321
396 begin
397 exec tsf_send_message 'active_directory_access_denied', null, 1;
398 end
399 else
400 throw;
401
402 if @@trancount > 0
403 rollback transaction;
404
405 end catch;
406
407 if @@trancount > 0
408 commit transaction;
409
410end