eghyK35U

1CREATE OR REPLACE PACKAGE JENKINS.ZZ_ldap
2AS
3
4    l_ldap_host VARCHAR2(256) := '172.25.189.150';
5    l_ldap_port VARCHAR2(256) := '389';
6    l_ldap_user VARCHAR2(256) := 'teh_es_uis_app';
7    l_ldap_passwd VARCHAR2(256) := 'Qwerty123';
8    l_ldap_base VARCHAR2(256) := 'OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
9
10       TYPE usr_rec_type IS RECORD (
11         user_login     VARCHAR2(50),
12         user_fullname  VARCHAR2(50),
13         user_id        VARCHAR2(50)
14     );
15
16     TYPE usr_attr_type IS RECORD (
17         id       NUMBER(20),
18         login    VARCHAR2(256),
19         attr     VARCHAR2(256),
20         val      VARCHAR2(256)
21     );
22
23    TYPE usr_tab_type IS TABLE OF usr_rec_type;
24
25    TYPE usr_tab_attr IS TABLE OF usr_attr_type;
26
27    FUNCTION get_data ( searchstring IN VARCHAR2 )RETURN usr_tab_attr PIPELINED;
28    
29    procedure sync_groups;
30    procedure sync_users;
31    procedure add_group(group_name in varchar2);
32    procedure delete_group(group_name in varchar2);
33    procedure add_user_to_group(user_name in varchar2, group_name in varchar2);
34    procedure delete_user_from_group(user_name in varchar2, group_name in varchar2);
35END;
36/
37
38/* HERE THE BODY*/
39
40CREATE OR REPLACE PACKAGE BODY JENKINS.ZZ_ldap
41AS
42
43FUNCTION get_data( searchstring IN VARCHAR2 )
44RETURN usr_tab_attr pipelined
45IS
46
47    user_row usr_attr_type;
48
49    l_filter VARCHAR2(256) := searchString;
50
51
52    l_retval PLS_INTEGER;
53    l_session DBMS_LDAP.session;
54    l_attrs DBMS_LDAP.string_collection;
55
56    v_entry_id NUMBER(12);
57    l_message DBMS_LDAP.message;
58    l_entry DBMS_LDAP.message;
59    l_attr_name VARCHAR2(256);
60    l_ber_element DBMS_LDAP.ber_element;
61    l_vals DBMS_LDAP.string_collection;
62
63
64BEGIN
65    DBMS_LDAP.USE_EXCEPTION := TRUE;
66    l_attrs(1)  := 'cn'; 
67    if l_filter like '(objectClass=group)' 
68    then 
69    l_ldap_base := 'OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
70    end if;
71    l_session := DBMS_LDAP.init(hostname => l_ldap_host,
72                                portnum  => l_ldap_port);
73
74    l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,
75                                        dn => l_ldap_user,
76                                        passwd => l_ldap_passwd
77                                        );
78
79    l_retval := DBMS_LDAP.search_s(ld       => l_session,
80                                   base     => l_ldap_base,
81                                   scope    => DBMS_LDAP.SCOPE_SUBTREE,
82                                   filter   => l_filter,
83                                   attrs    => l_attrs,
84                                   attronly => 0,
85                                   res      => l_message
86                                   );
87IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0
88THEN
89
90l_entry := DBMS_LDAP.first_entry(ld => l_session,
91                                 msg => l_message);
92
93
94<< entry_loop >>
95v_entry_id := 0;
96WHILE l_entry IS NOT NULL LOOP
97    v_entry_id := v_entry_id + 1;
98    -- Get all Attributes of the Entry
99    l_attr_name := DBMS_LDAP.first_attribute(ld => l_session,
100                                             ldapentry => l_entry,
101                                             ber_elem =>
102                                             l_ber_element);
103    << attributes_loop >>
104    WHILE l_attr_name IS NOT NULL
105       --AND l_attr_name  IN('sAMAccountName','employeeNumber','displayName')
106       LOOP
107
108        l_vals := DBMS_LDAP.get_values (ld        => l_session,
109                                        ldapentry => l_entry,
110                                        attr      => l_attr_name);
111
112
113        << values_loop >>
114        user_row.login := NULL;
115        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
116           /* IF l_attr_name = 'sAMAccountName' THEN
117                user_row.login := l_vals(i);
118            ELSE
119                user_row.login := NULL;
120            END IF;*/
121            user_row.id   := v_entry_id;
122            user_row.attr := l_attr_name;
123            user_row.val  := l_vals(i);
124            pipe ROW ( user_row );
125
126        END LOOP values_loop;
127        l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
128                                                ldapentry => l_entry,
129                                                ber_elem  => l_ber_element);
130    END LOOP attibutes_loop;
131
132    l_entry := DBMS_LDAP.next_entry(ld  => l_session,
133                                    msg => l_entry);
134
135
136
137END LOOP entry_loop;
138END IF;
139
140-- Close Connection to LDAP Server
141l_retval := DBMS_LDAP.unbind_s(ld => l_session);
142
143RETURN;
144END get_data;
145
146procedure sync_groups
147is
148begin
149insert into elk_ldap_groups (cn)
150    select val from TABLE(get_data('(objectClass=group)'))
151    where not exists (select cn from elk_ldap_groups where cn = val);
152end sync_groups;
153--
154procedure sync_users
155is
156begin 
157 for grec in (select * from elk_ldap_groups)
158    loop
159            insert into elk_ldap_users (gid, ad_login, created)
160            select grec.gid, d.val, sysdate from table(get_data('(&(objectClass=user)(memberOf=cn='||grec.cn||',OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc))')) d
161            where not exists (select gid, ad_login from elk_ldap_users where gid = grec.gid and ad_login = d.val);   
162    end loop;   
163end sync_users;
164
165procedure add_group(group_name in varchar2)
166-- Добавим новую запись в Active Directory
167IS
168    l_sess dbms_ldap.session;
169    l_dummy PLS_INTEGER;
170    l_dn VARCHAR2(200);
171    l_modarray dbms_ldap.MOD_ARRAY;
172    --l_group_name varcha2(50);
173BEGIN
174    l_sess := elk_ldap_helper.ldap_open;
175
176    -- подготовить данные для добавления сотрудника
177    l_dn := 'CN='||group_name||',OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
178    l_modarray := dbms_ldap.create_mod_array(4);
179    dbms_ldap.populate_mod_array(
180        l_modarray, 
181        dbms_ldap.MOD_ADD, 
182        'cn', 
183        elk_ldap_helper.to_strcol(group_name));
184    dbms_ldap.populate_mod_array(
185        l_modarray, 
186        dbms_ldap.MOD_ADD, 
187        'objectClass', 
188        elk_ldap_helper.to_strcol('group, top'));
189--    dbms_ldap.populate_mod_array(
190--        l_modarray, 
191--        dbms_ldap.MOD_ADD, 
192--        'description', 
193--        elk_ldap_helper.to_strcol('тестовая группа созданная из Oracle'));
194    -- добавить группу
195    l_dummy := dbms_ldap.add_s(l_sess, l_dn, l_modarray);
196
197    -- что получилось?
198--    ldap_helper.search_and_print(
199--        p_session => l_sess,
200--        p_base => l_dn,
201--        p_scope => dbms_ldap.SCOPE_BASE,
202--        p_filter => '(objectClass=*)',
203--        p_attrs => 'cn, givenName, sn, objectClass, telephoneNumber'
204--    );
205    
206    l_dummy := dbms_ldap.unbind_s(l_sess);
207    sync_groups;
208END add_group;
209
210
211procedure add_user_to_group(user_name in varchar2, group_name in varchar2)
212is
213    l_sess dbms_ldap.session;
214    l_dummy PLS_INTEGER;
215    l_dn VARCHAR2(200);
216    l_modarray dbms_ldap.MOD_ARRAY;
217    l_user_name varchar2(256);
218    l_group_vals DBMS_LDAP.STRING_COLLECTION;
219begin
220
221    l_user_name := 'CN='||user_name||',OU=Люди,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
222    l_dummy := -1;
223    l_sess := elk_ldap_helper.ldap_open;
224
225    -- подготовить данные для модификации группы
226    l_dn := 'CN='||group_name||',OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
227    l_modarray := dbms_ldap.create_mod_array(1);
228    l_group_vals(1) := l_user_name;
229    dbms_ldap.populate_mod_array(
230        l_modarray, 
231        dbms_ldap.MOD_ADD, 
232        'member', 
233        l_group_vals);
234    -- модифицировать группу
235    l_dummy := dbms_ldap.modify_s(l_sess, l_dn, l_modarray);
236
237    -- Free attribute array (group_array)
238    DBMS_LDAP.free_mod_array(l_modarray);
239    ---Закрываем соединение
240    l_dummy := dbms_ldap.unbind_s(l_sess);
241
242    --синхронизируем с таблицей elk_ldap_users
243    sync_users;
244END add_user_to_group;
245
246--====Удаление группы====--
247procedure delete_group(group_name in varchar2)
248IS
249    l_sess dbms_ldap.session;
250    l_dummy PLS_INTEGER;
251    l_dn VARCHAR2(200);
252BEGIN
253    l_sess := elk_ldap_helper.ldap_open;
254    l_dn := 'CN='||group_name||',OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
255    l_dummy := dbms_ldap.delete_s(l_sess, l_dn);
256    l_dummy := dbms_ldap.unbind_s(l_sess);
257    delete from elk_ldap_users where gid=(select gid from elk_ldap_groups where cn=group_name);
258    delete from elk_ldap_groups where cn=group_name;
259    commit;
260 --   sync_groups;
261 --   sync_users;
262END delete_group;
263
264--====Удаление ПОЛЬЗОВАТЕЛЯ из группы====--
265procedure delete_user_from_group(user_name in varchar2, group_name in varchar2)
266is
267    l_sess dbms_ldap.session;
268    l_dummy PLS_INTEGER;
269    l_dn VARCHAR2(200);
270    l_modarray dbms_ldap.MOD_ARRAY;
271    l_user_name varchar2(256);
272    l_group_vals DBMS_LDAP.STRING_COLLECTION;
273begin
274    l_user_name := 'CN='||user_name||',OU=Люди,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
275    l_dummy := -1;
276    l_sess := elk_ldap_helper.ldap_open;
277    l_dn := 'CN='||group_name||',OU=ES-UIS-APP,OU=Группы,OU=Центральный Офис,DC=msk,DC=ad2012,DC=loc';
278    l_modarray := dbms_ldap.create_mod_array(1);
279    l_group_vals(1) := l_user_name;
280    dbms_ldap.populate_mod_array(
281        l_modarray, 
282        dbms_ldap.MOD_DELETE, 
283        'member', 
284        l_group_vals);
285    l_dummy := dbms_ldap.modify_s(l_sess, l_dn, l_modarray);
286    DBMS_LDAP.free_mod_array(l_modarray);
287    l_dummy := dbms_ldap.unbind_s(l_sess);
288    delete from elk_ldap_users where gid=(select gid from elk_ldap_groups where cn=group_name) and ad_login = user_name;
289    commit;
290END delete_user_from_group;
291
292
293END;
294/