implementing Sql Authenticator to secure Weblogic , ADF and BPM workspace in 12. 2.1

Sql Authenticator is a weblogic provider that used to enable users stored in databases to login to weblogic console , EM. in addition to use it to authenticate users in ADF stand-alone applications and BPM workspace (using workaround was valid in 11g) .

in 2008, Edwin Biemond wrote very nice article that explain how to implement SQL Authenticator , you can find here here . before few days i have applied this steps in 12.2.1 environments with minor modification . so please pay attention for the Tips below before you go to original article .

• in  12c Groups names for Sql authenticator should be typical to that already defined in default authenticators . groups such as "administrators", "AdminChannelUsers" ...etc , should be added to the table "JHS_ROLES" and keep in mind that column "Short_Name" is sized with only 10 which not enough for Default authenticator groups , for that please consider to change it to 100
• Standalone ADF application which deployed on weblogic can be authenticated using Database users without any extra efforts
• the bad news is about using those users to login BPM workspace by implementing the workaround that specified here and here . this solution was valid in 11g but it's not supported any more based on oracle response to my SR which opened regarding this matter before couples of days  ! 

the error that you will receive as below :

The configured Identity Store provider "oracle.security.jps.internal.idstore.generic.GenericIdentityStoreProvider" is not supported by IDM IdentityDirectory     

to login to BPM workspace using users stored in database you have to use OVD                                                                                                        

How database Adapter Deal with procedures that use Cursors

in this article i gonna take you in short tour to show the way that database adapter manipulate with procedures that use Cursors .

through this article i will explain small BPM example starting from Timer and call Procedure and based on outputs it will raise some human tasks .

first of all, please see the structure of BPM notation :

simply this notation start every cycle (one day in my case ) and call the procedure "GET_COOPS_NOTIFY_YST" to get the group of users that meet the condition spicified the procedure "the remaining days for their end of financial year is 30 days or less " below is the procedure that search for that condition :


create or replace PROCEDURE "GET_COOPS_NOTIFY_YST"
(
  Num_Days IN NUMBER
, prodCur OUT SYS_REFCURSOR
) AS
BEGIN
OPEN prodCur FOR SELECT * FROM USERS  where (to_date( sysdate,'DD-MM-YYYY') - to_date( Fiscal_year_date,'DD-MM-YYYY')) > Num_Days;
END;

the output of this procedure in the Service Activity appeared as 2-dimensional array : rows and columns

the table "users" assumed to be like this :

once record has been returned , simple count check will be fired to make sure that procedure has returned rows and if count >0 the next step will be start to take confirmation from manager to send the notifications to users and if he clicked "APPROVE" the notification will be sent.

to keep the loops moving on i have added 3 scripts :

1. increase Rows : responsible to increase the variable iRows by 1 and reset the value of icols to it initial value
2. increase Columns :responsible to increase the variable icols by 1
3. Set Username : responsible for set value of username to usercoop field which will be set later to FYI notification letter human task .

final check is responsible to exit from loops when irows value exceed count of rows to avoid the index out of range exception .

hope you find this article is useful for you !