JDBC application : Whether to go for Stored procedure or Normal SQL queries? user is allowed to have multiple roles(no maximum limit)

IdentityIQ (IIQ) IIQ Discussion and Questions
1

We have a JDBC application where user is allowed to have multiple roles at a time(no maximum limit). Anyone could you please advise that whether we have to go for stored procedure or can we do it with normal insert(SQL) queries?

We have below concerns.

  1. Since user is allowed to have multiple roles(no maximum limit).
  2. If user raise a request to add roles and remove existing roles or vice versa. It will be an issue to manage in SAM.
  3. Since user is allowed to have multiple roles, managing user disable operation is complex through SAM.

Note : If it can be done through normal queries, could you please provide the reference code if available.

2

Hey @Venu1010 ,

Welcome to the dev community and congrats on your first post. I would recommend to still go with Stored Procs that is the suggested process.
And code reference would change from different integration but you can use the extensibility feature leveraging both JDBC Buildmap and Provisioning Rules.
Example code is always present in the examplerules.xml file.

image
image1536×151 3.5 KB

For a more detailed connector documentation you can refer it here.

Thanks,
Aman

1 Like
3

Hi @Venu1010

Regarding, your question you can use a JDBCProvision rule to prepare and execute your statements to provision accounts or groups in your database.

If you manage groups in your database as multi value field, you can use the following sentence in your rule:

PreparedStatement statement = connection.prepareStatement("update users set capability = ? where id = ?");

statement.setString(2, (String) account.getNativeIdentity());
statement.setString(1,Util.listToCsv(roles)); 
statement.executeUpdate();

result.setStatus(ProvisioningResult.STATUS_COMMITTED);

Please, check attached code as reference

JDBCProvision_rule.xml (13.6 KB)

4

Thank you Ismael for your reply. Really appreciate it. But
DB table
roles are saving in different rows in the database.

Please find the attached screenshot for your reference.

5

hI @Venu1010

In this case following code may be most appropiated

ProvisioningResult result = new ProvisioningResult();

for ( AccountRequest account : plan.getAccountRequests()) {
      for (AttributeRequest req : account.getAttributeRequests()) {
           if (req.getName().equals("roles")) {
                    PreparedStatement statement = connection.prepareStatement("INSERT INTO  userrolemap (USERID, ROLESEQ) VALUES (?, ?)");

                    statement.setString(1, (String) account.getNativeIdentity());
                    statement.setString(2, req.getValue()); 
                    statement.executeUpdate();
          }
    }
}
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
6

Thank you Ismael. Do u have reference code for it. If yes, Could you please share it.

7

Hi @Venu1010

Yes, you can check the following attached code

JDBDProvision_rule_v2.xml (10.6 KB)

Let me know if you have any additional doubt regarding this rule

8

Thank you for your prompt help. Really appreciate it :slight_smile:

1 Like