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