AnsweredAssumed Answered

How insert values into a SQL Database after collection

Question asked by Falco Dussault on Jan 3, 2020
Latest reply on Jan 6, 2020 by Andy Cheek

Like • Show 0 Likes0
Comment • 3

Hello.
I would like some help / advice on how to proceed since I have not found any information. Just help and then proceed independently.
I have IGL 7.1, HR csv as an authoritative source, AD provisioning, and an SQL database to provision. I was able to correctly collect the users that HR provides via csv and, automatically, I create identities in IGL and accounts on AD.
What I'm not able is populate the SQL database by inserting specific values into a new entry, values that I have in the csv file.

This database is used by all applications: these applications query the db and, based on the values present, allow a user to log in or not.
The database simply has 1 table with 4 fields: name, surname, unique code, authorization.
Authorization is a number that applications read to make authorization.
So I would like to create new entries on this database by inserting these 4 fields every time a new user is created by HR in the csv, in order to have a complete automation that from an HR csv file an account is created on AD and one entry on DB SQL.

I tried to create the connector to the database but what it does is create a new user in the database login security (so it creates a user to login to the database itself - I don't need this).
I would instead like to insert a new entry into the database itself, like:
name, surname, unique code, authorization.
I tried, as a test, also to create a request form, but also in this case the user is created in the login security and not the entry inside the db.
Can you give me some advice? Do you have any screenshots on the configuration?
Thank you

No one else has this question

Outcomes

Zoltan Izsak Jan 3, 2020 5:46 AM

I am not 100 percently sure about your business needs but technically this can be done during unification on db side.

In db take a look at post_data_process_handlers_pkg package body and there the Post_ID_Unifiaction_Handler. In procedure Post_ID_Unification_Handler you will find a commented line "Add Custom Code here". There you can add your custom code which inserts the needed values into your custom table.

Actions

Falco Dussault @ Zoltan Izsak on Jan 3, 2020 9:09 AM

Thanks Zoltan for your suggestion.

For me it is a bit difficult to apply it.... is there any other way?

For instance, for AD I created an account template and ableto decide what to insert into the AD, like name, surname, email etc. This through a form that a manager uses and fill and that fires that account template.

Now, I would like to have a similar situation and able to insert value in a specific sql application database (not the aveksa database) so that, for instance, after filling a form module the application database is filled by a new entry that contain fields coming from the form (name, surname, valuexxx). I don't know if I can implement for instance an account template also for a sql database

Thanks

Actions

Andy Cheek @ Falco Dussault on Jan 6, 2020 6:42 AM

I think there's a difference between provisioning, which is going to aim to create accounts for specific target systems/databases and simply populating data in a target database. Not sure AFX or provisoining rules will do that for you, so you'd have to have a separate process to connect to your database and insert the data.

What that process could be is a separate issue - Zoltan is right, there are hooks at various points in the collection/unification processes which you could possibly use. One advantage of that (as I see it) would be that you could scope it to creation of new users. Failing that you're dependent on a custom workflow (which RSA advise against using in production systems although I have long argued that these are the only way to implement various lifecycle type activities) - you'd need to be able to identify your newly created users and retrieve the relevant data to populate your database. Another option (which we have used in the past) is the trigger a change request via a User Attribute Change rule scoped to "new users" and in the workflow for that request do your data population. Trouble with that approach is you have to have something to add (i.e. some pseudo local meaningless entitlement) to generate the change request in the first place.

Caveat - we haven't done either of the first two options and we decommissioned our version of the third one several years ago … so there MAY be other options available under the more recent versions of the product that I haven't looked into (as we had no requirement).

Actions

3 Replies

Zoltan Izsak Jan 3, 2020 5:46 AM
Mark Correct
Correct Answer
I am not 100 percently sure about your business needs but technically this can be done during unification on db side.
In db take a look at post_data_process_handlers_pkg package body and there the Post_ID_Unifiaction_Handler. In procedure Post_ID_Unification_Handler you will find a commented line "Add Custom Code here". There you can add your custom code which inserts the needed values into your custom table.
Like • Show 0 Likes0
Actions
- Falco Dussault @ Zoltan Izsak on Jan 3, 2020 9:09 AM
  Mark Correct
  Correct Answer
  Thanks Zoltan for your suggestion.
  For me it is a bit difficult to apply it.... is there any other way?
  For instance, for AD I created an account template and ableto decide what to insert into the AD, like name, surname, email etc. This through a form that a manager uses and fill and that fires that account template.
  
  Now, I would like to have a similar situation and able to insert value in a specific sql application database (not the aveksa database) so that, for instance, after filling a form module the application database is filled by a new entry that contain fields coming from the form (name, surname, valuexxx). I don't know if I can implement for instance an account template also for a sql database
  
  Thanks
  Like • Show 0 Likes0
  Actions
  - Andy Cheek @ Falco Dussault on Jan 6, 2020 6:42 AM
    Mark Correct
    Correct Answer
    I think there's a difference between provisioning, which is going to aim to create accounts for specific target systems/databases and simply populating data in a target database. Not sure AFX or provisoining rules will do that for you, so you'd have to have a separate process to connect to your database and insert the data.
    What that process could be is a separate issue - Zoltan is right, there are hooks at various points in the collection/unification processes which you could possibly use. One advantage of that (as I see it) would be that you could scope it to creation of new users. Failing that you're dependent on a custom workflow (which RSA advise against using in production systems although I have long argued that these are the only way to implement various lifecycle type activities) - you'd need to be able to identify your newly created users and retrieve the relevant data to populate your database. Another option (which we have used in the past) is the trigger a change request via a User Attribute Change rule scoped to "new users" and in the workflow for that request do your data population. Trouble with that approach is you have to have something to add (i.e. some pseudo local meaningless entitlement) to generate the change request in the first place.
    Caveat - we haven't done either of the first two options and we decommissioned our version of the third one several years ago … so there MAY be other options available under the more recent versions of the product that I haven't looked into (as we had no requirement).
    Like • Show 0 Likes0
    Actions

How insert values into a SQL Database after collection

Outcomes

Related Content

Recommended Content