Friday, August 14, 2009

LDAP Sync Job unlocks all LDAP User

Clarity suggest to have separate node in LDAP directory for Clarity users. Only keep the users who intended to use clarity should be in that group only. If user should not use clarity then remove that user from Clarity node in LDAP directory so that user will be inactivated in clarity on running LDAP Sync Job. And doing this will ensure only required users are active in clarity.

There are many clients who don't want to change their LDAP structure for clarity and they use common node which all application uses. They same time want that LDAP sync job don't unlock the users who were locked before running LDAP sync JOB. Following is the solution for that. Please add following trigger to cmn_sec_users.






Code:

CREATE OR REPLACE TRIGGER "NIKU"."LDAP_LOCK_USERS"

BEFORE INSERT OR UPDATE ON CMN_SEC_USERS

FOR EACH ROW

declare

userid number;

BEGIN

//This select statement checks status of Sync new and Update job if it is processing we pick user who schedule //that job. So if job is not running we will have last_updated_by will be null and following condition will never //satisfy so trigger will effective only while Sync new and update job runs.

s
elect last_updated_by into userid from cmn_sch_jobs_v where job_definition_id = 50069 and status = 'Processing' and rownum = 1 order by start_date;

//During that job is processing we will check for insertion and updating.

//If last updated by user name matches we lock new users. In other words if currently edited user's updated by and user who is running the job are same we will lock the user.

IF INSERTING and :NEW.LAST_UPDATED_BY = userid THEN

:NEW.USER_STATUS_ID := 202;

//If last updated by user name matches while updates and if user old status is lock we lock them (as sync new //and update job activates user on sync)

ELSE IF UPDATING AND :OLD.USER_STATUS_ID = 202 and :new.last_updated_by = userid THEN

:NEW.USER_STATUS_ID := 202;

END IF;

END IF;

exception

when NO_DATA_FOUND THEN

NULL;

END;




*Note: Revisit this trigger after every SP/FP or Upgrade if this piece needs to added again.

2 comments:

Anonymous said...

Hello Naman,
i believe your issue about locked user in conjunction with ldap maybe helpfull of our replication. Nevertheless, I don't understand which trigger must be changed. In another words, where I be added this script?
best reagrds
Michael Ziegler
T-Systems Enterprise

NP said...

Hi Michael,

You should put it on

BEFORE INSERT OR UPDATE ON CMN_SEC_USERS
FOR EACH ROW

Thanks,
-Naman.