[midPoint] Get the generated email address back to database
Ivan Noris
Ivan.Noris at evolveum.com
Thu Nov 13 09:17:26 CET 2014
Hi Jason,
unfortunately I have no example for SQL Server and the synchronization configuration nor detailed knowledge of SQL Server.
But, as a last resort, I believe that DB configuration with some kind of "datetime" column updated automatically when content of the row changes should be possible (as in other databases).
Regards,
Ivan
----- Original Message -----
> From: "Jason Everling" <jeverling at bshp.edu>
> To: "midPoint General Discussion" <midpoint at lists.evolveum.com>
> Sent: Wednesday, November 12, 2014 8:59:33 PM
> Subject: Re: [midPoint] Get the generated email address back to database
> Awesome! I got it working, well i at least have the email address and
> password going back into the database with a little dirty work,
> So, the only issue I have so far is the change_timestamp column for sql
> server, how should this column be created for SQL Server?
> JASON
> On Wed, Nov 12, 2014 at 11:29 AM, Jason Everling < jeverling at bshp.edu >
> wrote:
> > What is the jdbcURLTemplate for SQL Server when using the database table
> > sync
>
> > <icscdbtable:jdbcUrlTemplate>jdbc:sqlserver://%h:%p;databaseName=%d;</icscdbtable:jdbcUrlTemplate>
>
> > Where to put username/password because sql server has to be something like
> > or
> > is the below correct?
>
> > jdbc:sqlserver://%h:%p;databaseName=%d;user=%user;password=%password
>
> > JASON
>
> > On Wed, Nov 12, 2014 at 10:41 AM, Jason Everling < jeverling at bshp.edu >
> > wrote:
>
> > > I am going about this the hard way, I was just thinking about this, I
> > > could
> > > create another database on the sql server and actually write a sql server
> > > integration services script to sync the information that I am putting in
> > > the
> > > CSV file and instead of syncing from the csv file I could sync from that
> > > database that would only have 1 table!
> >
>
> > > This should be do-able!
> >
>
> > > JASON
> >
>
> > > On Wed, Nov 12, 2014 at 10:28 AM, Jason Everling < jeverling at bshp.edu >
> > > wrote:
> >
>
> > > > Oh Ok, I don't really want to provision or update anything else in the
> > > > database, that would require a lot of joins, we have a table basically
> > > > for
> > > > each attribute, name table, address table, email table, email2 table,
> > > > address2 table, status table for more or less employeeType, department
> > > > table
> > > > and so on. Each table's unique key is soc_sec which is the student id
> > > > and
> > > > is
> > > > the employeeNumber in midpoint.
> > >
> >
>
> > > > Would a correlation still be needed if the groovy sql statement is
> > > > already
> > > > matching employeeNumber using the update statement when using the
> > > > simple
> > > > method?
> > >
> >
>
> > > > JASON
> > >
> >
>
> > > > On Wed, Nov 12, 2014 at 9:45 AM, Ivan Noris < Ivan.Noris at evolveum.com >
> > > > wrote:
> > >
> >
>
> > > > > Hi Jason,
> > > >
> > >
> >
>
> > > > > depending on direction(s) you wish to use, you may need the -sync
> > > > > version
> > > > > of
> > > > > the resource AND the sync Groovy script - if you wish to use the
> > > > > connector
> > > > > as bi-directional.
> > > >
> > >
> >
>
> > > > > If you want to do it only for provisioning (and keeping CSV for
> > > > > import/recon/livesync), you don't need the sync Groovy script.
> > > >
> > >
> >
>
> > > > > Be adwised that the -simple resource sample does NOT contain any
> > > > > correlation
> > > > > expression configuration. Which is mandatory if you wish to use it as
> > > > > proposed by me yesterday (the "dirty" part).
> > > >
> > >
> >
>
> > > > > Regards,
> > > >
> > >
> >
>
> > > > > Ivan
> > > >
> > >
> >
>
> > > > > > From: "Jason Everling" < jeverling at bshp.edu >
> > > > >
> > > >
> > >
> >
>
> > > > > > To: "midPoint General Discussion" < midpoint at lists.evolveum.com >
> > > > >
> > > >
> > >
> >
>
> > > > > > Sent: Wednesday, November 12, 2014 3:46:04 PM
> > > > >
> > > >
> > >
> >
>
> > > > > > Subject: Re: [midPoint] Get the generated email address back to
> > > > > > database
> > > > >
> > > >
> > >
> >
>
> > > > > > Yes, I cannot use the DBTable Connector because of how are database
> > > > > > is,
> > > > > > there
> > > > > > are so many tables that require joins which is why I went with the
> > > > > > CSV
> > > > > > file,
> > > > > > easier to dump then scp over to the midpoint host.
> > > > >
> > > >
> > >
> >
>
> > > > > > I am leaning more toward the ScriptedSQL after reviewing it, the
> > > > > > update
> > > > > > sample script should be fine, I just need to remove everything and
> > > > > > just
> > > > > > leave the staement with email and edit the SQL statement.
> > > > >
> > > >
> > >
> >
>
> > > > > > Would I use the Sync sample script or just the localhost simple?
> > > > > > Not
> > > > > > really
> > > > > > syncing the info, just pushing the info back.
> > > > >
> > > >
> > >
> >
>
> > > > > > JASON
> > > > >
> > > >
> > >
> >
>
> > > > > > On Tue, Nov 11, 2014 at 1:13 PM, Ivan Noris <
> > > > > > ivan.noris at evolveum.com
> > > > > > >
> > > > > > wrote:
> > > > >
> > > >
> > >
> >
>
> > > > > > > Hi Jason,
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > > Yes, the real database the CSV file is being dumped from, SQL
> > > > > > > > Server
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > > 2008 R2, I have already loaded the jdbc driver for SQL Server
> > > > > > > > in
> > > > > > > > the
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > > rebuilt war so that is already taken care of,
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Well, there are several possibilities:
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > 1.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - use the CSV export from SQL DB
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - create user with email address in midPoint
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - provision to either specific SQL DB table OR LDAP/Active
> > > > > > > Directory
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > attribute, from which your SQL DB application will fetch the
> > > > > > > attribute.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > This way you will have no direct connection to SQL DB and you'll
> > > > > > > keep
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > most of your current settings. But the SQL DB must fetch the data
> > > > > > > from
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > some intermediate table or LDAP/AD attribute.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > 2.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - use the CSV export from SQL DB
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - create user with email address in midPoint
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - provision directly to your SQL DB.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > This is somehow "dirty" as you will provision to the same
> > > > > > > resource
> > > > > > > as
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > you indirectly use as your authoritative resource. For midPoint,
> > > > > > > attempt
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > to provision email attribute to the SQL DB will look like "add"
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > operation, but connector will discover existing user record in
> > > > > > > SQL
> > > > > > > DB
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > and modify it instead of adding. This requires 100% correct
> > > > > > > correlation
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > expression to automagically convert the add to modify.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > 3.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > - use the SQL DB as both authoritative source for your users as
> > > > > > > well
> > > > > > > as
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > target resource for email address provisioning
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > This is the most clean solution, with no intermediate steps and
> > > > > > > no
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > magic. You just set the proper resource mappings (outbounds,
> > > > > > > inbounds).
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > The option "3" is recomended over the "2".
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > As for your SQL DB: connecting (as either source or target
> > > > > > > resource)
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > would require a connector. In midPoint, you have three
> > > > > > > possibilities:
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > a) DB Table connector: this is suitable for ONE table (no joins).
> > > > > > > Which,
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > I suppose is not your case...
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > b) ScriptedSQL connector: universal scriptable (Groovy)
> > > > > > > connector.
> > > > > > > This
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > can do anything, but you have to write your operations (add,
> > > > > > > modify,
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > delete, search etc.). The connector is just a glue that puts the
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > operation scripts to a form usable from midPoint.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > c) custom SQL connector (must be written in Java)
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Btw. you don't need to have JDBC drivers bundled in (custom build
> > > > > > > of)
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > midpoint. It's completely OK just drop the driver JAR files to
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > appropriate directory of your application server, in my case for
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > example, customer's "/opt/tomcat/lib" contains MySQL and MS SQL
> > > > > > > drivers
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > needed for the deployment.
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Regards,
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Ivan
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > --
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Ing. Ivan Noris
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > Senior Identity Management Engineer
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > evolveum.com
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > ___________________________________________
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > "Idem per idem - semper idem Vix."
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > _______________________________________________
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > midPoint mailing list
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > midPoint at lists.evolveum.com
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > > > >
> > > > >
> > > >
> > >
> >
>
> > > > > > CONFIDENTIALITY NOTICE:
> > > > >
> > > >
> > >
> >
>
> > > > > > This e-mail together with any attachments is proprietary and
> > > > > > confidential;
> > > > > > intended for only the recipient(s) named above and may contain
> > > > > > information
> > > > > > that is privileged. You should not retain, copy or use this e-mail
> > > > > > or
> > > > > > any
> > > > > > attachments for any purpose, or disclose all or any part of the
> > > > > > contents
> > > > > > to
> > > > > > any person. Any views or opinions expressed in this e-mail are
> > > > > > those
> > > > > > of
> > > > > > the
> > > > > > author and do not represent those of the Baptist School of Health
> > > > > > Professions. If you have received this e-mail in error, or are not
> > > > > > the
> > > > > > named
> > > > > > recipient(s), you are hereby notified that any review,
> > > > > > dissemination,
> > > > > > distribution or copying of this communication is prohibited by the
> > > > > > sender
> > > > > > and to do so might constitute a violation of the Electronic
> > > > > > Communications
> > > > > > Privacy Act, 18 U.S.C. section 2510-2521. Please immediately notify
> > > > > > the
> > > > > > sender and delete this e-mail and any attachments from your
> > > > > > computer.
> > > > >
> > > >
> > >
> >
>
> > > > > > _______________________________________________
> > > > >
> > > >
> > >
> >
>
> > > > > > midPoint mailing list
> > > > >
> > > >
> > >
> >
>
> > > > > > midPoint at lists.evolveum.com
> > > > >
> > > >
> > >
> >
>
> > > > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > > >
> > > >
> > >
> >
>
> > > > > --
> > > >
> > >
> >
>
> > > > > Ing. Ivan Noris
> > > >
> > >
> >
>
> > > > > Senior Identity Management Engineer
> > > >
> > >
> >
>
> > > > > evolveum.com
> > > >
> > >
> >
>
> > > > > ___________________________________________
> > > >
> > >
> >
>
> > > > > "Idem per idem - semper idem Vix."
> > > >
> > >
> >
>
> > > > > _______________________________________________
> > > >
> > >
> >
>
> > > > > midPoint mailing list
> > > >
> > >
> >
>
> > > > > midPoint at lists.evolveum.com
> > > >
> > >
> >
>
> > > > > http://lists.evolveum.com/mailman/listinfo/midpoint
> > > >
> > >
> >
>
> CONFIDENTIALITY NOTICE:
> This e-mail together with any attachments is proprietary and confidential;
> intended for only the recipient(s) named above and may contain information
> that is privileged. You should not retain, copy or use this e-mail or any
> attachments for any purpose, or disclose all or any part of the contents to
> any person. Any views or opinions expressed in this e-mail are those of the
> author and do not represent those of the Baptist School of Health
> Professions. If you have received this e-mail in error, or are not the named
> recipient(s), you are hereby notified that any review, dissemination,
> distribution or copying of this communication is prohibited by the sender
> and to do so might constitute a violation of the Electronic Communications
> Privacy Act, 18 U.S.C. section 2510-2521. Please immediately notify the
> sender and delete this e-mail and any attachments from your computer.
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
--
Ing. Ivan Noris
Senior Identity Management Engineer
evolveum.com
___________________________________________
"Idem per idem - semper idem Vix."
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.evolveum.com/pipermail/midpoint/attachments/20141113/12053dc5/attachment.htm>
More information about the midPoint
mailing list