JDBC Provisioning Rule Enquiry

Identity Security Cloud (ISC) ISC Discussion and Questions
, ,
1

Hi All,

May I know if you can suggest some ways for me to write a JDBC provisioning rule that can fulfil the following requirements? Thank you so much.

Here is my use case:

  • Role is a multi-value attribute and it is an entitlement.
  • When there is a new role granted to the user, I want to add an additional row to my DB.
  • When there is a role deleted, I want to delete that particular row.

Here is how my DC should look like:
UserID, FirstName, LastName, Email, Role
1001, Emily, Lee, [email protected], HR
1002, Eric, Chan, [email protected], Finance
1003, James, Bond, [email protected], Sales
1001, Emily, Lee, [email protected], Finance (<- this is the new role granted to Emily)

This JDBC rule is working fot me but the above function is not fulfilled.
sample JDBC provision rule - work.xml (4.7 KB)

2

Hi @iris_deloitte,

When a new role is granted to a user, an additional row should be added to the database. To achieve this, your table schema must allow multiple entries for the same UserID with different roles. Create a composite primary key or unique constraint on the combination of UserID and Role . The table schema should be as follows.

CREATE TABLE dbo.POC (
    UserID VARCHAR(255),
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Email VARCHAR(255),
    Role VARCHAR(255),
    PRIMARY KEY (UserID, Role)
);

JDBC_Provisioning.java (5.1 KB)

Once the table schema is updated, you can try using the attached provisioning rule.

Regards,
Arun

1 Like
4

Hi @iris_deloitte,

Rule does not exist or rule name is wrong in your ISC environment.
Could you please check that?

Regards,
Arun

5

Hi @iris_deloitte,

Did the rule meet your requirements?

Regards,
Arun

6

Hi Arun,

Thank you so much for your reply. I have tried it but the records seem to have some issue.

image
As shown in the image, when I add a new entitlement, the first name, last name and email value are null but a row with new role can be inserted into the db.

For the case of deleting one entitlement, it turns out that that particular row can be deleted. But the role value of other rows are set to be null as shown in the image.

May I know if you have any idea in fixing it? I do think your code makes perfect sense and really appreciate it. Thank you very much.

7

Hi @iris_deloitte,

I have removed the first name, last name, email from add new entitlement plan. Now, you can add a new entitlement as per your requirement.
Regards to the delete entielement, primary key is combination of role and User ID in Table schema, . Can’t insert null into primary key field(Role).
Is there any business reason to set the Role to null?
JDBC_Provisioning.java (4.8 KB)

Regards,
Arun

1 Like
8

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.