27. One Identity Oracle
21 февраля 2020 г.
15:32
Connect Oracle Database
Ключевые поля таблиц
Employee – TAB_NUM
Accounts – LOGIN
Organizations – ID
Phone_Types – CDL_COD_OID
Roles – ID
Status – LOGIN
USER_ROLE – LOGIN
To configure synchronization with the native database connector
-
Create a new synchronization project.
-
Add mappings. Define property mapping rules and object matching rules.
-
Create synchronization workflows.
-
Create a start up configuration.
-
Define the synchronization scope.
-
Specify the base object of the synchronization.
-
Specify the extent of the synchronization log.
-
Run a consistency check.
-
Activate the synchronization project.
-
Save the new synchronization project in the database.
------
!!! Перед тем, как подцепить Базу данных, надо создать Target System Type (Manager - Custom Target System).
После - надо создать Target System и указать в поле Target System Type только что созданный (Надо перезапустить Manager перед этим, чтобы он подтянул из БД все корректно).
И у нее указать Synchronized by -> Scripted synchronization
Потом можно соединять БД.
После - надо делать Mapping, Workflow, Startup Configuration.
При маппинге, ему надо указать UID_UNSRootB
Заходим в Configuration (Sync Editor) - Variables создать
IdentUNSRoot (Name и DisplayName), Value = (имя созданнной Target system в Manager)
Затем, в mapping
Справа создать свойство vrtIdentUNSRoot как fixed value – string - значение \$IdentUNSRoot\$
Смапить его с левой VRT_UID_UNSRootB
Зайти в One Identity Manager connection – Edit Scope - UNSRootB
Ident_UNSRoot='\$IdentUNSRoot\$'
USER
GROUP
USER_ROLE
LOGIN = AccountName
Создание записи в Oracle
Есть в Oracle DB процедуры по созданию пользователя (USSC_CREATE_USER)
Запуск процедуры из VB скрипта
https://stackoverflow.com/questions/44262036/vbscript-for-executing-oracle-procedure
#If Not SCRIPTDEBUGGER Then
References Devart.Data.Oracle.dll
Imports Devart.Data.Oracle
#End If
Public Function GetOracleConnectionString() As String
Dim cs As New Devart.Data.Oracle.OracleConnectionStringBuilder()
cs.ConnectMode = Devart.Data.Oracle.OracleConnectMode.Default
cs.Sid = ""
cs.ServiceName ="OIM_SD"
cs.UserId = "OIM_SD"
cs.Password = "Password123"
cs.Server = "[REDACTED_IP]"
cs.Direct = True
Return cs.ConnectionString
End Function
Public Sub OracleExecute(ByVal strSQL As String)
Dim dbFac As IDbFactory = DbApp.GetFactoryByName("VI.DB.Oracle.ViOracleFactory,VI.DB.Oracle")
Using sf = DbApp.ConnectTo(GetOracleConnectionString()).Using(dbFac).BuildSessionFactory()
Using dbSession = sf.OpenDbSession()
dbSession.SqlExecuteNonQuery(strSQL)
End Using
End Using
End Sub
Рабочий запуск обновления строки
Public Sub OracleExecute()
Dim dbFac As IDbFactory = DbApp.GetFactoryByName("VI.DB.Oracle.ViOracleFactory,VI.DB.Oracle")
Dim strSQL As String
Dim tabnum As String
'strSQL = "ussc_enable_employee(10024)"
strSQL = "UPDATE ADMIN.ITSM_PERSONS SET PER_JOBTitle = 'Старший инспектор канцелярии' WHERE PER_OID = 281478295650441"
Using sf = DbApp.ConnectTo(GetOracleConnectionString()).Using(dbFac).BuildSessionFactory()
Using dbSession = sf.OpenDbSession()
'dbSession.SqlExecuteStoredProcedure(strSQL)
dbSession.SqlExecuteNonQuery(strSQL)
End Using
End Using
End Sub
-----
Запуск процедуры
EXEC ussc_enable_user('AndriyanovGY');
~~При создании UNSRootB - не надо указывать NamespaceManagedBy = Generic!~~
Иначе обработкой процессов INSERT, UPDATE, DELETE будет заниматься процесс VI_UnsAccountB_Generic (Call Script VI_UnsAccount_\<Ident_UNSRoot>_\<Event>)
Все норм, этот процесс и должен заниматься обработкой событий, мы пишем только корректно названные скрипты, которые на вход получают снимок объекта пользователя в UNSAccountB и из этого снимка можно получать нужные атрибуты.
Шаблоны скриптов находятся в скрипте
TSB_Uns_Generic_Templates
В таблице UNSAccountB у поля AccountName поменял с
Value = \$cn\$
На
If Not CBool(Variables("FULLSYNC")) Then
Value = \$FK(UID_Person).CentralAccount\$
Else
Value = \$cn\$
End if
#If Not SCRIPTDEBUGGER Then
Imports System.Data.Common
#End If
Public Function Z_Connector_Oracle_Add_Account(systemName As String, account As SingleDbObjectSnapshot) As String
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "insert into Z_dell_user_1(cn,department) values(:cn, :department)"
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
Z_Add_String_Sql_Param(cmd, "cn", account.GetValue("cn").String)
Z_Add_String_Sql_Param(cmd, "department", account.GetValue("CCC_DepartmentName").String)
cmd.ExecuteNonQuery()
Finally
conn.Dispose()
End Try
Return Nothing
End Function
Public Function Z_Connector_Oracle_Add_Account_1(systemName As String, account As SingleDbObjectSnapshot) As String
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "create user """ & account.GetValue("cn").String & """ identified by ""1qaz2WSX"""
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
cmd.ExecuteNonQuery()
sql = "grant create session to """ & account.GetValue("cn").String & """"
cmd = Z_Get_Db_Command(conn, sql)
cmd.ExecuteNonQuery()
Finally
conn.Dispose()
End Try
Return Nothing
End Function
Public Sub Z_Connector_Oracle_Update_Account(systemName As String, account As SingleDbObjectSnapshot)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update.txt", "update", False)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update2.txt", "cn=" & account.GetValue("cn").String & "; dep anme= " & account.GetValue("CCC_DepartmentName").String, False)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update3.txt", "update 3", False)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update " & account.GetValue("cn").String & ".txt", "update ", False)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update " & account.GetValue("cn").String & ".txt", "update " & account.ToXmlString(True), False)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\systemName.txt", "systemName=" & systemName, False)
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "update Z_dell_user_1 set department = :department where cn = :cn"
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
Z_Add_String_Sql_Param(cmd, "department", account.GetValue("CCC_DepartmentName").String)
Z_Add_String_Sql_Param(cmd, "cn", account.GetValue("cn").String)
Dim rowNum As Integer = cmd.ExecuteNonQuery()
' My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\update4.txt", "updated rows = " & rowNum, False)
Finally
conn.Dispose()
End Try
End Sub
Public Sub Z_Connector_Oracle_Remove_Account_From_Group(systemName As String, account As SingleDbObjectSnapshot, groupObj As SingleDbObjectSnapshot)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\delete membeship.txt","create ", False)
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "delete from z_dell_user_group_1 where account_cn=:v_account_cn and group_cn=:v_group_cn"
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
Z_Add_String_Sql_Param(cmd, "v_account_cn", account.GetValue("cn").String)
Z_Add_String_Sql_Param(cmd, "v_group_cn", groupObj.GetValue("cn").String)
cmd.ExecuteNonQuery()
Finally
conn.Dispose()
End Try
End Sub
Public Sub Z_Connector_Oracle_Add_Account_To_Group(systemName As String, account As SingleDbObjectSnapshot, groupObj As SingleDbObjectSnapshot)
'My.Computer.FileSystem.WriteAllText("C:\temp\TargetSystem1\create membeship nw.txt","create ", False)
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "insert into z_dell_user_group_1 (account_cn, group_cn) values(:v_account_cn, :v_group_cn)"
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
Z_Add_String_Sql_Param(cmd, "v_account_cn", account.GetValue("cn").String)
Z_Add_String_Sql_Param(cmd, "v_group_cn", groupObj.GetValue("cn").String)
cmd.ExecuteNonQuery()
Finally
conn.Dispose()
End Try
End Sub
Public Sub Z_Connector_Oracle_Provision_Current_Roles(systemName As String, account As SingleDbObjectSnapshot)
Dim f As ISqlFormatter = Connection.SqlFormatter
Dim user As String = account.GetValue("cn").String
Dim UID_UNSAccountB As String = account.GetValue("UID_UNSAccountB").String
Dim roleList As New List(Of String)
Dim currentRolesRows As IColDbObject = Nothing
currentRolesRows = Connection.CreateCol("UNSAccountBInUNSGroupB")
currentRolesRows.Prototype.WhereClause = f.UidComparison("UID_UNSAccountB", UID_UNSAccountB)
currentRolesRows.Prototype("UID_UNSGroupB").IsDisplayItem = True
currentRolesRows.Load()
For Each nextRole As IColElem In currentRolesRows
roleList.Add(Connection.GetSingleProperty("UNSGroupB", "cn", f.UidComparison("UID_UNSGroupB",nextRole.GetValue("UID_UNSGroupB").String)))
Next
Dim roleListStr As String = String.Join(";", roleList)
Dim conn As DbConnection = Z_Get_Db_Connection(systemName)
Try
Dim sql As String = "begin pkg_doc_rule.grants_modes(:user,:roles,:result); end;"
Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
Z_Add_String_Sql_Param(cmd, "user", user)
Z_Add_String_Sql_Param(cmd, "roles", roleListStr)
Z_Add_String_Sql_Output_Param(cmd, "result")
cmd.ExecuteNonQuery()
Dim result As String = cmd.Parameters("result").Value.ToString()
'Return result
Finally
conn.Dispose()
End Try
End Sub
'don't analyze input systemId; we want to do it for all BPM systems
Public Sub Z_Provision_To_BPM(systemId As String)
Try
Dim BPMCookieContainer As CookieContainer = Z_BPM_Authenticate()
Dim pendingRequests As IColDbObject = Nothing
pendingRequests = Connection.CreateCol("UNSRootB")
pendingRequests.Prototype.WhereClause = "UID_UNSRootB in (select rb.UID_UNSRootB from PersonWantsOrg pwo inner join BaseTree bt on pwo.UID_Org=bt.UID_Org inner join QERReuseUS qr on qr.UID_AccProduct=bt.UID_AccProduct inner join QERResourceType qrt on qrt.UID_QERResourceType=qr.UID_QERResourceType inner join UNSRootB rb on rb.UID_UNSRootB=qr.ccc_UID_UNSRootB inner join DPRNameSpace ns on ns.UID_DPRNameSpace=rb.UID_DPRNameSpace where qrt.Ident_QERResourceType='OracleRole' and ns.Ident_DPRNameSpace='OracleSystemWithSubRoles' and (pwo.ccc_provisioningstatus='New' or pwo.ccc_provisioningstatus='Pending'))"
pendingRequests.Prototype("UID_UNSRootB").IsDisplayItem = True
pendingRequests.Load()
For Each nextRequest As IColElem In pendingRequests
logger.Trace("next oracle system to handle : " & nextRequest.GetValue("UID_UNSRootB").String)
Z_Provision_To_BPM_For_System(nextRequest.GetValue("UID_UNSRootB").String, BPMCookieContainer)
Next
Catch ex As Exception
Z_Send_Email_To_Error_Auditor("Idm. BPM integration error.", "Error in BPM integration." & VbCrLf & "Operation will be retried during next task run.", ex)
End Try
End Sub
Public Sub Z_Provision_To_BPM_For_System(systemId As String, BPMCookieContainer As CookieContainer )
Z_BPM_Check_Request_Status(systemId, BPMCookieContainer)
Z_BPM_Send_Requests(systemId, BPMCookieContainer)
End Sub
'check status of tickets that were sent to BPM
'if it is approved then mark assignedment as assigned (if assignment for the same role exists then abort it)
Public Sub Z_BPM_Check_Request_Status(systemId As String, ext_BPMCookieContainer As CookieContainer)
Dim f As ISqlFormatter = Connection.SqlFormatter
'pending tickets
Dim pendingRequests As IColDbObject = Nothing
pendingRequests = Connection.CreateCol("CCCBPMProvisioningRequest")
pendingRequests.Prototype.WhereClause = "ccc_status='Pending' and " & f.UidComparison("CCC_UID_UNSRootB", systemId)
pendingRequests.Prototype("UID_CCCBPMProvisioningRequest").IsDisplayItem = True
pendingRequests.Prototype("CCC_externalId").IsDisplayItem = True
pendingRequests.Prototype("CCC_UID_Person").IsDisplayItem = True
pendingRequests.Prototype("CCC_type").IsDisplayItem = True
pendingRequests.Load()
For Each nextRequest As IColElem In pendingRequests
Dim externalId As String = nextRequest.GetValue("CCC_externalId").String
Dim res As String = Nothing
Dim resMessage As String = Nothing
'check ticket status in BPM
Try
Dim result As Dictionary(Of String, String) = Z_BPM_Get_Ticket_Status(externalId, ext_BPMCookieContainer)
Dim ticketStatus = result.Item("status")
resMessage = result.Item("solutionText")
If ticketStatus.Contains("approved") Then
res = "approved"
End If
If ticketStatus.Contains("rejected") Then
res = "rejected"
End If
Catch ex As Exception
logger.Error(ex, "error while getting tikcet status for ticket " & externalId)
Z_Send_Email_To_Error_Auditor("Idm. BPM ticket status error.", "Error while checking ticket status for ticket with id = " & externalId & "." & VbCrLf & "Operation will be retried during next task run.", ex)
End Try
If Not res Is Nothing Then
Dim ticket As ISingleDbObject = Connection.CreateSingle("CCCBPMProvisioningRequest", nextRequest.GetValue("UID_CCCBPMProvisioningRequest").String)
VID_PutValueSafe(ticket, "CCC_status", If (res = "approved", "Approved", "Rejected")) '
ticket.Save()
Dim ticketItems As IColDbObject = Nothing
ticketItems = Connection.CreateCol("CCCBPMRequestHasPWO")
ticketItems.Prototype.WhereClause = f.UidComparison("UID_CCCBPMProvisioningRequest", nextRequest.GetValue("UID_CCCBPMProvisioningRequest").String)
ticketItems.Prototype("XObjectKey").IsDisplayItem = True
ticketItems.Prototype("UID_PersonWantsOrg").IsDisplayItem = True
ticketItems.Load()
For Each nextTicketItem As IColElem In ticketItems
Dim ticketItemObj As ISingleDbObject = Connection.CreateSingle(New DbObjectKey (nextTicketItem.GetValue("XObjectKey").String))
VID_PutValueSafe(ticketItemObj, "CCC_status", If (res = "approved", "Approved", "Rejected")) '
ticketItemObj.Save()
Dim pwo As ISingleDbObject = Connection.CreateSingle("PersonWantsOrg", ticketItemObj.GetValue("UID_PersonWantsOrg").String)
logger.Trace("setting CCC_ProvisioningStatus to null")
VID_PutValueSafe(pwo, "CCC_ProvisioningStatus", Nothing)
pwo.Save()
pwo = Connection.CreateSingle("PersonWantsOrg", ticketItemObj.GetValue("UID_PersonWantsOrg").String)
Dim os As String = pwo.GetValue("OrderState").String
logger.Trace("current pwo state = " & os)
If os \<> "Aborted" Then
pwo.Custom.CallMethod("MakeDecision", "sa", If (res = "approved", True, False), resMessage)
pwo.Save()
End If
If ticketItemObj.GetValue("CCC_type") = "ChangeSubRoles" Then
'we have to abort previous request for the same resource
Dim PreviousPWOs As IColDbObject = Connection.CreateCol("PersonWantsOrg")
PreviousPWOs.Prototype("UID_PersonWantsOrg").IsDisplayItem = True
PreviousPWOs.Prototype.WhereClause = "not " & f.UidComparison("UID_PersonWantsOrg", pwo.GetValue("UID_PersonWantsOrg").String) & " and " & f.UidComparison("UID_PersonOrdered", pwo.GetValue("UID_PersonOrdered").String) & " and " & f.UidComparison("UID_Org", pwo.GetValue("UID_Org").String) & " and OrderState in ('Assigned', 'OrderProduct') "
PreviousPWOs.Load()
For Each nextPendingPwo As IColElem In PreviousPWOs
Dim pwoToCancel As ISingleDbObject = Connection.CreateSingle("PersonWantsOrg", nextPendingPwo.GetValue("UID_PersonWantsOrg").String)
Dim cancelResult As Object = pwoToCancel.Custom.CallMethod("Abort", "Согласована заявка на предоставление той же роли", Nothing, Nothing)
pwoToCancel.Save()
Next
End If
Next
End If
Next
End Sub
' provision new requests
Public Sub Z_BPM_Send_Requests(systemId As String, BPMCookieContainer As CookieContainer)
Dim f As ISqlFormatter = Connection.SqlFormatter
Dim newRequests As IColDbObject = Nothing
newRequests = Connection.CreateCol("PersonWantsOrg")
newRequests.Prototype.WhereClause = "UID_PersonWantsOrg in (select pwo.UID_PersonWantsOrg from PersonWantsOrg pwo inner join BaseTree bt on pwo.UID_Org=bt.UID_Org inner join QERReuseUS qr on qr.UID_AccProduct=bt.UID_AccProduct inner join QERResourceType qrt on qrt.UID_QERResourceType=qr.UID_QERResourceType inner join UNSRootB rb on rb.UID_UNSRootB=qr.ccc_UID_UNSRootB inner join DPRNameSpace ns on ns.UID_DPRNameSpace=rb.UID_DPRNameSpace where qrt.Ident_QERResourceType='OracleRole' and ns.Ident_DPRNameSpace='OracleSystemWithSubRoles' and (pwo.ccc_provisioningstatus='New') and " & f.UidComparison("rb.UID_UNSRootB", systemId) & " )"
newRequests.Prototype("UID_PersonWantsOrg").IsDisplayItem = True
newRequests.Prototype("UID_PersonOrdered").IsDisplayItem = True
newRequests.Load()
Dim allBenfs As New HashSet(Of String)
For Each nextRequest As IColElem In newRequests
Dim pwo As ISingleDbObject = Connection.CreateSingle("PersonWantsOrg", nextRequest.GetValue("UID_PersonWantsOrg").String)
Dim benfId = nextRequest.GetValue("UID_PersonOrdered").String
allBenfs.Add(benfId)
Next
For Each nextBenfId As String In allBenfs
'check if active BPM ticket exists for this user - if so then postpone provisioning
If Connection.Exists("CCCBPMProvisioningRequest", "ccc_status='Pending' and " & f.UidComparison("CCC_UID_UNSRootB", systemId) & " and " & f.UidComparison("CCC_UID_Person", nextBenfId)) Then
Continue For
End If
'check if other requests have not reached provisioning phase
Dim otherRequests As IColDbObject = Nothing
otherRequests = Connection.CreateCol("PersonWantsOrg")
otherRequests.Prototype.WhereClause = "UID_PersonWantsOrg in (select pwo.UID_PersonWantsOrg from PersonWantsOrg pwo inner join BaseTree bt on pwo.UID_Org=bt.UID_Org inner join QERReuseUS qr on qr.UID_AccProduct=bt.UID_AccProduct inner join QERResourceType qrt on qrt.UID_QERResourceType=qr.UID_QERResourceType inner join UNSRootB rb on rb.UID_UNSRootB=qr.ccc_UID_UNSRootB inner join DPRNameSpace ns on ns.UID_DPRNameSpace=rb.UID_DPRNameSpace where qrt.Ident_QERResourceType='OracleRole' and ns.Ident_DPRNameSpace='OracleSystemWithSubRoles' and (pwo.ccc_provisioningstatus is null and pwo.OrderState in ('OrderProduct', 'OrderUnsubscribe')) and " & f.UidComparison("rb.UID_UNSRootB", systemId) & " and " & f.UidComparison("pwo.UID_PersonOrdered", nextBenfId) & " )"
otherRequests.Prototype("UID_PersonWantsOrg").IsDisplayItem = True
otherRequests.Prototype("UID_PersonOrdered").IsDisplayItem = True
otherRequests.Load()
If otherRequests.Count \<> 0 Then
Continue For
End If
Dim userRequests As New List (Of ISingleDbObject)
For Each nextRequest As IColElem In newRequests
Dim pwo As ISingleDbObject = Connection.CreateSingle("PersonWantsOrg", nextRequest.GetValue("UID_PersonWantsOrg").String)
Dim benfId = nextRequest.GetValue("UID_PersonOrdered").String
If nextBenfId = benfId Then
userRequests.Add(pwo)
End If
Next
Dim unsAccountId = Connection.GetSingleProperty("UNSAccountB", "UID_UNSAccountB", f.UidComparison("UID_Person", nextBenfId) & " and " & f.UidComparison("UID_UNSRootB", systemId)).String
Dim systemName As String = Connection.GetSingleProperty("UNSRootB", "Ident_UNSRoot", f.UidComparison("UID_UNSRootB", systemId) )
If String.IsNullOrEmpty(unsAccountId) Then
Dim tp As String = "TargetSystem\UNS\ITResource\ & systemName & "\Prefix"
Dim tstPref As String = Connection.GetConfigParm(tp)
logger.Trace("tstPref = " & tstPref & " for " & tp)
tstPref = If (String.IsNullOrEmpty(tstPref), "", tstPref.ToUpper())
Dim accntLogin = tstPref & Connection.GetSingleProperty("Person", "CentralAccount", f.UidComparison("UID_Person", nextBenfId)).String
accntLogin = accntLogin.ToUpper()
'mail template that has a body with SQL script used to crate user
Dim createScriptTemplateName As String = Connection.GetConfigParm("TargetSystem\UNS\ITResource\ & systemName & "\CreateUserScriptTemplateName")
If String.IsNullOrEmpty(createScriptTemplateName) Then
'using default template
createScriptTemplateName = "Z_Oracle_Create_Script_AD_Auth_2"
End If
Dim generatedPwd As String = Nothing
Dim useInternalPwd As String = Connection.GetConfigParm("TargetSystem\UNS\ITResource\ & systemName & "\UseInternalPassword")
If useInternalPwd = "1" Then
generatedPwd = "1234567yY"
End If
Dim scriptBodyTemplate As String = Z_Get_Email_Template_Body(createScriptTemplateName)
Dim scriptParams As New Dictionary(Of String, Object)
scriptParams.Add("accountLogin", accntLogin)
scriptParams.Add("accountPassword", generatedPwd)
Dim scriptBody As String = Z_Velocity_Apply_Template(scriptBodyTemplate, scriptParams)
logger.Trace("executing script: " & scriptBody)
Dim accountAlreadyExists As Boolean = False
'execute create user script line by line; create user instruction can't be inside begin-end pl sql block
Dim conn As DbConnection = Z_Get_Db_Connection(systemId)
Try
Using cmd As DbCommand = Z_Get_Db_Command(conn, "select username from all_users where upper(username)='" & accntLogin & "'")
Dim dr As IDataReader = cmd.ExecuteReader()
If dr.Read() Then
logger.trace("user " & accntLogin & " already exists in database; linking it")
accountAlreadyExists = True
End If
End Using
If Not accountAlreadyExists Then
For Each nextCmdLine As String In scriptBody.Split(ControlChars.CrLf.ToCharArray)
logger.Trace("nextCmdLine = " & nextCmdLine)
If String.IsNullOrWhiteSpace(nextCmdLine)
Continue For
End If
nextCmdLine = nextCmdLine.Trim()
If nextCmdLine.EndsWith(";") Then
'to remove trailing ";" symbol
nextCmdLine = nextCmdLine.Substring(0, nextCmdLine.Length-1)
End If
logger.Trace("command to execute = " & nextCmdLine)
Using cmd As DbCommand = Z_Get_Db_Command(conn, nextCmdLine)
cmd.ExecuteNonQuery()
End Using
Next
End If
'Dim sql As String = "create user """ & accntLogin & """ identified by ""1qaz2WSX"""
'Dim cmd As DbCommand = Z_Get_Db_Command(conn, sql)
'cmd.ExecuteNonQuery()
'sql = "grant create session to """ & accntLogin & """"
'cmd = Z_Get_Db_Command(conn, sql)
'cmd.ExecuteNonQuery()
Finally
conn.Dispose()
End Try
Connection.Variables.Put("FULLSYNC", True)
Try
Dim account As ISingleDbObject = Connection.CreateSingle("UNSAccountB")
VID_PutValueSafe(account, "UID_UNSRootB", systemId)
VID_PutValueSafe(account, "cn", accntLogin)
VID_PutValueSafe(account, "AccountName", accntLogin)
VID_PutValueSafe(account, "IsGroupAccount", 1)
VID_PutValueSafe(account, "UID_TSBBehavior", "TSB-FullManaged")
VID_PutValueSafe(account, "UID_Person", nextBenfId)
account.Save()
unsAccountId = account.GetValue("UID_UNSAccountB").String
Finally
Connection.Variables.Remove("FULLSYNC")
End Try
If useInternalPwd = "1" AndAlso Not accountAlreadyExists Then
Dim pwdEmailParams As New Dictionary(Of String, Object)
pwdEmailParams.Add("accountId", unsAccountId)
pwdEmailParams.Add("systemId", systemId)
pwdEmailParams.Add("targetUserId", nextBenfId)
pwdEmailParams.Add("newPassword", generatedPwd)
Dim benfEmail As String = Connection.CreateSingle("Person", nextBenfId).GetValue("DefaultEmailAddress").String
Z_Send_Html_Mail_With_Template(benfEmail, "Z_New_Account_Oracle_With_Password", pwdEmailParams)
End If
End If
Dim currentlyAssignedRoles As IColDbObject = Nothing
currentlyAssignedRoles = Connection.CreateCol("PersonWantsOrg")
currentlyAssignedRoles.Prototype.WhereClause = "UID_PersonWantsOrg in (select pwo.UID_PersonWantsOrg from PersonWantsOrg pwo inner join BaseTree bt on pwo.UID_Org=bt.UID_Org inner join QERReuseUS qr on qr.UID_AccProduct=bt.UID_AccProduct inner join QERResourceType qrt on qrt.UID_QERResourceType=qr.UID_QERResourceType inner join UNSRootB rb on rb.UID_UNSRootB=qr.ccc_UID_UNSRootB inner join DPRNameSpace ns on ns.UID_DPRNameSpace=rb.UID_DPRNameSpace where qrt.Ident_QERResourceType='OracleRole' and ns.Ident_DPRNameSpace='OracleSystemWithSubRoles' and (pwo.OrderState not in ('Aborted', 'Dismissed', 'Unsubscribed', 'OrderProduct') or (pwo.OrderState = 'Aborted' and pwo.CCC_ProvisioningStatus='New')) and " & f.UidComparison("rb.UID_UNSRootB", systemId) & " and " & f.UidComparison("pwo.UID_PersonOrdered", nextBenfId) & " )"
currentlyAssignedRoles.Prototype("UID_PersonWantsOrg").IsDisplayItem = True
currentlyAssignedRoles.Prototype("UID_PersonOrdered").IsDisplayItem = True
currentlyAssignedRoles.Prototype("AdditionalData").IsDisplayItem = True
currentlyAssignedRoles.Prototype("CCC_UID_Department").IsDisplayItem = True
currentlyAssignedRoles.Prototype("UID_Department").IsDisplayItem = True
currentlyAssignedRoles.Prototype("UID_Org").IsDisplayItem = True
currentlyAssignedRoles.Load()
Dim currentlyAssignedRolesInfo As New List(Of Dictionary(Of String, String))
For Each nextCurrentRole As IColElem In currentlyAssignedRoles
Dim resourceId As String = Z_Get_MultiRequestableUnsubscribableProductTypeId_From_PWO(nextCurrentRole.GetValue("UID_PersonWantsOrg").String)
Dim resourceName As String = Connection.GetSingleProperty("QERReuseUS", "CCC_ExternalRef", f.UidComparison("UID_QERReuseUS", resourceId ))
Dim nextInfo As New Dictionary(Of String, String) From {{"name", resourceName}, {"UID_Department", nextCurrentRole.GetValue("UID_Department").String}, {"CCC_UID_Department", nextCurrentRole.GetValue("CCC_UID_Department").String}}
currentlyAssignedRolesInfo.Add(nextInfo)
Next
Dim addedRolesInfo As New List(Of Dictionary(Of String, String))
Dim removedRolesInfo As New List(Of Dictionary(Of String, String))
Dim changedRolesInfo As New List(Of Dictionary(Of String, String))
For Each pwo As ISingleDbObject In userRequests
Dim orderState As String = pwo.GetValue("OrderState").String
Dim operation As String = Nothing
'a new product is requested to change sub roles of already assigned role
If orderState = "OrderProduct" Then
'For Each nextCurrentRole As IColElem In currentlyAssignedRoles
' If nextCurrentRole.GetValue("UID_Org").String = pwo.GetValue("UID_Org").String
' operation = "ChangeSubRoles"
' Exit For
' End If
'Next
If operation Is Nothing Then
operation = "Add"
End If
Else
operation = "Remove"
End If
Dim resourceId As String = Z_Get_MultiRequestableUnsubscribableProductTypeId_From_PWO(pwo.GetValue("UID_PersonWantsOrg").String)
Dim resourceName As String = Connection.GetSingleProperty("QERReuseUS", "CCC_ExternalRef", f.UidComparison("UID_QERReuseUS", resourceId))
Dim nextInfo As New Dictionary(Of String, String) From {{"name", resourceName}, {"UID_Department", pwo.GetValue("UID_Department").String}, {"CCC_UID_Department", pwo.GetValue("CCC_UID_Department").String}}
If operation = "Add" Then
addedRolesInfo.Add(nextInfo)
ElseIf operation = "ChangeSubRoles"
changedRolesInfo.Add(nextInfo)
Else
removedRolesInfo.Add(nextInfo)
End If
Next
Dim ticketInternalName As String = Z_Get_Dialog_Next_Id("BPMTicketNumber", 10)
Dim dictionary As New Dictionary(Of String, Object)
dictionary.Add("sequenceTicketNumber", ticketInternalName)
dictionary.Add("accountId", unsAccountId)
dictionary.Add("systemId", systemId)
dictionary.Add("targetUserId", nextBenfId)
dictionary.Add("addedRolesInfo", addedRolesInfo)
dictionary.Add("changedRolesInfo", changedRolesInfo)
dictionary.Add("removedRolesInfo", removedRolesInfo)
dictionary.Add("currentlyAssignedRolesInfo", currentlyAssignedRolesInfo)
'Dim templateName As String = "Z_BPM_Ticket_HTML"
Dim templateName As String = "Z_BPM_Ticket_Plain_Text"
Dim bodyTemplate As String = Z_Get_Email_Template_Body(templateName)
Dim subjectTemplate As String = Z_Get_Email_Template_Subject(templateName)
Dim body As String = Z_Velocity_Apply_Template(bodyTemplate, dictionary)
Dim subject As String = Z_Velocity_Apply_Template(subjectTemplate, dictionary)
logger.Trace("ticket subject = " & subject)
logger.Trace("ticket body = " & body)
'Z_Send_Html_Mail("[redacted-email]
'Z_Send_Plain_Text_Mail("[redacted-email]
If BPMCookieContainer Is Nothing Then
BPMCookieContainer = Z_BPM_Authenticate()
End If
Dim extTicketId As String = Nothing
Dim extTicketNumber As String = Nothing
Dim opStatus As String = "Pending"
Try
Dim caseDate As New Dictionary(Of String, String)
Dim person As ISingleDbObject = Connection.CreateSingle("Person", nextBenfId)
Dim contactId As String = Nothing
If Not String.IsNullOrEmpty(person.GetValue("CCC_AdditionalEmail").String) Then
contactId = Z_BPM_Find_Contact(person.GetValue("InternalName").String, person.GetValue("CCC_AdditionalEmail").String, BPMCookieContainer)
End If
If String.IsNullOrEmpty(contactId) Then
contactId = Z_BPM_Find_Contact(person.GetValue("InternalName").String, person.GetValue("DefaultEmailAddress").String, BPMCookieContainer)
End If
Dim servicePactId As String = Z_BPM_Find_Service_Pact_By_Contact(contactId, BPMCookieContainer)
'use default contact and service pact
If String.IsNullOrEmpty(servicePactId) Then
For Each paramName As String In {"ContactId", "ServicePactId", "ServiceItemId", "CategoryId", "GroupId"}
Dim ParVal2 = Connection.GetConfigParm("TargetSystem\UNS\ITResource\BPM\DefaultTicketParameters\ & paramName)
Dim ParVal1 = Connection.GetConfigParm("TargetSystem\UNS\ITResource\ & systemName & "\DefaultTicketParameters\ & paramName)
Dim ParVal = If (String.IsNullOrEmpty(ParVal1), ParVal2, ParVal1 )
caseDate.Add(paramName, ParVal)
Next
Else
'use specific contact and service pact
caseDate.Add("ContactId", contactId)
caseDate.Add("ServicePactId", servicePactId)
For Each paramName As String In { "ServiceItemId", "CategoryId", "GroupId"}
Dim ParVal2 = Connection.GetConfigParm("TargetSystem\UNS\ITResource\BPM\DefaultTicketParameters\ & paramName)
Dim ParVal1 = Connection.GetConfigParm("TargetSystem\UNS\ITResource\ & systemName & "\DefaultTicketParameters\ & paramName)
Dim ParVal = If (String.IsNullOrEmpty(ParVal1), ParVal2, ParVal1 )
caseDate.Add(paramName, ParVal)
Next
End If
Dim createdTicketData As Dictionary(Of String, String) = Z_BPM_Create_Ticket(subject, body, caseDate, BPMCookieContainer)
extTicketId = createdTicketData.Item("id")
extTicketNumber = createdTicketData.Item("number")
Catch ex As Exception
logger.Error(ex, "error while creating ticket for " & nextBenfId)
opStatus = "Failed"
Z_Send_Email_To_Error_Auditor("Idm. BPM ticket creation error.", "Error while creating ticket for user with id = " & nextBenfId & "." & VbCrLf & "Ticket is marked as failed in CCCBPMProvisioningRequest table. Ticket will not be send again until you change CCC_ProvisioningStatus column in PersonWantsOrg table to 'New'.", ex)
End Try
Dim ticket As ISingleDbObject = Connection.CreateSingle("CCCBPMProvisioningRequest")
VID_PutValueSafe(ticket, "CCC_status", opStatus)
VID_PutValueSafe(ticket, "CCC_type", "ChangesInRoles")
VID_PutValueSafe(ticket, "CCC_UID_UNSRootB", systemId)
VID_PutValueSafe(ticket, "CCC_UID_Person", nextBenfId)
VID_PutValueSafe(ticket, "CCC_externalId", extTicketId)
VID_PutValueSafe(ticket, "CCC_externalName", extTicketNumber)
VID_PutValueSafe(ticket, "CCC_internalName", ticketInternalName)
ticket.Save()
Dim ticketId As String = ticket.GetValue("UID_CCCBPMProvisioningRequest").String
For Each pwo As ISingleDbObject In userRequests
Dim ticketTpPWO As ISingleDbObject = Connection.CreateSingle("CCCBPMRequestHasPWO")
Dim orderState As String = pwo.GetValue("OrderState").String
Dim operation As String = Nothing
'a new product is requested to change sub roles of already assigned role
If orderState = "OrderProduct" Then
'For Each nextCurrentRole As IColElem In currentlyAssignedRoles
' If nextCurrentRole.GetValue("UID_Org").String = pwo.GetValue("UID_Org").String
' operation = "ChangeSubRoles"
' Exit For
' End If
'Next
If operation Is Nothing Then
operation = "Add"
End If
Else
operation = "Remove"
End If
VID_PutValueSafe(ticketTpPWO, "UID_CCCBPMProvisioningRequest", ticketId)
VID_PutValueSafe(ticketTpPWO, "UID_PersonWantsOrg", pwo.GetValue("UID_PersonWantsOrg").String)
VID_PutValueSafe(ticketTpPWO, "CCC_status", opStatus)
VID_PutValueSafe(ticketTpPWO, "CCC_type", operation)
ticketTpPWO.Save()
logger.Trace("setting CCC_ProvisioningStatus to " & opStatus)
VID_PutValueSafe(pwo, "CCC_ProvisioningStatus", opStatus) '
pwo.Save()
Next
Next
End Sub
Public Function Z_Get_SubRole_Type_Name(depId As String) As String
If String.IsNullOrEmpty(depId) Then
Return Nothing
End If
Dim typ As String = Connection.CreateSingle("Department", depId).GetValue("CustomProperty03").String
If typ = "BE" Then
Return "Балансовая единица"
ElseIf typ = "VP" Then
Return "Точка зрения"
ElseIf typ = "Lang" Then
Return "Вид языка"
ElseIf typ = "Audit" Then
Return "Вид аудита"
ElseIf typ = "Analysis" Then
Return "Вид анализа"
ElseIf typ = "Workshop" Then
Return "Цех/предприятие"
ElseIf typ = "DepartmentOrg" Then
Return "Подразделение (Организация)"
ElseIf typ = "WorkshopOnly" Then
Return "Цех"
ElseIf typ = "Company" Then
Return "Предприятие"
ElseIf typ = "Department" Then
Return "Подразделение"
End If
Return Nothing
End Function
Public Function Z_Connector_Oracle_Recon_And_Link(sysName As String) As String
Dim f As ISqlFormatter = Connection.SqlFormatter
Dim sysId = Z_Get_UNS_System_Id(sysName)
Connection.Variables.Put("FULLSYNC", True)
Try
Dim conn As DbConnection = Z_Get_Db_Connection(sysId)
Try
Using cmd As DbCommand = Z_Get_Db_Command(conn, "select username from all_users order by 1")
Dim dr As IDataReader = cmd.ExecuteReader()
While dr.Read()
Dim login As String = dr("username").tostring()
logger.trace("handling " & login)
Dim currentaccntId As String = Connection.GetSingleProperty("UNSAccountB", "UID_UNSAccountB",f.UidComparison("UID_UNSRootB", sysId) & " and " & f.Comparison("AccountName", login, ValType.String))
If Not String.IsNullOrEmpty(currentaccntId) Then
logger.trace("already reconciled")
Continue While
End If
Dim persId As String = Connection.GetSingleProperty("Person", "UID_Person", f.Comparison("CentralAccount", login, ValType.string))
If String.IsNullOrEmpty(persId) Then
logger.trace("persId not found")
Continue While
End If
Dim account As ISingleDbObject = Connection.CreateSingle("UNSAccountB")
VID_PutValueSafe(account, "UID_UNSRootB", sysId)
VID_PutValueSafe(account, "cn", login)
VID_PutValueSafe(account, "AccountName", login)
VID_PutValueSafe(account, "IsGroupAccount", 1)
VID_PutValueSafe(account, "UID_TSBBehavior", "TSB-FullManaged")
VID_PutValueSafe(account, "UID_Person", persId)
account.Save()
End While
End Using
Finally
conn.Dispose()
End Try
Finally
Connection.Variables.Remove("FULLSYNC")
End Try
Return "ok"
End Function
![Name Display name aase propertj Description Sea rch p ropertj Ulc UNSRDDts Value resolution for Target system UD UNSRoot3 Target system identifier. Ident_UNSRoot Ignore case Z] Save unresolvablekeys O Ha rd le failure to resolve as error Read-ony Report unresolvablekeys](../media/27. One Identity Oracle/media/image3.png)
