Informix protection from LINKED SERVERS

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 ANDa.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 #badusersSELECT @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

Mike, Package it up as a shell archive or zip file with a README and go to the IIUG Repository submission page to upload the package and a copy of the README file and submit it to the repository. Art On Jan 26, 2011 2:43 PM, "MIKE MAGIE" <jmmagie@yahoo.com> wrote: > 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 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --20cf3054a5b1e0cd9f049ac7813c