The Identity Management Wizard

One thing that has come up a lot recently is how to provision Oracle DB users from ActiveRoles Server.  Oracle DB users are not very hard to create but most people using ARS have little to no experience with managing DB users.  And, unlike applications, DB users can’t simply be inserted into a table as tables like SYSUSER may have dependencies to other parts of the DB.

There are a lot of different ways to do this, but below is an outline of something I wrote almost 2 years ago, and is still applicable today.  Note: this is all in VB Script and is compatible with any version of ARS 6.x.  It may even work with 5.x.  I do have plans on converting this to PowerShell for use in 6.5, but this ought to be enough to get you moving.

Start off with a script library called “DB Code.”   The idea is to have all the connection and DB execution code in one place, and then decide which DB code (the actual SQL commands) gets called into the ARS event handlers.  Here is that code with a hard-coded connection string.

Option Explicit

' ************************************************************
' This function executes any SQL command sent to it against
' the Oracle DB
' ************************************************************
Function ExecuteSQLScript(p_sSQLToExecute)
 Dim oFS
 Dim oFSFile
 Dim strConnectionString
 Dim oConnection
 Dim oCmd

 Const cnstCommand = 1 'Command type - 1 is for standard query
 Const ForReading = 1
 Const ForWriting = 2
 Const ForAppending = 8

 ' sample connection string from www.connectionstrings.com
 ' Driver={Microsoft ODBC For Oracle};Server=myServerAddress;Uid=myUsername;Pwd=myPassword;

 ' connection string - only server name and DB name should change
 strConnectionString = "Driver={Microsoft ODBC for Oracle};Server=OraDB1;Uid=SYSTEM;Pwd=Password1;"

' uncomment next 3 lines for debugging
 On Error Resume Next
 Set oFS = CreateObject("Scripting.FileSystemObject")
 Set oFSFile = oFS.OpenTextFile("C:\\Log.txt", ForAppending , False) 

 ' connect to oracle db
 Set oConnection = CreateObject("ADODB.Connection") 

 'Open connection using ConectionString
 oConnection.Open strConnectionString 

 Set oCmd = CreateObject("ADODB.Command")

 ' prepare SQL Statement

 ' create the user to be external
 oCmd.CommandText = p_sSQLToExecute
 oCmd.CommandType = cnstCommand
 oCmd.ActiveConnection = oConnection
 oCmd.Execute

 ' uncomment next line for debugging and put them right where you think the error is happening
 oFSFile.WriteLine(p_sSQLToExecute & " *** Error: " & Err.Number & " " & Err.Description)

 ' close the connection and disconnect from oracle db
 Set oCmd = Nothing

 oConnection.Close
 Set oConnection = Nothing

' uncomment next 3 lines for debugging
 oFSFile.Close
 Set oFSFile = Nothing
 Set oFS = Nothing
End Function

Note that I’ve got some debugging coded in there, and this will write every SQL command sent to a file called c:\Log.txt.  You may wish to turn this off in production.  Also, this script is meant to go into “Script Modules/QSC Scripts” and is called “DB Code”.  If you alter any of this, you’ll need to change the objLib references below.

Next, you simply create some script policies that have event handlers which will execute this code.  My suggestion is to use the OnPost set of events so that if something happens, the rest of the action can continue. The first bit of sample code is for creating the account in Oracle.  Now, keep in mind that creating a user doesn’t do anything until you GRANT the user some rights.  So its OK to create users since they cannot connect up and do anything just yet.

Option Explicit

Sub onPostCreate(Request)
 Dim strsAMAccountName
 Dim strExecuteSQL
 Dim strTitle
 Dim objLib

 Set objLib = ScriptLib.Load("Script Modules/QSC Scripts/DB Code")

 Request.GetInfo
 strsAMAccountName = UCase(Request.Get("samaccountname"))
 strTitle = UCase(Request.Get("title"))

 ' if this change is not for a user, get out
 If (LCase(Request.Class) <> LCase("user")) Then Exit Sub    

 ' prepare SQL Statement

 ' write the new account into the table
 strExecuteSQL = " CREATE USER " & strsAMAccountName & " IDENTIFIED BY Password1 "

 Call objLib.ExecuteSQLScript(strExecuteSQL)

 'grant access if the new account is a production dba
 If strTitle = "PRODUCTION DBA" Then
 strExecuteSQL = " GRANT CONNECT TO " & strsAMAccountName & " "
 Call objLib.ExecuteSQLScript(strExecuteSQL)
 End If 

End Sub

Next, we have an example that shows a simple grant/revoke command based on someone’s job title (if the user is a Production DBA he gets connect access while anyone else is revoked).

Option Explicit

Sub onPostModify(Request)
 Dim strsAMAccountName
 Dim strExecuteSQL
 Dim objLib
 Dim strTitle

 Set objLib = ScriptLib.Load("Script Modules/QSC Scripts/DB Code")

 Dim objObj
 On Error Resume Next
 If (DirObj Is Nothing) Then
    Set objObj = Request
 Else
    Set objObj = DirObj
 End If
 On Error GoTo 0

 ' if this change is not for a user, get out
 If (LCase(objObj.Class) <> LCase("user")) Then Exit Sub    

 strsAMAccountName = UCase(objObj.Get("samaccountname"))
 strTitle = UCase(objObj.Get("title"))
 strEmpStatus = UCase(objObj.Get("edsvaEmpStatus"))

 ' prepare SQL Statement

 ' If you are DBA you should have connect rights
 If strTitle = "PRODUCTION DBA" Then
 ' write the new account into the table
 strExecuteSQL = " GRANT CONNECT TO " & strsAMAccountName & " IDENTIFIED BY Password1"

 Else
 ' delete the account from the table
 strExecuteSQL = " REVOKE CONNECT FROM """ & strsAMAccountName & " "
 End If

 Call objLib.ExecuteSQLScript(strExecuteSQL)

 If strEmpStatus = "TERMINATED" Then
 objObj.Put "edsvaDeprovisionType", 1
 objObj.SetInfo
 End If

End Sub

Finally, here’s an example of what you can do when someone is deprovisioned:

Option Explicit

Sub onPreDeprovision(Request)
 Dim strsAMAccountName
 Dim strExecuteSQL
 Dim objLib

 Set objLib = ScriptLib.Load("Script Modules/QSC Scripts/DB Code")

 ' if this change is not for a user, get out
 If (LCase(DirObj.Class) <> LCase("user")) Then Exit Sub    

 strsAMAccountName = UCase(DirObj.Get("samaccountname"))

 ' prepare SQL Statement

 ' delete the account from the table
 strExecuteSQL = " DROP USER " & strsAMAccountName & "" & " CASCADE "

 Call objLib.ExecuteSQLScript(strExecuteSQL)
End Sub

At the end of it all, all I’m doing is calling SQL statements that a DBA would use in creating and managing users within Oracle.  This same approach will actually work with MySQL, SQL Server, DB2 and most other RDBMS provided the correct syntax.  And, for example, if you want to get really clever, you could create AD groups that are analogous to Oracle Roles, like SYSDBA,

I would suggest you be careful with that last one as the CASCADE part of the command will drop any objects owned by the user within Oracle, and you could lose data.  Be sure to talk to the Oracle DBAs first, and walk them through what you’re doing.  And, as always, this post is made available with no guarantees, assurances, promises or commitments.  Your mileage may vary, and you really should contact Quest Professional Services if you need assistance with ActiveRoles Server.

{ Comments on this entry are closed }