www.idmwizard.com

Some time in Q3 of last year, Stu Harrison (the PM for Defender) got me a beta copy of the latest version of Defender, which was due to have GridSure in it.  Of course, I took the time to record a quick demo of it, but then Stu asked me to delay releasing it.  One thing led to another, and I never got to posting the demo up here.  Today, however, while going through Defender with another architect, I remembered that I had this recording.

Before I go any further, you may be asking, “what is GridSure?”  It is another type of token that is available with Defender, and you can see a 3 minute marketing demo of it in the next URL.  This recording does a good job of explaining how it is used by the end user:

http://www.quest.com/defender/DefenderGrIDsureWeb/DefenderGrIDsureWebVideo.html

Whilst the demo above gives you a good idea of what the end user will see, I recorded a demo showing how to configure the token, and policy and what the user does to register.  In addition, I show the standard Defender desktop token being used with the ISAPI filter at the very beginning of the video.  I’ll apologize now for the microphone settings, and without further ado, here’s the 3 minute, 20 second video:

http://www.idmwizard.com/quest/Defender-GridSure/Defender-GridSure.html

Regards,
Dmitry

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.

I had a client a long time ago (in 2007) ask if they can have a way to never re-use an account name.  They were looking at Quest ActiveRoles Server (google for it), and this was a key requirement.  Well, this would be very easy to do with the built-in policies if they kept their disabled users around.  However, they didn’t want to clutter AD with similar account names, and disabled accounts.  Which meant that QARS wouldn’t be able to check AD for uniqueness as the accounts would be wiped out.  I initially suggested they use an ADAM (now called AD LDS) store for this, and have AD include it in the scope.  However, but they thought it was too cumbersome for this task (and, honestly, it was).

So I had to come up with a scripted solution to get past this hurdle and still provide them a way to create unique names into perpetuity without leaving objects in AD or ADAM.  This question has come up again internally, so I thought it would make sense to publish this to the rest of the world for future reference. First is a recording of how to install the bits and show you how it works.  The short version is that it:

a. creates a table in the QARS database to keep track of every user name created as the account is being provisioned.
b. it installs a policy that checks the table from part (a) and generates a new user name based on the previous names in the DB.

The way the script is written (and this is what the client wanted), they wanted to create a user with first name, then last initial.  If that was taken, use the next 2 letters, 3 letters, and so on.  At some point, you run out of options, and have to resort to numbering.  Obviously, the script needs to be modified to meet your needs, and there are some great Professional Services people at Quest to help if you need it, but perhaps this sample is enough.

This post, as with all others, implies no warranty, and I do NOT support this solution (unless you wish to pay me) and is posted as an example of what is possible with Quest ActiveRoles Server.  If you have questions, please contact your Quest account manager about what support options are available.

Now . . . without further ado, here are the links you want.  First, here is a recorded video of the installation and usage:

http://www.idmwizard.com/quest//UniqueUserID/index.html

And here is the zip file shown in the video (albeit renamed – but you should be able to figure it out):
http://www.idmwizard.com/quest/UniqueUserID/UniqueUserID_policy.zip

Cheers,
Dmitry

(note: edited 2009-09-22 – changed some text and updated links to open in new windows and work properly).