www.idmwizard.com

I spent the better part of a week in October in Luxembourg, and most of this post was written on October 21st, 2009.  However, its been sitting in my Drafts queue this whole time.

In any case, it was about what I expected.  It was a cute little city/country nestled inside of Europe with a good mix of people from various other countries.  The one thing that did truly surprise me was a Greek developer name Michael P.  What surprised me most was that he was able to take Quest VSJ and get it running in about 30 minutes, with minimal instructions, and having a machine on a different domain, with no DNS resolution to the AD domain where VSJ was installed.

Now, I should point out that Barry G and I have gone out of our way to make VSJ accessible to customers trying it out the first time, but its still a hard product for developers to truly ‘grok.’  And the product itself is not difficult but the whole notion of ‘Java using Microsoft for authentication and authorisation’ seems to really trouble some people.  Even though its really a code library, like many others, and is often used as a servlet filter, like many others, developers get really hung up with the fact that its working with Active Directory.  Its some sort of stumbling block for them, even though it behaves like any other piece of Java code.

But Mike just nodded when I told him it was a servlet filter, and grabbed the USB stick from me with the binaries.  One of a few developers I’ve met in a long time that didn’t subscribe to any sort of ‘religion.’

Within 10 minutes, he came back saying he was getting licensing errors!  Perfect – it was ‘as expected’ as I forgot to give him a file called vsj-license.jar and he disappeared when I did.  About 5 minutes after that, he came back with ‘access denied’ error messages, which meant that he installed it, and it was behaving ‘as expected.’

Some things Mike saw when he first tried to log in turn into good tips to keep in mind if you’re actually deploying or testing VSJ:

• Kerberos didn’t work, which was expected, so he failed to login.  This was not a surprise, as VSJ defaults to leaving NTLM and Basic Auth turned off.  Good settings for a production environment, but one that trips people up when they first using VSJ in a development and test environment.  So he checked the vsj.properties file and turned both options on for testing.
• Internet Explorer is an abysmal browser.  Once it starts doing something (like NTLM) there is no way to get it to stop.  Even turning NTLM off in the servlet filter doesn’t stop it from trying.  Nothing short of a reboot stops it from behaving this way.  So we enabled SP-NEGO in Firefox and continued on.  We also turned off NTLM, but left Basic Auth on, which then has the servlet filter requesting the Kerberos ticket on the user’s behalf.
• Mike P was not in the test domain, so Kerberos would never work for him off his machine.  Unless . . . he used the “runas” command.  But even runas doesn’t work well off machines that are on another domain.  However, we used the “runas /netonly” command which gets past that.
• Finally, we did reconfigure Mike’s machine to use the test domain’s DNS.  There is no way to do proper Kerberos without good name resolution, and we needed to get host and SRV records to get a ticket onto Mike’s machine.

There are definitely a lot more things to keep in mind when working with VSJ.  If you do need help, check out these forums or call your Quest rep to get you in touch with some knowledgable people.

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.