mar
14
2017

T-SQL Tuesday #88 – Auditando la seguridad, WTF?

tsql2sday150x150

 
Esta publicación forma parte del T-SQL Tuesday, que es una fiesta de blog mensual el segundo martes de cada mes. Todos son bienvenidos y tienen la oportunidad de escribir sobre SQL Server.
El tema de este mes es Tu WTF del día a día (relacionado con la base de datos)
, y una de las cosas más importantes para los administradores de bases de datos es la seguridad, de modo que ahí está mi “Auditando la seguridad, WTF?”.

 
Antecedentes

La seguridad es una tarea muy importante para los profesionales de bases de datos y no importa si acaba de aterrizar en una nueva empresa, si es un consultor o si trabajas en un equipo donde varias personas tienen privilegios elevados en los servidores, siempre es bueno saber quién puede hacer qué en un servidor, (también conocido como quién puede tumbar el servido).

No estoy seguro de qué me asustaría más, si hay muchos [sysadmin] o simplemente [sa] porque el primero da miedo, pero el segundo implica descubrir quién tiene acceso a la contraseña de [sa] y para saber quién hizo qué, puede ser un verdadero dolor en el cuello.

De una forma u otra, como dije, quiero saber las diferentes personas y el nivel de acceso a mi(s) servidor(es), por lo que en su día creé este procedimiento almacenado, y ahora quiero compartirlo con ustedes.

Podemos encontrar toda esa información usando diferentes DMVs y en mi caso utilizo sys.server_principals, sys.server_role_members and sys.server_permissions y un procedimiento almacenado que apuesto a que no es tan conocido, sys.xp_logininfo que ayuda a obtener una imagen más detallada de los Grupos del Directorio Activo de Windows.

Los grandes WTF que he llegado a encontrar van desde logins de aplicaciones de terceros con privilegios elevados en un servidor compartido (sysadmin, serveradmin, etc.), logins de desarrollo o test en producción, montón de logins de personas que dejaron la empresa hace años, muchos logins que se podrían agrupar en un Grupo de Windows AD y mucho más, hay demasiada creatividad por ahí 🙂

 
The truco

Así que ahí vamos, este es el script, y debo decir que es uno de esos que utilizo muy a menudo para verificar los problemas de seguridad.

