-- Copyright (c) 2014, 2016 Lambert Boskamp
-- All rights reserved.
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:
-- 1. Redistributions of source code must retain the above copyright
-- notice, this list of conditions and the following disclaimer.
-- 2. Redistributions in binary form must reproduce the above
-- copyright notice, this list of conditions and the following
-- disclaimer in the documentation and/or other materials provided
-- with the distribution.
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-- COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
-- INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
-- (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
-- SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
-- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
-- STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
-- OF THE POSSIBILITY OF SUCH DAMAGE.
--
-- *******************************************************************
--
-- Synopsis: Find any string in SAP(R) Identity Management (IDM)
-- JavaScript or SQL source code, job definitions or tasks.
--
-- THIS IS THE VERSION FOR IDM 8.0 ON MICROSOFT(R) SQL SERVER.
--
-- The most recent version of this file as well as
-- versions for other databases can be found at GitHub:
--
-- https://github.com/boskamp/idmacs
--
-- Usage: 1. Paste this source code into the SQL editor of any
-- graphical SQL client that can display CLOB and/or
-- XML data. Microsoft(R) SQL Server Management Studio,
-- Oracle(R) SQL Developer and IBM(R) Data Studio are
-- known to work fine. Others may work as well.
--
-- 2. In the SQL editor, replace YOUR_SEARCH_TERM_HERE
-- near the end of the code with the string you want
-- to search for. See also section "Example".
--
-- 3. Execute the resulting query as ADMIN user (MXMC_ADMIN).
--
-- 4. (Optional) Examine MATCH_LOCATION_* and MATCH_DOCUMENT
-- values of the result set directly in the SQL client.
--
-- 5. (Optional) TODO: Describe how to locate tasks or jobs
-- corresponding to NODE_ID values from result set in
-- SAP(R) IDM Developer Studio.
--
-- Example: To search for all occurences of the string MX_DISABLED,
-- the code near the end should look like:
--
-- where contains(upper-case($t),upper-case("MX_DISABLED"))
--
-- Search is CASE-INSENSITIVE and uses SUBSTRING MATCHING,
-- so this query would find any of the following strings,
-- for instance:
--
-- MX_DISABLED
-- mx_disabled
-- #mx_disabled
--
-- Result: The result set will list any locations that contain your
-- search term. Its rows have the following structure:
--
-- 1. NODE_TYPE : char(1)
-- 2. NODE_ID : int
-- 3. NODE_NAME : varchar(max)
-- 4. MATCH_LOCATION_XML : xml (MSSQL only)
-- 5. MATCH_LOCATION_TEXT : varchar(max)
-- 6. MATCH_DOCUMENT : xml
-- 7. NODE_PATH : varchar(max)
--
-- NODE_TYPE = [ 'A' -- Attribute (Identity Store attribute)
-- | 'T' -- Task
-- | 'S' -- Script (package script)
-- | 'J' -- Job
-- ]
--
-- NODE_ID = [ attribute_id
-- | task_id
-- | script_id
-- | job_id
-- ]
--
-- NODE_NAME = [ attribute_name
-- | task_name
-- | script_name
-- | job_name
-- ]
--
-- MATCH_LOCATION_XML is similar to MATCH_LOCATION_TEXT,
-- just represented as XML to enable convenient hyperlink
-- navigation in Microsoft(R) SQL Server Management Studio.
-- This column exists in the MSSQL version only.
--
-- MATCH_LOCATION_TEXT is a piece of text contained in
-- MATCH_DOCUMENT. This piece of text contains your search
-- term at least once.
--
-- MATCH_DOCUMENT is an XML representation of the whole
-- designtime object (attribute, task, script or job)
-- identified by NODE_TYPE and NODE_ID.
--
-- For MATCH_DOCUMENTs that contain your search term multiple
-- times, the result set will generally contain multiple lines
-- which differ only in their MATCH_LOCATION_* values.
--
-- NODE_PATH is a textual representation of where this match
-- is located in SAP(R) IDM Developer Studio. It helps you to
-- manually navigate through the Developer Studio's main tree.
-- For matches in linked tasks or their corresonding provisioning
-- jobs, all paths will be displayed, one per result set line.
--
-- Credits: Martin Smith http://stackoverflow.com/users/73226/martin-smith
-- Thanks for explaining how to display large text in SSMS
--
-- Finn Ellebaek Nielsen https://ellebaek.wordpress.com
-- Thanks for explaining how to convert LONG to CLOB on the fly
--
-- *******************************************************************
with text_datasource_cte(node_id,node_type,node_name,native_xml) as (
select
a.attr_id
,'A'-- Attribute
,a.attrname
,(select
a.attr_id as "ATTR_ID"
,a.is_id as "IS_ID"
,i.idstorename as "IDSTORENAME"
,a.attrname as "ATTR_NAME"
,a.info as "INFO"
,a.deltask as "DELTASK"
,a.modtask as "MODTASK"
,a.instask as "INSTASK"
,a.display_name as "DISPLAY_NAME"
,a.tooltip as "TOOLTIP"
,a.regexvalidate as "REGEXVALIDATE"
,a.sqlvalues as "SQLVALUES"
,a.sqlaccesstask as "SQLACCESSTASK"
,a.sqlvaluestable as "SQLVALUESTABLE"
,a.sqlvaluesid as "SQLVALUESID"
,(select
v.attr_value as "ATTR_VALUE"
from mxi_attrvaluechoice v with (nolock)
where v.attr_id=a.attr_id
order by v.attr_value
for xml path('ATTRIBUTE_VALUE_CHOICE_S')
,root('ATTRIBUTE_VALUE_CHOICE_T')
,type)
for xml path('ATTRIBUTE_S')
,type)
from mxiv_allattributes a with (nolock)
inner join mxi_idstores i with (nolock)
on a.is_id=i.is_id
union all select
t.taskid
,'T'-- Task
,t.taskname
,(select
t.taskid as "TASKID"
,t.taskname as "TASKNAME"
,t.boolsql as "BOOLSQL"
,t.onsubmit as "ONSUBMIT"
,(select
ta.attr_id as "ATTR_ID"
,ta.attrname as "ATTRNAME"
,ta.sqlvalues as "SQLVALUES"
from mxiv_taskattributes ta with (nolock)
where ta.taskid=t.taskid
order by ta.attr_id
for xml path('TASK_ATTRIBUTE_S')
,root('TASK_ATTRIBUTE_T')
,type)
,(select
tx.sqlscript as "SQLSCRIPT"
,tx.targetsqlscript as "TARGETSQLSCRIPT"
,a.attrname as "ATTRNAME"
,ta.attrname as "TARGETATTRNAME"
,e.mcmskeyvalue as "MSKEYVALUE"
,te.mcmskeyvalue as "TARGETMSKEYVALUE"
from mxpv_taskaccess tx with (nolock)
left outer join mxi_attributes a with (nolock)
on tx.attr_id=a.attr_id
left outer join mxi_attributes ta with (nolock)
on tx.targetattr_id=ta.attr_id
left outer join idmv_entry_simple e with (nolock)
on tx.mskey=e.mcmskey
left outer join idmv_entry_simple te with (nolock)
on tx.targetmskey=te.mcmskey
where tx.taskid=t.taskid
for xml path('TASK_ACCESS_S')
,root('TASK_ACCESS_T')
,type)
order by t.taskid
for xml path('TASK_S')
-- Result set will always contain one task only,
-- so let task be the root element, not tasks
--,root('tasks')
,type)
from mxpv_alltaskinfo t with (nolock)
--Searching in stored procedure source code works only with MXMC_OPER.
--object_definition() will always return NULL for other users, so the
--query still works, but matches in stored procedure source code will
--not be found.
union all select
object_id(routine_name)
,'P'
,routine_name
,(select
routine_name as "PROCEDURE_NAME"
,object_definition(
object_id(
routine_name)) as "PROCEDURE_DEFINITION"
for xml path('PROCEDURE_S')
,type)
from information_schema.routines with (nolock)
)
,b64_enc_prefix_cte(node_id, node_type, node_name, b64_enc_prefix,
is_xml) as (
select
b.mcscriptid
,'S'--Package Script
,b.mcscriptname
,b.mcscriptdefinition
,0
from mc_package a with (nolock)
inner join mc_package_scripts b with (nolock)
on a.mcpackageid=b.mcpackageid
union all select
jobid
,'J'--Job
,name
,jobdefinition
,1
from mc_jobs with (nolock)
where jobdefinition is not null
)
,b64_enc_cte(node_id, node_type, node_name, b64_enc,is_xml)as (
select
node_id
,node_type
,node_name
-- Casting to varchar(max) before applying substring
-- is required, otherwise B64_ENC_PREFIX will be truncated
-- to 8000 bytes.
,substring(
cast(b64_enc_prefix as varchar(max))
,len('{B64}') + 1
,datalength(b64_enc_prefix) - len('{B64}')
)
,is_xml
from b64_enc_prefix_cte
)
,b64_dec_cte(node_id,node_type,node_name,b64_dec,is_xml) as (
select
node_id
,node_type
,node_name
,cast(N'' AS XML).value(
'xs:base64Binary(sql:column("B64_ENC"))'
,'VARBINARY(MAX)'
)
,is_xml
from b64_enc_cte
)
,xml_utf8_hex_cte(name,value) as (
select
'PROLOG'
-- This is the hexadecimal byte values
-- of the following UTF-8 encoded text:
--
,'5D5D3E3C2F524F4F543E'
)
,xml_utf8_bin_cte(name,value) as (
select
name
,cast(N'' as XML).value(
'xs:hexBinary(sql:column("VALUE"))'
, 'VARBINARY(MAX)'
)
from xml_utf8_hex_cte
)
,b64_datasource_cte(node_id,node_type,node_name,native_xml) as (
select
node_id
,node_type
,node_name
,case is_xml
--If B64_DEC is not well-formed XML by itself,
--decorate it with UTF-8 encoded XML prolog and epilog.
--Casting UTF-8 binary to VARCHAR or NVARCHAR won't work.
--See https://support.microsoft.com/en-us/kb/232580
when 0 then cast(
(select
value
from xml_utf8_bin_cte
where name='PROLOG')
+ b64_dec
+ (select
value
from xml_utf8_bin_cte
where name='EPILOG')
as xml)
--If B64_DEC already is well-formed XML,
--just cast it to T-SQL's native XML data type.
else cast(b64_dec as xml)
end
from b64_dec_cte a
)
,any_datasource_cte(node_id,node_type,node_name,native_xml) as (
select
*
from b64_datasource_cte
union all select
*
from text_datasource_cte
)
,node_cte as (
-- Identity Stores
select is_id as node_id
,NULL as parent_node_id
,NULL as parent_node_type
,idstorename as node_name
,'I' as node_type
from mxi_idstores with (nolock)
-- Top Level Package Folders for Identity Store
union all
select group_id as node_id
,idstore as parent_node_id
,'I' as parent_node_type
,group_name as node_name
,'G' as node_type
from mc_group with (nolock)
where provision_group=2 -- package folder
and parent_group is null
and mcpackageid is null
-- Packages Contained in a Folder
union all
select p.mcpackageid as node_id
,p.mcgroup as parent_node_id
,'G' as parent_node_type
,p.mcqualifiedname as node_name
,'P' as node_type
from mc_group g with (nolock)
inner join mc_package p with (nolock)
on p.mcgroup=g.group_id
-- Tasks Contained in a Folder
union all
select t.taskid as node_id
,t.taskgroup as parent_node_id
,'G' as parent_node_type
,t.taskname as node_name
,'T' as node_type
from mc_group g with (nolock)
inner join mxp_tasks t with (nolock)
on t.taskgroup=g.group_id
-- Top Level Process, Form or Job Folder of Package
union all
select group_id as node_id
,mcpackageid as parent_node_id
,'P' as parent_node_type
,group_name as node_name
,'G' as node_type
from mc_group with (nolock)
where NOT provision_group=2 -- package folder
and parent_group is null
-- Package, Process, Form or Job Folders Below Other Folders (Child Folders)
union all
select group_id as node_id
,parent_group as parent_node_id
,'G' as parent_node_type
,group_name as node_name
,'G' as node_type
from mc_group with (nolock)
where parent_group IS NOT NULL
-- Tasks Contained in a Process (Task Group)
union all
select
l.tasklnk as node_id
,l.taskref as parent_node_id
,'T' as parent_node_type
,CASE p.actiontype
WHEN -4 --Switch Task
THEN cast('[CASE '
+
l.childgroup
+
'] - '
+
c.taskname as varchar(max))
WHEN -3 --Conditional Task
THEN
CASE l.childgroup
WHEN '1'
THEN cast('[CASE TRUE] - '
+
c.taskname as varchar(max))
ELSE cast('[CASE FALSE] - '
+
c.taskname as varchar(max))
END
ELSE c.taskname
END as node_name
,'T' as node_type
from mxp_tasklnk l with (nolock)
inner join mxp_tasks p with (nolock)
on l.taskref=p.taskid
inner join mxp_tasks c with (nolock)
on l.tasklnk=c.taskid
-- Provisioning Jobs
union all
select j.jobid as node_id
,t.taskid as parent_node_id
,'T' as parent_node_type
,j.NAME as node_name
,'J' as node_type
from mc_jobs j with (nolock)
inner join mxp_tasks t with (nolock)
on j.jobguid=t.jobguid
where j.provision=1
-- Regular Jobs
union all
select j.jobid as node_id
,group_id as parent_node_id
,'G' as parent_node_type
,j.NAME as node_name
,'J' as node_type
from mc_jobs j with (nolock)
where j.provision=0
-- Package Scripts
union all
select mcscriptid as node_id
,mcpackageid as parent_node_id
,'P' as parent_node_type
,mcscriptname as node_name
,'S' as node_type
from mc_package_scripts with (nolock)
)--node_cte
,graph_cte as (
select node_id
,node_type
,node_name
,parent_node_id
,parent_node_type
,cast('/'
+
cast(node_id as varchar(max))
+
':'
+
node_name as varchar(max)
) as node_path
,0 as path_len
from node_cte
where parent_node_id is null
union all
select n.node_id
,n.node_type
,n.node_name
,n.parent_node_id
,n.parent_node_type
,cast(t.node_path
+
'/'
+
cast(n.node_id as varchar(max))
+
':'
+
n.node_name
as varchar(max)
) as node_path
,t.path_len+1 as path_len
from node_cte n
inner join graph_cte t
on t.node_id=n.parent_node_id
and t.node_type=n.parent_node_type
-- Guard against infinite recursion in case of cyclic links.
-- The below will query to a maximum depth of 99, which will
-- work fine with MSSQL's default maxrecursion limit of 100.
and t.path_len<100
)
,tree_cte as (
select g1.*
from graph_cte g1
left outer join graph_cte g2
on g1.node_type=g2.node_type
and g1.node_id=g2.node_id
-- Path length shorter than g1...
and g2.path_len g1's node_path, which will be included in the result set,
-- is the shortest (in terms of path elements) available path
-- to the given node .
)
select
a.node_type
,a.node_id
,a.node_name
,b.node_path
--Column MATCH_LOCATION_XML is specific to the MSSQL version
--of this query. It's only required to provide hyperlink
--navigation and to preserve line breaks in SSMS.
,(select
xml_sequence.value('.', 'VARCHAR(MAX)')
as [processing-instruction(X)]
for xml path('')
,type)
as match_location_xml
--Must use value() method of XML data type to avoid entitization
--of XML characters, e.g. encoding of "<" to "<".
,xml_sequence.value('.', 'VARCHAR(MAX)') as match_location_text
,xml_sequence.query('/') as match_document
from any_datasource_cte a
-- ================= BEGIN: LINKED TASKS HANDLING ===================
-- The default is to show ALL paths that lead to a linked task
-- containing a match, one per result set line.
--
-- If you prefer to see ONLY ONE result set line per match in any
-- linked task, you can join on TREE_CTE instead of GRAPH_CTE as
-- shown below. This will result in showing ONLY ONE path to any
-- matching linked task. The path shown will be the one with the
-- minimum number of path elements (the shortest, one could say).
-- COMMENT next line:
left outer join graph_cte b
-- UNCOMMENT next line:
-- left outer join tree_cte b
-- ================= END: LINKED TASKS HANDLING =====================
on a.node_type=b.node_type
and a.node_id=b.node_id
cross apply
native_xml.nodes('
(: Release 2005 of MSSQL does not have fn:upper-case() yet. :)
(: As a workaround, you can omit the upper-case() calls in the :)
(: XQuery WHERE clause, resulting in case-sensitive search. :)
for $t in (//attribute::*, /descendant-or-self::text())
where contains(upper-case($t),upper-case("YOUR_SEARCH_TERM_HERE"))
return $t
') as t(xml_sequence)
order by a.node_type,a.node_id
;