IDS Forum

Re: Informix protection from LINKED SERVERS

Posted By: Art Kagel
Date: Wednesday, 26 January 2011, at 5:39 p.m.

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

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.