SQL server - Check for unauthorized application Options

[RA]

CODE

-----------------------------------------------------------------
-- Name: SQLUnauthorizedApplication.lua
-- Author: Kevin Prestage
-- Required INM version: 3.3
-- Version: 1.1
-- Date: 2007-09-21
-- Description: This script will check all active connections
-- and make sure they are on a list of logins and application
-- names allowed to access the server.  You can also validate
-- the host the connection if coming from if desired.
--
-- Arguments:
-- 1) Server Name
-- 2) config file path
-- 3) Check Hosts is a boolean value to determine if you want
-- to enforce the host the connection is coming from.


-- The config file is a tab delimited text file on the INM host
-- machine.  The format should be:
-- login    application        hostname
-----------------------------------------------------------------


--simple function to split a string into an array (table) based on a seperator.
function MakeTable2(str, sep)
    fields = {};
    string.gsub(str,"([^"..sep.."]*)"..sep, function(c) table.insert(fields, c) end);
      return fields;
end



--grab the parameters
servername = GetArgument(0);
fn = GetArgument(1);
bhost = GetArgument(2);


--open the file in the context of the INM host.
file = TLuaFile(true);
iLength = file:GetFileSize( fn );
iRet = file:Open(fn,false);
RawData = "";
RawData, iLength = file:Read(iLength);
file:Close();

--convert the file text to lower case for easier comparison.
RawData = string.lower(RawData);
    
RawData = string.gsub(RawData,"\t","_");
RawData = string.gsub(RawData," ","_");
RawData = string.gsub(RawData,"-","_");


--create a TLuaDB object and connect to the database.
db = TLuaDB();
con = servername .. "@master";
x = db:Connect(con,TLuaDB.CLIENT_SQLSERVER);

--set the sql statement to run against the db
sql = "select spid, rtrim(ltrim(loginame)), rtrim(ltrim(hostname)), rtrim(ltrim(program_name)) from sysprocesses";

--create variables to store results.
result = "";
count = 0;

spid = "";
app = "";
login = "";
host = "";

--if the db connection succeeded we can now execute our sql statement.
if (x == true) then
    x = db:Execute(sql);

    --read the results of the statement.
    if (db:ResultAvilable() == true) then
        while (db:NextRow() == true) do
            count = count + 1;
            spid = db:GetCol(1);
            app = db:GetCol(4);
            login = db:GetCol(2);
            host = db:GetCol(3);
            login = string.lower(login);
            host = string.lower(host);
            app = string.lower(app);
              find = "";
            app = string.gsub(app," ", "_");
            app = string.gsub(app,"-", "_");
            login = string.gsub(login," ", "_");
            login = string.gsub(login,"-", "_");
            host = string.gsub(host," ", "_");
            host= string.gsub(host,"-", "_");            
            if ((login ~= "sa") and (app ~= "")) then

                if (bhost == "true") then
                    find = login.."_"..app.."_"..host;
                else
                       find = login.."_"..app;
                end        
                
                iPos2 = string.find(RawData,find );
                if (iPos2 == nil) then
                        result = result .. login .. "\t" .. app .."\t"..host.."\tIs Not Allowed.\n";
                        b = false;
                    else
                    --print("fOUND iT");
                end

            end
            
        end
    end


else
    result = db:GetErrorDescription();
end



if (result == "") then
    result = "All connections ok!";
else
    x = false;
end

SetExitStatus(result, x);