[midPoint] ScriptedSQL - add/remove entitlements
Wojciech Staszewski
wojciech.staszewski at diagnostyka.pl
Thu Dec 15 15:15:29 CET 2016
OK.
It is a very simple system, all stuff is in 3 database tables:
- User table: "users",
- Group table: "usrgrp",
- A table that binds users with groups: "users_groups". It has 3
columns: id, userid and usrgrpid.
User has attributes: __UID__ (db:userid),__NAME__ (db:alias), firstname
(db:name), surname(db:surname), __ENABLE__.
Group has attributes: __UID__ (db:usrgrpid), __NAME__ (db:name).
The users and usrgrp tables have some other columns, I don't need to touch them.
If I understand, adding an entitlement (a group membeship) is made by
"groups" attribute of __ACCOUNT__ and "members" attribute of __GROUP__.
These are multivalued attributes as the user can be a member of
multiple groups, and the group can have multiple members.
###################################
CREATE script:
###################################
import groovy.sql.Sql;
import groovy.sql.DataSet;
import org.identityconnectors.common.security.GuardedString;
import org.identityconnectors.common.security.SecurityUtil;
import java.security.MessageDigest
log.info("Entering "+action+" Script");
def sql = new Sql(connection);
String newUid; //Create must return UID.
switch ( objectClass ) {
case "__ACCOUNT__":
// MAIL is in separate table "media", column "mediaid" is required, but is not autoincrement, so we have to calculate next value.
// USER is in table "users", column "userid" is required, but is not autoincrement, so we have to calculate next value.
def nextuserid = sql.firstRow("select ifnull((select userid+1 from users order by userid desc limit 1),1) as userid").userid;
def nextmediaid = sql.firstRow("select ifnull((select mediaid+1 from media order by mediaid desc limit 1),1) as mediaid").mediaid;
// Password MD5 hashing
def digest = MessageDigest.getInstance("MD5")
def clearpass = SecurityUtil.decrypt(attributes?.get("__PASSWORD__")?.get(0)).toString()
def md5pass = new BigInteger(1,digest.digest(clearpass.getBytes())).toString(16).padLeft(32,"0")
def keys = sql.executeInsert("INSERT INTO users (userid, alias, name,surname,passwd,url,autologin,autologout,lang,refresh,type,theme,rows_per_page) values (?,?,?,?,?,?,?,?,?,?,?,?,?)",
[
nextuserid,
id,
attributes?.get("firstname")?.get(0),
attributes?.get("surname")?.get(0),
// MD5 passwd
md5pass,
"zabbix.php?action=dashboard.view",
1,
0,
"pl_PL",
120,
2,
"default",
100
])
//newUid = keys[0][0];
newUid = nextuserid;
def keys1 = sql.executeInsert("INSERT INTO media (mediaid, userid, mediatypeid,sendto,active,severity,period) values (?,?,?,?,?,?,?)",
[
nextmediaid,
nextuserid,
1,
attributes?.get("email")?.get(0),
// negate __ENABLE__ attribute
!(attributes?.get("__ENABLE__")?.get(0) as Boolean),
48,
"1-7,00:00-24:00"
])
break
case "Group":
// Groups are in "usrgrp" table, column "usrgrpid" is required, but is not autoincrement, so we have to calculate next value.
def nextgrpid = sql.firstRow("select ifnull((select usrgrpid+1 from usrgrp order by usrgrpid desc limit 1),1) as userid").usrgrpid;
def keys = sql.executeInsert("INSERT INTO usrgrp (usrgrpid,name,gui_access,user_status,debug_mode) values (?,?,?,?,?)",
[
nextgrpid,
id,
0,
0,
0
])
//newUid = keys[0][0];
newUid = nextgrpid;
break
// Don't care about organizations at the moment...
case "Organization":
def keys = sql.executeInsert("INSERT INTO Organizations (name,description) values (?,?)",
[
id,
attributes?.get("description")?.get(0)
])
newUid = keys[0][0];
break
}
return newUid;
###################################
UPDATE script is totaly incomplete
###################################
import groovy.sql.Sql;
import groovy.sql.DataSet;
import org.identityconnectors.common.security.GuardedString;
import org.identityconnectors.common.security.SecurityUtil;
import java.security.MessageDigest
log.info("Entering "+action+" Script");
def sql = new Sql(connection);
def doCommit = false;
def preparedStatementPrefixAccounts = "UPDATE users SET ";
def preparedStatementPrefixGroups = "UPDATE usrgrp SET ";
def preparedStatementPrefixOrganizations = "UPDATE Organizations SET ";
def preparedStatementAttributes = "";
def preparedStatementAttributesList = [];
def preparedStatementColumns = [];
def accountAttrNames = ["__NAME__", "firstname", "surname", "email", "__PASSWORD__", "__ENABLE__", "groups" ];
def groupAttrNames = ["__NAME__", "members" ];
def orgAttrNames = ["__NAME__", "description" ];
switch ( action ) {
case "UPDATE":
switch ( objectClass ) {
case "__ACCOUNT__":
for (attr in accountAttrNames) {
if (attributes.get(attr) != null) {
//log.info("XXXX Processing attribute: " + attr + "=" + attributes.get(attr));
switch (attr) {
case "__NAME__":
// __NAME__ corresponds to "alias" column
preparedStatementAttributesList.add("alias" + " = ?");
preparedStatementColumns.add(attributes.get(attr)?.find { true });
break;
case "__ENABLE__":
// Disabling users by adding him to group having "1" in column "user_status".
// By default this group is called "Disabled" but it may have any name.
def disabledgroup = "Disabled";
// We have to find usrgrpid "Disabled" group.
def disabledgroupid = sql.firstRow("select usrgrpid from usrgrp where name = ?",[disabledgroup]).usrgrpid as Integer;
// isdisabled: 0 - enabled, 1 - disabled
def isdisabled = !(attributes.get(attr)?.find { true }) as Boolean;
if (isdisabled == true){
// generate new id, as id column is not autoincrement
def nextid = sql.firstRow("select ifnull((select id+1 from users_groups order by id desc limit 1),1) as id").id as Integer;
// Add user to Disabled group
sql.execute("INSERT INTO users_groups (id,usrgrpid,userid) VALUES (?,?,?)",[nextid,disabledgroupid,uid])
} else {
// Remove user from disabled group
sql.execute("DELETE FROM users_groups WHERE usrgrpid = ? and userid = ?",[disabledgroupid,uid])
}
break;
case "__PASSWORD__":
// Read plain text password, generate MD5 hash
def digest = MessageDigest.getInstance("MD5")
def clearpass = SecurityUtil.decrypt(attributes?.get("__PASSWORD__")?.get(0)).toString()
def md5pass = new BigInteger(1,digest.digest(clearpass.getBytes())).toString(16).padLeft(32,"0")
// __PASSWORD__ corresponds to (MD5) "passwd" column
preparedStatementAttributesList.add("passwd" + " = ?");
preparedStatementColumns.add(md5pass);
break;
default:
// all other attributes
preparedStatementAttributesList.add(attr + " = ?");
preparedStatementColumns.add(attributes.get(attr)?.find { true });
}
}
}
preparedStatementAttributes = preparedStatementAttributesList.join(',');
if (preparedStatementAttributes != "") {
preparedStatementColumns.add(uid as Integer);
// log.info("XXXXXXXXXXXX" + preparedStatementPrefixAccounts + preparedStatementAttributes + " WHERE id = ?", preparedStatementColumns);
sql.executeUpdate(preparedStatementPrefixAccounts + preparedStatementAttributes + " WHERE userid = ?", preparedStatementColumns);
doCommit = true;
}
//if (doCommit) {
// sql.commit();
//}
break
case "Group":
for (attr in groupAttrNames) {
if (attributes.get(attr) != null) {
preparedStatementAttributesList.add((attr == '__NAME__' ? "name" : attr) + " = ?");
preparedStatementColumns.add(attributes.get(attr)?.find { true });
}
}
preparedStatementAttributes = preparedStatementAttributesList.join(',');
if (preparedStatementAttributes != "") {
preparedStatementColumns.add(uid as Integer);
sql.executeUpdate(preparedStatementPrefixGroups + preparedStatementAttributes + " WHERE usrgrpid = ?", preparedStatementColumns);
doCommit = true;
}
//sql.commit();
break
case "Organization":
for (attr in orgAttrNames) {
if (attributes.get(attr) != null) {
preparedStatementAttributesList.add((attr == '__NAME__' ? "name" : attr) + " = ?");
preparedStatementColumns.add(attributes.get(attr)?.find { true });
}
}
preparedStatementAttributes = preparedStatementAttributesList.join(',');
if (preparedStatementAttributes != "") {
preparedStatementColumns.add(uid as Integer);
sql.executeUpdate(preparedStatementPrefixOrganizations + preparedStatementAttributes + " WHERE id = ?", preparedStatementColumns);
doCommit = true;
}
//sql.commit();
break
default:
uid;
}
break
case "ADD_ATTRIBUTE_VALUES":
if(objectClass == "__ACCOUNT__")
{
for(String group : attributes.get("groups"))
{
def existingEntitlement = sql.rows("SELECT 1 FROM users_groups WHERE userid=? AND usrgrpid=?",[uid as String, group as String]);
if(existingEntitlement.isEmpty())
{
log.info("Sample - Adding entitlement ${group} to user ${uid}");
def nextid = sql.firstRow("select ifnull((select id+1 from users_groups order by id desc limit 1),1) as id").id as Integer;
sql.execute("insert into users_groups (id, userid, usrgrpid) values (" + nextid + "," + uid + "," + group + ")");
}
else
{
log.info("Sample - Skipping assignment because user ${uid} already has group ${group}");
}
}
}
case "REMOVE_ATTRIBUTE_VALUES":
//todo
break
default:
uid
}
W dniu 15.12.2016 o 13:43, Nicolas Rossi pisze:
> Can you share with us the create and update scripts ?
>
> Regards
>
>
>
> Ing Nicolás Rossi
> Identicum S.A.
> Jorge Newbery 3226
> Tel: +54 (11) 4552-3050
> www.identicum.com <http://www.identicum.com>
>
> On Thu, Dec 15, 2016 at 5:15 AM, Wojciech Staszewski
> <wojciech.staszewski at diagnostyka.pl
> <mailto:wojciech.staszewski at diagnostyka.pl>> wrote:
>
> I've done it with joins instead "where .. and .. and", and it works, but
> now I my account appears multiple times on the resource (the number of
> accounts = the number of groups). This is definitely not a simple thing
> and the documentation is weak. :(
>
> W dniu 15.12.2016 o 02:34, Nicolas Rossi pisze:
> > The Search script should return the ID, Name and Members of the group.
> > It doesn't matter if you use 1, 2 or more queries but you should return
> > an array with one row for each group where the members attribute is an
> > array too.
> >
> > Can you copy the sql error of the query with the where filter?
> >
> > Regards,
> >
> >
> > Nicolás
> >
> >
> > El El mié, 14 de dic. de 2016 a las 09:12, Wojciech Staszewski
> > <wojciech.staszewski at diagnostyka.pl
> <mailto:wojciech.staszewski at diagnostyka.pl>
> > <mailto:wojciech.staszewski at diagnostyka.pl
> <mailto:wojciech.staszewski at diagnostyka.pl>>> escribió:
> >
> > Just 4 more questions.
> >
> >
> >
> > I have a little trouble with search script.
> >
> >
> >
> > 1. Searching associated groups: can it be in a separate sql query in
> >
> > __ACCOUNT__ case?
> >
> > 2. It must return: "__UID__", "__NAME__" and "groups" attributes, right?
> >
> > (the list of groups)
> >
> > 3. Or it must be one single guery returning all attributes including
> >
> > group membership? But then it will return more than one row...
> >
> >
> >
> > 4. How to construct the SQL query using "where" template?
> >
> > I tried to put something like this:
> >
> >
> >
> > "select g.name <http://g.name> <http://g.name> as name,
> u.alias from users_groups
> > ug, usrgrp g, users u"
> >
> > + where + " AND g.usrgrpid = ug.usrgrpid and u.userid = ug.userid"
> >
> >
> >
> > (msg:Search WHERE clause is: WHERE u.userid = 1)
> >
> >
> >
> > But i got SQL syntax error. I log this query, Ctrl+C from log,
> Ctrl+V in
> >
> > SQL console and it works.
> >
> >
> >
> > Thanks,
> >
> > Best regards, WS
> >
> >
> >
> > W dniu 13.12.2016 o 18:30, Wojciech Staszewski pisze:
> >
> > > Thank you very much!
> >
> > > Regards, WS
> >
> > >
> >
> > > Dnia poniedziałek, 12 grudnia 2016 21:45:00 CET Nicolas
> Rossi pisze:
> >
> > >> Hi, you have to add the association between Users and
> Groups. It's
> >
> > >> something like that:
> >
> > >>
> >
> > >> <association>
> >
> > >> <ref>ri:GroupObjectClass</ref>
> >
> > >> <kind>entitlement</kind>
> >
> > >> <intent>default</intent>
> >
> > >> <tolerant>false</tolerant>
> >
> > >> <direction>subjectToObject</direction>
> >
> > >> <associationAttribute>ri:groups</associationAttribute>
> >
> > >> <valueAttribute>icfs:uid</valueAttribute>
> >
> > >>
> >
> <shortcutAssociationAttribute>ri:members</shortcutAssociationAttribute>
> >
> > >> <shortcutValueAttribute>icfs:uid</shortcutValueAttribute>
> >
> > >> </association>
> >
> > >>
> >
> > >> You can find more information about the association and the
> tolerant
> >
> > >> parameter here:
> >
> > >>
> >
> https://wiki.evolveum.com/display/midPoint/Entitlements#Entitlements-AssociationDefinition
> <https://wiki.evolveum.com/display/midPoint/Entitlements#Entitlements-AssociationDefinition>
> >
> > >>
> >
> > >> Inside your Update script the operation should be
> > ADD_ATTRIBUTE_VALUE for
> >
> > >> objectClass __ACCOUNT__ and the attribute received should be
> > "groups":
> >
> > >>
> >
> > >> case "ADD_ATTRIBUTE_VALUES":
> >
> > >>
> >
> > >> if(objectClass == "__ACCOUNT__")
> >
> > >> {
> >
> > >> for(String group : attributes.get("groups"))
> >
> > >> {
> >
> > >> def existingEntitlement = sql.rows("SELECT
> 1 FROM
> >
> > >> UserGroups WHERE user_id=? AND group_id=?",[uid as String,
> group as
> >
> > >> String]);
> >
> > >> if(existingEntitlement.isEmpty())
> >
> > >> {
> >
> > >> log.info <http://log.info>
> <http://log.info>("Sample - Adding
> > entitlement ${group} to user
> >
> > >> ${uid}");
> >
> > >> sql.execute("insert into UserGroups (user_id,
> > group_id)
> >
> > >> values (" + uid + "," + group + ")");
> >
> > >> }
> >
> > >> else
> >
> > >> {
> >
> > >> log.info <http://log.info>
> <http://log.info>("Sample - Skipping
> > assignment because user
> >
> > >> ${uid} already has group ${group}");
> >
> > >> }
> >
> > >> }
> >
> > >> }
> >
> > >>
> >
> > >> You should also handle the REMOVE_ATTRIBUTE_VALUES with the same
> > logic.
> >
> > >> Radovan and Ivan have helped us few weeks ago with the ScriptedSQL
> >
> > >> resource. You can find the conversation in the mailing list. I am
> > sure it
> >
> > >> will help you too.
> >
> > >>
> >
> > >> Regards,
> >
> > >>
> >
> > >>
> >
> > >>
> >
> > >>
> >
> > >>
> >
> > >> Ing Nicolás Rossi
> >
> > >> Identicum S.A.
> >
> > >> Jorge Newbery 3226
> >
> > >> Tel: +54 (11) 4552-3050
> >
> > >> www.identicum.com <http://www.identicum.com>
> <http://www.identicum.com>
> >
> > >>
> >
> > >> On Mon, Dec 12, 2016 at 7:11 PM, Wojciech Staszewski <
> >
> > >> wojciech.staszewski at diagnostyka.pl
> <mailto:wojciech.staszewski at diagnostyka.pl>
> > <mailto:wojciech.staszewski at diagnostyka.pl
> <mailto:wojciech.staszewski at diagnostyka.pl>>> wrote:
> >
> > >>
> >
> > >>> Hello,
> >
> > >>>
> >
> > >>> I'm playing with ScriptedSQL resource, based on Evolveum example
> > from
> >
> > >>> Github.
> >
> > >>> I'm able to list/add/remove users/groups and enable/disable
> > accounts.
> >
> > >>> Great.
> >
> > >>> But now I want to apply an assignment (a group) to user.
> > Unfortunately
> >
> > >>> "Update_Script.groovy" is incomplete,
> >
> > >>> ADD_ATTRIBUTE_VALUES and REMOVE_ATTRIBUTE_VALUES cases are empty.
> >
> > >>> Where can I find some examples?
> >
> > >>>
> >
> > >>> Thanks a lot!
> >
> > >>> WS
> >
> > >>> _______________________________________________
> >
> > >>> midPoint mailing list
> >
> > >>> midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>
> <mailto:midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>>
> >
> > >>> http://lists.evolveum.com/mailman/listinfo/midpoint
> <http://lists.evolveum.com/mailman/listinfo/midpoint>
> >
> > >>>
> >
> > >>
> >
> > >
> >
> > >
> >
> > _______________________________________________
> >
> > midPoint mailing list
> >
> > midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>
> <mailto:midPoint at lists.evolveum.com
> <mailto:midPoint at lists.evolveum.com>>
> >
> > http://lists.evolveum.com/mailman/listinfo/midpoint
> <http://lists.evolveum.com/mailman/listinfo/midpoint>
> >
> >
> >
> > _______________________________________________
> > midPoint mailing list
> > midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
> > http://lists.evolveum.com/mailman/listinfo/midpoint
> <http://lists.evolveum.com/mailman/listinfo/midpoint>
> >
>
> --
> Wojciech Staszewski
> Administrator Systemów Sieciowych
> tel. kom: 663 680 236
> www.diagnostyka.pl <http://www.diagnostyka.pl>
> Diagnostyka Sp. z o. o.
> ul. Prof. M. Życzkowskiego 16, 31-864 Kraków
> Numer KRS: 0000381559 (Sąd Rejonowy dla Krakowa-Śródmieścia w Krakowie,
> XI Wydział Gospodarczy KRS)
> NIP: 675-12-65-009; REGON: 356366975
> Kapitał zakładowy: 33 756 500 zł.
>
> Pomyśl o środowisku zanim wydrukujesz ten e-mail.
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com <mailto:midPoint at lists.evolveum.com>
> http://lists.evolveum.com/mailman/listinfo/midpoint
> <http://lists.evolveum.com/mailman/listinfo/midpoint>
>
>
>
>
> _______________________________________________
> midPoint mailing list
> midPoint at lists.evolveum.com
> http://lists.evolveum.com/mailman/listinfo/midpoint
>
--
Wojciech Staszewski
Administrator Systemów Sieciowych
tel. kom: 663 680 236
www.diagnostyka.pl
Diagnostyka Sp. z o. o.
ul. Prof. M. Życzkowskiego 16, 31-864 Kraków
Numer KRS: 0000381559 (Sąd Rejonowy dla Krakowa-Śródmieścia w Krakowie,
XI Wydział Gospodarczy KRS)
NIP: 675-12-65-009; REGON: 356366975
Kapitał zakładowy: 33 756 500 zł.
Pomyśl o środowisku zanim wydrukujesz ten e-mail.
More information about the midPoint
mailing list