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.