USE [master];
GO
IF OBJECT_ID('dbo.sqlg_securityAuditServerLogins') IS NULL EXECUTE sp_executesql N'CREATE PROCEDURE dbo.sqlg_securityAuditServerLogins AS RETURN';
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author:		Raul Gonzalez @SQLDoubleG
-- Create date: 28/06/2013
-- Description:	Returns Server Login information
--				This SP returns
--					- Server logins with server roles and permissions, logins included if it is a Windows Group, groups the login belong to and database users mapped to the login
--
-- Change log:	2014-03-04 RAG	- Removed the last 2 resultsets and included that info into que main one, columns [IncludedLogins] and [IncludedInWindowsGroups]
--								- Functionality to search a AD user when is included in a Windows Group
--				2014-05-15 RAG	- Included list of database users the login is mapped to 
--				2014-09-09 RAG	- Added column CREATE_LOGIN, which contain the script required to recreate the login and its server roles if any
--				2016-05-13 RAG	- Fixed bug when scripting server roles
--				2017-03-14 RAG	- Removed deprecated view syslogins
--								- Added state_desc to the permisssion list (DENY, REVOKE, GRANT, GRANT_WITH_GRANT_OPTION)
--								- Added server permisssions to the CREATE LOGIN statement
--
-- Copyright:   (C) 2017 Raul Gonzalez (@SQLDoubleG http://www.sqldoubleg.com)
--
--              THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--              ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--              TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--              PARTICULAR PURPOSE.
--
--              THE AUTHOR SHALL NOT BE LIABLE TO YOU OR ANY THIRD PARTY FOR ANY INDIRECT, 
--              SPECIAL, INCIDENTAL, PUNITIVE, COVER, OR CONSEQUENTIAL DAMAGES OF ANY KIND
--
--              YOU MAY ALTER THIS CODE FOR YOUR OWN *NON-COMMERCIAL* PURPOSES. YOU MAY
--              REPUBLISH ALTERED CODE AS LONG AS YOU INCLUDE THIS COPYRIGHT AND GIVE DUE CREDIT. 
--
-- =============================================
ALTER PROCEDURE [dbo].[sqlg_securityAuditServerLogins]
	@loginName	SYSNAME = NULL
AS
BEGIN
	
	SET NOCOUNT ON;

	DECLARE @sqlString		NVARCHAR(4000);

	DECLARE @groupName		SYSNAME
			, @numGroups	INT
			, @countGroups	INT = 1;

	DECLARE @version		NVARCHAR(128)	= CONVERT(NVARCHAR(128),SERVERPROPERTY('ProductVersion'))
	DECLARE @numericVersion	DECIMAL(3,1)	= CONVERT(DECIMAL(3,1), (LEFT( @version,  CHARINDEX('.', @version, 0) + 1 )) )

	CREATE TABLE #usersInGroups (
		accountName			SYSNAME
		, [Type]			SYSNAME
		, [privilege]		SYSNAME
		, [mappedLogin]		SYSNAME
		, [permissionPath]	SYSNAME);

	CREATE TABLE #allDbUsers(
		database_name		SYSNAME
		, database_username SYSNAME
		, sid				VARBINARY(85)
	);

	DECLARE @GO				CHAR(4) = CHAR(10) + 'GO' + CHAR(10);

	SELECT IDENTITY(INT, 1, 1) AS ID
			, name AS GroupName
		INTO #windowsGroups
		FROM sys.server_principals AS sp
		WHERE sp.type = 'G'
			AND name NOT LIKE 'NT SERVICE\%';

	SET @numGroups = @@ROWCOUNT;

	WHILE @countGroups <= @numGroups BEGIN
		SELECT @groupName = GroupName
			FROM #windowsGroups
			WHERE ID = @countGroups;

		SET @sqlString = 'EXEC XP_LOGININFO ' + QUOTENAME(@groupName) + ', [members]';

		INSERT INTO #usersInGroups
			EXECUTE sp_executesql @sqlString; 

		SET @countGroups += 1;

	END;

	-- Get all DB users
	EXECUTE sp_MSforeachdb N'
		USE [?]
		INSERT INTO #allDbUsers
			SELECT DB_NAME() AS database_name
					, name AS database_username
					, sid AS principal_sid
				FROM sys.database_principals
				WHERE is_fixed_role = 0
					AND type <> ''R''
					AND sid IS NOT NULL
					AND name NOT IN (''guest'')
	';

	-- All server logins with their server roles
	SELECT @@SERVERNAME AS ServerName
			, sp.principal_id 
			, sp.name AS LoginName
			, sp.type_desc AS LoginType
			, CASE WHEN sp.is_disabled = 1 THEN 'Yes' ELSE 'No' END AS IsDisabled
			, sp.default_database_name
			, STUFF((SELECT ', ' + sp2.name
						FROM sys.server_role_members AS srm
							LEFT JOIN sys.server_principals AS sp2
								ON sp2.principal_id = srm.role_principal_id
						WHERE srm.member_principal_id = sp.principal_id
						FOR XML PATH('')), 1, 2, '') AS ServerRoles
			, STUFF((SELECT ', ' + p.state_desc + ' ' + p.permission_name 
						FROM sys.server_permissions AS p
						WHERE p.grantee_principal_id = sp.principal_id 
						FOR XML PATH('')), 1, 2, '') AS ServerPermissions				
			, STUFF( (SELECT ', ' + QUOTENAME(mappedLogin) 
						FROM #usersInGroups AS u WHERE u.permissionPath = sp.name ORDER BY mappedLogin FOR XML PATH('')), 1, 2, '') AS IncludedLogins
			, STUFF( (SELECT ', ' + QUOTENAME(permissionPath) 
						FROM #usersInGroups AS u WHERE u.mappedLogin = sp.name ORDER BY permissionPath FOR XML PATH('')), 1, 2, '') AS IncludedInWindowsGroups
			, STUFF( (SELECT ', ' + QUOTENAME(database_name) + '.' + QUOTENAME(database_username) 
						FROM #allDbUsers AS u WHERE u.sid = sp.sid ORDER BY database_name FOR XML PATH('')), 1, 2, '') AS MappedToDBuser
			, STUFF((SELECT @GO + 
								CASE WHEN @numericVersion >= 11 THEN 'ALTER SERVER ROLE ' + QUOTENAME(sp2.name) + ' DROP MEMBER ' + QUOTENAME(sp.name)
									ELSE 'EXECUTE sys.sp_dropsrvrolemember ' + QUOTENAME(sp.name) + ', ' + QUOTENAME(sp2.name) 
								END
						FROM sys.server_role_members AS srm
							LEFT JOIN sys.server_principals AS sp2
								ON sp2.principal_id = srm.role_principal_id
						WHERE srm.member_principal_id = sp.principal_id
						FOR XML PATH('')), 1, 4, '') AS DROP_SERVER_ROLE
			,	'USE [master]' + @GO
				+ CASE 
					WHEN sp.type IN ('U', 'G') THEN 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' FROM WINDOWS WITH DEFAULT_DATABASE = ' + QUOTENAME(sp.default_database_name)
					ELSE 'CREATE LOGIN ' + QUOTENAME(sp.name) + ' WITH PASSWORD = ' + CONVERT(NVARCHAR(256), LOGINPROPERTY( sp.name, 'PasswordHash' ), 1) + 
						' HASHED, SID = ' + CONVERT(NVARCHAR(256), (sp.sid), 1) + ', DEFAULT_DATABASE = '  + QUOTENAME(sp.default_database_name) +
					', CHECK_POLICY = ' + CASE WHEN sqll.is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + 
					', CHECK_EXPIRATION = ' + CASE WHEN sqll.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + @GO
					+ (SELECT p.state_desc + ' ' + p.permission_name + ' TO ' + QUOTENAME(sp.name) + @GO AS [text()]
								FROM sys.server_permissions AS p
								WHERE p.grantee_principal_id = sp.principal_id 
								FOR XML PATH(''))
				END +
				CASE WHEN sp.is_disabled = 1 THEN 'ALTER LOGIN ' + QUOTENAME(sp.name) + ' DISABLE' + @GO ELSE '' END +
				ISNULL(STUFF( (SELECT CHAR(10) + 
											CASE WHEN @numericVersion >= 11 THEN 'ALTER SERVER ROLE '  + QUOTENAME(name) + ' ADD MEMBER ' + QUOTENAME(sp.name) 
												ELSE 'EXECUTE sp_addsrvrolemember ' + QUOTENAME(sp.name) + ', ' + QUOTENAME(name)
											END			
										+ CHAR(10) + 'GO'
									FROM sys.server_role_members AS rm
										INNER JOIN sys.server_principals AS r
											ON r.principal_id = rm.role_principal_id
												AND r.type = 'R'
									WHERE rm.member_principal_id = sp.principal_id
									FOR XML PATH('')), 1, 1, ''), '') 
			AS CREATE_LOGIN

		FROM sys.server_principals AS sp
			LEFT JOIN sys.sql_logins AS sqll
				ON sqll.sid = sp.sid
		WHERE sp.type <> 'R' -- R = Server role
			AND sp.name NOT LIKE '#%#'
			AND sp.name NOT LIKE 'NT %\%'
			AND ( sp.name LIKE ISNULL(@loginName, sp.name)
					-- Lookup for the given login within all windows groups in the server
					OR EXISTS ( SELECT 1 FROM #usersInGroups AS uig WHERE uig.permissionPath = sp.name AND uig.mappedLogin LIKE ISNULL(@loginName, uig.mappedLogin) ) )
		ORDER BY ServerRoles DESC
			, LoginType ASC
			, LoginName ASC;

	DROP TABLE #windowsGroups;
	DROP TABLE #usersInGroups;
END;
GO 

 
Conclusión

Si ejecutas este procedimiento almacenado puede que te lleves alguna sorpresa y veas cosas que te hagan decir WTF!, pero es mucho mejor si conocemos la situación antes de que sea demasiado tarde.

Y por último quiero agradecer de nuevo a Kennie Nybo ser el aanfitrión este mes y a vosotros por leer este post.

Como siempre, cualquier pregunta o comentario, no dudéis en usar los comentarios.

 

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *