Seguridad – Permisos en SQL Server (Relevamiento)

Seguridad – Permisos en SQL Server (Relevamiento):
Puntos para el relevar y permitir establecer y documentar permisos en servidores de bases de datos MS SQL Server (version 2005).

• Verificar modo de autenticación y si la cuenta sa está habilitada con contraseña fuerte.
• Especificaciones de Seguridad vigentes:
  
  • Relevar SERVER ROLE, SQL LOGIN (incluyendo políticas/expiración), WINDOWS GROUP, WINDOWS LOGIN – Identificar los creados por el organismo.
  
  
  • Relevar DATABASE ROLE, APPLICATION ROLE, SQL USER, WINDOWS GROUP, WINDOWS USER, etc. – diferenciar los creados por el organismo.
  
  

• Identificar bases de datos, sus objetos y esquemas.
• Definición de Permisos.
• Reporte de Auditoria.

---

1. Modo de Autenticación al servidor

1. Windows Authentication [ ]
2. SQL Server & Windows Authentication (Mixed Mode) [ ]
   
   • Cuenta sa Habilitada [ ] strong password [ ]
   
   

2. Entidades de Seguridad (Security Principals)

2.1 Capa del Servidor

Se incluyen SQL Logins.

select principal_id, name, type_desc, is_disabled, default_database_name
from sys.server_principals
order by type_desc;

2.2. Capa de Base de Datos

select principal_id, name, type_desc
from sys.database_principals
order by type_desc, name;

2.3. SQL Logins: Politicas de Password / Expiración

Listado de logins habilitados con especificación de Password Policy / Expiration

Select principal_id, name, type_desc, is_policy_checked, is_expiration_checked
from sys.sql_logins
order by name;

3. Base de Datos

3.1 Listado de bases de datos de cada instancia

select database_id, name, state_desc
from sys.databases

3.2 Usuarios y Roles

IF EXISTS ( SELECT *

FROM tempdb.dbo.sysobjects

WHERE id =

OBJECT_ID(N’[tempdb].[dbo].[SQL_DB_REP]‘) )

DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ;

GO


CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]

(

[Server] [varchar](100) NOT NULL,

[DB_Name] [varchar](70) NOT NULL,

[User_Name] [nvarchar](90) NULL,

[Group_Name] [varchar](100) NULL,

[Account_Type] [varchar](22) NULL,

[Login_Name] [varchar](80) NULL,

[Def_DB] [varchar](100) NULL

)

ON [PRIMARY]


INSERT INTO [tempdb].[dbo].[SQL_DB_REP]

Exec sp_MSForEachDB ‘SELECT CONVERT(varchar(100),

SERVERPROPERTY(”Servername”)) AS Server,

”?” as DB_Name,

usu.name u_name

,CASE

WHEN (usg.uid is null) then ”public”

ELSE usg.name

END as Group_Name

,CASE

WHEN usu.isntuser=1 then ”Windows Domain Account”

WHEN usu.isntgroup = 1 then ”Windows Group”

WHEN usu.issqluser = 1 then ”SQL Account”

WHEN usu.issqlrole = 1 then ”SQL Role”

END as Account_Type

,lo.loginname

,lo.dbname as Def_DB


FROM

[?]..sysusers usu LEFT OUTER JOIN

([?]..sysmembers mem INNER JOIN [?]..sysusers usg ON

mem.groupuid = usg.uid) ON usu.uid = mem.memberuid

LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =

lo.sid


WHERE

(usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =

1) and

(usg.issqlrole = 1 or usg.uid is null)’


SELECT [Server],

[DB_Name],

[User_Name],

[Group_Name],

[Account_Type],

[Login_Name],

[Def_DB]

FROM [tempdb].[dbo].[SQL_DB_REP]

USE [tempdb]

GO

DROP TABLE [dbo].[SQL_DB_REP]

GO

3.3. Esquemas

3.3.1 Listado de todos los esquemas

select catalog_name, schema_name, schema_owner

from information_schema.schemata

3.3.2 Listado de esquemas y tablas por base de datos

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + ‘.’ + QUOTENAME(name)

AS SchemaTable

FROM sys.tables

order by SCHEMA_NAME(schema_id), name

3.4 Tablas y vistas

select *

from information_schema.tables

order by table_type, table_name

4. Permisos

4.1. Permisos sobre el servidor

Select

permission_name,

PER.class_desc permission_class,

state_desc state,

PRI1.name grantee,

PRI2.name grantor

from

sys.server_permissions PER,

sys.server_principals PRI1,

sys.server_principals PRI2

where

PER.grantee_principal_id = PRI1.principal_id

and PER.grantor_principal_id = PRI2.principal_id

order by permission_name

4.2. Permisos en la base de datos

select

class_desc,

OBJ.type_desc tipo_objeto,

OBJ.name objeto,

PRI1.name grantee,

PRI2.name grantor,

permission_name,

state_desc

from sys.database_permissions PER

left join sys.all_objects OBJ on OBJ.object_id = major_id

left join sys.database_principals PRI1 on PER.grantee_principal_id = PRI1.principal_id

left join sys.database_principals PRI2 on PER.grantor_principal_id = PRI2.principal_id

order by PRI1.name desc

5. Reporte de Seguridad para Auditoria

5.1 Datos a obtener

