Hi All. Managing several Oracle Servers with large numbers of students can by time consuming. I’ve no desire to spend a couple of hours creating user accounts so insted I spent a couple of hours building a VBScript to do it for me. Fortunately I can export the data I need from the University’s student management system and create the csv file quite quickly. Anyway, here’s the code. It’s probably full of examples of how not to do things but as far as I am concerned it works. Any feedback would be appreciated!
'A script to create Oracle schema users
'NOTE: sqlplus must be installed and configured to connect to Oracle!
'Created by Rob Childs
'thisbrokenworld.wordpress.com
'=======================================
intArgs = WScript.Arguments.Count
If intArgs < 2 then
WScript.Echo "usage: cscript createOracleUsers.vbs [inputFile] [mode] [OracleSid] [OracleAdminUser]"
WScript.Echo "Creates Oracle Schema users from a specified csv file"
WScript.Echo " "
WScript.Echo "example: createOracleUsers.vbs users.csv 1 sid sys"
Wscript.Echo "Mode - 0 Create SQL Script only"
Wscript.Echo " 1 Create SQL Script and users"
WScript.Echo " [OracleSid] and [OracleAdminUser] is required for modes 1 and 2"
WScript.Echo " "
WScript.Echo "Note that the CSV file must be in the following format with 2 fields and remember that"
WScript.Echo "Oracle user passwords can't start with a number!"
WScript.Echo " USERNAME,PASSWORD"
WScript.Quit
end If
'set this to 'yes' to have the sql script quit sqlplus after completing
'Note that if you do this you'll have no way to tell if the script succeeded or not
autoQuit = "no"
'Default Oracle roles to assign to new users
roles = "CONNECT, RESOURCE"
'USERS Tablespace Quota in Mb
intQuota = 10
'grab the command line arguments
inputFile = WScript.Arguments.Item(0)
mode = WScript.Arguments.Item(1)
'the file name for the .sql script we're going to create
sqlScript = inputFile & ".sql"
if mode > 2 then
WScript.Echo "Not a valid mode"
WScript.Quit
End If
if intArgs = 4 then
oraAdmin = WScript.Arguments.Item(3)
oraConnect = oraAdmin & "@" & dbInst & " as sysdba"
end if
'Open the input file
Const ForReading = 1
Set objInputFSO = CreateObject("Scripting.FileSystemObject")
Set objInputFile = objinputFSO.OpenTextFile(inputfile, ForReading)
'create the sql file we're going to output to
WScript.Echo sqlScript
Set objOutputFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objOutputFSO.CreateTextFile(sqlScript)
objOutputFile.close
'add lines to our new sql script
intUsersToProcess = 0
Do Until objInputFile.AtEndOfStream
arrStr = split(objInputFile.ReadLine,",")
strUser = arrStr(0)
strPwd = arrStr(1)
intUsersToProcess = intUsersToProcess + 1
WScript.Echo "Processing " & strUser
strCreateUserLine = "CREATE USER " & strUser & " IDENTIFIED BY " & strPwd & ";"
strGrantLine = "GRANT " & roles & " TO " & strUser & ";"
strAlterUserLine = "ALTER USER " & strUser & " QUOTA " & intQuota & "M ON USERS;"
Set WriteFile = objOutputFSO.OpenTextFile(sqlScript, 8, True)
WriteFile.WriteLine(strCreateUserLine)
WriteFile.WriteLine(strGrantLine)
WriteFile.WriteLine(strAlterUserLine)
WriteFile.Close
loop
WScript.Echo intUsersToProcess & " Users Processed"
'if autoQuit is set to yes, add the quit command to the end of our sql script so that it exits.
'the problem with that is you'll miss any errors thown by sqlplus when running the script
if autoQuit = "yes" then
Const ForAppending = 8
Set objFile = objOutputFSO.OpenTextFile(sqlScript, ForAppending)
objFile.WriteLine("quit")
objFile.Close
end if
'if the autoCreateUsers is set to yes, run sqlplus and execute the sql script.
if mode > 0 then
Set WshShell = WScript.CreateObject("WScript.Shell")
WScript.Echo "connecting to sqlplus " & oraConnect & " @" & sqlScript
Return = WshShell.Run("sqlplus " & oraConnect & " @" & sqlScript, 1, true)
end if
This Broken World 