NetIQ IDM - JDBC statemens using policy builder

Few examples of using JDBC statements using dirxml policies

On the Output policy:

Handling matching policies with operation-data support:

<rule> <description>[DB] Convert Query to DDL doc</description> <comment name="author" xml:space="preserve">Maqsood Ali Bhatti</comment> <comment name="version" xml:space="preserve">5</comment> <comment name="lastchanged" xml:space="preserve">Dec 20, 2017</comment> <conditions> <and> <if-operation mode="case" op="equal">query</if-operation> </and> </conditions> <actions> <do-append-xml-element expression=".." name="jdbc:statement" /> <do-append-xml-element expression="../jdbc:statement[last()]" name="jdbc:sql" /> <do-append-xml-text expression="../jdbc:statement/jdbc:sql[last()]"> <arg-string> <token-text xml:space="preserve">SELECT COUNT(*) FROM ALL_USERS WHERE USERNAME = '</token-text> <token-upper-case> <token-op-attr name="USERNAME" /> </token-upper-case> <token-text xml:space="preserve">'</token-text> </arg-string> </do-append-xml-text> <do-append-xml-element expression="../jdbc:statement[last()]" name="operation-data" /> <do-set-xml-attr expression="../jdbc:statement[last()]/operation-data[last()]" name="type"> <arg-string> <token-text xml:space="preserve">Matching</token-text> </arg-string> </do-set-xml-attr> <do-set-xml-attr expression="../jdbc:statement[last()]/operation-data[last()]" name="DN"> <arg-string> <token-op-attr name="USERNAME" /> </arg-string> </do-set-xml-attr> <do-strip-xpath expression="self::query" /> </actions> </rule>

Cathing Output of the Matching on Input Publisher

<rule> <description>Handle jdbc:result-set for matching objects</description> <comment name="author" xml:space="preserve">Maqsood Ali Bhatti</comment> <comment name="version" xml:space="preserve">2</comment> <comment name="lastchanged" xml:space="preserve">Dec 20, 20117</comment> <conditions> <and> <if-xpath op="true">self::jdbc:result-set/@jdbc:number-of-rows = 1</if-xpath> <if-xpath op="true">./operation-data[@type="Matching"]</if-xpath> </and> </conditions> <actions> <do-set-local-variable name="ASSO-VALUE" scope="policy"> <arg-string> <token-xpath expression="*//*[local-name()='value']/text()" /> </arg-string> </do-set-local-variable> <do-set-local-variable name="getDN" scope="policy"> <arg-string> <token-xpath expression="./operation-data/@DN" /> </arg-string> </do-set-local-variable> <do-trace-message> <arg-string> <token-text xml:space="preserve">add association</token-text> <token-local-variable name="getDN" /> </arg-string> </do-trace-message> <!-- association --> <do-if> <arg-conditions> <and> <if-local-variable mode="nocase" name="ASSO-VALUE" op="not-equal" /> <if-local-variable mode="nocase" name="ASSO-VALUE" op="equal">1</if-local-variable> </and> </arg-conditions> <arg-actions> <do-trace-message> <arg-string> <token-text xml:space="preserve">add association</token-text> </arg-string> </do-trace-message> <do-append-xml-element expression=".." name="instance" /> <do-set-xml-attr expression="../instance" name="class-name"> <arg-string> <token-text xml:space="preserve">User</token-text> </arg-string> </do-set-xml-attr> <do-set-xml-attr expression="../instance" name="src-dn"> <arg-string> <token-local-variable name="getDN" /> <token-text xml:space="preserve">:</token-text> <token-local-variable name="ASSO-VALUE" /> </arg-string> </do-set-xml-attr> <do-append-xml-element expression="../instance" name="association" /> <do-append-xml-text expression="../instance/association"> <arg-string> <token-local-variable name="getDN" /> <token-text xml:space="preserve">:</token-text> <token-local-variable name="ASSO-VALUE" /> </arg-string> </do-append-xml-text> </arg-actions> <arg-actions /> </do-if> <do-if> <arg-conditions> <and> <if-local-variable mode="nocase" name="ASSO-VALUE" op="not-equal" /> <if-local-variable mode="nocase" name="ASSO-VALUE" op="equal">0</if-local-variable> </and> </arg-conditions> <arg-actions> <do-trace-message> <arg-string> <token-text xml:space="preserve">NO MATCH</token-text> </arg-string> </do-trace-message> <do-break /> </arg-actions> <arg-actions /> </do-if> </actions> </rule>

Handle Add policies (Stored procedure with parameters)

<rule> <description>[DB] Convert Add to DDL doc</description> <comment name="author" xml:space="preserve">Maqsood Ali Bhatti</comment> <comment name="version" xml:space="preserve">5</comment> <comment name="lastchanged" xml:space="preserve">Dec 20, 2017</comment> <conditions> <and> <if-operation mode="case" op="equal">add</if-operation> </and> </conditions>
<actions> <do-set-local-variable name="local.otp.UserName" scope="policy"> <arg-string> <token-op-property name="prop.idm.UserName" /> </arg-string> </do-set-local-variable> <do-set-local-variable name="local.otp.UserPassword" scope="policy"> <arg-string> <token-op-property name="prop.idm.UserPassword" /> </arg-string> </do-set-local-variable> <do-append-xml-element expression=".." name="jdbc:statement" /> <do-append-xml-element expression="../jdbc:statement[last()]" name="jdbc:call-procedure" /> <do-set-xml-attr expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]" name="jdbc:name"> <arg-string> <token-text xml:space="preserve">IDM.CREATEUSER</token-text> </arg-string> </do-set-xml-attr> <do-append-xml-element expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]" name="jdbc:param" /> <do-append-xml-element expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]/jdbc:param[last()]" name="jdbc:value" /> <do-append-xml-text expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]/jdbc:param[last()]/jdbc:value[last()]"> <arg-string> <token-local-variable name="local.otp.UserName" /> </arg-string> </do-append-xml-text> <do-append-xml-element expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]" name="jdbc:param" /> <do-append-xml-element expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]/jdbc:param[last()]" name="jdbc:value" /> <do-append-xml-text expression="../jdbc:statement[last()]/jdbc:call-procedure[last()]/jdbc:param[last()]/jdbc:value[last()]"> <arg-string> <token-base64-decode charset="UTF-8"> <token-local-variable name="local.otp.UserPassword" /> </token-base64-decode> </arg-string> </do-append-xml-text> <do-strip-xpath expression="self::add" /> </actions> </rule>