1. Listado de usuarios con permisos ‘sa’.
2. Listado de cuentas con acceso como local administrator.
3. Listado de logins usados
   
   • NT Login
   • SQL Login
   • NT Group Login
   
   
4. Listado de quienes pertenecen a los grupos NT (si es que se usan)
5. Verificar si cuentas hay Windows que no existan y que estén definidas en SQL Server
6. Lista de usuarios que usan roles de base de datos incluyendo los roles en cuestión.

5.2 Requerimientos de ejecución

SELECT * FROM sys.configurations
where name = 'xp_cmdshell' or name = 'show advanced options';
GO

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

-- deshabilitar 'xp_cmdshell'
sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO

5.3 Script

USE master
GO
SET nocount ON

-- roles
CREATE TABLE #temp_srvrole
(ServerRole VARCHAR(128), Description VARCHAR(128))
INSERT INTO #temp_srvrole
EXEC sp_helpsrvrole

-- syslogins
CREATE TABLE #temp_memberrole
(ServerRole VARCHAR(128),
MemberName VARCHAR(265),
MemberSID VARCHAR(300))

DECLARE @ServerRole VARCHAR(128)

DECLARE srv_role CURSOR FAST_FORWARD FOR
SELECT ServerRole FROM #temp_srvrole
OPEN srv_role
FETCH NEXT FROM srv_role INTO @ServerRole

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_memberrole
EXEC sp_helpsrvrolemember @ServerRole
FETCH NEXT FROM srv_role INTO @ServerRole
END

CLOSE srv_role
DEALLOCATE srv_role

SELECT ServerRole, MemberName FROM #temp_memberrole

-- IF BUILTIN\Administrators is exist and sysadmin
IF EXISTS(SELECT *FROM #temp_memberrole
WHERE MemberName = 'BUILTIN\Administrators'
AND ServerRole = 'sysadmin' )
BEGIN
CREATE TABLE #temp_localadmin (output VARCHAR(8000))
INSERT INTO #temp_localadmin
EXEC xp_cmdshell 'net localgroup administrators'

SELECT output AS local_administrator
FROM #temp_localadmin
WHERE output LIKE '%\%'
DROP TABLE #temp_localadmin
END

DROP TABLE #temp_srvrole
DROP TABLE #temp_memberrole

-- Logins individuales
SELECT name, 'Individual NT Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 1
UNION
SELECT name, 'Individual SQL Login' LoginType
FROM syslogins
WHERE isntgroup = 0 AND isntname = 0
UNION ALL
-- Logins de grupos
SELECT name,'NT Group Login' LoginType
FROM syslogins
WHERE isntgroup = 1

-- lista de grupos
CREATE TABLE #temp_groupadmin
(output VARCHAR(8000))
CREATE TABLE #temp_groupadmin2
(groupName VARCHAR(256), groupMember VARCHAR(1000))
DECLARE @grpname VARCHAR(128)
DECLARE @sqlcmd VARCHAR(1000)

DECLARE grp_role CURSOR FAST_FORWARD FOR
SELECT REPLACE(name,'US\','')
FROM syslogins
WHERE isntgroup = 1 AND name LIKE 'US\%'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @grpname

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sqlcmd = 'net group "' + @grpname + '" /domain'
TRUNCATE TABLE #temp_groupadmin

PRINT @sqlcmd
INSERT INTO #temp_groupadmin
EXEC xp_cmdshell @sqlcmd

SET ROWCOUNT 8
DELETE FROM #temp_groupadmin

SET ROWCOUNT 0

INSERT INTO #temp_groupadmin2
SELECT @grpname, output FROM #temp_groupadmin
WHERE output NOT LIKE ('%The command completed successfully%')

FETCH NEXT FROM grp_role INTO @grpname
END

CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_groupadmin2

DROP TABLE #temp_groupadmin
DROP TABLE #temp_groupadmin2

PRINT 'EXEC sp_validatelogins '
PRINT '----------------------------------------------'
EXEC sp_validatelogins
PRINT ''

-- roles de base de datos para miembros especificos
CREATE TABLE #temp_rolemember
(DbRole VARCHAR(128),MemberName VARCHAR(128),MemberSID VARCHAR(1000))
CREATE TABLE #temp_rolemember_final
(DbName VARCHAR(100), DbRole VARCHAR(128),MemberName VARCHAR(128))

DECLARE @dbname VARCHAR(128)
DECLARE @sqlcmd2 VARCHAR(1000)

DECLARE grp_role CURSOR FOR
SELECT name FROM sysdatabases
WHERE name NOT IN ('tempdb')
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'

OPEN grp_role
FETCH NEXT FROM grp_role INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE #temp_rolemember
SET @sqlcmd2 = 'EXEC [' + @dbname + ']..sp_helprolemember'

PRINT @sqlcmd2
INSERT INTO #temp_rolemember
EXECUTE(@sqlcmd2)

INSERT INTO #temp_rolemember_final
SELECT @dbname AS DbName, DbRole, MemberName
FROM #temp_rolemember

FETCH NEXT FROM grp_role INTO @dbname
END

CLOSE grp_role
DEALLOCATE grp_role

SELECT * FROM #temp_rolemember_final

DROP TABLE #temp_rolemember
DROP TABLE #temp_rolemember_final

-- deshabilitar 'xp_cmdshell' – ver “Requerimientos”