IDS Forum

Informix protection from LINKED SERVERS

Posted By: MIKE MAGIE
Date: Wednesday, 26 January 2011, at 4:43 p.m.

All -

We use Informix and SQL Server now throughout our entire enterprise. Most of our most critical data is still (for now) on the Informix side. We set up SQL Server linked serves to hit the Informix data via SQL Server - whatever. Anyhoo - it came to our attention that a linked server in our SQL Server test environment referenced production Informix data via an account that had update access on the Informix-side. To prevent this from happening again I created the below TSQL script, ran as an agent job in SQL Server to make sure none of the linked servers ref production data as updateable users... this is not Informix code so if it gets removed I am cool with it. Enough of us are forced to work with SQL Server and Informix that these kind of scripts could be helpful to someone other than me. Or not. Sorry for the format. The script only reports...

--Create a temp table to hold all Informix linked servers and the account name used to access the linked server.
--By using the 'provider_string' column in predicate I ensure that the linked server is a production Informix server.
--I also use the 'provider_string' to further verify the linked server points to production data.
IF OBJECT_ID('tempdb..#lnksrvusers') IS NOT NULL
DROP TABLE #lnksrvusers
CREATE TABLE #lnksrvusers (username VARCHAR(500), lnksrvname VARCHAR(500) )

INSERT INTO #lnksrvusers
SELECT DISTINCT remote_name,name FROM sys.servers A, sys.linked_logins B
WHERE a.server_id = b.server_id AND b.remote_name IS NOT NULL AND a.provider_string LIKE '%INFORMIX%' AND a.provider_string LIKE '%Host=rmkc00a%'

--Declare all variables we need
DECLARE @Lnksrv CHAR(30)
DECLARE @Dbname CHAR(20)
DECLARE @UserName CHAR(20)
DECLARE @Tsql VARCHAR(5000)
DECLARE @EmailSubject VARCHAR(1000)
DECLARE @EmailBody VARCHAR(5000)
DECLARE @RecipientList VARCHAR(1000)
DECLARE @EmailProfile VARCHAR(1000)
DECLARE @RowCnt INT
DECLARE @Status INT
DECLARE @BadNames VARCHAR(2000)
DECLARE @Holder VARCHAR(1000)

--Go ahead and set these up as they will not change.
SET @EmailSubject = 'LINKED SERVER USERS ' + @@SERVERNAME
SET @RecipientList = 'beyonce@poop.com'
SET @EmailProfile = 'SQL Mail Agent'
SET NOCOUNT ON

--Create a second temp table. This one will hold the user name, linked server name and the
--Informix database name when a user is found to have update access on the production Informix db.
IF OBJECT_ID('tempdb..#badusers') IS NOT NULL
DROP TABLE #badusers
CREATE TABLE #badusers (username CHAR(20), lnksrvname CHAR(30), dbname CHAR(20) )

--Use a cursor to hold the linked server name. For each linked server we will try to select
--from the Informix meta-data tables to get permission info. If the linked server is 'broke'
--we will insert a row into the #badusers temp table and report it as an elevated user. If
--the linked server can be queried we will check the sysusers and systabauth Informix system
--tables to see if the user has DBA or other heightened database access followed by a check
--to be sure that no specific table level update permissions exist.
DECLARE lnksrv_cursor CURSOR FOR SELECT lnksrvname FROM #lnksrvusers
OPEN lnksrv_cursor
FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv
WHILE @@fetch_status=0
BEGIN

SET @Dbname = (SELECT LEFT(@Lnksrv, charindex('_ifx_',@Lnksrv) - 1 ))

SET @UserName = (SELECT DISTINCT username FROM #lnksrvusers WHERE lnksrvname = @Lnksrv AND username IS NOT NULL)

SET @Tsql = 'SELECT * FROM ' + @Lnksrv + '.'+ @Dbname + '.' + 'informix'+ '.' + 'sysusers ' + 'WHERE username = ' + ''''+@UserName+'''' + ' AND usertype IN ' + '(' + '''' + 'D' + '''' + ','+''''+'R' + '''' + ')'

BEGIN TRY

EXEC (@Tsql)

END TRY

BEGIN CATCH

IF ERROR_NUMBER() <> 0

SET @Status = 1

INSERT INTO #badusers (username,lnksrvname,dbname) VALUES (@UserName,@Lnksrv,@Dbname)

END CATCH

IF @@ROWCOUNT <> 0

BEGIN

PRINT 'User has DBA permissions...'

INSERT INTO #badusers (username,lnksrvname,dbname) VALUES (@UserName,@Lnksrv,@Dbname)

FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv

END

ELSE

BEGIN

IF @Status = 1

BEGIN

PRINT 'Cannot access linked server'

FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv

END

ELSE

BEGIN

PRINT 'User as no DBA access - linked server is accessible - checking table access...'

SET @Tsql = 'SELECT * FROM ' + @Lnksrv + '.'+ @Dbname + '.' + 'informix'+ '.' + 'systabauth '+'WHERE tabauth <> ' + '''' + 's--------' + '''' + ' AND grantee =' + '''' + @UserName + ''''

EXEC (@Tsql)

IF @@ROWCOUNT <> 0

BEGIN

PRINT 'HERE'

PRINT @UserName PRINT @LnkSrv PRINT @Dbname

INSERT INTO #badusers (username,lnksrvname,dbname) VALUES (@UserName,@Lnksrv,@Dbname)

FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv

END

ELSE

BEGIN

PRINT 'ALL GOOD ' + @Dbname

FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv

END

END

END
END
FETCH NEXT FROM lnksrv_cursor INTO @Lnksrv
CLOSE lnksrv_cursor
DEALLOCATE lnksrv_cursor

--Now process the rows in #badusers so we can email out a report.
SELECT * FROM #badusers
SELECT @RowCnt = COUNT(*) FROM #badusers
IF @RowCnt > 0
BEGIN

SET @Holder = 'The following list shows linked server accounts that have DBA/UPDATE or other elevated access to the Informix database they reference.'

+ CHAR(13) + 'For more information look at the permissions for the user on the appropriate Unix server.'

+ CHAR(13)+ CHAR(13) + 'USER' + CHAR(9) + CHAR(9) + CHAR(9) + CHAR(9) + 'LINKED_SERVER' + CHAR(9) + CHAR(9)+ CHAR(9) + CHAR(9) + 'INFORMIX_DBNAME' + CHAR(13)

SELECT @BadNames = ISNULL(@BadNames,'') + CHAR(13) + username + CHAR(9) + lnksrvname + CHAR(9) + dbname + CHAR(13)

FROM #badusers

SET @EmailBody = @Holder + @BadNames

PRINT @EmailBody

EXEC msdb..sp_send_dbmail @profile_name=@EmailProfile,@recipients=@RecipientList,@subject=@EmailSubject, @body=@EmailBody
END

Messages In This Thread

• 
  --
  • Re: Informix protection from LINKED SERVERS
    Art Kagel -- Wednesday, 26 January 2011, at 5:39 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.